Solved

ddl error

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

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now