Skip to main content
Delphix

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

 

 

KBA

KBA# 5852

At a Glance

Summary: This KBA provides Oracle queries that can be used to list Indexes, ConstraintsTriggers, and listings of Materialized Tables and Calculated Columns.

These queries can provide insight into performance, masking issues, or more details about Oracle errors. 

Please note that the queries provided are for illustrative purposes only, and there may be variations between different versions of Oracle.
Applicable DB: Oracle
Investigation: Steps: 
  1. Open a SQL Client (SQLPLUS or other).
  2. Set variables (see below)
  3. Use query as instructed.
More info: For additional Oracle queries, please also see:

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

Sample queries for Oracle 

This page details sample Oracle queries to collect details about: 

  • Triggers.
  • Constraints.
  • Indexes.
  • List Materialized Views.
  • List Calculated Columns.
Note

Note:

Sufficient privileges are needed to execute these queries. It is recommended to use elevated privileges. 

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

Note

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.

Note

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.

Note

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.

Note

Notes:

  • Additional filtering might be required.

 

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