Back to Ari Kaplan's Home Page?Subject: Re: [Q] how to find how many row in each table in database? Newsgroups: comp.databases.oracle.server References: <33DDEE18.5C465511@hkstar.com> Organization: InterAccess, Co. - Chicagoland's Full Service Internet Provider Reply-To: akaplan@interaccess.com Distribution: Larry Leung (llarry@hkstar.com) wrote: : hello everyone, : : i just wonder is there a better way to find out how many row of each : table in : the database. It is because i am assigned a job to refresh our : development : database from the production one. : : by 'select count(*)' from each table seems time consuming. : : thanks in advance : larry Larry, What you should do is make a SQL script that creates another SQL script containing all tables. Try the following: SET HEAD OFF SET PAGESIZE 0 SET FEEDBACK OFF SPOOL SCRIPT.SQL SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||';' FROM ALL_TABLES SPOOL OFF At this point, there will be a script called "SCRIPT.SQL" that contains all "SELECT COUNT(*)" statements you will need. To find the results of this script, do the following: SET ECHO ON @SCRIPT.SQL -Ari Kaplan Independent Oracle DBA Consultant <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-> <-> For 90+ Oracle tips, visit my Web Page: <-> <-> <-> <-> www.arikaplan.com <-> <-> <-> <-> email: akaplan@interaccess.com <-> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-> I see you work at the Hong Kong Star. Did you know that the McDonalds at the Star Ferry Terminal is the second most visited (behind Moscow's)