-----------------------------------------------------------
-- List all TEMP tables from instance
--  (sorted by owner, dbname, table)
--
-- V.Shulzhenko   DBA_Tools
-----------------------------------------------------------
set isolation to dirty read;
select   tn.tabname[ 1 , 18 ] temp_table
        ,tn.dbsname[ 1 , 18 ] db_name
        ,tn.owner[ 1 , 8 ]  owner
        ,s.name[ 1 , 18 ]   dbspace
--        ,ti_partnum partnum
--        ,ti_flags
        ,substr(bitval(ti_flags, 32 ), 1 , 1 )  sys
        ,substr(bitval(ti_flags, 64 ), 1 , 1 ) log
        ,substr(bitval(ti_flags, 128 ), 1 , 2 ) srt
from systabnames tn,systabinfo ti,sysdbspaces s
where   tn.partnum = ti.ti_partnum 
        and s.dbsnum=partdbsnum(ti_partnum)         
--      and trunc( mod(ti_flags,128) / 64,0) = 1
        and (bitval(ti_flags, 32 )= 1  or bitval(ti_flags, 64 )= 1  or bitval(ti_flags, 128 )= 1 )
order by  3 , 2 , 1 , 4 
--------------------------------------------------------------
--      flags partition_header
--
--          1 Page Level Locking
--          2 Row Level Locking
--         32 System created Temp Table
--         64 User created Temp Table (logged dbspace - V.Shulzhenko)
--        128 Sort File
--        256 Contains Varchar Data Type
--        512 Contains BLOBSpace BLOBS
--       1024 Contains TBLSpace BLOBS
--       2048 Contains either Varchars,BLOBS or Rows > PAGESIZE-
--       4096 Contains optical Sub-System BLOBS
--       8192 Permanent System created Table ( undroppable )
--       16384  Special Function Temp Tables, no Bitmap Maintenanc

-----------------------------------------------------------
-- (DBACCESS only) - Create all kinds temp tables into current session
--
--      IDS 7.1+
--
-- V.Shulzhenko  DBA Tools  02-2002
-----------------------------------------------------------
select * from items,customer
into temp temp_select_nolog with no log;
----------------------------
select * from items
into temp temp_select_log ;
----------------------------
create temp table temp_create_log
(a1 char,  a2 dec( 5 , 2 ) );
----------------------------
create temp table temp_create_nolog
(a1 char,  a2 dec( 5 , 2 ) )
with no log;
----------------------------
-- sort tempfile
select * from items,customer 
order by  4 ;
----------------------------

User-created Temp tables :
  partnum  tabname                  rowsize
  2000e3   temp_create_nolog          5 
  300b78   temp_create_log             5 
  300b75   temp_select_log            671 
  2000e0   temp_select_nolog         679