Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ddl error

Posted on 2014-04-15
5
Medium Priority
?
1,272 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 2000 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 23

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

610 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