Member Avatar for MARKAND911

I want to find out the names of temperory tables from oracle database?

  • 2 Contributors
  • 1 Reply
  • 91 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by babyDBA

Dani AI

Generated

A good starting point from — Oracle records global temporary table definitions in the data dictionary. Which dictionary view to query depends on scope: use USER_TABLES for objects you own, ALL_TABLES for objects you can access, and DBA_TABLES when you need to see everything (DBA privilege required). The dictionary rows include the schema/owner so you can tell which user created each temporary table.

Important behavioral notes that matter when working with these objects: Oracle global temporary tables are persistent definitions but their data is session- or transaction-private. Creation options control whether rows are cleared at each COMMIT or only at session end. To inspect the exact creation options (for example whether it was created ON COMMIT DELETE ROWS or PRESERVE ROWS) extract the table DDL (DBMS_METADATA.GET_DDL is commonly used) rather than relying on row counts in the dictionary, because the dictionary shows definitions, not session data. To see whether a temp table currently contains rows, query that table from the same session.

Troubleshooting tips: if you find no results, confirm you are connected to the intended schema and that you have the right catalog privileges. Watch for quoted identifiers and case sensitivity when looking up names. If the goal is to track temporary space or who is using temp segments, use the appropriate V$ and performance views (that requires monitoring privileges) rather than the dictionary tables of definitions.

Member Avatar for babyDBA

select *
from all_tables
where temporary='Y'

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.