Scripting Oracle database : all objects from selected schemas

Question

Currently we are using Oracle SQL Developer for scripting all objects from selected schemas. But this is very uncomfortable solution, since we should select schema, search for all objects, select all objects and move all selected objects to the list. And each step is time expensive. And repeat it 12 times (12 shcemas should be exported).

Is it possible to improove export dialog through Oracle SQL Developer plugins? Or may by the better way is to use shell/command prompt utilty for scripting Oracle DB... Which utility to use?

What is the export for? Can you use expdp instead? Or if you are only getting the object definitions, automatically generate dbms_metadata calls yourself - which is what happens under the hood anyway. You could also look at the data modeller as that can generate DDL, but I'm not very familiar with it. Depends what you're actually doing though, and why... — Alex Poole
– Alex Poole, Commented Mar 5, 2014 at 8:11
@AlexPoole : exdp could be a solution I will check it. Thank you. This is for Oracle db migration to SQL Server project. And since there is parallel development of current Oracle solution we are diong Meta/DDL snapshots regularly at the start of each iteration. — Roman Pokrovskij
– Roman Pokrovskij, Commented Mar 5, 2014 at 8:18
Yes, normal way to do this would be to write a batch script to create the schemas you require and expdp/impdp the database objects and data. Either that or create a build script with database objects and insert scripts which you will probably need at some point anyway — kayakpim
– kayakpim, Commented Apr 11, 2014 at 13:20

thatjeffsmith · Accepted Answer · 2018-09-25 22:57:59Z

1

Use SQLcl!

Write some javascript, take advantage of built in SPOOL and DDL commands. Much faster than using our GUIs.

Example here (from Kris) - and it actually goes through ALL_OBJECTS https://gist.github.com/krisrice/de6f694b5241682059968bd5beadbcf7

SQLcl uses the same code base as SQL Developer, but it's a command line interface and it offers client side scripting so you can do things like loop through a set of objects and interact with the OS.

answered Sep 25, 2018 at 22:57

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Scripting Oracle database : all objects from selected schemas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related