Home » SQL & PL/SQL » SQL & PL/SQL » Query table names from dba_tables (Oracle Database 12c 12.1.0.2.0 - 64bit Production )
Query table names from dba_tables [message #658890] Thu, 29 December 2016 11:18 Go to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Hello everyone,

I have a requirement to list out all the available tables in a schema. All tables implies just good old plain tables which exludes any temporary tables/IOTs etc.
Now I tried the below query:
SELECT *
 FROM dba_tables c
  WHERE owner          = '<schema_name>'
   AND TEMPORARY       != 'Y'
   AND duration        IS NULL
   AND tablespace_name IS NOT NULL
   AND IOT_type        IS NULL;

But, there seems to be still discrepancies present. So, before I proceed further, I was just wondering if someone can help me with a better query to achieve the results. To reiterate, I just need names of normal tables available in my schema.

Thanks
Re: Query table names from dba_tables [message #658892 is a reply to message #658890] Thu, 29 December 2016 11:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
You also need to exclude materialized view underlying tables, materialized view log tables, domain index tables and possibly other auxiliary tables.

SY.
Re: Query table names from dba_tables [message #658893 is a reply to message #658890] Thu, 29 December 2016 11:53 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
What about clustered tables? Check CLUSTER_NAME IS NOT NULL.
I think you may not be excluding your IOT mapping tables and overflow segments either.
Re: Query table names from dba_tables [message #658894 is a reply to message #658893] Thu, 29 December 2016 12:06 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
@John/@Solomon Yakobson:: Thanks for the suggestion..I checked with the conditions and it seems there are no records with CLUSTER_NAME is not null. Also, for excluding the conditions Solomon has suggested,will try to search for the filters.
Thanks
Re: Query table names from dba_tables [message #658895 is a reply to message #658893] Thu, 29 December 2016 12:13 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Try the following
SELECT A.Owner || '.' || A.Object_name Table_name
    FROM Dba_tables B, Dba_objects A
   WHERE     A.Owner = '<schema>'
         AND A.Object_type = 'TABLE'
         AND A.Owner = B.Owner
         AND A.Object_name = B.Table_name
         AND B.Temporary = 'N'
         AND B.Iot_type IS NULL
         AND B.Cluster_name IS NULL
         AND NOT EXISTS
                 (SELECT NULL
                    FROM Dba_mviews C
                   WHERE A.Owner = C.Owner AND A.Object_name = C.Mview_name)
ORDER BY Object_name;

[Updated on: Thu, 29 December 2016 13:26]

Report message to a moderator

Re: Query table names from dba_tables [message #658898 is a reply to message #658895] Thu, 29 December 2016 13:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
This will still return nested tables, materialized view logs and domain index tables. DBA_TABLES.SECONDARY = 'N' will take care of domain index tables and DBA_TABLES.NESTED = 'NO' will take care of nested tables. Adding NOT EXSITS on DBA_MVIEW_LOGS should take case of materialized view logs.

SY.

[Updated on: Thu, 29 December 2016 13:15]

Report message to a moderator

Re: Query table names from dba_tables [message #658899 is a reply to message #658898] Thu, 29 December 2016 13:16 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Great...thanks everyone..it did work fine Smile
Re: Query table names from dba_tables [message #658900 is a reply to message #658899] Thu, 29 December 2016 13:26 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
So the final query is and it must be run in an account with access to the DBA views. If the account doesn't have access then change all the "DBA_" to "ALL_"

  SELECT A.Owner || '.' || A.Object_name Table_name
    FROM Dba_tables B, Dba_objects A
   WHERE     A.Owner = '<schema>'
         AND A.Object_type = 'TABLE'
         AND A.Owner = B.Owner
         AND A.Object_name = B.Table_name
         AND B.Temporary = 'N'
         AND B.Iot_type IS NULL
         AND B.Cluster_name IS NULL
         AND B.Secondary = 'N'
         AND B.Nested = 'NO'
         AND NOT EXISTS
                 (SELECT NULL
                    FROM Dba_snapshot_logs C
                   WHERE A.Owner = C.Log_owner AND A.Object_name = C.Log_table)
         AND NOT EXISTS
                 (SELECT NULL
                    FROM Dba_mviews C
                   WHERE A.Owner = C.Owner AND A.Object_name = C.Mview_name)
ORDER BY Object_name;

[Updated on: Thu, 29 December 2016 13:28]

Report message to a moderator

Previous Topic: query next value
Next Topic: Calculate percentage based on month
Goto Forum:
  


Current Time: Thu Feb 22 07:33:27 CST 2018

Total time taken to generate the page: 0.03003 seconds