Table structure: columns, constraints (primary, foreign, unique), triggers, defaults etc.
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- -------------------- ----------------- ------------------- ---------- --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 DEV1 linux001 11.2.0.4.0 25/09/2015 19:45:24 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
1 row selected.
Found - Owner: DEVELOPMENT01 Table: CHECK_ENVIRONMENTS Created: 10/06/2015 09:34:43
Table:
Partitioned: NO
Monitored : YES
Columns:
Column Data Type NULLABE Length Precision
------ --------- ------- ------ ---------
ID NUMBER NOT NULL 22
FULFILLMNTID NUMBER NOT NULL 22
DB_LINK_ID NUMBER NOT NULL 22
ENV_TYPE_ID NUMBER NOT NULL 22
PRC_ORDER NUMBER NOT NULL 22
OWNER VARCHAR2 30
DESCRIPTION VARCHAR2 NOT NULL 100
SHORT_NAME VARCHAR2 NOT NULL 30
OPT_TEST_AGAINST VARCHAR2 NOT NULL 1
OPT_SEARCH_AGAINST VARCHAR2 NOT NULL 1
OPT_ROLLBACK_IN VARCHAR2 1
STATUS VARCHAR2 30
CREATED_DATE DATE 7
CREATED_BY VARCHAR2 30
UPDATED_DATE DATE 7
UPDATED_BY VARCHAR2 30
Defaults:
Column Default
------ -------
OPT_TEST_AGAINST 'Y'
OPT_SEARCH_AGAINST 'Y'
OPT_ROLLBACK_IN 'Y'
STATUS 'ACTIVE'
Primary Key : CHECK_ENVIRONMENTS_PK (Index: DEVELOPMENT01.CHECK_ENVIRONMENTS_PK)
Columns: ID
Foreign Keys:
Constraint Owner Table Status Generated Deferrable Columns
---------- ----- ----- ------ --------- ---------- -------
CHECK_ENVIRONMENTS_FK1 . CHECK_BUSINESS_PROC ENABLED USER NAME NOT DEFERRAB FULFILLMNTID => ID
CHECK_ENVIRONMENTS_FK2 . CHECK_ENVIRONMENT_TYPES ENABLED USER NAME NOT DEFERRAB ENV_TYPE_ID => ID
CHECK_ENVIRONMENTS_FK3 . CHECK_DB_LINKS ENABLED USER NAME NOT DEFERRAB DB_LINK_ID => ID
Attached Foreign Keys:
Owner Table Constraint Status Generated Delete Rule Columns
----- ----- ---------- ------ --------- ----------- -------
. CHECK_ENVIRONMENT_ACCESS CHECK_ENVIRONMENT_ACCESS_FK1 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_ENV_DEFAULTS_USED CHECK_ENV_DEFAULTS_USED_FK5 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_ERROR_DESCRIPTIONS CHECK_ERROR_DESCRIPTIONS_FK2 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_HELP CHECK_HELP_FK3 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_ITEM_ENV_STATUS CHECK_ITEM_ENV_STATUS_FK3 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_PROCESSS CHECK_PROCESSS_FK2 ENABLED USER NAME NO ACTION CURRENT_ENV_ID
. CHECK_PROCESS_ENV_STATUS CHECK_PROCESS_ENV_STATUS_FK2 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_PROCESS_RUNS CHECK_PROCESS_RUNS_FK2 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_SEARCH_DEFAULTS_USED CHECK_SEARCH_DEFAULTS_USED_FK5 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_SEQUENCE_OWNERS CHECK_SEQUENCE_OWNERS_FK2 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
. CHECK_TABLE_OWNERS CHECK_TABLE_OWNERS_FK2 ENABLED USER NAME NO ACTION ENVIRONMENT_ID
Unique Keys:
Constraint Status Generated Columns
---------- ------ --------- -------
CHECK_ENVIRONMENTS_UC1 ENABLED USER NAME FULFILLMNTID, PRC_ORDER
CHECK_ENVIRONMENTS_UC2 ENABLED USER NAME FULFILLMNTID, ENV_TYPE_ID
CHECK_ENVIRONMENTS_UC4 ENABLED USER NAME FULFILLMNTID, DESCRIPTION
CHECK_ constraints:
Constraint Status Generated Search Condition
---------- ------ --------- ----------------
CHECK_ENVIRONMENTS_CHK1 ENABLED USER NAME OPT_test_against in ( 'Y', 'N' ) Cols:
CHECK_ENVIRONMENTS_CHK2 ENABLED USER NAME OPT_search_against in ( 'Y', 'N' ) Cols:
CHECK_ENVIRONMENTS_CHK3 ENABLED USER NAME OPT_rollback_in in ( 'Y', 'N' ) Cols:
CHECK_ENVIRONMENTS_CHK4 ENABLED USER NAME status in ( 'ACTIVE', 'INACTIVE' ) Cols:
SYS_C0019955 ENABLED GENERATED "ID" IS NOT NULL Cols:
SYS_C0019956 ENABLED GENERATED "FULFILLMNTID" IS NOT NULL Cols:
SYS_C0019957 ENABLED GENERATED "DB_LINK_ID" IS NOT NULL Cols:
SYS_C0019958 ENABLED GENERATED "ENV_TYPE_ID" IS NOT NULL Cols:
SYS_C0019959 ENABLED GENERATED "PRC_ORDER" IS NOT NULL Cols:
SYS_C0019960 ENABLED GENERATED "DESCRIPTION" IS NOT NULL Cols:
SYS_C0019961 ENABLED GENERATED "SHORT_NAME" IS NOT NULL Cols:
SYS_C0019962 ENABLED GENERATED "OPT_TEST_AGAINST" IS NOT NULL Cols:
SYS_C0019963 ENABLED GENERATED "OPT_SEARCH_AGAINST" IS NOT NULL Cols:
No other constraints.
Indexes:
CHECK_ENVIRONMENTS_PK (Type: NORMAL Status: VALID) [UNIQUE] ( ID )
CHECK_ENVIRONMENTS_UC1 (Type: NORMAL Status: VALID) [UNIQUE] ( FULFILLMNTID PRC_ORDER )
CHECK_ENVIRONMENTS_UC2 (Type: NORMAL Status: VALID) [UNIQUE] ( FULFILLMNTID ENV_TYPE_ID )
CHECK_ENVIRONMENTS_UC4 (Type: NORMAL Status: VALID) [UNIQUE] ( FULFILLMNTID DESCRIPTION )
Triggers:
DEVELOPMENT01.DTM_ENVS_BEF_INS
Event: INSERT
Status: ENABLED
When:
DEVELOPMENT01.DTM_ENVS_BEF_UPD
Event: UPDATE
Status: ENABLED
When:
Segments: table/table partitions
Segment Partition Tablespace Num Rows Blocks Empty Av Spc Av Row L Sample Size Analyzed Size
CHECK_ENVIRONMENTS DEV_001 173 8 0 0 99 173 17/07/2015 23:02 65,536 b
Segments: index/index partitions
Segment Partition Tablespace Num Rows Blevel Leaf Blk Dist Keys Sample Sz Analyzed Size
CHECK_ENVIRONMENTS_PK DEV_001 173 0 1 173 173 17/07/2015 23:02 65,536 b
CHECK_ENVIRONMENTS_UC1 DEV_001 173 0 1 173 173 17/07/2015 23:02 65,536 b
CHECK_ENVIRONMENTS_UC2 DEV_001 173 0 1 173 173 17/07/2015 23:02 65,536 b
CHECK_ENVIRONMENTS_UC4 DEV_001 173 0 1 173 173 17/07/2015 23:02 65,536 b
PL/SQL procedure successfully completed.
set feed off
prompt
prompt
prompt
prompt
prompt Enter # for current user
prompt
accept show_tables prompt 'List tables [N] : ' default 'N'
accept inp_owner prompt 'Table Owner: '
declare
cursor c_tabs is
select table_name from dba_tables
where owner = decode( '&&inp_owner', '#', user, upper( '&&inp_owner' ) )
order by table_name;
ls_output varchar2( 250 ) := '';
ln_counter number := 0;
begin
if upper( '&show_tables' ) <> 'Y' then
return;
end if;
for i in c_tabs loop
if length( ls_output ) > 100 then
dbms_output.put_line( ls_output );
ls_output := '';
ln_counter := ln_counter + 1;
end if;
ls_output := ls_output || i.table_name || ' - ';
if ln_counter > 20 then
exit;
end if;
end loop;
dbms_output.put_line( ls_output );
end;
/
accept inp_table_name prompt 'Table Name: '
set feed on
spool tlinks.rpt
select * from v$instance;
prompt
prompt
declare
ls_owner varchar2( 30 );
ls_table varchar2( 30 ) := upper( '&&inp_table_name' );
ls_cons_columns varchar2( 1000 );
ls_search_condition varchar2( 32000 );
ls_null varchar2( 15 );
lt_dba_constraint dba_constraints%rowtype;
lt_obj dba_objects%rowtype;
cursor c_obj is select * from dba_objects where owner = ls_owner and object_name = ls_table and object_type = 'TABLE';
cursor c_tab_cols is select * from dba_tab_columns where owner = ls_owner and table_name = ls_table order by column_id;
cursor c_constraint( i_owner in varchar2, i_constraint in varchar2 ) is select * from dba_constraints where owner = i_owner and constraint_name = i_constraint;
cursor c_constraints( i_type in varchar2 ) is select * from dba_constraints where owner = ls_owner and table_name = ls_table and constraint_type = i_type order by constraint_name;
cursor c_cons_columns( i_owner in varchar2, i_constraint in varchar2 ) is select * from dba_cons_columns where owner = i_owner and constraint_name = i_constraint order by position;
cursor c_att_fks is
select * from dba_constraints
where r_owner = ls_owner
and r_constraint_name in
( select unique constraint_name
from dba_cons_columns
where owner = ls_owner
and table_name = ls_table )
order by table_name;
cursor c_other_constraints is
select * from dba_constraints
where owner = ls_owner
and table_name = ls_table
and constraint_type not in ( 'R', 'P', 'U', 'C' )
order by table_name, constraint_name;
cursor c_indexes_c is select count(*) from dba_indexes where owner = ls_owner and table_name = ls_table order by index_name;
cursor c_indexes( x_index in varchar2 default null ) is
select * from dba_indexes
where table_owner = ls_owner
and table_name = ls_table
and ( x_index is null or index_name = x_index )
order by index_name;
lt_index dba_indexes%rowtype;
cursor c_ind_funcs( x_owner in varchar2, x_table in varchar2, x_index in varchar2 ) is
select *
from dba_ind_expressions
where index_owner = x_owner
and table_name = x_table
and index_name = x_index
order by column_position;
cursor c_ind_cols( x_owner in varchar2, x_index in varchar2 ) is select * from dba_ind_columns where index_owner = x_owner and index_name = x_index order by column_position;
cursor c_triggers_c is select count(*) from dba_triggers where table_owner = ls_owner and table_name = ls_table order by trigger_name;
cursor c_triggers is select * from dba_triggers where table_owner = ls_owner and table_name = ls_table order by trigger_name;
cursor c_table is select * from dba_tables where owner = ls_owner and table_name = ls_table;
lt_table dba_tables%rowtype;
cursor c_tab_parts ( x_part in varchar2 ) is
select *
from dba_tab_partitions
where table_owner = ls_owner
and table_name = ls_table
and partition_name = x_part;
lt_tab_part dba_tab_partitions%rowtype;
cursor c_ind_parts ( x_index in varchar2, x_part in varchar2 ) is
select *
from dba_ind_partitions
where index_owner = ls_owner
and index_name = x_index
and partition_name = x_part;
lt_ind_part dba_ind_partitions%rowtype;
cursor c_segments_t is
select a.*
from dba_segments a,
dba_tab_partitions b
where owner = ls_owner
and a.segment_name = ls_table
and a.segment_type in ( 'TABLE', 'TABLE PARTITION' )
and b.table_owner (+)= a.owner
and b.table_name (+)= a.segment_name
and b.partition_name (+)= a.partition_name
order by nvl( b.partition_position, 1 ), a.segment_type, a.segment_name, a.partition_name;
cursor c_segments_i is
select *
from dba_segments
where owner = ls_owner
and segment_type in ( 'INDEX', 'INDEX PARTITION' )
and segment_name in ( select index_name from dba_indexes
where owner = ls_owner
and table_name = ls_table
)
order by segment_type, segment_name, partition_name;
ln_count number := 0;
ls_outputline varchar2( 2000 );
ln_temp_counter number := 0;
begin
if '&&inp_owner' = '#' then
ls_owner := user;
else
ls_owner := upper( '&&inp_owner' );
end if;
open c_obj;
fetch c_obj into lt_obj;
if c_obj%notfound then
close c_obj;
dbms_output.put_line( 'Table not found: ' || chr(127) || ls_owner || chr(127) || ' ' || chr(127) || ls_table || chr(127) );
return;
end if;
close c_obj;
dbms_output.put_line( 'Found - Owner: ' || lt_obj.owner || ' ' || 'Table: ' || lt_obj.object_name || ' ' || 'Created: ' || to_char( lt_obj.created, 'dd/mm/yyyy hh24:mi:ss' ) );
dbms_output.put_line( '' );
dbms_output.put_line( 'Table:' );
open c_table; fetch c_table into lt_table; close c_table;
if lt_table.iot_name is not null then
dbms_output.put_line( 'IOT name : ' || lt_table.iot_name );
end if;
dbms_output.put_line( 'Partitioned: ' || lt_table.partitioned );
dbms_output.put_line( 'Monitored : ' || lt_table.monitoring );
dbms_output.put_line( '' );
dbms_output.put_line( '' );
dbms_output.put_line( 'Columns:' );
ln_count := 0;
dbms_output.put_line( rpad( 'Column', 30 ) || ' ' || rpad( 'Data Type', 15 ) || ' ' || 'NULLABE ' || ' ' || ' Length' || ' ' || ' Precision' );
dbms_output.put_line( rpad( '------', 30 ) || ' ' || rpad( '---------', 15 ) || ' ' || '------- ' || ' ' || ' ------' || ' ' || ' ---------' );
for i in c_tab_cols loop
ls_null := ' ';
if i.nullable = 'N' then
ls_null := 'NOT NULL';
end if;
dbms_output.put_line( rpad( i.column_name, 30 ) || ' ' || rpad( i.data_type, 15 ) || ' ' || ls_null || ' ' || to_char( i.data_length, '999999999' ) || ' ' || to_char( i.data_precision, '999999999' ) );
end loop;
if ln_count = 0 then
dbms_output.put_line( 'No defaults found' || chr(10) );
else
dbms_output.put_line( chr(10) );
end if;
dbms_output.put_line( '' );
dbms_output.put_line( 'Defaults:' );
ln_count := 0;
for i in c_tab_cols loop
if i.data_default is not null then
ln_count := ln_count +1;
if ln_count = 1 then
dbms_output.put_line( rpad( 'Column', 30 ) || ' ' || 'Default' );
dbms_output.put_line( rpad( '------', 30 ) || ' ' || '-------' );
end if;
dbms_output.put_line( rpad( i.column_name, 30 ) || ' ' || i.data_default );
end if;
end loop;
if ln_count = 0 then
dbms_output.put_line( 'No defaults found' || chr(10) );
else
dbms_output.put_line( chr(10) );
end if;
-- Primary Key
-- -----------
ln_temp_counter := 0;
for i in c_constraints( 'P' ) loop
ln_temp_counter := ln_temp_counter + 1;
dbms_output.put_line( 'Primary Key : ' || i.constraint_name || ' (Index: ' || i.index_owner || '.' || i.index_name || ')');
ls_outputline := 'Columns: ';
for j in c_cons_columns( ls_owner, i.constraint_name ) loop
ls_outputline := ls_outputline || ' ' || j.column_name;
end loop;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No primary key.' );
end if;
dbms_output.put_line( '' );
-- Foreign Keys From Table
-- -----------------------
ln_temp_counter := 0;
for i in c_constraints( 'R' ) loop
ln_temp_counter := ln_temp_counter + 1;
if ln_temp_counter = 1 then
dbms_output.put_line( 'Foreign Keys:' );
dbms_output.put_line( rpad( 'Constraint', 30 ) || ' ' || rpad( 'Owner', 30 ) || ' ' || rpad( 'Table', 30 ) || ' ' || rpad( 'Status', 12 ) || ' ' || rpad( 'Generated', 12 ) || ' ' || rpad( 'Deferrable', 12 ) || ' ' || rpad( 'Columns', 30 ) );
dbms_output.put_line( rpad( '----------', 30 ) || ' ' || rpad( '-----', 30 ) || ' ' || rpad( '-----', 30 ) || ' ' || rpad( '------', 12 ) || ' ' || rpad( '---------', 12 ) || ' ' || rpad( '----------', 12 ) || ' ' || rpad( '-------', 30 ) );
end if;
open c_constraint( i.r_owner, i.r_constraint_name ); fetch c_constraint into lt_dba_constraint; close c_constraint;
ls_outputline := rpad( i.constraint_name, 30 ) || ' ';
if i.r_owner = ls_owner then
ls_outputline := ls_outputline || rpad( '.', 30 ) || ' ';
else
ls_outputline := ls_outputline || rpad( i.r_owner, 30 ) || ' ';
end if;
ls_outputline := ls_outputline ||
rpad( lt_dba_constraint.table_name, 30 ) || ' ' ||
rpad( i.status, 12 ) || ' ' ||
rpad( i.generated, 12 ) || ' ' ||
rpad( i.deferrable, 12 );
for j in c_cons_columns( i.owner, i.constraint_name ) loop
if j.position = 1 then
ls_outputline := ls_outputline || ' ' || j.column_name;
else
ls_outputline := ls_outputline || ', ' || j.column_name;
end if;
end loop;
ls_outputline := ls_outputline || ' => ';
for j in c_cons_columns( i.r_owner, i.r_constraint_name ) loop
if j.position = 1 then
ls_outputline := ls_outputline || ' ' || j.column_name;
else
ls_outputline := ls_outputline || ', ' || j.column_name;
end if;
end loop;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No foreign keys.' );
end if;
dbms_output.put_line( '' );
-- Foreign keys attaching to table
-- -------------------------------
ln_temp_counter := 0;
for i in c_att_fks loop
ln_temp_counter := ln_temp_counter + 1;
if ln_temp_counter = 1 then
dbms_output.put_line( '' );
dbms_output.put_line( 'Attached Foreign Keys:' );
dbms_output.put_line( rpad( 'Owner', 30 ) || ' ' || rpad( 'Table', 30 ) || ' ' || rpad( 'Constraint', 30 ) || ' ' || rpad( 'Status', 12 ) || ' ' || rpad( 'Generated', 12 ) || ' ' || rpad( 'Delete Rule', 12 ) || ' ' || 'Columns' );
dbms_output.put_line( rpad( '-----', 30 ) || ' ' || rpad( '-----', 30 ) || ' ' || rpad( '----------', 30 ) || ' ' || rpad( '------', 12 ) || ' ' || rpad( '---------', 12 ) || ' ' || rpad( '-----------', 12 ) || ' ' || '-------' );
end if;
if i.owner = ls_owner then
ls_outputline := rpad( '.', 30 ) || ' ';
else
ls_outputline := rpad( i.owner, 30 ) || ' ';
end if;
ls_outputline := ls_outputline ||
rpad( i.table_name, 30 ) || ' ' ||
rpad( i.constraint_name, 30 ) || ' ' ||
rpad( i.status, 12 ) || ' ' ||
rpad( i.generated, 12 ) || ' ' ||
rpad( i.delete_rule, 12 );
for j in c_cons_columns( i.owner, i.constraint_name ) loop
if j.position = 1 then
ls_outputline := ls_outputline || ' ' || j.column_name;
else
ls_outputline := ls_outputline || ', ' || j.column_name;
end if;
end loop;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No attached foreign keys.' );
end if;
dbms_output.put_line( '' );
-- Unique Keys On Table
-- --------------------
ln_temp_counter := 0;
for i in c_constraints( 'U' ) loop
ln_temp_counter := ln_temp_counter + 1;
if ln_temp_counter = 1 then
dbms_output.put_line( 'Unique Keys:' );
dbms_output.put_line( rpad( 'Constraint', 30 ) || ' ' || rpad( 'Status', 12 ) || ' ' || rpad( 'Generated', 12 ) || ' ' || rpad( 'Columns', 30 ) );
dbms_output.put_line( rpad( '----------', 30 ) || ' ' || rpad( '------', 12 ) || ' ' || rpad( '---------', 12 ) || ' ' || rpad( '-------', 30 ) );
end if;
ls_outputline := rpad( i.constraint_name, 30 ) || ' ';
ls_outputline := ls_outputline ||
rpad( i.status, 12 ) || ' ' ||
rpad( i.generated, 12 );
for j in c_cons_columns( i.owner, i.constraint_name ) loop
if j.position = 1 then
ls_outputline := ls_outputline || ' ' || j.column_name;
else
ls_outputline := ls_outputline || ', ' || j.column_name;
end if;
end loop;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No unique keys.' );
end if;
dbms_output.put_line( '' );
-- Check Constraints
-- --------------------
ln_temp_counter := 0;
for i in c_constraints( 'C' ) loop
ln_temp_counter := ln_temp_counter + 1;
if ln_temp_counter = 1 then
dbms_output.put_line( 'Check constraints:' );
dbms_output.put_line( rpad( 'Constraint', 30 ) || ' ' || rpad( 'Status', 12 ) || ' ' || rpad( 'Generated', 10 ) || ' ' || 'Search Condition' );
dbms_output.put_line( rpad( '----------', 30 ) || ' ' || rpad( '------', 12 ) || ' ' || rpad( '---------', 10 ) || ' ' || '----------------' );
end if;
for j in c_cons_columns( i.owner, i.constraint_name ) loop
if j.position = 1 then
ls_outputline := ls_outputline || ' ' || j.column_name;
else
ls_outputline := ls_outputline || ', ' || j.column_name;
end if;
end loop;
ls_search_condition := replace( replace( i.search_condition, chr(13), '' ), chr(10), '' );
while instr( ls_search_condition, ' ' ) > 0 loop
ls_search_condition := replace( ls_search_condition, ' ', ' ' );
end loop;
ls_outputline := rpad( i.constraint_name, 30 ) || ' ' || rpad( i.status, 12 ) || ' ' || rpad( i.generated, 10 ) || ' ' || ls_search_condition || ' Cols: ' || ls_cons_columns;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No check constraints.' );
end if;
dbms_output.put_line( '' );
-- Other constraints
-- -----------------
ln_temp_counter := 0;
for i in c_other_constraints loop
ln_temp_counter := ln_temp_counter + 1;
if ln_temp_counter = 1 then
dbms_output.put_line( 'Other Constraints:' );
dbms_output.put_line( rpad( 'Constraint', 30 ) || ' ' || rpad( 'Status', 12 ) || ' ' || rpad( 'Generated', 10 ) || ' ' || 'Search Condition' );
dbms_output.put_line( rpad( '----------', 30 ) || ' ' || rpad( '------', 12 ) || ' ' || rpad( '---------', 10 ) || ' ' || '----------------' );
end if;
ln_count := 0;
ls_cons_columns := ' ';
for j in c_cons_columns( ls_owner, i.constraint_name ) loop
ln_count := ln_count + 1;
ls_cons_columns := ls_cons_columns || j.column_name || ', ';
end loop;
if ln_count > 0 then
ls_cons_columns := substr( ls_cons_columns, 1, length( ls_cons_columns ) - 2 );
end if;
ls_outputline := rpad( i.constraint_name, 30 ) || ' ' || rpad( i.status, 12 ) || ' ' || rpad( i.generated, 10 ) || ' ' || i.search_condition || ' Cols: ' || ls_cons_columns;
dbms_output.put_line( ls_outputline );
end loop;
if ln_temp_counter = 0 then
dbms_output.put_line( 'No other constraints.' );
end if;
dbms_output.put_line( '' );
open c_indexes_c;
fetch c_indexes_c into ln_count;
close c_indexes_c;
if ln_count = 0 then
dbms_output.put_line( 'No indexes.' || chr(10) );
else
dbms_output.put_line( 'Indexes:' );
for i in c_indexes loop
if i.index_type = 'FUNCTION-BASED NORMAL' then
dbms_output.put_line( i.index_name || ' (Type: ' || i.index_type || '):');
for j in c_ind_funcs( i.owner, i.table_name, i.index_name ) loop
dbms_output.put_line( to_char( j.column_position, '9999' ) || ' ' || j.column_expression );
end loop;
else
ls_outputline := rpad( i.index_name, 30 ) ||
' (Type: ' || i.index_type || ' Status: ' || i.status || ')' ||
' [' || i.uniqueness || '] ( ';
for j in c_ind_cols( i.owner, i.index_name ) loop
ls_outputline := ls_outputline || j.column_name || ' ';
end loop;
ls_outputline := ls_outputline || ' )';
dbms_output.put_line( ls_outputline );
end if;
dbms_output.put_line( '' );
end loop;
end if;
open c_triggers_c; fetch c_triggers_c into ln_count; close c_triggers_c;
if ln_count = 0 then
dbms_output.put_line( 'No triggers.' || chr(10) );
else
dbms_output.put_line( 'Triggers:' );
for i in c_triggers loop
dbms_output.put_line( i.owner || '.' || i.trigger_name );
dbms_output.put_line( ' Event: ' || i.triggering_event );
dbms_output.put_line( ' Status: ' || i.status );
dbms_output.put_line( ' When: ' || i.when_clause );
end loop;
end if;
dbms_output.put_line( '' );
dbms_output.put_line( 'Segments: table/table partitions' );
ls_outputline := rpad( 'Segment', 30 ) || ' ' ||
rpad( 'Partition', 10 ) || ' ' ||
rpad( 'Tablespace', 10 ) || ' ' ||
lpad( 'Num Rows', 15 ) || ' ' ||
lpad( 'Blocks', 8 ) || ' ' ||
lpad( 'Empty', 8 ) || ' ' ||
lpad( 'Av Spc', 8 ) || ' ' ||
lpad( 'Av Row Len', 8 ) || ' ' ||
lpad( 'Sample Size', 12 ) || ' ' ||
rpad( 'Analyzed', 16 ) || ' ' ||
lpad( 'Size', 8 );
dbms_output.put_line( ls_outputline );
for i in c_segments_t loop
ls_outputline := rpad( i.segment_name, 30 ) || ' ' ||
rpad( nvl( i.partition_name, ' ' ), 10 ) || ' ' ||
rpad( i.tablespace_name, 10 );
if i.segment_type = 'TABLE PARTITION' then
open c_tab_parts ( i.partition_name );
fetch c_tab_parts into lt_tab_part;
close c_tab_parts;
ls_outputline := ls_outputline || nvl( to_char( lt_tab_part.num_rows, '999,999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.empty_blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.avg_space, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.avg_row_len, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.sample_size, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_tab_part.last_analyzed, 'dd/mm/yyyy hh24:mi' ), ' ' );
else
ls_outputline := ls_outputline || nvl( to_char( lt_table.num_rows, '999,999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.empty_blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.avg_space, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.avg_row_len, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.sample_size, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_table.last_analyzed, 'dd/mm/yyyy hh24:mi' ), ' ' );
end if;
if i.bytes > (1024*1024) then
ls_outputline := ls_outputline || to_char( i.bytes/(1024*1024), '999,999' ) || ' MB';
else
ls_outputline := ls_outputline || to_char( i.bytes, '999,999' ) || ' b';
end if;
dbms_output.put_line( ls_outputline );
end loop;
dbms_output.put_line( '' );
dbms_output.put_line( 'Segments: index/index partitions' );
ls_outputline := rpad( 'Segment', 30 ) || ' ' ||
rpad( 'Partition', 10 ) || ' ' ||
rpad( 'Tablespace', 10 ) || ' ' ||
lpad( 'Num Rows', 15 ) || ' ' ||
lpad( 'Blevel', 8 ) || ' ' ||
lpad( 'Leaf Blks', 8 ) || ' ' ||
lpad( 'Dist Keys', 12 ) || ' ' ||
lpad( 'Sample Sz', 12 ) || ' ' ||
rpad( 'Analyzed', 16 ) || ' ' ||
lpad( 'Size', 8 );
dbms_output.put_line( ls_outputline );
for i in c_segments_i loop
ls_outputline := rpad( i.segment_name, 30 ) || ' ' ||
rpad( nvl( i.partition_name, ' '), 10 ) || ' ' ||
rpad( i.tablespace_name, 10 );
if i.segment_type = 'INDEX PARTITION' then
open c_ind_parts ( i.segment_name, i.partition_name );
fetch c_ind_parts into lt_ind_part;
close c_ind_parts;
ls_outputline := ls_outputline || nvl( to_char( lt_ind_part.num_rows, '999,999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_ind_part.blevel, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_ind_part.leaf_blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_ind_part.distinct_keys, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_ind_part.sample_size, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_ind_part.last_analyzed, 'dd/mm/yyyy hh24:mi' ), ' ' );
else
open c_indexes( i.segment_name );
fetch c_indexes into lt_index;
close c_indexes;
ls_outputline := ls_outputline || nvl( to_char( lt_index.num_rows, '999,999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_index.blevel, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_index.leaf_blocks, '999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_index.distinct_keys, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_index.sample_size, '999,999,999' ), ' ' ) || ' ' ||
nvl( to_char( lt_index.last_analyzed, 'dd/mm/yyyy hh24:mi' ), ' ' );
end if;
if i.bytes > (1024*1024) then
ls_outputline := ls_outputline || to_char( i.bytes/(1024*1024), '999,999' ) || ' MB';
else
ls_outputline := ls_outputline || to_char( i.bytes, '999,999' ) || ' b';
end if;
dbms_output.put_line( ls_outputline );
end loop;
dbms_output.put_line( '' );
end;
/
spool off