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
All	All

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 may be required.
• There may be triggers that are maintained by Oracle 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  

Add this 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.

Notes:

• Additional filtering may be required.
• There may be constraints that are maintained by Oracle under different Schema (owner) names.

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

Add this filter to get all constraints 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 may be Indexes that are created by Oracle (for example Indexes supporting Constraints).

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

Add this filter to get all indexes on all tables owned by table_owner (schema)

WHERE ai.table_owner = :SchemaO

Query: List Materialized Views

A Materialized View is a table that stores the result from a pre-processed query (view). The data is stored in a table but it cannot be modified (since the data is sourced from another table (or tables). 

These tables need to be removed from the Rule Set.

Notes:

Additional filtering might be required.

SELECT owner as mv.schema_name,
       mv.mview_name,
       mv.refresh_mode,
       mv.refresh_method,
       mv.build_mode,
       mv.last_refresh_date,
       mv.compile_state
from sys.dba_mviews mv
order by owner, mview_name;

Add this filter to get all Materialized Views owned by a specific owner:

WHERE mv.schema_name = :SchemaO

Query: List Calculated Columns 

The value Calculated Columns (aka Virtual Columns) are columns which value has been calculated (computed). These columns cannot be updated and hence not masked. 

Use the expression in the column to find the source columns and mask these.

Notes:

• Additional filtering might be required.

SELECT 
    owner,
    table_name,
    column_name, 
    virtual_column,
    data_default
FROM 
    all_tab_cols
WHERE table_name = :TableN;

Related Articles

• Profile Data in Database Column (KBA5846)