Home » SQL & PL/SQL » SQL & PL/SQL » Help required for getting create index DDL on partitioned table. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Help required for getting create index DDL on partitioned table. [message #656761] Tue, 18 October 2016 05:26 Go to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi All,

when executing below script in Production at that time getting below error:



select dbms_metadata.get_ddl('INDEX','IDX_SP_OPP_ALL_FLAT1','DNA_OWNER')
from dual;

Error :
-----


ORA-31603: object "IDX_SP_OPP_ALL_FLAT1" of type INDEX not found in schema "DNA_OWNER"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
31603. 00000 -  "object \"%s\" of type %s not found in schema \"%s\""
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.


[Updated on: Tue, 18 October 2016 05:27]

Report message to a moderator

Re: Help required for getting create index DDL on partitioned table. [message #656762 is a reply to message #656761] Tue, 18 October 2016 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
Either the index doesn't exist or your user doesn't have permissions to see it.
Re: Help required for getting create index DDL on partitioned table. [message #656763 is a reply to message #656762] Tue, 18 October 2016 06:11 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi Cookie,
But I can see the index on table using all_indexes:





select  index_name
from    all_indexes
where   table_name = 'DNA_SP_OPP_ALL_FLAT';


o/p

IDX_SP_OPP_ALL_FLAT1
IDX_SP_OPP_ALL_FLAT2
IDX_SP_OPP_ALL_FLAT3
IDX_SP_OPP_ALL_FLAT4


Is there any .sql query to confirm the restriction on table to see the DDL script.
Re: Help required for getting create index DDL on partitioned table. [message #656764 is a reply to message #656763] Tue, 18 October 2016 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Who is the owner of the table?
Who is the owner of the index?
Who are you? And what your privileges?


[Updated on: Tue, 18 October 2016 06:15]

Report message to a moderator

Re: Help required for getting create index DDL on partitioned table. [message #656765 is a reply to message #656763] Tue, 18 October 2016 06:24 Go to previous messageGo to next message
EdStevens
Messages: 888
Registered: September 2013
Senior Member
msol25 wrote on Tue, 18 October 2016 06:11
Hi Cookie,
But I can see the index on table using all_indexes:





select  index_name
from    all_indexes
where   table_name = 'DNA_SP_OPP_ALL_FLAT';


o/p

IDX_SP_OPP_ALL_FLAT1
IDX_SP_OPP_ALL_FLAT2
IDX_SP_OPP_ALL_FLAT3
IDX_SP_OPP_ALL_FLAT4


The error message was "ORA-31603: object "IDX_SP_OPP_ALL_FLAT1" of type INDEX not found in schema "DNA_OWNER""

With emphasis on "in schema "DNA_OWNER""

select  OWNER,
              index_name
from    all_indexes
where   INDEX_name = 'IDX_SP_OPP_ALL_FLAT1';
Re: Help required for getting create index DDL on partitioned table. [message #656766 is a reply to message #656765] Tue, 18 October 2016 07:01 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi Ed,

This is my doubt.Because I can see indexes after query on ALL_INDEXES.But, DBMS_METADATA.GET_DDL is showing there is no indexes in DNA_OWNER schema.


But, I can see indexes:


select  owner,index_name
from    all_indexes
where   table_name = 'DNA_SP_OPP_ALL_FLAT';


o/p >

OWNER            INDEX_NAME
------		--------------------
DNA_OWNER	IDX_SP_OPP_ALL_FLAT2
DNA_OWNER	IDX_SP_OPP_ALL_FLAT3
DNA_OWNER	IDX_SP_OPP_ALL_FLAT4
DNA_OWNER	IDX_SP_OPP_ALL_FLAT1 



Please note,currently connected with SUPPUSER and checking index on another schema i.e. DNA_OWNER.
Re: Help required for getting create index DDL on partitioned table. [message #656767 is a reply to message #656766] Tue, 18 October 2016 07:17 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
for partitioned indexes look in ALL_IND_PARTITIONS
Re: Help required for getting create index DDL on partitioned table. [message #656768 is a reply to message #656761] Tue, 18 October 2016 07:24 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
it appears that you need the select_catalog_role

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867
Re: Help required for getting create index DDL on partitioned table. [message #656771 is a reply to message #656765] Tue, 18 October 2016 08:12 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 October 2016 13:15

Who is the owner of the table?
Who is the owner of the index?
Who are you? And what your privileges?
Previous Topic: star keyword oracle
Next Topic: Insert a space in the middle of a six characther string
Goto Forum:
  


Current Time: Fri Jan 19 23:55:45 CST 2018

Total time taken to generate the page: 0.01567 seconds