<div>
If you want 100% accurate count you'll have to either gather statistics with a full compute (which will likely take a long time) then look at dba_tables &nbsp;this will essentially do count(*) for each table, as well as gather the other statistic values.<br />
<br />
or you'll have to count the tables yourself, which might also take a long time; but you won't have to do the other statistics or indexes or partition level stats.<br />
<br />
there's no other method for counting beside counting.<br />
<br />
You could attempt using estimates by average row size and number of blocks but that's not going to be reliable.<br />
<br />
<br />
Note, if you do a count on a unique index of columns constrained to be not null, that will likely be faster than counting the table itself (the optimizer should do this for you though)<br />
so, it might not be as bad as you think.<br />
<br />
Also, a lot of that 6TB is probably indexes anyway, so it's not like you'll have to actually read all of it. &nbsp;This doesn't mean it will done in a few seconds, but it's not necessarily a multi-hour/multi-day operation.
</div>


<div>
It could be time to make the case that having production data in a test (non-production) environment is an unnecessary risk -- as non-production environments typically skimp on the penetration controls and monitoring. &nbsp;IOW matching row counts may not be worth the bother.<br />
<br />
Instead, I advocate for a test environment to hold enough data to test all of the business rules and conditions. &nbsp;PII must of course be replaced -- tools and scripts exist. <br />
<br />
All of the performance plans, metrics, load replication, and other metadata can be moved from production -- giving you a test bed with test data, but prod performance simulation.
</div>


<div>
6 tera<br />
i assume you didn't prevent any new inserts on the production database ?<br />
the test database will probably not get any new inserts ... after the process is finished<br />
<br />
a count on a static test system and a count on an active &nbsp;production system will likely be different
</div>


<div>
yes Geert the count will be different. We are trying to find out how different. The production system is used heavily and there is no down time. So inserts, updates, deletes are occurring.<br />
<br />
The purpose of the test environment is to rebuild the production environment in a virtual environment. That is why there is production data in the test environment. We are close to building the production environment and other developers feel it is imperative that we know how different the two environments are. &nbsp;We are at a disadvantage because in both environments doing a select count takes more than 24 hours on most of the tables We were just hoping that there was an easier way to gather this information.
</div>


<div>
I'm still confused. &nbsp;Wouldn't your ETL or your backup set show success or failure?
</div>


<div>
are you doing Physical to Virtual ?
</div>


<div>
&gt;&gt;&gt; yes Geert the count will be different. We are trying to find out how different.<br />
<br />
<br />
Since you're trying to chase a moving target, that sounds like you're looking for an approximation anyway, so you can probably gather statistics and use those to get a relative difference. &nbsp;How long that takes will depend on how big your sample sizes are.
</div>


<div>
The production database is currently in a physical environment. &nbsp;The test environment is in a virtual environment with plans to move the production to the virtual environment. There is a lot going on in the current production environment. It is a RAC database with 2 nodes. There are scripts running at various times that are loading data etc. There is a application front end that has things I am not even aware of going on. Backups are running successfully.
</div>


<div>
how would I use &quot;dbms_stats.gather_table_s<wbr />tats to gather statistics on all the tables in the database
</div>


<div>
ok awesome. what if I want to exclude certain schemas
</div>


<div>
Not directly related to the question asked, so no points. &nbsp;Just a caution about visualization and Oracle.<br />
<br />
If your virtual environment is VMWare please read:<br />
Support Position for Oracle Products Running on VMWare Virtualized Environments (Doc ID 249212.1)<br />
<br />
In a nutshell:<br />
You will only be supported by Oracle if it is a known bug or the problem can be reproduced on a physical server.<br />
<br />
Personal experience:<br />
I once had a 'bug' that was due to one row in one table. &nbsp;Any time I selected just that specific row, I would get an ORA-3113. &nbsp;It had to do with how the row crossed database blocks. &nbsp;There was no way to reproduce the problem in any environment than production.
</div>


<div>
thanks. I'll do what works best
</div>


<div>
<div class="content wysiwyg-content">
I have a test environment and a production environment. I have production data in my test environment. I want to get a count from both databases and identify the count difference if any between the environments. A script was initially used to do a count against dba_tables. However, soon we discovered that the count isn't accurate until after the table (s) have been analyzed. Is there another method out there besides doing a count on the table themselves? I have tried looking at using statistics. However, it is unclear how to use it to get the information I need. Does anyone know what I can do to get this information?
</div>
</div>


I have a test environment and a production environment. I have production data in my test environment. I want to get a count from both databases and identify the count difference if any between the environments. A script was initially used to do a count against dba_tables. However, soon we discovered that the count isn't accurate until after the table (s) have been analyzed. Is there another method out there besides doing a count on the table themselves? I have tried looking at using statistics. However, it is unclear how to use it to get the information I need. Does anyone know what I can do to get this information?

how do I get data counts for tables in a database that has 6 terabytes of data

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

Linux is a UNIX-like open source operating system with hundreds of distinct distributions, including: Fedora, openSUSE, Ubuntu, Debian, Slackware, Gentoo, CentOS, and Arch Linux. Linux is generally associated with web and database servers, but has become popular in many niche industries and applications.