Link to home
Start Free TrialLog in
Avatar of pavan27
pavan27Flag for United States of America

asked on

how do i get table name and no of records

experts,

i have one small question. i have one schema  and it has lot of tables.

i need a sql statements which will give the output like this

table_name    no of records
xxxxxxx        7894566
yyyyyyyy      9852364

can any one provide me the query.
Avatar of Sean Stuber
Sean Stuber

if an estimate is good enough you can do this...


select table_name,num_rows from user_tables

or

select table_name,num_rows from dba_tables where owner = 'Your_schema_here'

if you need exact values you can't do it purely in sql
oops, actually, you can...


select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from user_tables
or

select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from dba_tables where owner = 'your_schema_here'
that's still not purely sql (dbms_xmlgen is a package)

but it doesn't require you to write extra pl/sql
Avatar of pavan27

ASKER

i think num_rows display no of records only if table is analyzed.
is above statement is correct?
what is this dbms_xmlgen.getxm function. what it  will do.
Correct.  You need updated statistics from an analyze to use num_rows.  If this is production, I would assume that you are doing this on a regular basis anyway.
yes, num_rows only applies if analyzed and even then it's just an estimate unless you forced full compute.  And then it's still just an estimate as the count is dated information.

yes, the xml queries are correct, you can verify by running them (putting your schema name in where indicated)

dbms_xmlgen.getxml will run the counting query passed to it and return it as an xml document.
using extractvalue allows us to parse the count out of the xml doc and return it as a number.

the fact that it is xml is not important,  the necessary part was having something that could run a dynamic query within the context of the query that finds all the table names.
if you have lots and lots of tables and/or they are very large the above query could take a long time.
Avatar of pavan27

ASKER

yes,

i need to run get no of records in production. here we have one problem. the schema which have lot of table are de commissioned. means account is locked and there are no operations from last 6 months.

is there any other way to get exact count of records?
the only way to get the exact count is to count them.

you can do it with the above query to the dba view if you can't log into the schema itself (query copied here)

select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from dba_tables where owner = 'your_schema_here'

you don't have to run "this" exact query, but if you want to get the counts, you'll have to have some thing similar that iterates through all of the tables and runs  "select count(*) from table_name"
on each of them.

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pavan27

ASKER

i am getting following error.please clarify me

SYSTEM> l
  1  select
  2  table_name,
  3  to_number(
  4  extractvalue(
  5  xmltype(
  6  sys.dbms_xmlgen.getxml('select count(*) c from '||table_name)
  7  ),'/ROWSET/ROW/C')) count
  8* from dba_tables where owner=upper('&owner')order by 1

sys.dbms_xmlgen.getxml('select count(*) c from '||table_name)
*
ERROR at line 6:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
you don't have access to read one or more of the tables.

use the "all_tables" version instead

and record which tables you can't read and see if someone else can read them for you

select table_name from dba_tables
where owner = upper('&owner')
minus
select table_name from all_tables
where owner = upper('&owner')
order by 1
Avatar of pavan27

ASKER

Experts,

finally i have one question.

to get table name and count of records by using below query will provide accurate results?

select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from all_tables where owner = 'your_schema_here'
yes, that's the query I posted earlier and it works

with the caveat that you will only be able to count the tables you have access to.