Query to fetch table names from dba_tables in oracle

Question

I want to fetch table names from dba_tables using join with dba_tab_statistics.

select table_name 
from dba_tables a,
     dba_tab_statistics b 
where a.owner like 'Owner' and a.table_name not like '%TMP'
  and a.LAST_ANALYZED IS NOT NULL and a.table_name = b.table_name
  and b.stattype_locked IS NULL;

This query doesn't give distinct table names. What changes are required ?

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Nov 25, 2016 at 8:53

XING · Accepted Answer · 2016-11-25 08:55:08Z

1

Use either of them:

SELECT a.table_name
  FROM dba_tables a, 
        dba_tab_statistics b
 WHERE     a.owner LIKE 'Owner'
       AND a.table_name NOT LIKE '%TMP'
       AND a.LAST_ANALYZED IS NOT NULL
       AND a.table_name = b.table_name
       AND b.stattype_locked IS NULL
 group by a.table_name ;

or

SELECT DISTINCT a.table_name
  FROM dba_tables a, 
        dba_tab_statistics b
 WHERE     a.owner LIKE 'Owner'
       AND a.table_name NOT LIKE '%TMP'
       AND a.LAST_ANALYZED IS NOT NULL
       AND a.table_name = b.table_name
       AND b.stattype_locked IS NULL ;

answered Nov 25, 2016 at 8:55

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M Denis · Accepted Answer · 2016-11-25 14:54:07Z

0

Another solution :

SELECT table_name
  FROM dba_tables a
 WHERE owner LIKE 'Owner'
   AND table_name NOT LIKE '%TMP'
   AND LAST_ANALYZED IS NOT NULL
   AND exists (SELECT 0 
              FROM dba_tab_statistics b
              WHERE a.table_name = b.table_name
              AND b.stattype_locked IS NULL);

answered Nov 25, 2016 at 14:54

M Denis

1387 bronze badges

2 Comments

Rax Over a year ago

Thanks it worked. Only thing is taking more time i.e. 15-20 minutes

Rax Over a year ago

select a.table_name from dba_tab_statistics a, dba_tables b where a.owner like 'SCHEMA1' and a.table_name not like '%TMP' and a.table_name not in ('TABLE1', 'TABLE2', 'TABLE3') and a.stattype_locked IS NULL and b.temporary = 'N' group by a.table_name order by max(a.NUM_ROWS) desc;

Collectives™ on Stack Overflow

Query to fetch table names from dba_tables in oracle

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related