Solved

ddl error

Posted on 2014-04-15
5
1,201 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 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