363 lines
18 KiB
MySQL
363 lines
18 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tables details
|
|
* Parameters : 1 - Owner (Use % as wild card, \ as ESCAPE)
|
|
* 2 - TableName (Use % as wild card, \ as ESCAPE)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 22-Aug-16 Vishal Gupta Added child foreign key constrainst list
|
|
* 30-Apr-15 Vishal Gupta Added join to dba_objects to get creation date
|
|
* 13-Mar-15 Vishal Gupta Added table's indexes in output
|
|
* 24-Apr-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE UPPER('&&table_name')
|
|
END "_table_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT ************************************
|
|
PROMPT * T A B L E D E T A I L S
|
|
PROMPT ************************************
|
|
|
|
COLUMN col1 FORMAT a60
|
|
COLUMN col2 FORMAT a60
|
|
|
|
set head off
|
|
SELECT 'Owner : ' || t.owner
|
|
|| chr(10) || 'Table Name : ' || t.table_name
|
|
|| chr(10) || 'Table Comments : ' || c.comments
|
|
|| chr(10) || 'Tablespace Name : ' || t.tablespace_name
|
|
&&_IF_ORA_10gR2_OR_HIGHER || chr(10) || 'Status : ' || t.status
|
|
|| chr(10) || 'Temporary : ' || t.temporary
|
|
|| chr(10) || 'Partitioned : ' || t.partitioned
|
|
|| chr(10) || 'Compression : ' || t.compression
|
|
&&_IF_ORA_11gR1_OR_HIGHER || chr(10) || 'Compress For : ' || t.compress_for
|
|
&&_IF_ORA_11gR2_OR_HIGHER || chr(10) || 'Segment Created : ' || t.segment_created
|
|
|| chr(10) || 'Degree : ' || TRIM(t.degree)
|
|
|| chr(10) || 'IOT Type : ' || t.iot_type
|
|
|| chr(10) || 'IOT Name : ' || t.iot_name
|
|
|| chr(10) || 'Logging : ' || t.logging
|
|
|| chr(10) || 'Backed Up : ' || t.backed_up
|
|
|| chr(10) || 'Instances : ' || TRIM(t.instances)
|
|
|| chr(10) || 'Table Lock : ' || t.table_lock
|
|
|| chr(10) || 'Secondary : ' || t.secondary
|
|
|| chr(10) || 'Nested : ' || t.nested
|
|
|| chr(10) || 'Row Movement : ' || t.row_movement
|
|
|| chr(10) || 'Duration : ' || t.duration
|
|
|| chr(10) || 'Skip Corrupt : ' || t.skip_corrupt
|
|
|| chr(10) || 'Monitoring : ' || t.monitoring
|
|
|| chr(10) || 'Cluster Owner : ' || t.cluster_owner
|
|
|| chr(10) || 'Cluster Name : ' || t.cluster_name
|
|
|| chr(10) || 'Dependencies : ' || t.dependencies
|
|
&&_IF_ORA_10gR1_OR_HIGHER || chr(10) || 'Dropped : ' || t.dropped
|
|
&&_IF_ORA_11gR1_OR_HIGHER || chr(10) || 'Read Only : ' || t.read_only
|
|
|| chr(10) || ' '
|
|
|| chr(10) || '[ Cache ] '
|
|
|| chr(10) || 'Cache : ' || TRIM(t.cache)
|
|
|| chr(10) || 'Buffer Pool : ' || t.buffer_pool
|
|
&&_IF_ORA_11gR2_OR_HIGHER || chr(10) || 'Flash Cache : ' || t.flash_cache
|
|
&&_IF_ORA_11gR2_OR_HIGHER || chr(10) || 'Cell Flash Cache : ' || t.cell_flash_cache
|
|
&&_IF_ORA_11gR2_OR_HIGHER || chr(10) || 'Result Cache : ' || t.result_cache
|
|
col1
|
|
,
|
|
'[ Dates ] '
|
|
|| chr(10) || 'Created : ' || TO_CHAR(o.created,'DD-MON-YYYY HH24:MI:SS')
|
|
|| chr(10) || 'Last DDL : ' || TO_CHAR(o.last_ddl_time,'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || 'Last Spec Change : ' || TO_CHAR(TO_DATE(o.timestamp,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || ' '
|
|
|| chr(10) || '[ Storage ] '
|
|
|| chr(10) || 'PCT Free : ' || t.pct_free
|
|
|| chr(10) || 'PCT Used : ' || t.pct_used
|
|
|| chr(10) || 'PCT Increase : ' || t.pct_increase
|
|
|| chr(10) || 'INI Trans : ' || t.ini_trans
|
|
|| chr(10) || 'Max Trans : ' || t.max_trans
|
|
|| chr(10) || 'Initial Extent Size : ' || t.initial_extent
|
|
|| chr(10) || 'Next Extent Size : ' || t.next_extent
|
|
|| chr(10) || 'Mininum Extents : ' || t.min_extents
|
|
|| chr(10) || 'Max Extents : ' || t.max_extents
|
|
|| chr(10) || 'Freelists : ' || t.freelists
|
|
|| chr(10) || 'Freelists Group : ' || t.freelist_groups
|
|
|| chr(10) || ' '
|
|
|| chr(10) || '[ Statistics ] '
|
|
|| chr(10) || 'Last Analyzed : ' || to_char(t.last_analyzed,'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || 'Global Stats : ' || t.global_stats
|
|
|| chr(10) || 'User Stats : ' || t.user_stats
|
|
|| chr(10) || 'Sample Size : ' || TRIM(TO_CHAR(t.sample_size,'999,999,999,999,999'))
|
|
|| chr(10) || 'Num Rows : ' || TRIM(TO_CHAR(t.num_rows,'999,999,999,999,999'))
|
|
|| chr(10) || 'Table Size : ' || TRIM(TO_CHAR((t.blocks * (select BLOCK_SIZE from dba_tablespaces tbs where tbs.tablespace_name = t.tablespace_name) )/power(1024,2),'999,999,999,999,999' )) || ' MB'
|
|
|| chr(10) || 'Blocks : ' || t.blocks
|
|
|| chr(10) || 'Empty Blocks : ' || t.empty_blocks
|
|
|| chr(10) || 'Average Row Length : ' || t.avg_row_len
|
|
|| chr(10) || 'Average Space : ' || t.avg_space
|
|
|| chr(10) || 'Chain Count : ' || t.chain_cnt
|
|
|| chr(10) || 'Freelist Blocks : ' || t.num_freelist_blocks
|
|
|| chr(10) || 'Avg Space Freelists Blocks: ' || t.avg_space_freelist_blocks
|
|
col2
|
|
FROM dba_tables t
|
|
JOIN dba_objects o ON o.owner = t.owner AND o.object_name = t.table_name AND o.object_type = 'TABLE'
|
|
LEFT OUTER JOIN dba_tab_comments c ON c.owner = t.owner AND c.table_name = t.table_name
|
|
WHERE t.owner = '&&owner'
|
|
AND t.table_name = '&&table_name'
|
|
;
|
|
set head on
|
|
|
|
|
|
/* Taken from tab_parts_summary.sql */
|
|
|
|
PROMPT *******************************
|
|
PROMPT * Table's Partitions Details
|
|
PROMPT *******************************
|
|
|
|
|
|
COLUMN table_name HEADING "Table Name" FORMAT a40
|
|
COLUMN partitioning_type HEADING "Part|Type" FORMAT a7
|
|
COLUMN partitioning_key_count HEADING "Part|Key|Count" FORMAT 99999
|
|
COLUMN interval HEADING "Interval" FORMAT a30
|
|
COLUMN subpartitioning_type HEADING "SubPart|Type" FORMAT a10
|
|
COLUMN subpartitioning_key_count HEADING "SubPart|Key|Count" FORMAT 99999
|
|
COLUMN def_subpartition_count HEADING "Default|Subpart|Count|PerPart" FORMAT 999
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN partition_count HEADING "Part|Count" FORMAT 999,999
|
|
COLUMN subpartition_count HEADING "SubPart|Count" FORMAT 999,999
|
|
COLUMN total_count HEADING "Total|Count" FORMAT 999,999
|
|
|
|
|
|
|
|
SELECT pt.owner || '.' || pt.table_name table_name
|
|
, pt.status
|
|
, pt.partitioning_type
|
|
, pt.partitioning_key_count
|
|
-- for interval RANGE partition, dba_part_tables.partition_count is always 1048575
|
|
-- , so we need to take actual partition count from dba_tab_partitions
|
|
, count(distinct p.partition_name) partition_count
|
|
, pt.interval
|
|
, pt.subpartitioning_type
|
|
, pt.subpartitioning_key_count
|
|
, pt.def_subpartition_count
|
|
, count(s.subpartition_name) subpartition_count
|
|
, GREATEST(count(distinct p.partition_name) , count(s.subpartition_name)) total_count
|
|
FROM dba_part_tables pt
|
|
JOIN dba_tab_partitions p ON p.table_owner = pt.owner AND p.table_name = pt.table_name
|
|
LEFT OUTER JOIN dba_tab_subpartitions s ON s.table_owner = pt.owner AND s.table_name = pt.table_name AND p.partition_name = s.partition_name
|
|
WHERE 1=1
|
|
AND pt.owner LIKE '&&owner' ESCAPE '\'
|
|
AND pt.table_name LIKE '&&table_name' ESCAPE '\'
|
|
GROUP BY
|
|
pt.owner
|
|
, pt.table_name
|
|
, pt.partitioning_type
|
|
, pt.interval
|
|
, pt.subpartitioning_type
|
|
, pt.def_subpartition_count
|
|
, pt.partitioning_key_count
|
|
, pt.subpartitioning_key_count
|
|
, pt.status
|
|
--ORDER BY total_count desc
|
|
ORDER BY pt.owner
|
|
, pt.table_name
|
|
;
|
|
|
|
|
|
|
|
|
|
/* Taken from tab_indexes.sql */
|
|
PROMPT
|
|
PROMPT ********************
|
|
PROMPT * Table's Indices
|
|
PROMPT ********************
|
|
|
|
COLUMN owner HEADING "Index Owner" FORMAT a20
|
|
COLUMN table_name HEADING "Table Name" FORMAT a40
|
|
COLUMN index_owner HEADING "Index Owner" FORMAT a20
|
|
COLUMN index_name HEADING "Index Name" FORMAT a40
|
|
COLUMN index_type HEADING "Index Type" FORMAT a10
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN visibility HEADING "Visible" FORMAT a10
|
|
COLUMN uniqueness HEADING "Uniqueness" FORMAT a15
|
|
COLUMN Columns HEADING "Index columns" FORMAT a90
|
|
|
|
BREAK ON TABLE_NAME
|
|
|
|
SELECT /*+ */
|
|
-- i.table_owner || '.' || i.table_name table_name
|
|
--,
|
|
i.owner || '.' || i.index_name index_name
|
|
, i.uniqueness
|
|
, i.index_type
|
|
, i.status
|
|
, i.visibility
|
|
-- , column_name
|
|
-- , column_position
|
|
-- , CONNECT_BY_ISLEAF
|
|
-- , LEVEL
|
|
--, SUBSTR(SYS_CONNECT_BY_PATH ( NVL(to_char(ie.column_expression),ic.column_name) || DECODE(ic.descend,'ASC','',' (desc) ') , ' , ' ),4) Columns
|
|
, SUBSTR(SYS_CONNECT_BY_PATH ( ic.column_name || DECODE(ic.descend,'ASC','',' (desc) ') , ' , ' ),4) Columns
|
|
FROM dba_indexes i
|
|
, dba_ind_columns ic
|
|
, dba_ind_expressions ie
|
|
WHERE i.owner = ic.index_owner
|
|
AND i.index_name = ic.index_name
|
|
AND i.table_owner = ic.table_owner
|
|
AND i.table_name = ic.table_name
|
|
AND ic.index_owner = ie.index_owner (+)
|
|
AND ic.index_name = ie.index_name (+)
|
|
AND ic.table_owner = ie.table_owner (+)
|
|
AND ic.table_name = ie.table_name (+)
|
|
AND ic.column_position = ie.column_position (+)
|
|
AND i.table_owner LIKE upper('&&owner') ESCAPE '\'
|
|
AND i.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
AND CONNECT_BY_ISLEAF = 1
|
|
AND LEVEL = ic.column_position
|
|
CONNECT BY ic.table_owner = PRIOR ic.table_owner
|
|
AND ic.table_name = PRIOR ic.table_name
|
|
AND ic.index_owner = PRIOR ic.index_owner
|
|
AND ic.index_name = PRIOR ic.index_name
|
|
AND ic.column_position - 1 = PRIOR ic.column_position
|
|
START WITH ic.table_owner LIKE upper('&&owner') ESCAPE '\'
|
|
AND ic.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
ORDER BY ic.table_owner
|
|
, ic.table_name
|
|
, ic.index_owner
|
|
, ic.index_name
|
|
;
|
|
|
|
|
|
-- Taken from columns.sql
|
|
/*
|
|
PROMPT
|
|
PROMPT ********************
|
|
PROMPT * Table's Columns
|
|
PROMPT ********************
|
|
|
|
COLUMN object_name HEADING "ObjectName" FORMAT a45
|
|
COLUMN object_type HEADING "ObjectType" FORMAT a17
|
|
COLUMN column_id HEADING "ColumnId" FORMAT 9999
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a30
|
|
COLUMN nullable HEADING "Null?" FORMAT a8
|
|
COLUMN Type HEADING "ColumnType" FORMAT a20
|
|
COLUMN data_default HEADING "Default|Value" FORMAT a40
|
|
COLUMN comments HEADING "Comments" FORMAT a60
|
|
|
|
|
|
SELECT
|
|
--c.owner || '.' || c.table_name object_name,
|
|
c.column_id
|
|
, c.column_name
|
|
, DECODE(c.nullable,'N','NOT NULL','') nullable
|
|
, CASE
|
|
WHEN c.data_type = 'NUMBER'
|
|
THEN c.data_type || NVL2(c.data_precision||c.data_scale, '(' || NVL(c.data_precision,'38') || ',' || NVL(c.data_scale,'127') || ')','')
|
|
WHEN c.data_type = 'FLOAT'
|
|
THEN c.data_type || NVL2(c.data_precision,'(' || c.data_precision || ')','')
|
|
WHEN c.data_type = 'VARCHAR2' OR c.data_type = 'CHAR'
|
|
THEN c.data_type || '(' || c.char_length || ' ' || DECODE(c.char_used,'B','BYTE','CHAR') || ')'
|
|
WHEN c.data_type = 'NVARCHAR2' OR c.data_type = 'NCHAR'
|
|
THEN c.data_type || '(' || c.char_length || ')'
|
|
WHEN c.data_type = 'RAW' OR c.data_type = 'UROWID'
|
|
THEN c.data_type || '(' || c.data_length || ')'
|
|
ELSE c.data_type
|
|
END Type
|
|
, c.data_default
|
|
, cc.comments
|
|
FROM dba_tab_columns c
|
|
LEFT OUTER JOIN dba_col_comments cc ON cc.owner = c.owner AND cc.table_name = c.table_name AND cc.column_name = c.column_name
|
|
WHERE UPPER(c.owner) like UPPER('&&owner') ESCAPE '\'
|
|
AND UPPER(c.table_name) like UPPER('&&table_name') ESCAPE '\'
|
|
AND c.table_name NOT LIKE 'BIN$%'
|
|
ORDER BY c.column_id ;
|
|
*/
|
|
|
|
|
|
PROMPT
|
|
PROMPT ****************************
|
|
PROMPT * Table's Constraints
|
|
PROMPT ****************************
|
|
|
|
COLUMN constraint_name HEADING "ConstraintName" FORMAT a30
|
|
COLUMN constraint_type HEADING "Type" FORMAT a4
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN delete_rule HEADING "Delete|Rule" FORMAT a9
|
|
COLUMN validated HEADING "Validated"
|
|
COLUMN generated HEADING "Generated"
|
|
COLUMN last_change HEADING "LastChange" FORMAT a18
|
|
COLUMN search_condition HEADING "SearchCondition" FORMAT a50
|
|
|
|
select *
|
|
from
|
|
xmltable( '/ROWSET/ROW'
|
|
passing dbms_xmlgen.getXMLType('
|
|
SELECT c.constraint_name
|
|
, c.constraint_type
|
|
, c.status
|
|
, c.delete_rule
|
|
, c.validated
|
|
, c.generated
|
|
, TO_CHAR(last_change,''DD-MON-YY HH24:MI:SS'') last_change
|
|
, c.search_condition /* <---- Long Column type*/
|
|
FROM dba_constraints c
|
|
WHERE UPPER(c.owner) like UPPER(''' || '&&owner' || ''') ESCAPE ''\''
|
|
AND UPPER(c.table_name) like UPPER(''' || '&&table_name' || ''') ESCAPE ''\''
|
|
')
|
|
columns
|
|
constraint_name varchar2(30)
|
|
, constraint_type varchar2(30)
|
|
, status varchar2(30)
|
|
, delete_rule varchar2(9)
|
|
, validated varchar2(30)
|
|
, generated varchar2(30)
|
|
, last_change varchar2(30)
|
|
, search_condition varchar2(2000)
|
|
)
|
|
WHERE 1=1
|
|
AND NOT (constraint_type = 'C' AND search_condition LIKE '%NOT NULL%' )
|
|
;
|
|
|
|
PROMPT
|
|
PROMPT ********************************************
|
|
PROMPT * Child Foreign Key Constraints
|
|
PROMPT ********************************************
|
|
|
|
|
|
SELECT c.owner
|
|
, c.constraint_name
|
|
, c.table_name
|
|
, c.r_constraint_name
|
|
, c.delete_rule
|
|
FROM dba_constraints c
|
|
WHERE (c.r_owner, c.r_constraint_name) IN (SELECT c2.owner, c2.constraint_name
|
|
FROM dba_constraints c2
|
|
WHERE UPPER(c2.owner) like UPPER('&&owner') ESCAPE '\'
|
|
AND UPPER(c2.table_name) like UPPER('&&table_name') ESCAPE '\'
|
|
AND c2.constraint_type in ('P','U')
|
|
)
|
|
;
|
|
|
|
@@footer
|