CODEWITHSUNDEEP
oracle-databasedatabase-administration
Michal Hruška
Michal Hruška

Reputation: 464

How to identify all global temporary tables

I need to identify which tables in my schema are Global Temporary Tables. Following script returns names of all my tables, but I am not able to identify which of these are GTTs and which are not.

SELECT OBJECT_NAME
FROM ALL_OBJECTS 
WHERE OBJECT_TYPE IN ('TABLE')
AND OWNER='owner_name';

Thank you!

Upvotes: 10

Views: 24738

Answers (1)

Praveen
Praveen

Reputation: 9345

You can use ALL_TABLES

select table_name
from all_tables
where TEMPORARY = 'Y'
AND OWNER='owner_name';

Temporary column indicates whether the table is temporary (Y) or not (N)

Upvotes: 14

Related Questions