Find Oracle indexes that are global vs locally partitioned.

We often run into cases where we have to find whether indexes are locally partitioned or are global across the whole table.

We are all familiar with the dba_indexes view, however, here are a couple of more views that make global vs local index queries possible.

To find local indexes use dba_part_indexes. The "locality" field from this view should be queried.

select owner, table_name, index_name, locality from dba_part_indexes;

OWNER TABLE_NAME                     INDEX_NAME                     LOCALI
----- ------------------------------ ------------------------------ ------
SYS   STREAMS$_APPLY_SPILL_MSGS_PART I_STREAMS_APPLY_SPILL_MSGS_PT1 LOCAL
SYS   WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SESSION_HISTORY_PK LOCAL
SYS   WRH$_SYSTEM_EVENT              WRH$_SYSTEM_EVENT_PK           LOCAL
SYS   WRH$_OSSTAT                    WRH$_OSSTAT_PK                 LOCAL
SYS   WRH$_PARAMETER                 WRH$_PARAMETER_PK              LOCAL
SYS   WRH$_SYSSTAT                   WRH$_SYSSTAT_PK                LOCAL
SYS   WRH$_SYS_TIME_MODEL            WRH$_SYS_TIME_MODEL_PK         LOCAL

The locality field will show as "LOCAL".

Global indexes are by definition, not partitioned, so they will not show in this view.

To find global indexes, we need to find all tables that are partitioned, but have indexes that are not partitioned. In order to do this, we use the following two views: dba_indexes and dba_part_tables.

select i.table_owner, i.table_name, i.owner, i.index_name
                  from dba_indexes i, 
                       dba_part_tables t
                  where i.table_owner = t.owner
                  and i.table_name = t.table_name
                  and i.partitioned = 'NO';

TABLE_OWNER TABLE_NAME                     OWNER INDEX_NAME
----------- ------------------------------ ----- ------------------------------
SYS         WRI$_OPTSTAT_HISTGRM_HISTORY   SYS   I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
SYS         WRI$_OPTSTAT_HISTGRM_HISTORY   SYS   I_WRI$_OPTSTAT_H_ST
SYS         WRI$_OPTSTAT_HISTHEAD_HISTORY  SYS   I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
SYS         WRI$_OPTSTAT_HISTHEAD_HISTORY  SYS   I_WRI$_OPTSTAT_HH_ST

Ora-31603 when calling dbms_metadata.get_ddl from Stored Procedure

I have a simple stored procedure that is calling dbms_metadata.get_ddl. The stored procedure is owned by UserA and it is trying to get the ddl for UserB.

create or replace PROCEDURE usera.get_ddl 
                    (p_table_owner VARCHAR2, 
                     p_table_name VARCHAR2)
IS
     l_ddl clob;
BEGIN
    SELECT dbms_metadata.get_ddl ('TABLE', 
                                  upper(p_table_name), 
                                  upper(p_table_owner))
        INTO l_ddl
        FROM dual;                 
END get_ddl;
/

  
Userb has the following table:


 create table userb.a ( a number, b varchar2(10));

Execute the stored procedure:


SQL> exec usera.get_ddl('userb','a');
BEGIN usera.get_ddl('userb','a'); END;

*
ERROR at line 1:
ORA-31603: object "a" of type TABLE not found in schema "userb"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
ORA-06512: at "USERA.GET_DDL", line 7
ORA-06512: at line 1

So first off user a needs the following grant:

grant select_catalog_role to usera;

Even after this the execute still gives the same error.

This error is due to Oracle Bug: 3960099 - ORA-31603 WHEN CALLING DBMS_METADATA FROM PROCEDURE 

There are 2 work arounds given by Oracle:

Option 1:

1- Create the procedure with AUTHID CURRENT_USER
2- Grant SELECT ANY DICTIONARY privilege to any user calling the procedure

OR

Option 2:
1- Create the procedure in the SYS schema
2- Grant execute on the procedure to the chosen users.

I chose Option 1 as this way there is no need to create a SYS stored procedure:

create or replace PROCEDURE usera.get_ddl 
                    (p_table_owner VARCHAR2, 
                     p_table_name VARCHAR2)
  AUTHID CURRENT_USER
IS
     l_ddl clob;
BEGIN
    SELECT dbms_metadata.get_ddl (
                                'TABLE', 
                                upper(p_table_name), 
                                upper(p_table_owner))
        INTO l_ddl
        FROM dual;                 
END get_ddl;
/

Give the grant:

grant select any dictionary to usera;

Now execute the stored procedure:

SQL> exec usera.get_ddl('userb','a');

PL/SQL procedure successfully completed.