SELECT /*+ OPT_PARAM ('_COMPLEX_VIEW_MERGING', 'FALSE') OPT_PARAM('_PUSH_JOIN_UNION_VIEW', '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'

)

'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

( 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

)

( 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

)

));