How to identify all global temporary tables

oracle database-administration
Michal Hruška picture Michal Hruška · Dec 6, 2016 · Viewed 15.2k times · Source

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!

Answer

Praveen picture Praveen · Dec 6, 2016

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)

Related questions

Find out the history of SQL queries

An update SQL query was executed on the server, which caused many problems later. How can I get the list of update queries executed in last 2 months, so that I can trace the exact problematic SQL query?

Read More
How can I kill all sessions connecting to my oracle database?

I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator. I don't want to just lock the database and let the users quit gracefully. How would I script …

Read More
How do I resolve this "ORA-01109: database not open" error?

I'm trying to create my own database using SQLPlus. So first I log into it as admin: sqlplus sys/sys_password as sysdba And then I try to create a new user, called sqlzoo : CREATE USER sqlzoo IDENTIFIED BY sqlzoo …

Read More