Home » SQL & PL/SQL » SQL & PL/SQL » Table count with rows and no rows (Oracle 10.2.0.2.0 , windows)
Table count with rows and no rows [message #615437] |
Wed, 04 June 2014 00:37 |
|
Jack14
Messages: 497 Registered: December 2011 Location: INDIA
|
Senior Member |
|
|
Hi,
There are 2000 to 3000 tables in a schema. Some tables having rows and some tables having
no rows .I want to find out and segregate how many tables have rows and how many tables
have no rows along with table name.
Is there a query to find out, if so could you share the query here?
Thanks,
Jack
|
|
|
Re: Table count with rows and no rows [message #615443 is a reply to message #615437] |
Wed, 04 June 2014 01:21 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If your statistics are up to date you can use USER_TABLES.NUM_ROWS.
For a safer result you can use the following query:
select table_name,
to_number(extractvalue(
dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
'/ROWSET/ROW/CNT')) rows_in_table
from user_tables
where -- a real table
( tablespace_name is not null
or partitioned='YES'
or nvl(iot_type,'NOT_IOT')='IOT' )
-- not an iot overflow
and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
-- not a mview log
and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
order by 1
/
which gives on ScOTT schema:
SCOTT> select table_name,
2 to_number(extractvalue(
3 dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
4 '/ROWSET/ROW/CNT')) rows_in_table
5 from user_tables
6 where -- a real table
7 ( tablespace_name is not null
8 or partitioned='YES'
9 or nvl(iot_type,'NOT_IOT')='IOT' )
10 -- not an iot overflow
11 and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
12 -- not a mview log
13 and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
14 order by 1
15 /
TABLE_NAME ROWS_IN_TABLE
------------------------------ -------------
BONUS 0
DEPT 4
EMP 14
SALGRADE 5
or if you just want the count of tables:
SCOTT> with
2 data as (
3 select table_name,
4 to_number(extractvalue(
5 dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
6 '/ROWSET/ROW/CNT')) rows_in_table
7 from user_tables
8 where -- a real table
9 ( tablespace_name is not null
10 or partitioned='YES'
11 or nvl(iot_type,'NOT_IOT')='IOT' )
12 -- not an iot overflow
13 and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
14 -- not a mview log
15 and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
16 )
17 select decode(rows_in_table, 0, 'EMPTY', 'SOME ROWS') what,
18 count(*) nb_tables
19 from data
20 group by decode(rows_in_table, 0, 'EMPTY', 'SOME ROWS')
21 /
WHAT NB_TABLES
--------- ----------
SOME ROWS 3
EMPTY 1
|
|
|
|
|
|
Re: Table count with rows and no rows [message #615457 is a reply to message #615443] |
Wed, 04 June 2014 04:10 |
|
Jack14
Messages: 497 Registered: December 2011 Location: INDIA
|
Senior Member |
|
|
Michel,
I get error when i try to run your query.
SQL>
SQL> select table_name,
2 to_number(extractvalue(
3 dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
4 '/ROWSET/ROW/CNT')) rows_in_table
5 from user_tables
6 where -- a real table
7 ( tablespace_name is not null
8 or partitioned='YES'
9 or nvl(iot_type,'NOT_IOT')='IOT' )
10 -- not an iot overflow
11 and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
12 -- not a mview log
13 and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
14 order by 1
15 /
dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
*
ERROR at line 3:
ORA-19202: Error occurred in XML processing
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
Could you help me to fix it ?
Regards,
Jack
|
|
|
Re: Table count with rows and no rows [message #615458 is a reply to message #615457] |
Wed, 04 June 2014 04:16 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Post the result of:
select table_name from user_tables
where ( translate(table_name,' ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_$#',' ') is not null
or translate(substr(table_name,1,1),' ABCDEFGHIJKLMNOPQRSTUVWXYZ',' ') is not null )
and ( tablespace_name is not null
or partitioned='YES'
or nvl(iot_type,'NOT_IOT')='IOT' )
-- not an iot overflow
and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
-- not a mview log
and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
order by 1
/
[Edit: Add restrictions on table selection]
[Updated on: Wed, 04 June 2014 04:18] Report message to a moderator
|
|
|
|
Re: Table count with rows and no rows [message #615461 is a reply to message #615459] |
Wed, 04 June 2014 04:25 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Try:
select table_name,
to_number(extractvalue(
dbms_xmlgen.getXMLtype ('select count(*) cnt from "'||table_name||'"'),
'/ROWSET/ROW/CNT')) rows_in_table
from user_tables
where -- a real table
( tablespace_name is not null
or partitioned='YES'
or nvl(iot_type,'NOT_IOT')='IOT' )
-- not an iot overflow
and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')
-- not a mview log
and (user, table_name) not in (select log_owner, log_table from user_mview_logs)
order by 1
/
And avoid the usage of lower case characters.
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 22:46:37 CDT 2024
|