Ora-31603 View Error Ddl Sql Developer
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back ora-31603 object not found in schema on and reload this page. Please enter a title. You can ora-31603: object of type table not found in schema "sys" not post a blank message. Please type your message and try again. More discussions in General ora-31603: object of type constraint not found in schema Database Discussions All PlacesDatabaseGeneral Database Discussions This discussion is archived 2 Replies Latest reply on Nov 24, 2015 8:32 PM by wolfej50 ora-31603 when attempting to get dbms_metadata.get_ddl user privileges ddl for tablespace wolfej50 Nov 24, 2015 7:55 PM Running Oracle 11gR2 on Oracle LinuxIn my schema, I've created a package that includes a procedure that will retrieve the DDL of a given tablespace. However, the line 'DBMS_METADATA.GET_DDL('TABLESPACE', '
Ora-06512: At Line 5805
object "
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more
Select_catalog_role
about Stack Overflow the company Business Learn more about hiring developers or posting authid current_user ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Get VIEW ddl using query up vote 0 down vote favorite https://community.oracle.com/thread/3870556 For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer. I successfully got DDL for table using- select dbms_metadata.get_ddl('TABLE','Table_name','Schema_Name') from dual; But facing problem with VIEW and MVIEW. Could anyone provide commands/keywords for elements other than table. Also, I want to export the result http://stackoverflow.com/questions/23515801/get-view-ddl-using-query in an excel file with first column as TableName and second column as DDL. sql oracle ddl database share|improve this question edited May 7 '14 at 12:09 Nicholas Krasnov 19.2k42849 asked May 7 '14 at 10:52 Mithun Khatri 1731313 2 Simply replace TABLE with VIEW and Table_Name with a View_Name in your dbms_metadata.get_ddl() call. –Nicholas Krasnov May 7 '14 at 11:53 just be aware that text of view will/could be different from originally submitted. –vav May 7 '14 at 14:30 @NicholasKrasnov I tried the same. but its giving below error ORA-31603: object "prs_talent_assessment_vw" of type VIEW not found in schema "oradba" 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. But prs_talent_assessment_vw is present in db and oradba can access it. –Mithun Khatri May 8 '14 at 6:49 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted Try the below query for view: select text from ALL_VIEWS where upper(view_name) like upper(
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/26026068/dbms-metadata-get-ddl-not-working Meta Discuss the workings and policies of this site About Us http://www.orafaq.com/forum/t/179062/ Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, not found just like you, helping each other. Join them; it only takes a minute: Sign up dbms_metadata.get_ddl not working up vote 2 down vote favorite I want to get the ddl of Table CARD_TABLE in XT schema SQL> select dbms_metadata.get_ddl('TABLE','CARD_TABLE','XT') from dual; ERROR: ORA-31603: object "CARD_TABLE" of type TABLE not found in schema "XT" ORA-06512: at "SYS.DBMS_METADATA", not found in line 5746 ORA-06512: at "SYS.DBMS_METADATA", line 8333 ORA-06512: at line 1 But my select Query works select count(*) from XT.CARD_TABLE; count(*) --------- 0 as requested i queried dba_objects it still got TABLE SQL> select owner,object_type from DBA_OBJECTS where object_name='CARD_TABLE' 2 3 ; PUBLIC SYNONYM XT TABLE PARTITION XT TABLE PARTITION XT TABLE PARTITION XT TABLE XT TABLE PARTITION VAT TABLE 7 rows selected. sql oracle share|improve this question edited Sep 24 '14 at 20:58 asked Sep 24 '14 at 20:40 user2711819 335520 1 Isn't it a VIEW or MATERIALIZED VIEW or a SYNONYM'. See DBA_OBJECTS`. –ibre5041 Sep 24 '14 at 20:44 @Ivan updated , it still had TABLE . How do i get that definition ? –user2711819 Sep 24 '14 at 20:58 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted From the dbms_metadata documentation: If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able
with ORA-31603 on dblink [message #544333] Tue, 21 February 2012 05:16 julb Messages: 17Registered: November 2010 Junior Member Hi, I have user U1 with dblink DBL1 (private dblink, not public). When I logged in with U1 and try to execute the following statement : SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME) FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK' AND OBJECT_NAME = 'DBL1'; I get ORA-31603 error: ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1" ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5843 ORA-06512: at line 1 DBL1 script is as follows: CREATE DATABASE LINK DBL1 CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1'; when I select from user_objects with the following query , it is there. SELECT * FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK' AND OBJECT_NAME = 'DBL1'; DBMS_METADATA.GET_DDL works for every other objects (tables for example) in this schema except for dblinks. What can cause this behavior? Report message to a moderator Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544346 is a reply to message #544333] Tue, 21 February 2012 06:04 Michel Cadot Messages: 63911Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator SQL> select object_name 2 FROM SYS.USER_OBJECTS 3 WHERE OBJECT_TYPE = 'DATABASE LINK' 4 AND OBJECT_NAME = 'MYLINK.WORLD' 5 / OBJECT_NAME ------------------------------ MYLINK.WORLD 1 row selected. SQL> SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME) 2 FROM SYS.USER_OBJECTS 3 WHERE OBJECT_TYPE = 'DATABASE LINK' 4 AND OBJECT_NAME = 'MYLINK.WORLD' 5 / SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME) -------------------------------------------------------------------------------- CREATE DATABASE LINK "MYLINK.WORLD" CONNECT TO "MICHEL" IDENTIFIED BY VALUES '050F0983FD639837FEF3377178AFB39CC4' USING 'MIKA.WORLD' It works for me. Regards Michel Report message to a moderator Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544367 is a reply to message #544346] Tue, 21 February 2012 07:01 julb Messages: 17Registered: November 2010 Junior Member I know that this script suppose to work. The question is why it doesn't work? Report message to a moderator Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544368 is a reply to message #544367] Tue, 21 February 2012 07:05 Michel Cadot Messages: 63911Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Why do