List Indexes, Constraints, and Triggers on Oracle (KBA5852)

Applicable Delphix Versions

This article is applicable to all Delphix Masking Version and all known Oracle Versions. 

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Set Variable

The examples below use a variable in the predicate. This makes it easy to both define and change the query as needed.

To set the variable in Oracle: 

variable TableN varchar2(30);
execute :TableN := 'Your_Table_Name';

For the alternative predicate:

variable SchemaO varchar2(30);
execute :SchemaO := 'Your_Schema_Name';

Query: List all Triggers

This sample query lists all triggers on a database.

Notes:

• Additional filtering might be required.
• There might be some Oracle maintained triggers under different Schema (owner) names.

SELECT owner as trigger_schema_name,
     trigger_name,
     trigger_type,
     triggering_event,
     table_owner as schema_name,
     table_name as object_name,
     base_object_type as object_type,
     status
FROM SYS.ALL_TRIGGERS
WHERE table_name = :TableN  

Alternative filter to get all triggers on all tables owned by table_owner (schema)

WHERE table_owner = :SchemaO

To see the trigger script, add this column: 

     trigger_body as script

Query: List all Constraints

This sample query lists all constraints on a database.

Note:

Additional filtering might be required.

SELECT ac.owner as schema_name,
       ac.constraint_name,
       ac.table_name,
       acc.column_name,
       ac.search_condition as constraint,
       ac.status
FROM sys.all_constraints ac
JOIN sys.all_cons_columns acc
     ON ac.owner = acc.owner
     AND ac.constraint_name = acc.constraint_name
     AND ac.table_name = acc.table_name
WHERE ac.table_name = :TableN

Alternative filter to get all triggers on all tables owned by table_owner (schema)

WHERE ai.owner = :SchemaO

Query: List all Indexes 

This sample query lists all Primary Indexes on a database.

Notes:

• Additional filtering might be required.
• There might be some Oracle maintained Indexes - these will appear with different Schema (owner) names.

SELECT ai.table_owner, 
    ai.table_name,
    ai.index_name,
    ai.index_type,
    ai.uniqueness
FROM sys.all_indexes ai
JOIN sys.all_ind_columns aic
    ON ai.owner = aic.index_owner
    AND ai.index_name = aic.index_name
WHERE ai.table_name = :TableN

Alternative filter to get all triggers on all tables owned by table_owner (schema)

WHERE ai.table_owner = :SchemaO

Related Articles

• Profile Data in Database Column (KBA5846)