SELECT /*+ ALLOW_HINTS */

NULL OWNER, NULL TABLE_NAME, NULL B, NULL P, NULL L, NULL U, NULL COMP, NULL POS,

NULL COLS, NULL TOTAL_GB, NULL "TOTAL_%", NULL "CUM_%", NULL "PART.",
NULL TABLE_GB, NULL TAB_TABSPACE, NULL "IND.", NULL INDEX_GB,
NULL IND_TABSPACE, NULL LOBS, NULL LOB_GB FROM DUAL WHERE 1 = 0
UNION ALL (
SELECT /* SAP Note 2388483 v88 */
NULL OWNER, NULL TABLE_NAME, NULL B, NULL P, NULL L, NULL U, NULL COMP, NULL POS,

NULL COLS, NULL TOTAL_GB, NULL "TOTAL_%", NULL "CUM_%", NULL "PART.",
NULL TABLE_GB, NULL TAB_TABSPACE, NULL "IND.", NULL INDEX_GB,
NULL IND_TABSPACE, NULL LOBS, NULL LOB_GB FROM DUAL WHERE 1 = 0
) UNION ALL ( SELECT * FROM (
WITH BASIS_INFO AS
( SELECT /*+ MATERIALIZE OPT_PARAM('OPTIMIZER_DYNAMIC_SAMPLING', 6)
OPT_PARAM('_OPTIMIZER_DISTINCT_AGG_TRANSFORM', 'FALSE') */
OWNER,
TABLESPACE_NAME,
TABLE_NAME,
NUM_RECORDS,
MIN_TOTAL_SIZE_MB,
ONLY_BASIS_TABLES,
ONLY_PART_ENGINE_TABLES,
DECODE(MIN_COLUMN_THRESHOLD, -1, 256, MIN_COLUMN_THRESHOLD)
MIN_COLUMN_THRESHOLD
FROM
( SELECT
'SAP%' OWNER,
'%' TABLESPACE_NAME,
'%' TABLE_NAME,
100 NUM_RECORDS,
-1 MIN_TOTAL_SIZE_MB,
' ' ONLY_BASIS_TABLES,
' ' ONLY_PART_ENGINE_TABLES,
230 MIN_COLUMN_THRESHOLD /* minimum number of columns for
compression exception */
/* minimum number of columns for compression exception */
FROM
DUAL
)
),
SEGMENTS AS
( SELECT /*+ MATERIALIZE */
S.OWNER,
S.SEGMENT_NAME,
S.PARTITION_NAME,
S.SEGMENT_TYPE,
S.TABLESPACE_NAME,
S.BYTES
FROM
BASIS_INFO BI,
DBA_SEGMENTS S
WHERE
S.TABLESPACE_NAME LIKE BI.TABLESPACE_NAME AND
S.OWNER LIKE BI.OWNER
),
TOTAL_SEGMENT_SIZE AS
( SELECT /*+ MATERIALIZE */
 SUM(BYTES) DB_NET_SIZE_BYTE
FROM
SEGMENTS
),
TABLES AS
( SELECT /*+ MATERIALIZE */
T.OWNER,
T.TABLE_NAME,
T.TABLESPACE_NAME,
MAX(DECODE(NVL(T.COMPRESSION, TP.COMPRESSION), 'ENABLED',
DECODE(NVL(T.COMPRESS_FOR, TP.COMPRESS_FOR),
'OLTP', 'Y', 'ADVANCED', 'Y', 'BASIC', 'B', 'H'), 'N')) COMPRESSION
FROM
BASIS_INFO BI,
DBA_TABLES T,
DBA_TAB_PARTITIONS TP
WHERE
T.OWNER = TP.TABLE_OWNER (+) AND
T.TABLE_NAME = TP.TABLE_NAME (+) AND
T.OWNER LIKE BI.OWNER
GROUP BY
T.OWNER,
T.TABLE_NAME,
T.TABLESPACE_NAME
),
INDEXES AS
( SELECT /*+ MATERIALIZE */
I.OWNER,
I.TABLE_NAME,
I.INDEX_NAME,
I.TABLESPACE_NAME,
MAX(DECODE(I.UNIQUENESS, 'UNIQUE', 'X', ' ')) UNIQUENESS,
MAX(DECODE(NVL(I.COMPRESSION, IP.COMPRESSION),
'ENABLED', 'Y', 'ADVANCED LOW', 'L', 'ADVANCED HIGH', 'H', 'N')) COMPRESSION
FROM
DBA_INDEXES I,
DBA_IND_PARTITIONS IP
WHERE
I.OWNER = IP.INDEX_OWNER (+) AND
I.INDEX_NAME = IP.INDEX_NAME (+)
GROUP BY
I.OWNER,
I.TABLE_NAME,
I.INDEX_NAME,
I.TABLESPACE_NAME
),
COLUMNS AS
( SELECT /*+ MATERIALIZE */
T.OWNER,
T.TABLE_NAME,
COUNT(*) NUM_COLUMNS,
DECODE(SUM(DECODE(TC.DATA_TYPE, 'LONG', 1, 'LONG RAW', 1, 0)), 0, ' ', 'X')
LONG_COLUMN,
DECODE(SUM(DECODE(TC.COLUMN_NAME, 'RELID', 1, 'SRTF2', 1, 'CLUSTR', 1,
'CLUSTD', 1, 0)), 4, 'X', ' ') INDX_COLUMNS
FROM
TABLES T,
DBA_TAB_COLUMNS TC
WHERE
 T.OWNER = TC.OWNER AND
T.TABLE_NAME = TC.TABLE_NAME
GROUP BY
T.OWNER,
T.TABLE_NAME
),
LOBS AS
( SELECT /*+ MATERIALIZE */
OWNER,
TABLE_NAME,
SEGMENT_NAME,
TABLESPACE_NAME,
INDEX_NAME,
COLUMN_NAME,
DECODE(COMPRESSION, 'NO', 'N', 'NONE', 'N', 'LOW', '1', 'MEDIUM', '2', 'HIGH',
'3', 'Y') COMPRESSION,
DECODE(SECUREFILE, 'YES', 'S', 'B') LOB_TYPE
FROM
DBA_LOBS
),
TABLE_SEGMENT_MAPPING AS
( SELECT /*+ MATERIALIZE */
T.OWNER,
T.TABLE_NAME,
MIN(NVL(T.TABLESPACE_NAME, S.TABLESPACE_NAME)) TABLESPACE_NAME,
'TABLE' SEGMENT_TYPE,
1 SEGMENTS,
SUM(DECODE(S.PARTITION_NAME, NULL, 0, 1)) PARTITIONS,
SUM(S.BYTES) BYTES,
MAX(T.COMPRESSION) COMPRESSION,
NULL ADDITIONAL_INFO
FROM
SEGMENTS S,
TABLES T
WHERE
S.OWNER = T.OWNER AND
S.SEGMENT_NAME = T.TABLE_NAME AND
S.SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
GROUP BY
T.OWNER,
T.TABLE_NAME
UNION ALL
( SELECT
I.OWNER OWNER,
I.TABLE_NAME TABLE_NAME,
MIN(S.TABLESPACE_NAME) TABLESPACE_NAME,
'INDEX' SEGMENT_TYPE,
COUNT(DISTINCT(I.INDEX_NAME)) SEGMENTS,
SUM(DECODE(S.PARTITION_NAME, NULL, 0, 1)) PARTITIONS,
SUM(S.BYTES) BYTES,
MAX(I.COMPRESSION) COMPRESSION,
MAX(I.UNIQUENESS) ADDITIONAL_INFO
FROM
SEGMENTS S,
INDEXES I
WHERE
S.OWNER = I.OWNER AND
S.SEGMENT_NAME = I.INDEX_NAME AND
S.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 GROUP BY
I.OWNER,
I.TABLE_NAME
)
UNION ALL
( SELECT
L.OWNER OWNER,
L.TABLE_NAME TABLE_NAME,
MIN(S.TABLESPACE_NAME) TABLESPACE_NAME,
'LOB' SEGMENT_TYPE,
COUNT(DISTINCT(L.COLUMN_NAME)) SEGMENTS,
SUM(DECODE(S.PARTITION_NAME, NULL, 0, 1)) PARTITIONS,
SUM(S.BYTES) BYTES,
DECODE(MAX(L.COMPRESSION), '1', 'L', '2', 'M', '3', 'H', MAX(L.COMPRESSION))
COMPRESSION,
MAX(L.LOB_TYPE) ADDITIONAL_INFO
FROM
SEGMENTS S,
LOBS L
WHERE
S.OWNER = L.OWNER AND
S.SEGMENT_NAME IN ( L.SEGMENT_NAME, L.INDEX_NAME ) AND
S.SEGMENT_TYPE IN ('LOBSEGMENT', 'LOBINDEX', 'LOB PARTITION')
GROUP BY
L.OWNER,
L.TABLE_NAME
)
),
PARTITIONING_ENGINE_TABLES AS
( SELECT /*+ MATERIALIZE */
OWNER,
TABLE_NAME
FROM
DBA_TABLES
WHERE
TABLE_NAME IN
( 'BDCP', 'BDCP2', 'BDCPS', 'CDCLS', 'CDHDR', 'CKIS', 'CKMI1', 'COEP',
'DBERCHZ1', 'DBERCHZ2', 'DBERCHZ3', 'DBERDL', 'DBERDLB', 'DBERDTAX',
'DFKKKO', 'DFKKOP', 'DFKKOPK', 'DFKKOPW', 'DFKKREP06', 'EBAN',
'EDI40', 'EDIDC', 'EDIDS', 'EKBE', 'EKES', 'EKET', 'EKKN', 'EKKO',
'EKPA', 'EKPO', 'FKKDEFREV', 'GLPCA', 'IMRG', 'LIPS', 'RESB',
'SWWLOGHIST', 'SWWWIHEAD', 'SWW_CONT', 'VBAP', 'VBRP'
)
),
BASIS_TABLES AS
( SELECT /*+ MATERIALIZE */
OWNER,
TABLE_NAME
FROM
DBA_TABLES
WHERE
TABLE_NAME IN
( '/AIF/ALERT_IDX', '/AIF/CDATA', '/AIF/CDATAF', '/AIF/MMSG_VARS',
'/AIF/PERS_QMSG', '/AIF/REP_MSG_CNT', '/AIF/T_MMSG_IDX', '/AIF/PERS_XML',
'/IWBEP/SU_ERRLOG', '/IWFND/L_MET_COL', '/IWFND/L_MET_DAT',
'/IWFND/SU_ERRLOG,', 'IWFND/SU_STATUS', '/SAPAPO/LISMAP',
'/SAPAPO/LISLOG', '/SAPTRX/APPTALOG', '/SAPTRX/AOTREF', '/SCMTMS/D_MSGREG',
'/SCMTMS/RES_POS',
'/SDF/CSSIZING', '/SDF/HDBSIZING', '/SDF/HDBTABSIZES', '/SDF/INDX',
'/SDF/RSSIZING',
'/SDF/IFR_TRFCQIN', '/SDF/IFR_TRFCQOU', '/SDF/MON', '/SDF/SMON_CLUST',
'/SDF/SMON_WPINFO',
'/SDF/ZQLMD', '/SSF/PTAB', '/TRP/RES_TRKLOG', '/TXINTF/TRACE',
'APQD', 'ARDB_STAT0', 'ARDB_STAT1', 'ARDB_STAT2', 'ARFCSSTATE', 'ARFCSDATA',
'ARFCRSTATE', 'BBPCONT', 'BBP_TRANSXSTRING',
'BALC', 'BALDAT', 'BALHDR', 'BALHDRP', 'BAL_INDX', 'BALM', 'BALMP', 'BC_MSG',
'BC_MSG_LOG', 'BC_MSG_VERSION', 'BC_MSG_AUDIT', 'BC_SLD_CHANGELOG',
'BDCP', 'BDCPS', 'BDCP2', 'BCST_CAM', 'BCST_SR', 'BTCEVTHISTORY', 'CCMLOG',
'CCMLOGD', 'CCMSESSION', 'CCMOBJLST', 'CCMOBJKEYS',
'COIX_DATA40', 'COUNT_CA_DETAIL', 'COUNT_CA_ERR_MSG', 'COUNT_CA_HEADER',
'COUNT_CA_ITEM', 'COUNT_CA_PRODUCT',
'CRM_ICI_TRACES', 'CROSS', 'DB2DB02TBSIZE', 'DB2DB02TSSIZE', 'D010TAB',
'DBMSGORA', 'DBTABLOG', 'DBTABPRT', 'DDLOG', 'DDPRS',
'DFKKDOUBTD_W', 'DFKKDOUBTD_RET_W', 'DRFD_OBJ_REP_STA', 'DRFD_SERVOUT_LOG',
'DRVLOG_HEADER', 'DRVLOG_ITEM', 'DRVLOG_FIELDVAL', 'DRVLOG_TRAIL',
'DSVASRESULTSGEN', 'DSVASRESULTSSEL', 'DSVASRESULTSCHK',
'DSVASRESULTSATTR', 'DSVASSESSADMIN', 'DYNPSOURCE', 'DYNPLOAD', 'ECLOG_CALL',
'ECLOG_DATA',
'ECLOG_EXEC', 'ECLOG_EXT', 'ECLOG_HEAD', 'ECLOG_RESTAB', 'ECLOG_SCNT',
'ECLOG_SCR', 'ECLOG_SEL', 'ECLOG_XDAT',
'EDI30C', 'EDI40', 'EDID4', 'EDIDC', 'EDIDOC', 'EDIDS', 'ENHLOG',
'FSBP_CNS_IMAGE', 'GRACSODREPDATA', 'GRACSODREPINDEX', 'GRACSODREPSTATUS',
'GVD_BGPROCESS', 'GVD_BUFF_POOL_ST', 'GVD_LATCH_MISSES',
'GVD_ENQUEUE_STAT', 'GVD_FILESTAT', 'GVD_INSTANCE', 'GVD_PGASTAT',
'GVD_PGA_TARGET_A', 'GVD_PGA_TARGET_H',
'GVD_SERVERLIST', 'GVD_SESSION_EVT', 'GVD_SESSION_WAIT', 'GVD_SESSION',
'GVD_PROCESS', 'GVD_PX_SESSION',
'GVD_WPTOTALINFO', 'GVD_ROWCACHE', 'GVD_SEGMENT_STAT', 'GVD_SESSTAT',
'GVD_SGACURRRESIZ', 'GVD_SGADYNFREE',
'GVD_SGA', 'GVD_SGARESIZEOPS', 'GVD_SESS_IO', 'GVD_SGASTAT',
'GVD_SGADYNCOMP', 'GVD_SEGSTAT', 'GVD_SPPARAMETER',
'GVD_SHAR_P_ADV', 'GVD_SQLAREA', 'GVD_SQL', 'GVD_SQLTEXT',
'GVD_SQL_WA_ACTIV','GVD_SQL_WA_HISTO',
'GVD_SQL_WORKAREA', 'GVD_SYSSTAT', 'GVD_SYSTEM_EVENT', 'GVD_DATABASE',
'GVD_CURR_BLKSRV', 'GVD_DATAGUARD_ST',
'GVD_DATAFILE', 'GVD_LOCKED_OBJEC', 'GVD_LOCK_ACTIVTY', 'GVD_DB_CACHE_ADV',
'GVD_LATCHHOLDER', 'GVD_LATCHCHILDS',
'GVD_LATCH', 'GVD_LATCHNAME', 'GVD_LATCH_PARENT', 'GVD_LIBRARYCACHE',
'GVD_LOCK', 'GVD_MANGD_STANBY', 'GVD_OBJECT_DEPEN',
'GVD_PARAMETER', 'GVD_LOGFILE', 'GVD_PARAMETER2', 'GVD_TEMPFILE',
'GVD_UNDOSTAT', 'GVD_WAITSTAT',
'ICFRECORDER', 'IDOCREL', 'INDX', 'JBDCPHDR2', 'JBDCPPOS2', 'JOB_LOG',
'MDMFDBID', 'MDMFDBEVENT', 'MDMFDBPR',
'MERGED_TRACES_FOR_COCKPIT', 'NRIVSHADOW', 'OBJECT_HISTORY', 'ODQDATA',
'ODQDATA_F', 'ORA_SNAPSHOT', 'ORA_SQLC_DATA', 'ORA_SQLC_HEAD',
'POC_D_EVTQ', 'POC_D_EVTQ_PREBO', 'POC_D_EVTQ_CNTXT', 'SWFRCNTXML',
'SWFREVTPOQ',
'PPFTMETHRU', 'PPFTTRIGG', 'QRFC_I_EXE_STATE', 'QRFC_I_QIN',
'QRFC_I_QIN_LOCK', 'QRFC_I_SDATA', 'QRFC_I_QIN_TOP', 'QRFC_I_UNIT',
'QRFC_I_UNIT_LOCK',
'QRFCLOG', 'QRFCTRACE', 'REPOSRC', 'REPOLOAD', 'RSBATCHCTRL',
'RSBATCHCTRL_PAR', 'RSBATCHDATA', 'RSBATCHHEADER', 'RSBATCHPROT', 'RSBATCHSTACK',
'RSBERRORLOG',
'RSBKDATA', 'RSBKDATAINFO', 'RSBKDATAPAKID', 'RSBKDATAPAKSEL',
'RSBERRORCROSS', 'RSBKSELECT', 'RSBMLOGPAR', 'RSBMLOGPAR_DTP',
'RSBMNODES', 'RSBMONMESS', 'RSBMONMESS_DTP', 'RSBMREQ_DTP', 'RSCRTDONE',
'RSDDSTATAGGR', 'RSDDSTATAGGRDEF',
'RSDDSTATCOND', 'RSDDSTATDTP', 'RSDDSTATDELE', 'RSDDSTATDM',
'RSDDSTATEVDATA', 'RSDDSTATHEADER', 'RSDDSTATINFO',
'RSDDSTATLOGGING', 'RSDELDONE', 'RSDRDLOGHEADER', 'RSDRDLOGPOSITION',
'RSECHIE_CL', 'RSECSESSION_CL','RSECTXT_CL', 'RSECUSERAUTH_CL','RSECVAL_CL',
'RSECLOG', 'RSERRORHEAD', 'RSERRORLOG', 'RSHIEDONE', 'RSICPROT', 'RSIXWWW',
'RSLDTDONE', 'RSMONFACT', 'RSMONICTAB',
'RSMONIPTAB', 'RSMONMESS', 'RSMONRQTAB', 'RSODSACTUPDTYPE',
'RSOTLOGOHISTORY', 'RSPCINSTANCE', 'RSPCINSTANCET',
'RSPCLOGCHAIN', 'RSPCPROCESSLOG', 'RSPCCOMMANDLOG', 'RSR_CACHE_DATA_B',
'RSR_CACHE_DATA_C', 'RSR_CACHE_DBS_BL', 'RSR_CACHE_FFB', 'RSR_CACHE_QUERY',
'RSR_CACHE_STATS', 'RSR_CACHE_VARSHB', 'RSREQDONE', 'RSRWBINDEX',
'RSRWBSTORE', 'RSSELDONE', 'RSSTATMANREQMDEL',
'RSSTATMANREQMAP', 'RSTCPDONE', 'RSTT_CALLSTACK', 'RSUICDONE',
'RSBTERRORLOG', 'RSBTERRORCHANGED',
'RSRULEDONE', 'RSWR_DATA', 'RSZWBOOKMARK', 'RSZWVIEW', 'RSZWITEM', 'RSZWOBJ',

'RSZCALC', 'RSZCEL', 'RSZCOMPIC', 'RSZELTATTR', 'RSZELTDIR', 'RSZELTPRIO',

'RSZELTPROP', 'RSZELTTXT', 'RSZELTXREF', 'RSZGLOBV', 'RSZRANGE',
'SACONT01', 'SALRT', 'SALRTCNT', 'SBCMCONT1', 'SBOI_DTRC_DETAIL',
'SBOI_DTRC_HEADER', 'SDBAH', 'SDBAD',
'SE16N_CD_DATA', 'SE16N_CD_KEY', 'SEC_CONTEXT_BLKD', 'SGOSHIST', 'SMO8FTCFG',
'SMO8FTSTP', 'SMO8_TMSG',
'SMO8_TMDAT', 'SMO8_DLIST', 'SMW3_BDOC', 'SMW3_BDOC1', 'SMW3_BDOC2',
'SMW3_BDOC4', 'SMW3_BDOC5', 'SMW3_BDOC6',
'SMW3_BDOC7', 'SMW3_BDOCQ', 'SMWT_TRC', 'SNAP', 'SPAF_ERR_LOG_MSG',
'SPAF_ERR_MSG', 'SQLMD', 'SRT_CDTC', 'SRT_MMASTER', 'SRT_MONILOG_DATA',
'SRT_MVERSIONS',
'SRT_RTC_DATA', 'SRT_RTC_DATA_RT', 'SRTM_SUB', 'SRT_RTC_DT_RT',
'SRT_SEQ_HDR_STAT', 'SRT_SEQ_REORG', 'SRT_UTIL_ERRLOG',
'SRT_UTIL_TRCFUNC', 'SRT_UTIL_TRCPERF', 'SRT_UTIL_TRCPLOA', 'SUAUTHVALTRC',
'SSCOOKIE', 'SWELOG', 'SWELTS', 'SWFREVTLOG', 'SWFGPROLEINST', 'SWP_HEADER',
'SWP_NODEWI', 'SWPNODE',
'SWPNODELOG', 'SWPSTEPLOG', 'SWW_CONT', 'SWW_CONTOB', 'SWW_WI2OBJ',
'SWWCNTP0', 'SWWCNTPADD', 'SWWEI',
'SWWLOGHIST', 'SWWLOGPARA', 'SWWWIDEADL', 'SWWWIHEAD', 'SWWWIRET', 'SWZAI',
'SWZAIENTRY', 'SWZAIRET', 'SWW_WIREGISTER', 'SWWUSERWI',
'SWFRXIHDR', 'SWFRXICNT', 'SWFRXIPRC', 'SWNCMONI', 'SWN_NOTIF',
'SWN_NOTIFTSTMP', 'SWN_SENDLOG', 'SXMSCLUR',
'SXMSCLUR2', 'SXMSCLUP', 'SXMSCLUP2', 'SXMSPFRAWH', 'SXMSPFRAWD',
'SXMSPHIST', 'SXMSPHIST2', 'SXMSPMAST', 'SXMSPMAST2',
'SXMSPEMAST', 'SXMSPEMAS2', 'SXMSPVERS', 'SXMSPVERS2', 'SXMSPERROR',
'SXMSPERRO2',
'SMW0REL', 'SOC3', 'SOFFCONT1', 'SOFM', 'SOOD', 'SOOS', 'SOST', 'SRRELROLES',
'T811E', 'T811ED', 'T811ED2', 'TAAN_DATA', 'TAAN_FLDS',
'TAAN_HEAD', 'TASKLOGS', 'BTCJOBEPP', 'TBTCO', 'TBTCP', 'TBTCS', 'TBTC_TASK',

'TBTCJOBLOG', 'TBTCJOBLOG0', 'TBTCJOBLOG1', 'TBTCJOBLOG2', 'TBTCJOBLOG3',

'TBTCJOBLOG4', 'TBTCJOBLOG5', 'TBTCJOBLOG6', 'TBTCJOBLOG7', 'TBTCJOBLOG8',
'TBTCJOBLOG9',
'TPRI_PAR', 'TRFC_I_SDATA', 'TRFC_I_UNIT', 'TRFC_I_DEST',
'TRFC_I_EXE_STATE', 'TRFC_I_ERR_STATE', 'TRFC_I_UNIT_LOCK',
'/TRP/RES_TRKLOG', 'TRFCQDATA', 'TRFCQIN', 'TRFCQOUT', 'TRFCQSTATE',
'TXMILOGRAW', 'TSPEVDEV', 'TSPEVJOB', 'TST01', 'TST03', 'UASE16N_CD_DATA',
'UASE16N_CD_KEY', 'UJ0_STAT_DTL', 'UJ0_STAT_HDR', 'UJF_DOC', 'UJF_DOC_CLUSTER',
'UPC_STATISTIC', 'UPC_STATISTIC2', 'UPC_STATISTIC3', 'VBDATA', 'VBMOD',
'VBHDR', 'VBERROR', 'VDCHGPTR', 'WBCROSSGT',
'WBCROSSI', 'XI_AF_MSG', 'XI_AF_MSG_AUDIT', 'WRI$_OPTSTAT_HISTGRM_HISTORY',
'WRI$_OPTSTAT_HISTHEAD_HISTORY',
'WRI$_OPTSTAT_IND_HISTORY', 'WRI$_OPTSTAT_TAB_HISTORY',
'WRH$_ACTIVE_SESSION_HISTORY'
 ) OR
TABLE_NAME LIKE '%~~OLD' OR
TABLE_NAME LIKE '$BPC$HC$%' OR
TABLE_NAME LIKE '$BPC$TMP%' OR
TABLE_NAME LIKE '/BI0/0%' OR
TABLE_NAME LIKE '/BIC/000APR%' OR
TABLE_NAME LIKE '/BI0/A%3' OR
TABLE_NAME LIKE '/BIC/A%3' OR
TABLE_NAME LIKE '/BI0/B%' OR
TABLE_NAME LIKE '/BIC/B%' OR
TABLE_NAME LIKE '/BI0/E1%' OR
TABLE_NAME LIKE '/BIC/E1%' OR
TABLE_NAME LIKE '/BI0/F1%' OR
TABLE_NAME LIKE '/BIC/F1%' OR
TABLE_NAME LIKE '/BI0/H%' OR
TABLE_NAME LIKE '/BIC/H%' OR
TABLE_NAME LIKE '/BI0/I%' OR
TABLE_NAME LIKE '/BIC/I%' OR
TABLE_NAME LIKE '/BI0/J%' OR
TABLE_NAME LIKE '/BIC/J%' OR
TABLE_NAME LIKE '/BI0/K%' OR
TABLE_NAME LIKE '/BIC/K%' OR
TABLE_NAME LIKE '/BI0/Z%' OR
TABLE_NAME LIKE '/BIC/Z%' OR
TABLE_NAME LIKE 'SWNCM_%' OR
TABLE_NAME LIKE 'ZBICZ%' OR
TABLE_NAME LIKE 'ZBI0Z%' OR
TABLE_NAME LIKE 'ZARIX%'
),
COMPRESSION_EXCEPTION_TABLES AS
( SELECT /*+ MATERIALIZE */
OWNER,
TABLE_NAME,
EXCLUDED_VIA,
REASON,
COMPRESSED
FROM
( SELECT
C.OWNER,
C.TABLE_NAME,
DECODE(C.NUM_COLUMNS - 256, -1, '-MCC ' || BI.MIN_COLUMN_THRESHOLD,
'technical restriction') EXCLUDED_VIA,
DECODE(C.NUM_COLUMNS - 256, -1, 'close to 255 columns', '> 255 columns')
REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
COLUMNS C
WHERE
T.OWNER = C.OWNER AND
T.TABLE_NAME = C.TABLE_NAME AND
C.NUM_COLUMNS > BI.MIN_COLUMN_THRESHOLD
UNION
( SELECT
C.OWNER,
C.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'INDX table' REASON,
 DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
COLUMNS C
WHERE
T.OWNER = C.OWNER AND
T.TABLE_NAME = C.TABLE_NAME AND
C.INDX_COLUMNS = 'X'
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'ABAP table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T
WHERE
T.TABLE_NAME LIKE BI.TABLE_NAME AND
T.TABLE_NAME IN ('REPOSRC', 'REPOLOAD')
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'Upd' || 'ate table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T
WHERE
T.TABLE_NAME IN ('VBHDR', 'VBDATA', 'VBMOD', 'VBERROR')
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'POOL table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
DDNTT DD
WHERE
DD.TABNAME = T.TABLE_NAME AND
DD.TABTYPE = 'P' AND
DD.TABFORM = 'T'
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'CLUSTER table' REASON,
 DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
DDNTT DD
WHERE
DD.TABNAME = T.TABLE_NAME AND
DD.TABTYPE = 'C' AND
DD.TABFORM = 'T'
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'POOL table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
DDNTT DD
WHERE
DD.TABNAME = T.TABLE_NAME AND
DD.TABTYPE = 'P' AND
DD.TABFORM = 'T'
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'-SCT' EXCLUDED_VIA,
'CLUSTER table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T,
DDNTT DD
WHERE
DD.TABNAME = T.TABLE_NAME AND
DD.TABTYPE = 'C' AND
DD.TABFORM = 'T'
)
UNION
( SELECT
T.OWNER,
T.TABLE_NAME,
'_reorg_excl_tab' EXCLUDED_VIA,
'RFC table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T
WHERE
T.TABLE_NAME IN ('ARFCSSTATE', 'ARFCSDATA', 'ARFCRSTATE', 'TRFCQDATA',
'TRFCQIN', 'TRFCQOUT', 'TRFCQSTATE', 'QRFCTRACE', 'QRFCLOG')
)
UNION
( SELECT
T.OWNER,
 T.TABLE_NAME,
'_reorg_excl_tab' EXCLUDED_VIA,
'Number range table' REASON,
DECODE(T.COMPRESSION, 'N', ' ', 'X') COMPRESSED
FROM
BASIS_INFO BI,
TABLES T
WHERE
T.TABLE_NAME IN ('NRIV')
)
)
),
LINES AS
( SELECT 1 LINENR, 'TOTAL' DESCRIPTION FROM DUAL UNION ALL
( SELECT 2 LINENR, 'TABLE' DESCRIPTION FROM DUAL ) UNION ALL
( SELECT 3 LINENR, 'INDEX' DESCRIPTION FROM DUAL ) UNION ALL
( SELECT 4 LINENR, 'LOB' DESCRIPTION FROM DUAL )
)
SELECT
D.OWNER,
D.TABLE_NAME,
DECODE(D.BASIS_TABLE, NULL, ' ', 'X') B,
DECODE(D.PART_ENGINE_TABLE, NULL, ' ', 'X') P,
D.LONG_COLUMN L,
DECODE(UNIQUENESS, 'X', 'X', ' ') U,
COMP,
TO_CHAR(ROWNUM, 990) POS,
TO_CHAR(NUM_COLUMNS, 990) COLS,
TO_CHAR(TOTAL_BYTES / 1024 / 1024 / 1024, 99990.99) TOTAL_GB,
TO_CHAR(TOTAL_BYTES / DB_NET_SIZE_BYTE * 100, 990.99) "TOTAL_%",
TO_CHAR(SUM(TOTAL_BYTES / DB_NET_SIZE_BYTE * 100) OVER (ORDER BY TOTAL_BYTES DESC
RANGE UNBOUNDED PRECEDING), 990.99) "CUM_%",
TO_CHAR("PART.", 9990) "PART.",
TO_CHAR(TAB_BYTES / 1024 / 1024 / 1024, 99990.99) TABLE_GB,
TAB_TABSPACE,
TO_CHAR("INDEXES", 990) "IND.",
TO_CHAR(IND_BYTES / 1024 / 1024 / 1024, 9990.99) INDEX_GB,
IND_TABSPACE,
LPAD(DECODE(LOBS, 0, NULL, LOB_TYPE) || TO_CHAR(LOBS), 4) LOBS,
TO_CHAR(LOB_BYTES / 1024 / 1024 / 1024, 9990.99) LOB_GB
FROM
( SELECT
OWNER,
TABLE_NAME,
MAX(BASIS_TABLE) BASIS_TABLE,
MAX(PART_ENGINE_TABLE) PART_ENGINE_TABLE,
MAX(LONG_COLUMN) LONG_COLUMN,
MAX(NUM_COLUMNS) NUM_COLUMNS,
MAX(DECODE(COMPONENT, 'TABLE', DECODE(COMP_EXCEPT_TABLE, NULL, ' ', '!') ||
COMPRESSION)) ||
MAX(DECODE(COMPONENT, 'INDEX', COMPRESSION)) ||
MAX(DECODE(COMPONENT, 'LOB', COMPRESSION)) COMP,
SUM(DECODE(COMPONENT, 'TOTAL', BYTES, 0)) TOTAL_BYTES,
SUM(DECODE(COMPONENT, 'TABLE', COUNTER, 0)) "PART.",
SUM(DECODE(COMPONENT, 'TABLE', BYTES, 0)) TAB_BYTES,
MAX(DECODE(COMPONENT, 'TABLE', TABLESPACE_NAME)) TAB_TABSPACE,
SUM(DECODE(COMPONENT, 'INDEX', COUNTER, 0)) "INDEXES",
SUM(DECODE(COMPONENT, 'INDEX', BYTES, 0)) IND_BYTES,
MAX(DECODE(COMPONENT, 'INDEX', TABLESPACE_NAME)) IND_TABSPACE,
 MAX(DECODE(COMPONENT, 'INDEX', ADDITIONAL_INFO, ' ')) UNIQUENESS,
SUM(DECODE(COMPONENT, 'LOB', COUNTER, 0)) LOBS,
SUM(DECODE(COMPONENT, 'LOB', BYTES, 0)) LOB_BYTES,
MAX(DECODE(COMPONENT, 'LOB', ADDITIONAL_INFO, ' ')) LOB_TYPE
FROM
( SELECT
TSM.OWNER OWNER,
TSM.TABLE_NAME TABLE_NAME,
BT.TABLE_NAME BASIS_TABLE,
PET.TABLE_NAME PART_ENGINE_TABLE,
CET.TABLE_NAME COMP_EXCEPT_TABLE,
C.LONG_COLUMN,
C.NUM_COLUMNS,
L.DESCRIPTION COMPONENT,
DECODE(L.DESCRIPTION,
'TOTAL', MAX(DECODE(TSM.SEGMENT_TYPE, 'TABLE', TSM.TABLESPACE_NAME)),
'TABLE', MAX(DECODE(TSM.SEGMENT_TYPE, 'TABLE', TSM.TABLESPACE_NAME)),
'INDEX', MAX(DECODE(TSM.SEGMENT_TYPE, 'INDEX', TSM.TABLESPACE_NAME)),
'LOB', MAX(DECODE(TSM.SEGMENT_TYPE, 'LOB', TSM.TABLESPACE_NAME)))
TABLESPACE_NAME,
DECODE(L.DESCRIPTION,
'TOTAL', SUM(TSM.BYTES),
'TABLE', SUM(DECODE(TSM.SEGMENT_TYPE, 'TABLE', TSM.BYTES)),
'INDEX', SUM(DECODE(TSM.SEGMENT_TYPE, 'INDEX', TSM.BYTES)),
'LOB', SUM(DECODE(TSM.SEGMENT_TYPE, 'LOB', TSM.BYTES))) BYTES,
DECODE(L.DESCRIPTION,
'TOTAL', 0,
'TABLE', SUM(DECODE(TSM.SEGMENT_TYPE, 'TABLE', TSM.PARTITIONS)),
'INDEX', SUM(DECODE(TSM.SEGMENT_TYPE, 'INDEX', TSM.SEGMENTS)),
'LOB', SUM(DECODE(TSM.SEGMENT_TYPE, 'LOB', TSM.SEGMENTS))) COUNTER,
DECODE(L.DESCRIPTION,
'TOTAL', ' ',
'TABLE', NVL(MAX(DECODE(TSM.SEGMENT_TYPE, 'TABLE', TSM.COMPRESSION)), '-'),
'INDEX', NVL(MAX(DECODE(TSM.SEGMENT_TYPE, 'INDEX', TSM.COMPRESSION)), '-'),
'LOB', NVL(MAX(DECODE(TSM.SEGMENT_TYPE, 'LOB', TSM.COMPRESSION)), '-'))
COMPRESSION,
DECODE(L.DESCRIPTION,
'TOTAL', ' ',
'TABLE', MAX(DECODE(TSM.SEGMENT_TYPE, 'TABLE', ' ')),
'INDEX', MAX(DECODE(TSM.SEGMENT_TYPE, 'INDEX', TSM.ADDITIONAL_INFO)),
'LOB', MAX(DECODE(TSM.SEGMENT_TYPE, 'LOB', TSM.ADDITIONAL_INFO)))
ADDITIONAL_INFO
FROM
BASIS_INFO BI,
TABLE_SEGMENT_MAPPING TSM,
LINES L,
BASIS_TABLES BT,
PARTITIONING_ENGINE_TABLES PET,
COMPRESSION_EXCEPTION_TABLES CET,
COLUMNS C
WHERE
TSM.TABLE_NAME LIKE BI.TABLE_NAME AND
TSM.TABLE_NAME = BT.TABLE_NAME (+) AND
TSM.OWNER = PET.OWNER (+) AND
TSM.TABLE_NAME = PET.TABLE_NAME (+) AND
TSM.OWNER = CET.OWNER (+) AND
TSM.TABLE_NAME = CET.TABLE_NAME (+) AND
TSM.OWNER = C.OWNER AND
TSM.TABLE_NAME = C.TABLE_NAME
 GROUP BY
L.LINENR,
L.DESCRIPTION,
TSM.OWNER,
TSM.TABLE_NAME,
PET.OWNER,
PET.TABLE_NAME,
CET.OWNER,
CET.TABLE_NAME,
C.OWNER,
C.TABLE_NAME,
C.LONG_COLUMN,
C.NUM_COLUMNS,
BT.TABLE_NAME
ORDER BY
SUM(TSM.BYTES),
TSM.TABLE_NAME,
L.LINENR
)
GROUP BY
OWNER,
TABLE_NAME
ORDER BY
8 DESC
) D,
TOTAL_SEGMENT_SIZE TSS,
BASIS_INFO BI
WHERE
( BI.ONLY_BASIS_TABLES = ' ' OR D.BASIS_TABLE IS NOT NULL ) AND
( BI.ONLY_PART_ENGINE_TABLES = ' ' OR D.PART_ENGINE_TABLE IS NOT NULL ) AND
( BI.NUM_RECORDS = -1 OR ROWNUM <= BI.NUM_RECORDS ) AND
( BI.MIN_TOTAL_SIZE_MB = -1 OR TOTAL_BYTES / 1024 / 1024 >=
BI.MIN_TOTAL_SIZE_MB )
));