Solved

ddl error

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 62
SQL Workhours Count beetween Workhours 3 26
oracle numeric condition check 4 27
oracle differnce between two timestamps 5 31
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

821 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