Monday, December 20, 2010

You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created




    SELECT t.tabname ,t.dbsname,t.owner,
    DECODE(d.is_logging,1,"Y","N") AS db_with_log,
    s.name AS dbspace,
    DBINFO("UTC_TO_DATETIME",ti_created) AS created,
    DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
    table_using_log,ti_npused AS num_usedpages,
    ti_nptotal AS num_pages
    FROM sysmaster:systabnames t, sysmaster:systabinfo i,
    sysmaster:sysdbspaces s, sysmaster:sysdatabases d
    WHERE t.partnum=ti_partnum AND
    d.name=t.dbsname AND
    s.dbsnum=TRUNC(t.partnum/1048576) AND
    hex(mod(ti_flags,256)/16) IN ( 6,2 )

No comments: