support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/csierra/cs_table.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

818 lines
31 KiB
SQL
Raw Permalink Blame History

----------------------------------------------------------------------------------------
--
-- File name: cs_table.sql
--
-- Purpose: Table Details
--
-- Author: Carlos Sierra
--
-- Version: 2023/01/20
--
-- Usage: Execute connected to PDB.
--
-- Enter table owner and table name when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_table.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
@@cs_internal/cs_cdb_warn.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_table';
--
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
/
PRO
PRO 2. Table Name:
DEF table_name = '&2.';
UNDEF 2;
COL table_name NEW_V table_name;
SELECT UPPER(TRIM(NVL('&&table_name.', '&&name.'))) table_name FROM DUAL;
--
SELECT '&&cs_file_prefix._&&cs_script_name._&&table_owner..&&table_name.' cs_file_name FROM DUAL;
--
@@cs_internal/cs_spool_head.sql
PRO SQL> @&&cs_script_name..sql "&&table_owner." "&&table_name."
@@cs_internal/cs_spool_id.sql
--
PRO TABLE_OWNER : &&table_owner.
PRO TABLE_NAME : &&table_name.
--
DEF specific_owner = '&&table_owner.';
DEF specific_table = '&&table_name.';
DEF order_by = 't.owner, t.table_name';
DEF fetch_first_N_rows = '1';
PRO
PRO SUMMARY &&table_owner..&&table_name.
PRO ~~~~~~~
@@cs_internal/cs_tables_internal.sql
--
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 num_rows FOR 999,999,999,990 HEA 'Num Rows';
COL kievlive FOR A8 HEA 'KievLive';
--
PRO
PRO KIEV LIVE (dba_tab_histograms) &&table_owner..&&table_name.
PRO ~~~~~~~~~
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 = 'KIEVLIVE'
ORDER BY
1
/
--
DEF cs_num_rows_limit_display = '1B';
DEF cs_num_rows_limit_number = '1e9';
@@cs_internal/cs_top_primary_keys_table.sql
@@cs_internal/cs_top_secondary_keys_table.sql
--
PRO
PRO SQL> @&&cs_script_name..sql "&&table_owner." "&&table_name."
--
@@cs_internal/cs_spool_tail.sql
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--
Reference in New Issue View Git Blame Copy Permalink