From Oramosswiki
REM *************************************************************************
REM AUTHOR: Jeff Moss
REM NAME: cbo.sql
REM
REM *************************************************************************
REM PURPOSE:
REM This script shows various pieces of inFORMATion for a given table
REM The purpose of the script is to provide inFORMATion which the CBO
REM may be using to determine its plans.
REM *************************************************************************
REM Local settings...
SET TIMING OFF
SET FEEDBACK OFF
SET VERIFY OFF
UNDEFINE table_name
UNDEFINE owner
PROMPT
ACCEPT owner PROMPT 'Please enter Name of Table Owner: '
ACCEPT table_name PROMPT 'Please enter Table Name to show Statistics for: '
SET WRAP OFF
BREAK ON partition_name
REM Get Table details...
SELECT partition_name
, tablespace_name
, compression
, pct_free
, pct_used
, high_value
, freelists
, degree
, TO_CHAR(last_analyzed,'DD/MM/YY') last_analyzed
, avg_space
FROM (
SELECT dtp.partition_name
, degree
, dtp.tablespace_name
, compression
, dtp.pct_free
, dtp.pct_used
, dtp.high_value
, dtp.freelists
,
, dtp.last_analyzed
, dtp.partition_position
, dtp.avg_space
FROM dba_tab_partitions dtp
WHERE dtp.table_owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dtp.table_name) = UPPER('&&Table_name')
UNION ALL
SELECT
,
, dtab.tablespace_name
, compression
, dtab.pct_free
, dtab.pct_used
, NULL
, dtab.freelists
, dtab.degree
, dtab.last_analyzed
, 0
, dtab.avg_space
FROM dba_tables dtab
WHERE dtab.owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dtab.table_name) = UPPER('&&Table_name')
)
ORDER BY partition_position
/
REM Get Table, partition and subpartition details...
REM Get Table, partition and subpartition details...
SELECT partition_name
, subpartition_name
, num_rows
, blocks
, empty_blocks
, logging
, chain_cnt
, avg_row_len
, space_mb
FROM (
SELECT p.partition_name
, p.partition_position
, s.subpartition_name
, s.subpartition_position
, s.num_rows
, s.blocks
, s.empty_blocks
, s.logging
, s.chain_cnt
, s.avg_row_len
, s.blocks * ((SELECT DISTINCT t.block_size
FROM dba_tablespaces t
WHERE p.tablespace_name = t.tablespace_name
) / (1024 * 1024)
) space_mb
FROM dba_tab_subpartitions s
, dba_tab_partitions p
WHERE s.table_owner = UPPER(NVL('&&Owner',USER))
AND UPPER(s.table_name) = UPPER('&&Table_name')
AND p.table_owner = UPPER(NVL('&&Owner',USER))
AND UPPER(p.table_name) = UPPER('&&Table_name')
AND p.partition_name = s.partition_name
UNION ALL
SELECT dtp.partition_name
, dtp.partition_position
, subpartition_name
, 0 subpartition_position
, dtp.num_rows
, dtp.blocks
, dtp.empty_blocks
, dtp.logging
, dtp.chain_cnt
, dtp.avg_row_len
, dtp.blocks * ((SELECT DISTINCT t.block_size
FROM dba_tablespaces t
WHERE dtp.tablespace_name = t.tablespace_name
) / (1024 * 1024)
) space_mb
FROM dba_tab_partitions dtp
WHERE dtp.table_owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dtp.table_name) = UPPER('&&Table_name')
UNION ALL
SELECT
, 0
,
, 0
, dtab.num_rows
, dtab.blocks
, dtab.empty_blocks
, dtab.logging
, dtab.chain_cnt
, dtab.avg_row_len
, dtab.blocks * (
NVL((SELECT DISTINCT t.block_size
FROM dba_tablespaces t
, dba_tab_partitions dtp
WHERE dtp.tablespace_name = t.tablespace_name
AND dtp.partition_position = 1
AND dtp.table_name = dtab.table_name
AND dtp.table_owner=dtab.owner
),(SELECT DISTINCT t.block_size
FROM dba_tablespaces t
WHERE dtab.tablespace_name = t.tablespace_name
)
) / (1024 * 1024)
) space_mb
FROM dba_tables dtab
WHERE dtab.owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dtab.table_name) = UPPER('&&Table_name')
)
ORDER BY partition_position,subpartition_position
/
SET WRAP ON
REM Get table column details...
SELECT t.COLUMN_name
, DECODE(t.data_type
,'NUMBER',t.data_type||'('||
DECODE(t.data_precision
,NULL,t.data_length||')'
,t.data_precision||','||t.data_scale||')')
,'DATE',t.data_type
,'LONG',t.data_type
,'LONG RAW',t.data_type
,'ROWID',t.data_type
,'MLSLABEL',t.data_type
,t.data_type||'('||t.data_length||')') ||' '||
DECODE(UPPER(t.nullable)
,'N','NOT NULL'
,NULL) col
, t.num_distinct
, t.num_nulls
, tcs.histogram
, t.density
, DECODE(t.low_value,NULL,'N/A'
,DECODE(t.data_type
,'DATE'
,LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),8,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),8,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),7,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),7,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),6,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),6,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),5,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),5,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),2,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),2,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),1,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),1,1))) * 16) - 100),2,'0')||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),4,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),4,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),3,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),3,1))) * 16) - 100),2,'0')||' '||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),10,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),10,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),9,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),9,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),12,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),12,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),11,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),11,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),14,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),14,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),13,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),13,1))) * 16) - 1),2,'0')
,'NUMBER'
,DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 3),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),4,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),4,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),3,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),3,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 5),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),6,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),6,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),5,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),5,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 7),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),8,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),8,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),7,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),7,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 9),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),10,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),10,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),9,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),9,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 11),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),12,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),12,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),11,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),11,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 13),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),14,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),14,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),13,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),13,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 15),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),16,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),16,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),15,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),15,1))) * 16) - 1)
),2,'0'))
,'N/A'
)) low_value
, DECODE(t.high_value,NULL,'N/A'
,DECODE(t.data_type
,'DATE'
,LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),8,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),8,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),7,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),7,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),6,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),6,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),5,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),5,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),2,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),2,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),1,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),1,1))) * 16) - 100),2,'0')||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),4,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),4,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),3,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),3,1))) * 16) - 100),2,'0')||' '||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),10,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),10,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),9,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),9,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),12,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),12,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),11,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),11,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),14,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),14,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),13,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),13,1))) * 16) - 1),2,'0')
,'NUMBER'
,DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 3),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),4,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),4,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),3,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),3,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 5),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),6,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),6,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),5,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),5,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 7),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),8,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),8,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),7,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),7,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 9),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),10,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),10,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),9,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),9,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 11),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),12,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),12,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),11,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),11,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 13),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),14,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),14,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),13,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),13,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 15),-1,
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),16,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),16,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),15,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),15,1))) * 16) - 1)
),2,'0'))
,'N/A'
)) high_value
, t.data_default
FROM dba_tab_cols t
, dba_tab_col_statistics tcs
WHERE UPPER(t.table_name) = UPPER('&Table_name')
AND t.owner = UPPER(NVL('&Owner',USER))
AND t.table_name = tcs.table_name(+)
AND t.column_name = tcs.column_name(+)
AND t.owner = tcs.owner(+)
/
REM Get Index details...
SELECT i.index_name
, i.index_type
, i.status
, DECODE(i.uniqueness,'UNIQUE','Yes','No') uniqueness
, i.blevel blev
, i.leaf_blocks
, i.distinct_keys
, DECODE(t.num_rows,0,-1,(i.distinct_keys / t.num_rows) * 100) cardinality
, i.compression
, i.avg_leaf_blocks_per_key
, i.avg_data_blocks_per_key
, i.clustering_factor
, (DECODE((t.num_rows - t.blocks),0,0,(i.clustering_factor) / (t.num_rows - t.blocks))) * 100 CLFPCT
, i.partitioned "Partitioned"
FROM dba_indexes i
, dba_tables t
WHERE i.table_name = t.table_name
AND UPPER(i.table_name) = UPPER('&Table_name')
AND i.table_owner = UPPER(NVL('&Owner',USER))
AND t.owner = UPPER(NVL('&Owner',user))
/
REM Get Index partition details...
SELECT dip.index_name
, dip.partition_name
, dip.subpartition_count
, dip.status
, dip.tablespace_name
, dip.compression
, dip.blevel
, dip.leaf_blocks
, dip.distinct_keys
, dip.avg_leaf_blocks_per_key
, dip.avg_data_blocks_per_key
, dip.clustering_factor
, (DECODE((t.num_rows - t.blocks),0,0,(ip.clustering_factor) / (t.num_rows - t.blocks))) * 100 CLFPCT
FROM dba_tables t
, dba_indexes ip
, dba_ind_partitions dip
WHERE ip.table_name = t.table_name
AND ip.index_name = dip.index_name
AND UPPER(ip.table_name) = UPPER('&Table_name')
AND ip.table_owner = UPPER(NVL('&Owner',USER))
AND ip.table_owner = t.owner
ORDER BY dip.index_name
, dip.partition_position
/
REM Get Index column details...
BREAK ON index_name
SELECT ic.index_name
, ic.COLUMN_name
, ic.COLUMN_POSITION
, DECODE(tc.data_type
,'NUMBER',tc.data_type||'('||
decode(tc.data_precision
,NULL,tc.data_length||')'
,tc.data_precision||','||tc.data_scale||')')
,'DATE',tc.data_type
,'LONG',tc.data_type
,'LONG RAW',tc.data_type
,'ROWID',tc.data_type
,'MLSLABEL',tc.data_type
,tc.data_type||'('||tc.data_length||')') ||' '||
DECODE(UPPER(tc.nullable),
'N','NOT NULL'
,NULL) col
, DECODE(i.distinct_keys,0,-1,ROUND((t.num_rows/i.distinct_keys))) selectivity
, DECODE(NVL(t.num_rows,0),0,0,ROUND(ROUND((i.distinct_keys/t.num_rows),2)* 100)) cardinality
FROM dba_ind_COLUMNs ic
, dba_indexes i
, dba_tab_COLs tc
, dba_tables t
WHERE UPPER(ic.table_name) = UPPER('&Table_name')
AND tc.owner = UPPER(NVL('&Owner',user))
AND t.owner = UPPER(NVL('&Owner',user))
AND ic.table_owner = UPPER(NVL('&Owner',USER))
AND ic.index_owner = UPPER(NVL('&Owner',USER))
AND i.owner = UPPER(NVL('&Owner',user))
AND ic.index_name = i.index_name
AND i.table_name = t.table_name
AND ic.table_name = t.table_name
AND tc.COLUMN_name = ic.COLUMN_name
AND tc.table_name = t.table_name
ORDER BY index_name
, COLUMN_position
/
REM Get constraint details...
SELECT constraint_name
, DECODE(r_constraint_name,NULL,'Check Constraint...',r_constraint_name) r_constraint_name
, search_condition
, status
, validated
FROM dba_constraints
WHERE UPPER(table_name) = UPPER('&Table_name')
AND owner = UPPER(NVL('&Owner',USER))
ORDER BY 4,2 NULLS LAST,1
/
REM Get Trigger details...
SELECT trigger_name
,trigger_type
,triggering_event
,status
FROM dba_triggers
WHERE UPPER(table_name) = UPPER('&Table_name')
AND table_owner = UPPER(NVL('&Owner',USER))
/
REM Get Partition Key details...
SELECT dpt.partitioning_type
, dpkc.object_type
, dpkc.column_name
, dpkc.column_position
FROM dba_part_key_columns dpkc
, dba_part_tables dpt
WHERE dpkc.owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dpkc.name) = UPPER('&&Table_name')
AND dpkc.owner = dpt.owner
AND dpkc.name = dpt.table_name
ORDER BY dpkc.column_position
/
REM Get SubPartition Key details...
SELECT dpt.subpartitioning_type
, dskc.object_type
, dskc.column_name
, dskc.column_position
FROM dba_subpart_key_columns dskc
, dba_part_tables dpt
WHERE dskc.owner = UPPER(NVL('&&Owner',USER))
AND UPPER(dskc.name) = UPPER('&&Table_name')
AND dskc.owner = dpt.owner
AND dskc.name = dpt.table_name
ORDER BY dskc.column_position
/
CLEAR BREAKS
SET TIMING ON
SET FEEDBACK ON
SET VERIFY ON
