> 
> Hi Ari, would you please give me the solution of the following:-
> 
> 	- How can we calculate the total number of rows in oracle database and in
> this 	  way get size of database?
> 
> Thanks
> 
> 
There are three ways that come to mind to find the number of rows in the
database:

1) Use the ANALYZE built-in package to estimate or compute statistics for
   all users, then issue

   SELECT SUM(NUM_ROWS) FROM ALL_TABLES;

2) Export the database with FULL=Y. The output of the export will display the
   number of records for each table. You will manually add the numbers.

3) SELECT COUNT(*) from all tables. Since there could be a large number of
   tables, you can write a script to dynamically create another script:

   set header off
   set feedback off
   spool count_it.sql
   SELECT 'SELECT COUNT(*) FROM '||owner||'.'||table_name||';'
   FROM ALL_TABLES;
   spool off

Now, you have a script, "count_it.sql" that contains multiple SELECT COUNT(*)
statements. Run count_it.sql and add up the totals.

The above three methods are good to find the number of records. Since there
is overhead, such as free space within a block, and since each table has
differing data and column types, the number of records does not always give
an indication of the size of the data. To do this, type:

SELECT COUNT(distinct substr(rowid,1,8)) FROM table_name;

This will give the number of physical database blocks that the table data
occupies. Usually a database block is 2k, 4k or 8k. You should be able to
get the block size by looking at your initSID.ora file or configSID.ora file.
Do the above script for all tables.


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 150+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page "../index.htm">Back to Ari Kaplan's Home Page