[askdba] Re: Audit Qry
- From: Chandra Pabba <chandra.pabba@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Mon, 8 Nov 2004 11:43:54 +0530
Hi Pradhan,
There are two options for this task:
1) Analyze the schema, which will populate the NUM_ROWS column of
user/dba_tables with the actual row count (as it exists at the time of
analyze).
This exercise will have an impact on the performance which the analyze
operation is in progress like high cpu utilization........so on. You
may also like to check to see if the application which uses this
database uses RBO or CBO. If RBO, you may like to delete the stats
with 'ANALYZE TABLE <tab_name> Delete Statistics;' after gathering the
required row counts.
OR
2) Execute the following script:
set head off
set pages 0
spool rowcount.sql
Select 'Select '''||table_name||''', count(*) from '||table_name||';'
from dba_tables where owner='<owner_name>';
spool off
and then
spool rowcounts.log
@rowcount.sql
spool off
The log file rowcounts.log will have table name along with row counts.
HTH
On Mon, 8 Nov 2004 11:32:04 +0530, Pradhan <pradhan@xxxxxxxxxxxxxxxx> wrote:
>
> Dear Folks,
>
> Is there any way that i can get the list of all tables in my schema along
> with the number of rows in each table - I checked out user_tables /
> dba_tables both shows a column called NUM_ROWS but both are null in all the
> tables.
>
> Can any one help me on this - required for a report to management.
>
> Regards,
>
> Pradhan
>
>
--
Regards,
Chandra Pabba
OCP, SCSA
Other related posts:
