Table counts

[sujosh]

Is there a way to get all the tables counts as a report on my database without doing select count(*) for each tables individually?

Thanks

 

[SantaMufasa]

Sujosh,

Yes, you can gather statistics for each table, in each schema, in your database. You can gather statistics by table, by schema, or for the entire database. As I mentioned to you in a thread yesterday, the syntax for gathering statistics one table at a time is:

analyze table <table_name> compute statistics;

To gather statistics for all tables in a schema, you can say:

exec dbms_utility.ANALYZE_SCHEMA(<schema_name>,'COMPUTE');

To gather statistics for all tables in all schemas on an Oracle 9i database, you can say:

exec dbms_stats.gather_database_stats(cascade=>true);

All of the above presume you are at the SQL*PLUS (SQL>) prompt.

You can also gather statistics by "sampling" a percentage of the rows in the tables. Anything over a 50 percent sampling does a full table scan, same as COMPUTE.

Once you have gathered statistics, you can issue commands such as:

SELECT table_name, avg_row_len, num_rows from USER_TABLES;

Let us know is this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:27 (21Jul04) UTC (aka "GMT" and "Zulu"), 11:27 (21Jul04) Mountain Time)

 

[sujosh]

Hi Mufasa,


When I tried the following code for my schema

"exec dbms_utility.ANALYZE_SCHEMA(abc'COMPUTE');" I got the following error saying abc must be declared.

Could you please tell me how and where I should declare the schema?

Thanks

 

[SantaMufasa]

Sujosh,

Sorry, in your case, the command should read:

exec dbms_utility.ANALYZE_SCHEMA('ABC','COMPUTE')

The schema must be within singe quotes, the schema name must be in upper case, and there must be a comma between your schema declaration and your statistics-gathering method.

Let us know if those revisions yield success.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:15 (21Jul04) UTC (aka "GMT" and "Zulu"), 13:15 (21Jul04) Mountain Time)

 

[sujosh]

Thanks Mufasa! I tried with double quotes but I did not try with single quotes. I kicked it off 15 - 20 min ago and It is still running.. Does this typically take some time?

Thanks

 

[SantaMufasa]

Sujosh,

It certainly can take time. If you use the 'COMPUTE' specification, it does a full table scan on any tables that are involved. If you want precise counts, that's what you do; if you can tolerate minor inaccuracies, then you may wish to consider using:

exec dbms_utility.analyze_schema('<SCHEMA_NAME>','ESTIMATE',NULL,'10',NULL)

...which uses a 10% sampling. That should speed things along. I would also recommend that if you want to update your statistics on a regular basis, that you schedule the re-gathering for a weekend evening when no one will be affected by your statistics gathering.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:07 (21Jul04) UTC (aka "GMT" and "Zulu"), 14:07 (21Jul04) Mountain Time)

 

[sujosh]

Mufasa, thank You very much!!