@@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