SELECT /*+ OPT_PARAM ('_COMPLEX_VIEW_MERGING', 'FALSE') OPT_PARAM('_PUSH_JOIN_UNION_VIEW', 'FALSE') 
  OPT_PARAM ('_OPTIMIZER_NEW_JOIN_CARD_COMPUTATION', 'FALSE') */
  NULL INST, NULL OWNER, NULL SEGMENT_NAME, NULL PART_NAME, NULL SEGMENT_TYPE, NULL POOL,
  NULL BLKSIZE, NULL POOL_GB, NULL SEG_POOL_MB, NULL "POOL_%", 
  NULL SEG_DISK_MB, NULL "SEG_CACHED_%", NULL "DIRTY_%", NULL "CR_%"
FROM DUAL WHERE 1 = 0
UNION ALL (
SELECT 
  NULL INST, NULL OWNER, NULL SEGMENT_NAME, NULL PART_NAME, NULL SEGMENT_TYPE, NULL POOL,
  NULL BLKSIZE, NULL POOL_GB, NULL SEG_POOL_MB, NULL "POOL_%", 
  NULL SEG_DISK_MB, NULL "SEG_CACHED_%", NULL "DIRTY_%", NULL "CR_%"
FROM DUAL WHERE 1 = 0
) UNION ALL ( SELECT * FROM (
WITH BASIS_INFO AS
( SELECT /*+ MATERIALIZE */
    DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_NUMBER) INSTANCE_NUMBER,
    OWNER,
    SEGMENT_NAME,
    BUFFER_POOL,
    SEGMENT_TYPE,
    BLOCK_SIZE,
    NUM_RECORDS
  FROM
  ( SELECT
      -1 INSTANCE_NUMBER,
      '%' OWNER,
      '%' SEGMENT_NAME,
      '%' BUFFER_POOL,
      '%' SEGMENT_TYPE,
      -1 BLOCK_SIZE,
      30 NUM_RECORDS
    FROM
      DUAL
  )
),
SEGMENTS AS
( SELECT /*+ MATERIALIZE */
    DS.OWNER,
    DS.SEGMENT_NAME,
    DS.SEGMENT_TYPE,
    DS.PARTITION_NAME,
    DS.BUFFER_POOL,
    DS.TABLESPACE_NAME,
    DS.BYTES,
    DT.BLOCK_SIZE
  FROM
    BASIS_INFO SI,
    DBA_SEGMENTS DS,
    DBA_TABLESPACES DT
  WHERE
    DS.OWNER LIKE SI.OWNER AND
    DS.SEGMENT_NAME LIKE SI.SEGMENT_NAME AND
    DS.TABLESPACE_NAME = DT.TABLESPACE_NAME 
  UNION ALL
  ( SELECT
      'SYS' OWNER,
      'Undo data' SEGMENT_NAME,
      'UNDO SEGMENT' SEGMENT_TYPE,
      NULL PARTITION_NAME,
      'DEFAULT' BUFFER_POOL,
      MIN(P.VALUE) TABLESPACE_NAME,
      SUM(BYTES) BYTES,
      MIN(DT.BLOCK_SIZE) BLOCK_SIZE
    FROM
      BASIS_INFO BI,
      DBA_SEGMENTS S,
      GV$PARAMETER P,
      DBA_TABLESPACES DT
    WHERE
      P.INST_ID = BI.INSTANCE_NUMBER AND
      P.NAME = 'undo_tablespace' AND
      DT.TABLESPACE_NAME = P.VALUE AND
      S.TABLESPACE_NAME = P.VALUE AND
      'Undo data' LIKE BI.SEGMENT_NAME
  )
),
OBJECTS AS
( SELECT /*+ MATERIALIZE */
    OWNER,
    OBJECT_NAME,
    SUBOBJECT_NAME,
    DATA_OBJECT_ID,
    OBJECT_TYPE
  FROM
    DBA_OBJECTS
  UNION ALL
  ( SELECT
      'SYS' OWNER,
      'Undo data' OBJECT_NAME,
      NULL SUBOBJECT_NAME,
      4294967295 DATA_OBJECT_ID,
      'UNDO SEGMENT' OBJECT_TYPE
    FROM
      DUAL
  )
),
CACHES AS
( SELECT /*+ MATERIALIZE */
    SDC.COMPONENT CACHE_NAME,
    SDC.CURRENT_SIZE / 1024 / 1024 POOL_SIZE_MB,
    DECODE(SDC.COMPONENT, 
      'KEEP buffer cache', 'KEEP',
      'RECYCLE buffer cache', 'RECYCLE',
      'DEFAULT') BUFFER_POOL,
    DECODE(SDC.COMPONENT,
      'DEFAULT 2K buffer cache', 2048,
      'DEFAULT 4K buffer cache', 4096,
      'DEFAULT 8K buffer cache', 8192,
      'DEFAULT 16K buffer cache', 16384,
      'DEFAULT 32K buffer cache', 32768,
      B.VALUE) BLOCK_SIZE
  FROM
    BASIS_INFO BI,
    GV$SGA_DYNAMIC_COMPONENTS SDC,
    GV$PARAMETER B
  WHERE
    BI.INSTANCE_NUMBER = SDC.INST_ID AND
    BI.INSTANCE_NUMBER = B.INST_ID AND
    SDC.COMPONENT IN
    ( 'DEFAULT buffer cache',
      'KEEP buffer cache',
      'RECYCLE buffer cache',
      'DEFAULT 2K buffer cache',
      'DEFAULT 4K buffer cache',
      'DEFAULT 8K buffer cache',
      'DEFAULT 16K buffer cache',
      'DEFAULT 32K buffer cache'
    ) AND
    B.NAME = 'db_block_size' 
)
SELECT
  'Eva.' INST,
  'Time:' OWNER,
  TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss') SEGMENT_NAME,
  NULL PART_NAME,
  NULL SEGMENT_TYPE,
  NULL POOL,
  NULL BLKSIZE,
  NULL POOL_GB,
  NULL SEG_POOL_MB,
  NULL "POOL_%",
  NULL SEG_DISK_MB,
  NULL "SEG_CACHED_%",
  NULL "DIRTY_%",
  NULL "CR_%"
FROM 
  DUAL
UNION ALL
( SELECT
    NULL INST,
    NULL OWNER,
    NULL SEGMENT_NAME,
    NULL PART_NAME,
    NULL SEGMENT_TYPE,
    NULL POOL,
    NULL BLKSIZE,
    NULL POOL_GB,
    NULL SEG_POOL_MB,
    NULL "POOL_%",
    NULL SEG_DISK_MB,
    NULL "SEG_CACHED_%",
    NULL "DIRTY_%",
    NULL "CR_%"
  FROM
    DUAL
)
UNION ALL
( SELECT
    INST,
    OWNER,
    SEGMENT_NAME,
    PARTITION_NAME PART_NAME,
    SEGMENT_TYPE,
    POOL,
    BLKSIZE,
    POOL_GB,
    SEG_POOL_MB,
    "POOL_%",
    SEG_DISK_MB,
    "SEG_CACHED_%",
    "DIRTY_%",
    "CR_%"
  FROM
  ( SELECT 
      TO_CHAR(INST_ID, 990) INST,
      OWNER,
      SEGMENT_NAME,
      PARTITION_NAME,
      SEGMENT_TYPE,
      BUFFER_POOL POOL,
      TO_CHAR(BLOCK_SIZE, 999990) BLKSIZE,
      TO_CHAR(POOL_SIZE_MB / 1024, 990.99) POOL_GB,
      TO_CHAR(BLOCKS * BLOCK_SIZE / 1024 / 1024, 9999990.99) SEG_POOL_MB,
      TO_CHAR(DECODE(POOL_SIZE_MB, 0, 0, BLOCKS * BLOCK_SIZE / 1024 / 1024 / 
       POOL_SIZE_MB * 100), 990.99) "POOL_%",
       TO_CHAR(SEG_DISK_BYTE / 1024 / 1024, 9999990.99) SEG_DISK_MB,
      TO_CHAR(DECODE(SEG_DISK_BYTE, 0, 0, BLOCKS * BLOCK_SIZE / SEG_DISK_BYTE * 100), 99999990.99) "SEG_CACHED_%",
      TO_CHAR(DECODE(BLOCKS, 0, 0, DIRTY_BLOCKS / BLOCKS * 100), 990.99) "DIRTY_%",
      TO_CHAR(DECODE(BLOCKS, 0, 0, CR_BLOCKS / BLOCKS * 100), 990.99) "CR_%",
      NUM_RECORDS
    FROM 
    ( SELECT 
        BI.INSTANCE_NUMBER INST_ID,
        S.OWNER,
        S.SEGMENT_NAME,
        S.PARTITION_NAME,
        S.SEGMENT_TYPE,
        S.BYTES SEG_DISK_BYTE,
        COUNT(*) BLOCKS,
        SUM(DECODE(B.DIRTY, 'Y', 1, 0)) DIRTY_BLOCKS,
        SUM(DECODE(B.STATUS, 'cr', 1, 0)) CR_BLOCKS,
        C.POOL_SIZE_MB,
        C.BUFFER_POOL,
        C.BLOCK_SIZE,
        ROW_NUMBER() OVER (PARTITION BY O.DATA_OBJECT_ID 
          ORDER BY O.OBJECT_TYPE) CLUSTRN,
        BI.NUM_RECORDS
      FROM 
        SEGMENTS S,
        OBJECTS O, 
        GV$BH B,
        CACHES C,
        BASIS_INFO BI
      WHERE 
        BI.INSTANCE_NUMBER = B.INST_ID AND
        S.OWNER = O.OWNER AND
        S.SEGMENT_NAME = O.OBJECT_NAME AND
        NVL(S.PARTITION_NAME, ' ') = NVL(O.SUBOBJECT_NAME, ' ') AND
        O.DATA_OBJECT_ID = B.OBJD AND
        C.BUFFER_POOL LIKE BI.BUFFER_POOL AND
        (BI.BLOCK_SIZE = -1 OR C.BLOCK_SIZE = BI.BLOCK_SIZE) AND
        S.SEGMENT_TYPE LIKE BI.SEGMENT_TYPE AND
        C.BUFFER_POOL = S.BUFFER_POOL AND
        C.BLOCK_SIZE = S.BLOCK_SIZE AND
        C.POOL_SIZE_MB > 0
      GROUP BY
        BI.INSTANCE_NUMBER,
        S.OWNER,
        S.SEGMENT_NAME,
        S.PARTITION_NAME, 
        S.SEGMENT_TYPE,
        S.BYTES,
        C.POOL_SIZE_MB,
        C.BUFFER_POOL,
        C.BLOCK_SIZE,
        O.DATA_OBJECT_ID,
        O.OBJECT_TYPE,
        BI.NUM_RECORDS
    )
    WHERE
      CLUSTRN = 1 
    ORDER BY 
      BLOCKS DESC
    )
  WHERE
    ROWNUM <= NUM_RECORDS
)
));