Table info and statistics
Get current optimizer statistics for a table
Author : Daniel W. Fink
SET VERIFY OFF PAGESIZE 400 LINESIZE 135
DEFINE owner_table = &1
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT
SELECT 'tab_idx_info_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname
FROM dual
/
SPOOL &&spool_name
COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT TO_NUMBER(t.degree) tab_degree,
t.partitioned tab_partitioned,
t.num_rows tab_num_rows,
t.blocks tab_alloc_blocks,
(t.blocks - t.empty_blocks) tab_hwm_blocks,
TO_CHAR(t.last_analyzed, 'MM/DD/YYYY hh24:mi') tab_last_analyzed_time,
ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2)
tab_analyzed_pct,
t.avg_space tab_avg_space,
t.avg_row_len tab_avg_row_length,
t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
ORDER BY t.table_name
/
COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A20 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A10 HEADING 'Nullable?'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.99999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A16 HEADING 'Histogram'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT tc.column_name tab_column_name,
tc.data_type tab_column_datatype,
DECODE(tc.nullable, 'N', 'NOT NULL', NULL) tab_column_nullable,
tc.num_distinct tab_column_numdistinct,
tc.density tab_column_density,
tc.num_nulls tab_column_numnulls,
DECODE(tc.histogram,'NONE', NULL, tc.histogram) tab_column_histogram,
TO_NUMBER(DECODE(tc.num_buckets,1,NULL,
tc.num_buckets)) tab_column_numbuckets
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
ORDER BY tc.column_id
/
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT i.index_name ind_name,
i.status ind_status,
DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique,
i.blevel ind_blevel,
i.leaf_blocks ind_leafblocks,
i.num_rows ind_numrows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, 'MM/DD/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
ORDER BY i.uniqueness DESC, i.index_name
/
COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'
BREAK ON index_name NODUP
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT
WITH col_hi_lo_vals AS
( select tc.column_name
, tc.data_type
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
from dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position
/
SPOOL off
====
APTDB\sys> @table_index SCOTT.EMP
*********************************************************************************
Table Statistics for SCOTT.EMP
*********************************************************************************
Allocated HWM Analyze Avg Block Avg Row
Deg Prtn Rows Blocks Blocks Analyzed Date Pct Free Space Length Mntr
----- ---- ------------ ------------ ------------ ----------------- ------- ---------- ------- ----
1 NO 14 5 5 07/07/2011 22:00 100.00 0 38 YES
*********************************************************************************
Column Statistics for SCOTT.EMP
*********************************************************************************
Distinct Number
Column Name Datatype Nullable? Values Density of Nulls Histogram Buckets
------------------------------ -------------------- ---------- ------------ -------- ------------ ---------------- --------
EMPNO NUMBER NOT NULL 14 .07143 0
ENAME VARCHAR2 14 .07143 0
JOB VARCHAR2 5 .20000 0
MGR NUMBER 6 .16667 1
HIREDATE DATE 13 .07692 0
SAL NUMBER 12 .08333 0
COMM NUMBER 4 .25000 10
DEPTNO NUMBER 3 .03571 0 FREQUENCY 3
8 rows selected.
*********************************************************************************
Index Statistics for SCOTT.EMP
*********************************************************************************
IND_NAME Status Unq Lvl Leaf Blks Indx Rows Dstnct Keys Clstr Fctr Analyzed Date
------------------------------ ------- --- ---- --------- ------------ ------------ ------------ -----------------
PK_EMP VALID Y 0 1 14 14 1 07/07/2011 22:00
*********************************************************************************
Index Columns for SCOTT.EMP
*********************************************************************************
Index Name Column Name Low Value High Value
------------------------------ ------------------------------ -------------------- --------------------
PK_EMP EMPNO 7369 7934
---------------------
define table_owner='SCOTT'define owner='SCOTT' define table_name='TABLESUSER' define 1='SCOTT' --- owner
COL owner NEW_V owner FOR A30 HEA 'TABLE_OWNER'; SELECT DISTINCT t.owner FROM dba_tables t, dba_users u WHERE u.username = t.owner AND u.oracle_maintained = 'N' AND u.username NOT LIKE 'C##'||CHR(37) ORDER BY 1 / COL table_owner NEW_V table_owner FOR A30; PRO PRO 1. Table Owner: DEF table_owner = '&1.'; UNDEF 1; SELECT UPPER(TRIM(NVL('&&table_owner.', '&&owner.'))) table_owner FROM DUAL / -- COL name NEW_V name FOR A30 HEA 'TABLE_NAME'; COL num_rows FOR 99,999,999,990; COL blocks FOR 99,999,999,990; COL rows_per_block FOR 999,990.0; COL avg_row_len FOR 999,990; COL lobs FOR 9990; SELECT t.table_name name, t.num_rows, t.blocks, ROUND(t.num_rows / NULLIF(t.blocks, 0), 1) AS rows_per_block, t.avg_row_len, (SELECT COUNT(*) FROM dba_lobs l WHERE l.owner = t.owner AND l.table_name = t.table_name) AS lobs, t.partitioned FROM dba_tables t, dba_users u WHERE t.owner = UPPER(TRIM('&&table_owner.')) AND u.username = t.owner AND u.oracle_maintained = 'N' AND u.username NOT LIKE 'C##'||CHR(37) ORDER BY 1 / -- COL owner FOR A30 HEA 'Owner' TRUNC; COL segment_name FOR A30 TRUNC; COL partition_name FOR A30 TRUNC; COL column_name FOR A30 TRUNC; COL segments FOR 9,999,990; -- COL mebibytes FOR 999,999,990.000 HEA 'Size MiB'; COL megabytes FOR 999,999,990.000 HEA 'Size MB'; COL tablespace_name FOR A30 HEA 'Tablespace'; BREAK ON REPORT; COMPUTE SUM LABEL 'TOTAL' OF mebibytes megabytes segments ON REPORT; -- PRO PRO SEGMENTS (dba_segments) top 100 &&table_owner..&&table_name. PRO ~~~~~~~~ WITH t AS ( SELECT owner, table_name FROM dba_tables WHERE owner = '&&table_owner.' AND table_name = '&&table_name.' ), s AS ( SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name FROM t, dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%' UNION ALL SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name FROM t, dba_indexes i, dba_segments s WHERE i.table_owner = t.owner AND i.table_name = t.table_name AND s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%' UNION ALL SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name FROM t, dba_lobs l, dba_segments s WHERE l.owner = t.owner AND l.table_name = t.table_name AND s.owner = l.owner AND s.segment_name = l.segment_name AND s.segment_type LIKE 'LOB%' ) --SELECT ROUND(bytes/POWER(2,20),3) AS mebibytes, segment_type, owner, column_name, segment_name, partition_name, tablespace_name SELECT ROUND(bytes/POWER(10,6),3) AS megabytes, segment_type, owner, column_name, segment_name, partition_name, tablespace_name FROM s ORDER BY bytes DESC, oby, segment_type, owner, column_name, segment_name, partition_name FETCH FIRST 100 ROWS ONLY / -- PRO PRO SEGMENT TYPE (dba_segments) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~ WITH t AS ( SELECT owner, table_name FROM dba_tables WHERE owner = '&&table_owner.' AND table_name = '&&table_name.' ), s AS ( SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name FROM t, dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%' UNION ALL SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name FROM t, dba_indexes i, dba_segments s WHERE i.table_owner = t.owner AND i.table_name = t.table_name AND s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%' UNION ALL SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name FROM t, dba_lobs l, dba_segments s WHERE l.owner = t.owner AND l.table_name = t.table_name AND s.owner = l.owner AND s.segment_name = l.segment_name AND s.segment_type LIKE 'LOB%' ) --SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(10,6),3) AS megabytes, tablespace_name FROM s GROUP BY oby, segment_type, tablespace_name ORDER BY oby, segment_type, tablespace_name / -- CLEAR BREAK COMPUTE; -- COL partitioned FOR A4 HEA 'Part'; COL degree FOR A10 HEA 'Degree'; COL temporary FOR A4 HEA 'Temp'; COL blocks FOR 999,999,990 HEA 'Blocks'; COL num_rows FOR 999,999,999,990 HEA 'Num Rows'; COL avg_row_len FOR 999,999,990 HEA 'Avg Row Len'; COL size_MiB FOR 999,999,990.000 HEA 'Size MiB'; COL seg_size_MiB FOR 999,999,990.000 HEA 'Seg Size MiB'; COL estimated_MiB FOR 999,999,990.000 HEA 'Estimated MiB'; COL size_MB FOR 999,999,990.000 HEA 'Size MB'; COL seg_size_MB FOR 999,999,990.000 HEA 'Seg Size MB'; COL estimated_MB FOR 999,999,990.000 HEA 'Estimated MB'; COL sample_size FOR 999,999,999,990 HEA 'Sample Size'; COL last_analyzed FOR A19 HEA 'Last Analyzed'; COL compression FOR A12 HEA 'Compression'; COL tablespace_name FOR A30 HEA 'Tablespace'; -- PRO PRO TABLES (dba_tables) &&table_owner..&&table_name. PRO ~~~~~~ SELECT CASE t.partitioned WHEN 'YES' THEN (SELECT TRIM(TO_CHAR(COUNT(*))) FROM dba_tab_partitions tp WHERE tp.table_owner = t.owner AND tp.table_name = t.table_name) ELSE t.partitioned END AS partitioned, t.degree, t.temporary, t.blocks, --t.blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB, t.blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB, --(SELECT SUM(s.bytes) / POWER(2,20) FROM dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%') AS seg_size_MiB, (SELECT SUM(s.bytes) / POWER(10,6) FROM dba_segments s WHERE s.owner = t.owner AND s.segment_name = t.table_name AND s.segment_type LIKE 'TABLE%') AS seg_size_MB, t.num_rows, t.avg_row_len, --t.num_rows * t.avg_row_len / POWER(2,20) AS estimated_MiB, t.num_rows * t.avg_row_len / POWER(10,6) AS estimated_MB, t.sample_size, TO_CHAR(t.last_analyzed, '&&cs_datetime_full_format.') AS last_analyzed, t.compression, t.tablespace_name FROM dba_tables t, dba_tablespaces b, v$parameter p WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' AND b.tablespace_name(+) = t.tablespace_name AND p.name = 'db_block_size' / -- COL analyzetime FOR A19 HEA 'Analyze Time'; COL rowcnt FOR 999,999,999,990 HEA 'Row Count'; COL blkcnt FOR 999,999,990 HEA 'Block Count'; COL avgrln FOR 999,999,990 HEA 'Avg Row Len'; COL samplesize FOR 999,999,999,990 HEA 'Sample Size'; COL rows_inc FOR 999,999,999,990 HEA 'Rows Increase'; COL days_gap FOR 999,990.0 HEA 'Days Gap'; COL monthly_growth_perc FOR 999,990.000 HEA 'Monthly Growth Perc%'; -- PRO PRO CBO STAT TABLE HISTORY (wri$_optstat_tab_history and dba_tables) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~~~~~~~~~ WITH cbo_hist AS ( SELECT h.analyzetime, h.rowcnt, h.blkcnt, h.avgrln, h.samplesize FROM dba_objects o, wri$_optstat_tab_history h WHERE o.owner = '&&table_owner.' AND o.object_name = '&&table_name.' AND o.object_type = 'TABLE' AND h.obj# = o.object_id AND h.analyzetime IS NOT NULL UNION SELECT t.last_analyzed AS analyzetime, t.num_rows AS rowcnt, t.blocks AS blkcnt, t.avg_row_len AS avgrln, t.sample_size AS samplesize FROM dba_tables t WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' ), cbo_hist_extended AS ( SELECT h.analyzetime, h.rowcnt, h.blkcnt, h.avgrln, h.samplesize, h.rowcnt - LAG(h.rowcnt) OVER (ORDER BY h.analyzetime) AS rows_inc, h.analyzetime - LAG(h.analyzetime) OVER (ORDER BY h.analyzetime) AS days_gap, 100 * (365.25 / 12) * (h.rowcnt - LAG(h.rowcnt) OVER (ORDER BY h.analyzetime)) / NULLIF((h.analyzetime - LAG(h.analyzetime) OVER (ORDER BY h.analyzetime)), 0) / NULLIF(h.rowcnt, 0) AS monthly_growth_perc FROM cbo_hist h ) SELECT TO_CHAR(h.analyzetime, '&&cs_datetime_full_format.') AS analyzetime, h.blkcnt, h.rowcnt, h.rows_inc, h.days_gap, h.monthly_growth_perc, h.avgrln, h.samplesize FROM cbo_hist_extended h ORDER BY 1 / PRO PRO GROWTH (wri$_optstat_tab_history and dba_tables) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WITH oldest AS ( SELECT h.analyzetime, h.rowcnt FROM dba_objects o, wri$_optstat_tab_history h WHERE o.owner = '&&table_owner.' AND o.object_name = '&&table_name.' AND o.object_type = 'TABLE' AND h.obj# = o.object_id AND h.analyzetime IS NOT NULL AND h.rowcnt > 0 ORDER BY h.analyzetime FETCH FIRST 1 ROW ONLY ), newest AS ( SELECT t.last_analyzed AS analyzetime, t.num_rows AS rowcnt FROM dba_tables t WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' AND t.num_rows > 0 AND ROWNUM = 1 ) SELECT 100 * (365 / 12) * (n.rowcnt - o.rowcnt) / (n.analyzetime - o.analyzetime) / o.rowcnt AS monthly_growth_perc FROM oldest o, newest n WHERE n.analyzetime > o.analyzetime / -- COL object_type HEA 'Object Type' FOR A30; COL object_id FOR 999999999 HEA 'Object ID'; COL object_name FOR A30 HEA 'Object Name' TRUNC; COL subobject_name FOR A30 HEA 'Sub Object Name' TRUNC; COL created FOR A23 HEA 'Created'; COL last_ddl_time FOR A23 HEA 'Last DDL Time'; -- PRO PRO TABLE OBJECTS (dba_objects) up to 100 &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~ SELECT o.object_type, o.subobject_name, o.object_id, TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created, TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time FROM dba_objects o WHERE o.owner = '&&table_owner.' AND o.object_name = '&&table_name.' AND o.object_type LIKE 'TABLE%' ORDER BY o.object_type, o.object_id FETCH FIRST 100 ROWS ONLY / -- COL index_name FOR A30 HEA 'Index Name'; COL partitioned FOR A4 HEA 'Part'; COL orphaned_entries FOR A8 HEA 'Orphaned|Entries'; COL degree FOR A10 HEA 'Degree'; COL index_type FOR A27 HEA 'Index Type'; COL uniqueness FOR A10 HEA 'Uniqueness'; COL columns FOR 999,999 HEA 'Columns'; COL status FOR A8 HEA 'Status'; COL visibility FOR A10 HEA 'Visibility'; COL blevel FOR 99,990 HEA 'BLevel'; COL leaf_blocks FOR 999,999,990 HEA 'Leaf Blocks'; COL size_MiB FOR 999,999,990.000 HEA 'Size MiB'; COL seg_size_MiB FOR 999,999,990.000 HEA 'Seg Size MiB'; COL size_MB FOR 999,999,990.000 HEA 'Size MB'; COL seg_size_MB FOR 999,999,990.000 HEA 'Seg Size MB'; COL distinct_keys FOR 999,999,999,990 HEA 'Dist Keys'; COL clustering_factor FOR 999,999,999,990 HEA 'Clust Fact'; COL num_rows FOR 999,999,999,990 HEA 'Num Rows'; COL sample_size FOR 999,999,999,990 HEA 'Sample Size'; COL last_analyzed FOR A19 HEA 'Last Analyzed'; COL compression FOR A13 HEA 'Compression'; COL tablespace_name FOR A30 HEA 'Tablespace'; -- PRO PRO INDEXES (dba_indexes) &&table_owner..&&table_name. PRO ~~~~~~~ SELECT i.index_name, CASE i.partitioned WHEN 'YES' THEN (SELECT TRIM(TO_CHAR(COUNT(*))) FROM dba_ind_partitions ip WHERE ip.index_owner = i.owner AND ip.index_name = i.index_name) ELSE i.partitioned END AS partitioned, i.orphaned_entries, i.degree, i.index_type, i.uniqueness, (SELECT COUNT(*) FROM dba_ind_columns ic WHERE ic.index_owner = i.owner AND ic.index_name = i.index_name) AS columns, i.status, i.visibility, i.blevel, i.leaf_blocks, --i.leaf_blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB, i.leaf_blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB, --(SELECT SUM(s.bytes) / POWER(2,20) FROM dba_segments s WHERE s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%') AS seg_size_MiB, (SELECT SUM(s.bytes) / POWER(10,6) FROM dba_segments s WHERE s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%') AS seg_size_MB, i.distinct_keys, i.clustering_factor, i.num_rows, i.sample_size, TO_CHAR(i.last_analyzed, '&&cs_datetime_full_format.') AS last_analyzed, i.compression, i.tablespace_name FROM dba_indexes i, dba_tablespaces b, v$parameter p WHERE i.table_owner = '&&table_owner.' AND i.table_name = '&&table_name.' AND b.tablespace_name(+) = i.tablespace_name AND p.name = 'db_block_size' ORDER BY i.index_name / -- PRO PRO INDEX OBJECTS (dba_objects) up to 100 &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~ SELECT o.object_type, o.object_name, o.subobject_name, o.object_id, TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created, TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time FROM dba_indexes i, dba_objects o WHERE i.table_owner = '&&table_owner.' AND i.table_name = '&&table_name.' AND o.owner = i.owner AND o.object_name = i.index_name AND o.object_type LIKE 'INDEX%' ORDER BY o.object_type, o.object_name, o.subobject_name FETCH FIRST 100 ROWS ONLY / -- COL part_sub FOR A12 HEA 'LEVEL'; COL object_type FOR A5 HEA 'TYPE'; COL owner FOR A30 HEA 'Owner' TRUNC; COL name FOR A30 HEA 'Name' TRUNC; COL column_position FOR 999 HEA 'POS'; COL column_name FOR A30 TRUNC; -- PRO PRO PARTITION KEYS (dba_part_key_columns and dba_subpart_key_columns) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~ -- WITH /* PART_KEY_COLUMNS */ dba_tables_m AS ( SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_tables) */ t.owner, t.table_name FROM dba_tables t WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' ), dba_indexes_m AS ( SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_indexes) */ i.owner, i.index_name FROM dba_tables_m t, dba_indexes i WHERE i.table_owner = t.owner AND i.table_name = t.table_name ), objects_m AS ( SELECT owner, table_name AS name, 'TABLE' AS object_type FROM dba_tables_m UNION SELECT owner, index_name AS name, 'INDEX' AS object_type FROM dba_indexes_m ) SELECT 'PARTITION' AS part_sub, p.object_type, p.owner, p.name, p.column_position, p.column_name FROM dba_part_key_columns p, objects_m o WHERE o.owner = p.owner AND o.name = p.name AND o.object_type = p.object_type UNION ALL SELECT 'SUBPARTITION' AS part_sub, p.object_type, p.owner, p.name, p.column_position, p.column_name FROM dba_subpart_key_columns p, objects_m o WHERE o.owner = p.owner AND o.name = p.name AND o.object_type = p.object_type ORDER BY 1 ASC, 2 DESC, 3, 4, 5 / -- COL index_name FOR A30 HEA 'Index Name'; COL visibility FOR A10 HEA 'Visibility'; COL partitioned FOR A4 HEA 'Part'; COL column_position FOR 999 HEA 'Pos'; COL column_name FOR A30 HEA 'Column Name'; COL data_type FOR A33 HEA 'Data Type'; COL nullable FOR A8 HEA 'Nullable'; COL data_default FOR A30 HEA 'Data Default'; COL num_distinct FOR 999,999,999,990 HEA 'Num Distinct'; COL low_value_translated FOR A64 HEA 'Low Value Translated'; COL high_value_translated FOR A64 HEA 'High Value Translated'; COL density FOR 0.000000000 HEA 'Density'; COL num_nulls FOR 999,999,999,990 HEA 'Num Nulls'; COL num_buckets FOR 999,990 HEA 'Buckets'; COL histogram FOR A15 HEA 'Histogram'; COL sample_size FOR 999,999,999,990 HEA 'Sample Size'; COL last_analyzed FOR A19 HEA 'Last Analyzed'; COL avg_col_len FOR 999,999,990 HEA 'Avg Col Len'; COL data_length FOR 999,999,990 HEA 'Data Length'; COL char_length FOR 999,999,990 HEA 'Char Length'; -- BRE ON index_name SKIP 1 ON visibility ON partitioned; -- PRO PRO INDEX COLUMNS (dba_ind_columns) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~ WITH ic AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_ind_columns WHERE table_owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1), tc AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_tab_cols WHERE owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1), ix AS (SELECT /*+ MATERIALIZE NO_MERGE */ * FROM dba_indexes WHERE table_owner = '&&table_owner.' AND table_name = '&&table_name.' AND ROWNUM >= 1) SELECT i.index_name, x.visibility, x.partitioned, i.column_position, c.column_name, c.data_type, c.nullable, c.data_default data_default, c.num_distinct, CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.low_value)) WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.low_value)),1,64) WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.low_value)),1,64) WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.low_value)) WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.low_value)) WHEN c.data_type = 'DATE' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,13,2),'XX')-1,'00'))) WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,13,2),'XX')-1,'00'))||'.'|| to_number(substr(c.low_value,15,8),'XXXXXXXX')) END low_value_translated, CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.high_value)) WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.high_value)),1,64) WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.high_value)),1,64) WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.high_value)) WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.high_value)) WHEN c.data_type = 'DATE' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,13,2),'XX')-1,'00'))) WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,13,2),'XX')-1,'00'))||'.'|| to_number(substr(c.high_value,15,8),'XXXXXXXX')) END high_value_translated, c.density, c.num_nulls, c.num_buckets, c.histogram, c.sample_size, TO_CHAR(c.last_analyzed, '&&cs_datetime_full_format.') last_analyzed, c.avg_col_len, c.data_length, c.char_length FROM ic i, tc c, ix x WHERE i.table_owner = '&&table_owner.' AND i.table_name = '&&table_name.' AND c.owner = i.table_owner AND c.table_name = i.table_name AND c.column_name = i.column_name AND x.table_owner = i.table_owner AND x.table_name = i.table_name AND x.index_name = i.index_name ORDER BY i.index_name, i.column_position / -- CL BRE; -- COL column_id FOR 999 HEA 'ID'; COL column_name FOR A30 HEA 'Column Name'; COL data_type FOR A33 HEA 'Data Type'; COL nullable FOR A8 HEA 'Nullable'; COL data_default FOR A30 HEA 'Data Default'; COL num_distinct FOR 999,999,999,990 HEA 'Num Distinct'; COL low_value_translated FOR A64 HEA 'Low Value Translated'; COL high_value_translated FOR A64 HEA 'High Value Translated'; COL density FOR 0.000000000 HEA 'Density'; COL num_nulls FOR 999,999,999,990 HEA 'Num Nulls'; COL num_buckets FOR 999,990 HEA 'Buckets'; COL histogram FOR A15 HEA 'Histogram'; COL sample_size FOR 999,999,999,990 HEA 'Sample Size'; COL last_analyzed FOR A19 HEA 'Last Analyzed'; COL avg_col_len FOR 999,999,990 HEA 'Avg Col Len'; COL data_length FOR 999,999,990 HEA 'Data Length'; COL char_length FOR 999,999,990 HEA 'Char Length'; -- BRE ON owner ON table_name SKIP 1; -- PRO PRO TABLE COLUMNS (dba_tab_cols) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~ SELECT c.column_id, c.column_name, c.data_type, c.nullable, c.data_default data_default, c.num_distinct, CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.low_value)) WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.low_value)),1,64) WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.low_value)),1,64) WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.low_value)) WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.low_value)) WHEN c.data_type = 'DATE' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,13,2),'XX')-1,'00'))) WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.low_value,1,2) ,'XX')-100) + (to_number(substr(c.low_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.low_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.low_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.low_value,13,2),'XX')-1,'00'))||'.'|| to_number(substr(c.low_value,15,8),'XXXXXXXX')) END low_value_translated, CASE WHEN c.data_type = 'NUMBER' THEN to_char(utl_raw.cast_to_number(c.high_value)) WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN SUBSTR(to_char(utl_raw.cast_to_varchar2(c.high_value)),1,64) WHEN c.data_type IN ('NVARCHAR2','NCHAR') THEN SUBSTR(to_char(utl_raw.cast_to_nvarchar2(c.high_value)),1,64) WHEN c.data_type = 'BINARY_DOUBLE' THEN to_char(utl_raw.cast_to_binary_double(c.high_value)) WHEN c.data_type = 'BINARY_FLOAT' THEN to_char(utl_raw.cast_to_binary_float(c.high_value)) WHEN c.data_type = 'DATE' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,13,2),'XX')-1,'00'))) WHEN c.data_type LIKE 'TIMESTAMP%' THEN rtrim( ltrim(to_char(100*(to_number(substr(c.high_value,1,2) ,'XX')-100) + (to_number(substr(c.high_value,3,2) ,'XX')-100),'0000'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,5,2) ,'XX') ,'00'))||'-'|| ltrim(to_char( to_number(substr(c.high_value,7,2) ,'XX') ,'00'))||'T'|| ltrim(to_char( to_number(substr(c.high_value,9,2) ,'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,11,2),'XX')-1,'00'))||':'|| ltrim(to_char( to_number(substr(c.high_value,13,2),'XX')-1,'00'))||'.'|| to_number(substr(c.high_value,15,8),'XXXXXXXX')) END high_value_translated, c.density, c.num_nulls, c.num_buckets, c.histogram, c.sample_size, TO_CHAR(c.last_analyzed, '&&cs_datetime_full_format.') last_analyzed, c.avg_col_len, c.data_length, c.char_length FROM dba_tab_cols c WHERE c.owner = '&&table_owner.' AND c.table_name = '&&table_name.' ORDER BY c.column_id / -- CL BRE; -- COL column_name FOR A30 HEA 'Column Name'; COL index_name FOR A30 HEA 'Index Name'; COL segment_name FOR A30 HEA 'Segment Name'; COL bytes FOR 999,999,999,990 HEA 'Bytes'; COL blocks FOR 999,999,990 HEA 'Blocks'; COL size_MiB FOR 999,999,990.000 HEA 'Size MiB'; COL size_MB FOR 999,999,990.000 HEA 'Size MB'; COL deduplication FOR A13 HEA 'Deduplication'; COL compression FOR A11 HEA 'Compression'; COL encrypt FOR A7 HEA 'Encrypt'; COL cache FOR A5 HEA 'Cache'; COL securefile FOR A10 HEA 'SecureFile'; COL in_row FOR A6 HEA 'In Row'; COL tablespace_name FOR A30 HEA 'Tablespace'; -- BRE ON owner ON table_name SKIP 1; -- SET HEA OFF; PRO PRO COLUMN USAGE REPORT (dbms_stats.report_col_usage) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~~~~~~ SELECT DBMS_STATS.report_col_usage('&&table_owner.', '&&table_name.') FROM DUAL / SET HEA ON; -- PRO PRO LOBS (dba_lobs) PRO ~~~~ SELECT l.column_name, l.index_name, l.segment_name, SUM(s.bytes) AS bytes, SUM(s.blocks) AS blocks, --SUM(s.blocks) * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(2,20) AS size_MiB, SUM(s.blocks) * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB, l.deduplication, l.compression, l.encrypt, l.cache, l.securefile, l.in_row, l.tablespace_name FROM dba_lobs l, dba_segments s, dba_tablespaces b, v$parameter p WHERE l.owner = '&&table_owner.' AND l.table_name = '&&table_name.' AND s.owner = l.owner AND s.segment_name = l.segment_name AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') AND b.tablespace_name(+) = l.tablespace_name AND p.name = 'db_block_size' GROUP BY l.column_name, l.index_name, l.segment_name, l.deduplication, l.compression, l.encrypt, l.cache, l.securefile, l.in_row, l.tablespace_name, b.block_size, p.value ORDER BY l.column_name / -- CL BRE; -- COL owner FOR A30 HEA 'Owner' TRUNC; COL table_name FOR A30 HEA 'Table Name' TRUNC; COL index_name FOR A30 HEA 'Index Name' TRUNC; COL metadata FOR A200 HEA 'Metadata'; -- PRO PRO TABLE METADATA (DBMS_METADATA.get_ddl) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~ SELECT t.owner, t.table_name, DBMS_METADATA.get_ddl('TABLE', t.table_name, t.owner) AS metadata FROM dba_tables t WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' ORDER BY t.owner, t.table_name / -- PRO PRO INDEX METADATA (DBMS_METADATA.get_ddl) &&table_owner..&&table_name. PRO ~~~~~~~~~~~~~~ SELECT i.owner, i.table_name, i.index_name, DBMS_METADATA.get_ddl('INDEX', i.index_name, i.owner) AS metadata FROM dba_tables t, dba_indexes i WHERE t.owner = '&&table_owner.' AND t.table_name = '&&table_name.' AND i.table_owner = t.owner AND i.table_name = t.table_name ORDER BY i.owner, i.table_name, i.index_name / -- COL kievlive FOR A8 HEA 'KievLive'; -- PRO PRO KIEV LIVE (dba_tab_histograms) &&table_owner..&&table_name. PRO ~~~~~~~~~ COL num_rows FOR 999,999,999,990 HEA 'Num Rows'; SELECT SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(SUBSTR(LPAD(TO_CHAR(h.endpoint_value,'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),30,'0'),1,12)), 1, 8) kievlive, h.endpoint_number - LAG(h.endpoint_number, 1, 0) OVER (ORDER BY h.endpoint_value) num_rows FROM dba_tab_histograms h WHERE h.owner = '&&table_owner.' AND h.table_name = '&&table_name.' -- AND h.column_name = 'XXXX' ORDER BY 1 /
No comments:
Post a Comment