Search

OakieTags

more tags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Affiliations

Home

HOWTO: Count all Rows in all Tables – The XMLDB Way

Submitted by marco on Wed, 2011-07-27 10:59

Someone beat me to it in this good post: Oracle Tip: Counting ROWS for all tables in a Schema . So here a reminder for me where to find it.

#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">TABLE_NAME
#66cc66;">,      to_number#66cc66;">(extractvalue#66cc66;">(xmltype#66cc66;">(
       dbms_xmlgen#66cc66;">.getxml#66cc66;">(#ff0000;">'select count(*) c from '#66cc66;">||table_name#66cc66;">)#66cc66;">)
       #66cc66;">,#ff0000;">'/ROWSET/ROW/C'#66cc66;">)#66cc66;">) #993333; font-weight: bold;">COUNT
#993333; font-weight: bold;">FROM user_tables;

That said, I don’t like the use of DBMS_XMLGEN (performance reasons/compatibility), so also for future reference, I also still have to come up with a better one. The use of extractvalue has at least the advantage that this one should be backwards compatible to 9.2.0.3.