We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

PL SQL Create ddl for all user objects, cant use dbms_metadata.get_ddla

Hec Ramsey
Hec Ramsey asked
on
325 Views
Last Modified: 2017-04-26
I need to export all user objects ddl while having minimal rights on the schema.  I cannot use dbms_metadata.get_ddl.

This needs to be automated.  I prefer to use code.  I know sqldeveloper and toad, etc can export via GUI, so a command line solution would work too.  

Does anyone know an efficient way to do this?

2nd question, how to Google this issue, I'm going nuts trying to weed through lots of hits.


Thanks!!!
Oracle 12c
Windows 7 64bit
Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
for search, I tried:
export object ddl -dbms_metadata.get_ddl
export object ddl -dbms_metadata.get_ddl -data
export object ddl oracle -dbms_metadata.get_ddl -data

You can use Oracle's SQL Developer to export DDL
http://www.oracle.com/technetwork/developer-tools/sql-developer/export-intro-1-161239.html#exp_def

Author

Commented:
@PortletPaul  the problem is any page that discusses oracle ddl will mention dbms_metadata SOMEWHERE, even to say "in the event you can't use dbms_metadata, why not try ....."
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
yes, that is likely, notwithstanding I had more relevant results I think using  -dbms_metadata

http://deepak-sharma.net/2014/01/02/generate-ddldml-of-oracle-database-objects-using-oracle-sql-developer/
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
exp/imp has to know how to rebuild the objects, so it is capable of getting you the DDL.  It is not in a runnable form, you will have edit it some, but if you cannot use the other tools, it should work.  As I recall, this is the way to do it with exp/imp.

exp user/pass compress=n constraints=y file=out.dmp full=y grants=y indexes=y rows=n triggers=y
imp user/pass file=out.dmp indexfile=ddl.out

It is a 2 step process, but after you run that the resulting out.dmp should have almost everything you are looking for.  Some of them are commented out, but they are there.

I'm sure that it can be done with expdp/impdp as well, but I haven't done it so I don't know the exact steps.
David VanZandtSr Software Engineer
CERTIFIED EXPERT

Commented:
I tend to be overly literal, so forgive me if this isn't relevant. But, you seem to be asking for all-object access on someone else's schema, for which you have minimal access. If it were your own objects / schema, I'd expect you to have full access to the objects that you created.  For example, if you have the appropriate object privileges you might create your own payroll table. That you don't have access to the HR department's payroll table makes perfect sense. So, am I missing something?

Author

Commented:
David thanks for the response.  The situation is we have read only access to the clients schema.  They have asked us to do an automated BU of the schema.  We have of course asked for more access, but according to client, that will never happen due to bureacracy of their dept.

I have written script that copies the tables, I am looking for a method of doing same for all/any other user created objects.

I can read all system tables, incoluding all_source, but I'd like to find a more elegant way of copying the schema other than cobbling together dynamic SQL based on reads of sys tables.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
If you are trying to do a backup, then why not do a backup.  Use RMAN and back up the entire database.  That way you get everything and you don't need any privileges on their objects.

I guess I don't understand the end goal here.  Being able to reverse engineer the objects and trying to back something up are two very different things to me.
David VanZandtSr Software Engineer
CERTIFIED EXPERT

Commented:
Ah yes, treat the developers like mushrooms again (think darkness, damp, and fertilizer). If you go to the customer and say, this is what we can provide within our constraints, that answer might irritate someone as being inadequate (albeit logical). In your situation, I might think about using my own schema to develop the proof-of-concept (hey, I've found the automated solution that you requested) -- then show them how they can execute it under their own permissions.

Author

Commented:
johnson -- I haven't tried it but i doubt I have rights to RMAN on this schema.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
RMAN doesn't require any special privileges.  It is copying the data files, not reading the contents.

Author

Commented:
Johnson, thanks I check it out.

EDIT  I only have access to this DB via a Database Link through another schema.  I cannot run any kind of command line process on this schema.

Through the DBlink I can read all system tables, so i am confident I can yank out the code, I may just do it piecemeal in dynamic sql.
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I'll have to try it all out.  I've tried connecting directly to target, no go so far.  THXS!
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
In order to connect, you would have to use the credentials that are in the database link.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Here is a link to the documentation for expdp.

Here is a link to the NETWORK_LINK parameter for expdp.  You would specify this to use the link to do the export.  Should do what you are trying to do through the link and you should be able to use the login you are currently using.

That being said, if you are supposed to be backing up the data, which to me is what a backup means, you would need the privileges to be able to select the data in the first place.

Now, if you are trying to only export the DDL, you should be able to do that using the link as well, just specify CONTENT=METDATA_ONLY (doc link here.  Then you can import that and get just empty tables.

Author

Commented:
As I said int he OP, I need to export all user objects. Not the data.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Abhimanyu SuriDatabase Engineer
CERTIFIED EXPERT
Top Expert 2016

Commented:
To add to Johnsone's comment :

Import data dump into a SQL file, that will give you DDLs

Author

Commented:
Thanks for all the input!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.