All Forums Database
Moutusi 38 posts Joined 03/13
27 Aug 2014
Identifying GTT or volatile tables created during a session

How can I identify what are the GTT or volatile tables created during a session?

Thanks, Moutusi
Raja_KT 1246 posts Joined 07/09
27 Aug 2014

For VT you can use
help volatile table
Also you can read the lines:

To obtain a list of all global temporary tables in the system, query the CommitOpt column of the DBC.TablesV view. For example:

SELECT * FROM DBC.TablesV WHERE CommitOpt IN ('D', 'P');

The CommitOpt column shows the value D or P for a global temporary table. (P is for ON COMMIT PRESERVE ROWS and D is ON COMMIT DELETE ROWS. N indicates the object is not a temporary table.)

To obtain a list of all global temporary tables you own, query the restricted (DBC.TablesVX) view.

You can also query the TransLog column to determine if there is transaction logging for a global temporary table. For more information, see Utilities.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
27 Aug 2014

Materialized GTT within your session are listed in dbc.AllTempTablesVX

Dieter

You must sign in to leave a comment.