Menu
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!
835
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.
After creation, global temporary tables become visible to any user and any connection. They can be manually dropped with DROP TABLE command. Global temporary tables are automatically dropped when the session that create the table completes and there is no active references to that table.
The CREATETAB privilege to define a declared temporary table in the database that is defined AS WORKFILE, which is the database for declared temporary tables. The USE privilege to use the table spaces in the database that is defined as WORKFILE. All table privileges on the table and authority to drop the table.
Local temp tables are only accessible from their creation context, such as the connection. Global temp tables are accessible from other connection contexts. Both local and global temp tables reside in the tempdb database.
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)
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
