Displays table indexes and columns for a given owner.
SQL> @icols
[= allowed] Table Owner : TRANS_001
[= allowed] Table Name :
Owner: TRANS_001
---------------
Table: APP_STATUS
APP_STATUS_PK [unique] ( APP, CHANNEL )
Table: SEVEN05_ALT_NOD_DETAIL_LOG
ALT_NOD_DETAIL_ID_PK [unique] ( ALT_NOD_DETAIL_ID )
Table: SEVEN05_CAST_DETAIL_LOG
CAST_DETAIL_ID_PK [unique] ( CAST_DETAIL_ID )
Table: SEVEN05_CAST_EMAIL_ADDR_LOG
CAST_EMAIL_ADDR_ID_PK [unique] ( CAST_EMAIL_ADDR_ID )
Table: SEVEN05_CAST_MOB_NO_LOG
CAST_MOB_NO_ID_PK [unique] ( CAST_MOB_NO_ID )
Table: SEVEN05_NOD_DETAIL_LOG
SEVEN05_NOD_DETAILS_LOG_PK [unique ( DISR_NOD_DETAIL_ID )
Table: SEVEN05_RNP_DETAIL_LOG
DIST_RNP_LOG_QUEUE_RM_STS_IDX ( OWNING_OFFICE_ID, QUEUE_REMOVE_STS, EVENT_TYPE )
RNP_DETAIL_ID_PK [unique] ( RNP_DETAIL_ID )
Table: SEVEN05_VIZ_MONITOR_LOG
No indexes.
Table: NOD_EVENT_BOOKING_DATA
NOD_EVENT_BOOKING_DATA_PK [unique] ( NOD_ID, EVENT_CODE, BOOKING_REF )
Table: NOD_EVENT_DATA
NOD_EVENT_DATA_PK [unique] ( NOD_EVENT_ID )
NOD_EVENT_DATA_UK [unique] ( OP_CARRIER_CD, OP_FLIGHT_NO, LEG_DEP_DT_LOCAL, LEG_DEP_STATION_CD, EVENT_CODE )
Table: LPC_EXPIRY_TIMES
LPC_ET_IDX ( DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FROM_OUTBOUND_DAYS, TO_OUTBOUND_DAYS, EXPIRY_HOURS, PRIORITY )
LPC_ET_UK [unique] ( DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FROM_OUTBOUND_DAYS, ROUTE_CONNECTION_IND )
Table: LPC_PARAMETERS
LPC_PARAM_PK [unique] ( ID )
Table: PRS_MSG_TRACKER
No indexes.
Table: PORT_CMH_MSG_STORE
PORT_MSD_ID_PK [unique] ( MSG_ID )
SYS_IL0000472439C00002$$ [unique] LOB
Table: PORT_NOD_EVENT
PK_PORT_NOD_EVENT [unique] ( NOD_EVENT_ID )
UK_PORT_NOD_EVENTK [unique] ( OP_CARRIER_CD, OP_FLIGHT_NO, LEG_DEP_STATION_CD, LEG_ARR_STATION_CD, FLIGHT_DTM_LOC )
Table: TCRM_CONFIG
TCRM_CONFIG_PK [unique] ( JOB_NAME )
Table: XSBAG_EDIFACT_MESSAGES
XEM_IDX1 ( TX_END_TIME, CREATE_DATE, PNR )
XEM_IDX2 ( PNR )
Table: XSBAG_ODS_EDIFACT_MESSAGES
XOEM_DEL_IDX1 ( TX_END_TIME, CREATE_DATE, PNR )
XOEM_DEL_IDX2 ( PNR )
PL/SQL procedure successfully completed.
@icols.sql
prompt
prompt
accept x_owner prompt "[= allowed] Table Owner : "
prompt
accept x_table prompt "[= allowed] Table Name : "
prompt
declare
ls_owner varchar2( 30 );
ls_table varchar2( 30 );
ls_out_line varchar2( 2000 );
ls_table_name varchar2( 200 ) := '#';
ln_table_indexes number := 0;
ln_total_indexes number := 0;
cursor c_owners is select * from dba_users where username like ls_owner order by username;
cursor c_tables( i_owner in varchar2 ) is select * from dba_tables where owner = i_owner and table_name like ls_table order by table_name;
cursor c_indexes( i_owner in varchar2, i_table in varchar2 ) is select * from dba_indexes where owner = i_owner and table_name = i_table order by index_name;
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;
begin
ls_owner := upper( '&x_owner' );
if upper( '&x_table' ) is null then
ls_table := '%';
else
ls_table := upper( '&x_table' );
end if;
for i in c_owners loop
dbms_output.put_line( 'Owner: ' || i.username );
dbms_output.put_line( rpad( '-', length( 'Owner: ' || i.username ), '-' ) );
for j in c_tables( i.username ) loop
dbms_output.put_line( '' );
dbms_output.put_line( ' Table: ' || j.table_name );
dbms_output.put_line( '' );
ln_table_indexes := 0;
for k in c_indexes( i.username, j.table_name ) loop
ls_out_line := ' ' || k.index_name;
if k.uniqueness = 'UNIQUE' then
ls_out_line := ls_out_line || ' [unique]';
end if;
if k.index_type = 'LOB' then
ls_out_line := rpad( ls_out_line, 38 ) || ' ' || k.index_type;
else
ls_out_line := rpad( ls_out_line, 38 ) || ' ( ';
for l in c_ind_cols( i.username, k.index_name ) loop
ls_out_line := ls_out_line || l.column_name || ', ';
end loop;
ls_out_line := substr( ls_out_line, 1, length( ls_out_line ) - 2 );
ls_out_line := ls_out_line || ' ) ';
end if;
dbms_output.put_line( ls_out_line );
ln_table_indexes := ln_table_indexes + 1;
ln_total_indexes := ln_total_indexes + 1;
end loop;
if ln_table_indexes = 0 then
dbms_output.put_line( ' No indexes.' );
end if;
dbms_output.put_line( '' );
end loop;
dbms_output.put_line( chr(10) );
end loop;
if ln_total_indexes = 0 then
dbms_output.put_line( '' );
dbms_output.put_line( 'No indexes found.' );
end if;
end;
/
prompt
prompt @icols.sql
prompt
prompt
prompt