3

How to query Teradata database to see which volatile tables are at moment in it? I have tried with

    SELECT * FROM dbc.tables
WHERE DatabaseName = 'MyDB'

It did not list any of the present volatile tables.

topchef
  • 19,091
  • 9
  • 63
  • 102
Adam
  • 2,347
  • 12
  • 55
  • 81

1 Answers1

4

Volatile Tables are only visible within the current session.

HELP VOLATILE TABLE; returns the list of VTs for the current session, but there's no easy way to get that info for VTs in other sessions. You might try to query DBQL or AccessLog for CREATE VOLATILE TABLE statements submitted by currently logged on sessions, when this logging is enabled.

Dieter

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • This command gives the following output: "Syntax error, expected something like a 'TABLE' keyword between the VOLATILE keyword and the SESSION keyword" – Adam Jul 10 '13 at 14:59
  • 1
    Sorry, of course this was wrong, it's HELP VOLATILE TABLE, i mmodified my answer. – dnoeth Jul 10 '13 at 18:35
  • Thanks! It works for the default database. How can I query any database to see the list of current volatile tables? – Adam Jul 11 '13 at 07:29
  • 1
    Such list doesn't exist, a session doesn't know about any VT in another session and there's no system table keeping track of VTs (only for materialized Global Temporar Tables there's dbc.AllTempTablesV). And HELP VOLATILE TABKE doesn't work with your default database, it works with your user :-) Btw, why do you need it? – dnoeth Jul 11 '13 at 20:22
  • I need it to see all existing volatile tables :) – Adam Jul 12 '13 at 07:07
  • 1
    Are you the DBA? You could do the apporach i described: Enable Access Logging for CREATE/DROP TABLE, and then query the AccessLog for 'CREATE VOLATILE TABLE' (but no DROP TABLE within the same session) and add a WHERE SessionNo IN (SELECT SessionNo FROM dbc.SessionInfoV) – dnoeth Jul 12 '13 at 13:50