0

I want to create a query in ORACLE- SQL Developer that returns tables specified in the query and their count/ number of rows in real time.

Ideally the output would look like;

| Schema.Table | Count |
|  abc.123     |  1000 |    
|  def.345     |  1223 |      etc.

So far I have a very basic query that uses dba_tables;

SELECT OWNER ||'.'|| table_name as "Schema.Tablename", 
       num_rows as "Number of Rows"  
  FROM dba_tables 
WHERE
   table_name = '123' and OWNER = 'abc'
or table_name = '345' and OWNER = 'def'

However, I want to be able to have the query count in real time so I don't want to usee dba_tables or num_rows.

Does anybody have any tips or advice for this task?

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Mark X
  • 1
  • [Faster alternative in Oracle to SELECT COUNT(*) FROM sometable](http://stackoverflow.com/questions/1840538/faster-alternative-in-oracle-to-select-count-from-sometable) – Alex K. Jan 31 '17 at 16:17

1 Answers1

0

If you are listing the tables explicitly, then just write the query using the tables:

SELECT 'abc.123' as table_name, count(*) as cnt
FROM abc.123
UNION ALL
SELECT 'def.345' as table_name, count(*) as cnt
FROM def.345;

This is much simpler than a PL/SQL loop and dynamic SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Is there a more efficient way to do this? I've tried it but it took way too long for the amount of tables I need to specify and the server froze – Mark X Jan 31 '17 at 19:24
  • @MarkX . . . It takes time to read all the tables. That's why there are approximation methods that are much faster. – Gordon Linoff Feb 01 '17 at 02:50