| Query table names from dba_tables [message #658890] |
Thu, 29 December 2016 11:18  |
 |
abhi_orcl
Messages: 40
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 #658895 is a reply to message #658893] |
Thu, 29 December 2016 12:13  |
Bill B
Messages: 1971
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 |
Solomon Yakobson
Messages: 3273
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 #658900 is a reply to message #658899] |
Thu, 29 December 2016 13:26 |
Bill B
Messages: 1971
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
