Skip to main content
Delphix

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

 

KBA

KBA# 5852

At a Glance

Summary: Detailed here are queries that can be used on Oracle to list Indexes, Constraints, and Triggers.
The queries used are using the same construct as are used on the Masking Engine.
DIY Investigation: Steps: 
  1. Open a SQL Client (SQLPLUS or other).
  2. Use a query below as instructed.

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

Sample queries for Oracle 

This page details sample Oracle queries to collect details about: 

  • Triggers.
  • Constraints.
  • Indexes.
Note

Note:

Sufficient privileges are needed to execute these queries. The same privileges are needed to run a masking job.

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.

Note

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

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.table_owner = :SchemaO

Query: List all Indexes 

This sample query lists all Primary Indexes on a database.

Note

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