pavan27
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.
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.
oops, actually, you can...
select table_name,
to_number(extractvalue(xml type(dbms_ xmlgen.get xml('selec t count(*) X from '||table_name))
,'/ROWSET/ROW/X')) count
from user_tables
select table_name,
to_number(extractvalue(xml
,'/ROWSET/ROW/X')) count
from user_tables
or
select table_name,
to_number(extractvalue(xml type(dbms_ xmlgen.get xml('selec t count(*) X from '||table_name))
,'/ROWSET/ROW/X')) count
from dba_tables where owner = 'your_schema_here'
select table_name,
to_number(extractvalue(xml
,'/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
but it doesn't require you to write extra pl/sql
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.
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.
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.
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?
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(xml type(dbms_ xmlgen.get xml('selec t 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.
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(xml
,'/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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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('se lect 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('se lect 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
SYSTEM> l
1 select
2 table_name,
3 to_number(
4 extractvalue(
5 xmltype(
6 sys.dbms_xmlgen.getxml('se
7 ),'/ROWSET/ROW/C')) count
8* from dba_tables where owner=upper('&owner')order
sys.dbms_xmlgen.getxml('se
*
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
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
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(xml type(dbms_ xmlgen.get xml('selec t count(*) X from '||table_name))
,'/ROWSET/ROW/X')) count
from all_tables where owner = 'your_schema_here'
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(xml
,'/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.
with the caveat that you will only be able to count the tables you have access to.
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