Oracle script to get Table Indexes and Constraints and Index/Constraint column positions

Script to get Table Index details

SELECT di.TABLE_OWNER,
  di.TABLE_NAME,
  di.INDEX_NAME,
  COLUMN_NAME,
  COLUMN_POSITION
FROM dba_indexes di,
  dba_ind_columns dc 
WHERE di.TABLE_OWNER = dc.TABLE_OWNER
AND di.OWNER         = dc.INDEX_OWNER
AND di.INDEX_NAME    = dc.INDEX_NAME
AND di.TABLE_NAME    = dc.TABLE_NAME
AND di.TABLE_NAME    = 'EMPLOYEES'
ORDER BY di.TABLE_OWNER,
  di.TABLE_NAME,
  di.INDEX_NAME,
  dc.COLUMN_POSITION;

Script to get Table Constraint Details

If the constraint is a check constraint then SEARCH_CONDITION column will give Check Constraint details like Column Not Null.
If the constraint is a Foreign Key constraint then there are 2 inline queries
1. To give the table name for the referenced table and
2. To give a comma separated list of referenced table PK columns.

SELECT dc.OWNER, dc.TABLE_NAME,
       dc.CONSTRAINT_NAME, decode(dc.constraint_type,
     'C', 'Check',
     'O', 'R/O View',
     'P', 'Primary',
     'R', 'Foreign',
     'U', 'Unique',
     'V', 'Check view',
  'H', 'Hash expression',
  'F', 'Constraint that involves a REF column') type,
     dcc.COLUMN_NAME, dcc.POSITION,  dc.R_OWNER, dc.R_CONSTRAINT_NAME,
     dc.SEARCH_CONDITION,
     (SELECT TABLE_NAME from dba_cons_columns dc1
      WHERE dc1.CONSTRAINT_NAME = dc.R_CONSTRAINT_NAME
       AND dc1.OWNER = dc.R_OWNER
      AND ROWNUM = 1) ref_table,
     (select listagg(COLUMN_NAME, ', ') within group (order by POSITION)
  from dba_cons_columns dc1
      WHERE dc1.CONSTRAINT_NAME = dc.R_CONSTRAINT_NAME
      AND dc1.OWNER = dc.R_OWNER) ref_columns
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.TABLE_NAME = dcc.TABLE_NAME
  AND dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
  AND dc.TABLE_NAME = ''; 

Script to get Index creation ddl

Below script gives the Index Creation ddl for a given Index Name. This will be required when you compare 2 schemas for syncing indexes of tables in one schema into the other schema.

set feedback off
set head off
set long 8000
set linesize 100
col a1 format a200 word_wrapped

spool get_index_ddl.log

select DBMS_METADATA.GET_DDL('INDEX','PK_EMPID') a1 FROM DUAL;
select DBMS_METADATA.GET_DDL('INDEX','PK_DEPTID') a1 FROM DUAL;
select DBMS_METADATA.GET_DDL('INDEX','INDX_BOOK_ID') a1 FROM DUAL;

spool off

To get the index ddls for function based indexes on a table

dba_indexes does not give the ddl statement for function based indexes. For that you need to query dba_ind_expressions table as given below

select
   owner,
   index_name,
   index_type
from
   dba_indexes
where
   index_type like 'FUNCTION-BASED%'
and
   owner not in ('XDB','SYS','SYSTEM')
and TABLE_NAME = 'EMPLOYEES';

SELECT * FROM DBA_IND_EXPRESSIONS
WHERE TABLE_NAME = 'EMPLOYEES'
and index_name = '';

Query to get the blocking session in oracle

select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
 from gv$lock l1, gv$lock l2
 where l1.block =1 and l2.request > 0
 and l1.id1=l2.id1
 and l1.id2=l2.id2;

To verify the query you can update a record from one session and NOT COMMIT the record, and from another session you can try to update the same record.

UPDATE EMPLOYEES SET EMAIL = 'sking@gmail.com' WHERE EMPLOYEE_ID = 101;

Now when you run the query on gv$lock table as a user with DBA privelages, you will get the blocking session

INST_ID	SID	'ISBLOCKING'	SID_1	TYPE	TYPE_1	LMODE	LMODE_1	INST_ID_1
1	134	IS BLOCKING	15	TX	TX	6	0	1

PLSQL script to enable/disable indexes on a table/list of tables

DECLARE
CURSOR C1 IS
SELECT di.TABLE_OWNER TABLE_OWNER,
  di.TABLE_NAME TABLE_NAME,
  di.INDEX_NAME INDEX_NAME
FROM dba_indexes di 
WHERE di.TABLE_NAME    IN  ('JOB_HISTORY','EMPLOYEES')
ORDER BY di.TABLE_OWNER,
  di.TABLE_NAME,
  di.INDEX_NAME;

v_sql VARCHAR2(500);
BEGIN  
  FOR rec IN C1
  LOOP --{
--    v_sql := 'ALTER INDEX ' || rec.TABLE_OWNER|| '.' || rec.INDEX_NAME || ' UNUSABLE';
    v_sql := 'ALTER INDEX ' || rec.TABLE_OWNER|| '.' || rec.INDEX_NAME || ' REBUILD';
    dbms_output.put_line('v_sql ' || v_sql);
    EXECUTE IMMEDIATE v_sql;
--ALTER INDEX HR.EMP_EMP_ID_PK REBUILD;
  END LOOP; 
END;

PLSQL script to enable/disable constraints on a table/list of tables

DECLARE
CURSOR C1 IS
SELECT 
    OWNER, TABLE_NAME,  CONSTRAINT_NAME
FROM dba_constraints dc
WHERE dc.TABLE_NAME IN  ('JOB_HISTORY','EMPLOYEES')
AND OWNER = 'HR'
order by TABLE_NAME;

v_sql  VARCHAR2(500);
BEGIN  
  FOR rec IN C1
  LOOP --{
--  v_sql := 'ALTER TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || ' DISABLE CONSTRAINT ' || rec.CONSTRAINT_NAME || ' CASCADE'; 
  v_sql := 'ALTER TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || ' ENABLE CONSTRAINT ' || rec.CONSTRAINT_NAME; 
  dbms_output.put_line('v_sql ' || v_sql);
  EXECUTE IMMEDIATE v_sql;
  END LOOP; --}
END;

References 

How to Enable and Disable Constraints Using PL/SQL Scripts
Disable and Later enable all indexes in Oracle

Quote for the day
“Security Is Mostly A Superstition. Life Is Either A Daring Adventure Or Nothing.”

-- Helen Keller

For God has not given us a spirit of fear, but of power and of love and of a sound mind.

Timothy 1:7