Solved

ddl error

Posted on 2014-04-15
5
1,190 Views
Last Modified: 2014-06-10
I have an error here

SQL>select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_RSAARC' ) from dual
  2       UNION ALL
  3       select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','PERF_ENGINEERING_RSAARC') from dual
  4            UNION ALL
  5            select dbms_metadata.get_granted_ddl('OBJECT_GRANT','PERF_ENGINEERING_RSAARC') from dual
               UNION ALL
  6    7                 select dbms_metadata.get_granted_ddl('ROLE_GRANT','PERF_ENGINEERING_RSAARC') from dual;

DBMS_METADATA.GET_DDL('USER','PERF_ENGINEERING_RSAARC')
--------------------------------------------------------------------------------

   CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7


  GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
  GRANT CREATE PROCEDURE TO

ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
0
Comment
Question by:tonydba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40002157
Look in the generated output from all the get_ddl commands and verify the generated syntax.

I think you'll find that dbms_metadata.get_ddl doesn't add the semi-colon to the end of the command so more than one command will not be 'valid' sql for a script.

You can try concatenating:


select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_RSAARC' ) || ';' from dual
UNION ALL
...


But you might run into trouble if the generated SQL exceeds 4000 characters.
0
 

Author Comment

by:tonydba
ID: 40002163
>select dbms_metadata.get_ddl( 'USER','PERF_ENGINEERING_RSAARC' ) || ';' from dual
  2       UNION ALL
  3       select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','PERF_ENGINEERING_RSAARC') || ';' from dual
  4            UNION ALL
  5            select dbms_metadata.get_granted_ddl('OBJECT_GRANT','PERF_ENGINEERING_RSAARC') || ';' from dual
  6                 UNION ALL
  7                 select dbms_metadata.get_granted_ddl('ROLE_GRANT','PERF_ENGINEERING_RSAARC') || ';' from dual;

DBMS_METADATA.GET_DDL('USER','PERF_ENGINEERING_RSAARC')||';'
--------------------------------------------------------------------------------

   CREATE USER "PERF_ENGINEERING_RSAARC" IDENTIFIED BY VALUES 'S:EECA10D0E816B7


  GRANT CREATE TRIGGER TO "PERF_ENGINEERING_RSAARC"
  GRANT CREATE PROCEDURE TO

ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1



SYS@TSSUDALM SQL>
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40002166
>>> ORA-31608: specified object of type OBJECT_GRANT not found

You get this because user PERF_ENGINEERING_RSAARC has no object privileges granted directly to it.


Also,  concatenating a single semicolon to the end of the get_ddl output won't work for the grants if there are multiple privileges.

For example, if the user has 2 system privileges (create session and create procedure) the output of the function would look something like this...


  GRANT CREATE SESSION TO "PERF_ENGINEERING_RSAARC"
 
  GRANT CREATE TABLE TO "PERF_ENGINEERING_RSAARC"

Open in new window


So,  puttting ';' on the end won't help.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40124054
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question