Home » SQL & PL/SQL » SQL & PL/SQL » A better way to check every table for data?
A better way to check every table for data? [message #569588] Tue, 30 October 2012 01:55 Go to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:

SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)


Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:

SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables


but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.

Thanks,
Neil
Re: A better way to check every table for data? [message #569590 is a reply to message #569588] Tue, 30 October 2012 02:05 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you try like...
select table_name, case  when num_rows > 0 then table_name end from all_tables where  owner='TEST' 
Re: A better way to check every table for data? [message #569591 is a reply to message #569590] Tue, 30 October 2012 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not give the actual result just the one the last time the tables were analyzed and nothing if the tables were never analyzed.

Regards
Michel
Re: A better way to check every table for data? [message #569595 is a reply to message #569588] Tue, 30 October 2012 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select table_name,
  2         extractvalue(
  3           dbms_xmlgen.getXMLtype
  4             ('SELECT 1 val FROM dual WHERE EXISTS(SELECT ''x'' FROM  '||table_name||')'),
  5              '/ROWSET/ROW/VAL') rows_in_table
  6  from user_tables
  7  where ( tablespace_name is not null or partitioned='YES' )
  8    and nvl(iot_type,'NOT_IOT') != 'IOT_OVERFLOW'
  9  order by 1
 10  /
TABLE_NAME                     ROWS_IN_TABLE
------------------------------ -------------
BONUS
DEPT                           1
EMP                            1
SALGRADE                       1

4 rows selected.

Regards
Michel
Re: A better way to check every table for data? [message #569597 is a reply to message #569595] Tue, 30 October 2012 02:43 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks Micheal for correction.. Can you explain why you are using the blow condtion on where clause..
where ( tablespace_name is not null or partitioned='YES' )
     and nvl(iot_type,'NOT_IOT') != 'IOT_OVERFLOW' 
Re: A better way to check every table for data? [message #569598 is a reply to message #569597] Tue, 30 October 2012 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
IOT_OVERFLOW is not a real table but an overflow one and so you can't query it (Oracle uses it internally when you access columns of an IOT that are not in the main segment, see index_org_table_clause on CREATE TABLE).

The other test excludes some "virtual" tables like external tables, nested tables or temporary tables.

Actually the tests are not complete, the following one is closer (maybe some cases are missing, feel free to complete it):
where ( tablespace_name is not null or partitioned='YES' or nvl(iot_type,'NOT_IOT')='IOT' )
  and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING') 

Regards
Michel


[Updated on: Tue, 30 October 2012 03:41]

Report message to a moderator

Re: A better way to check every table for data? [message #569603 is a reply to message #569598] Tue, 30 October 2012 04:42 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks
Re: A better way to check every table for data? [message #569619 is a reply to message #569603] Tue, 30 October 2012 06:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Also, you might want to exclude:

a) recycle bin: table_name not like 'BIN$%'
b) materialized view container tables (join with user_objects)
c) materialized view log tables (join with user_objects)

SY.
Re: A better way to check every table for data? [message #569623 is a reply to message #569619] Tue, 30 October 2012 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
a) recycle bin objects are excluded from user_tables by its definition ("bitand(o.flags, 128) = 0")
b) I won't but will add it below
c) good point, forgot them
So:
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 container
  and (user, table_name) not in (select owner, container_name from user_mviews)
      -- not a mview log
  and (user, table_name) not in (select log_owner, log_table from user_mview_logs)


There are also RUPD$_% tables created with mview log when there is a primary key on the table but they are not referenced in any user accessible dictionary view, so maybe one might want to exclude them based on the name ("table_name not like 'RUPD$\_%' escape '\'").

Regards
Michel

Re: A better way to check every table for data? [message #569643 is a reply to message #569623] Tue, 30 October 2012 10:59 Go to previous messageGo to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
This is excellent. Thank you for your help.

How often does the subquery execute? I am under the impression that a correlated subquery executes many times for each row in the outer query? Is that the case here?
Re: A better way to check every table for data? [message #569700 is a reply to message #569643] Wed, 31 October 2012 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
It does, but that's once per table in this case, not an issue, and there's no better way to do it.
Re: A better way to check every table for data? [message #569707 is a reply to message #569700] Wed, 31 October 2012 08:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
cookiemonster wrote on Wed, 31 October 2012 08:10
It does, but that's once per table in this case, not an issue, and there's no better way to do it.


Well, there is no need for subquery:

select  table_name,
        extractvalue(
                     dbms_xmlgen.getXMLtype(
                                            'SELECT ''Y'' val FROM "'|| table_name || '" WHERE ROWNUM = 1'
                                           ),
                     '/ROWSET/ROW/VAL'
                    ) table_has_data
  from  user_tables
  where ( tablespace_name is not null or partitioned='YES' )
    and nvl(iot_type,'NOT_IOT') != 'IOT_OVERFLOW'
  order by 1
/


SY.
Re: A better way to check every table for data? [message #569719 is a reply to message #569707] Wed, 31 October 2012 12:07 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
Technically speaking you've still got a sub-query there. Michel's original actually had a sub-query within a sub-query which I failed to notice.
Re: A better way to check every table for data? [message #569731 is a reply to message #569719] Wed, 31 October 2012 13:20 Go to previous message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just use OP's query to show him how to do it, no discussion on this part was intended (note that I just coy and paste his query, I (almost always) use only lower case in my queries).
Anyway, you use one query or the other one the cost is the same, it is just a matter of how you read the query. I had this discussion with T. Kyte on AskTom. I prefer the syntax that shows that I search if something exists (and don't like the rownum here), he prefers Solomon's syntax as it directly shows where you search and don't see any reason to query dual to get information on something else. Just a matter of thinking.

Regards
Michel

[Updated on: Wed, 31 October 2012 13:52]

Report message to a moderator

Previous Topic: Extracting User who does not exist in column
Next Topic: ORA-00997: illegal use of LONG datatype
Goto Forum:
  


Current Time: Tue Sep 30 13:33:28 CDT 2014

Total time taken to generate the page: 0.05405 seconds