--
--TBS_ID   : Tablespace ID
--TBS_TYPE : Memory tablespace type 
--           0 - System memory tablespace. A tablespace for storing metadata necessary for the operation of the database system
--           1 - System memory tablespace. A tablespace that can store data created by default when creating a database
--           2 - User memory tablespace. User-created memory tablespace
--           8 - Volatile tablespaces created by users
--TBS_NAME : Memory tablespace name
--MAX(M)   : Max amount of memory that can be used by memory tablespace
--           If MAXSIZE is not specified when creating a tablespace, MEM_MAX_DB_SIZE is displayed.        
--           If the tablespace attribute is AUTOEXTEND OFF, TOTAL is output.
--TOTAL(M) : Total number of pages allocated from the memory tablespace. It is the same as the size of the checkpoint image file creation.
--           It also includes a free page. Free pages are not loaded into memory when the Altibase server is started. So, it is difficult to use and judge physical memory as much as this value. 
--           This value is decreased only by executing DROP TABLESPACE. 
--ALLOC(M) : Amount of memory being used by the memory tablespace
--USED(M)  : The size of the memory storing data among ALLOCs
--USAGE(%) : ALLOC utilization rate compared to MAX
--STATE    : State of the tablespace 
--           1 - Offline, 2 - Online, 3 - Offline tablespace being backed up, 4 - Tablespace in an online state that are up-to-date, 
--           128 - Dropped tablespace, 1024-discarded tablespace, 1028-discarded tablespace being backed up
set linesize 1024
set colsize 20
SELECT ID TBS_ID
     , DECODE(TYPE, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') TBS_TYPE
     , NAME TBS_NAME
     , ROUND( DECODE(M.MAXSIZE, 140737488322560, D.MEM_MAX_DB_SIZE , 0 , T.TOTAL_PAGE_COUNT * T.PAGE_SIZE, M.MAXSIZE) /1024/1024, 2 ) 'MAX(M)'
     , ROUND( M.ALLOC_PAGE_COUNT * T.PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'
     , ROUND(NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT,T.TOTAL_PAGE_COUNT)*PAGE_SIZE/1024/1024, 2) 'ALLOC(M)'
     , NVL(MT.USED, 0) 'USED(M)'
     , ROUND(DECODE(MAXSIZE, 140737488322560, (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*T.PAGE_SIZE/ D.MEM_MAX_DB_SIZE ,0, (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT) / T.TOTAL_PAGE_COUNT , (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT) * T.PAGE_SIZE/ M.MAXSIZE) * 100 , 2) 'USAGE(%)'
     , DECODE(T.STATE,1,'OFFLINE',2,'ONLINE',5,'OFFLINE BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') STATE
     , DECODE(M.AUTOEXTEND_MODE,1,'ON','OFF') 'AUTOEXTEND'
  FROM V$DATABASE D
     , V$TABLESPACES T
     , (SELECT SPACE_ID
             , SPACE_NAME
             , ALLOC_PAGE_COUNT
             , FREE_PAGE_COUNT
             , DECODE(MAX_SIZE, 0, (SELECT VALUE1 FROM V$PROPERTY WHERE NAME = 'VOLATILE_MAX_DB_SIZE'), MAX_SIZE) AS MAXSIZE
             , AUTOEXTEND_MODE
          FROM V$VOL_TABLESPACES
         UNION ALL
        SELECT SPACE_ID
             , SPACE_NAME
             , ALLOC_PAGE_COUNT
             , FREE_PAGE_COUNT
             , MAXSIZE
             , AUTOEXTEND_MODE
          FROM V$MEM_TABLESPACES ) M LEFT OUTER JOIN(SELECT TABLESPACE_ID, ROUND(SUM((FIXED_USED_MEM + VAR_USED_MEM))/(1024*1024),3) USED
          FROM V$MEMTBL_INFO
         GROUP BY TABLESPACE_ID ) MT ON M.SPACE_ID = MT.TABLESPACE_ID
 WHERE T.ID = M.SPACE_ID;