I have several schemas I need to generate the schema script for each and have been using the schema browser > schema name > db object > Highlight all > create the scripts. The output of this is amazing but takes half a day to complete with so many mouse clicks (create scripts for all object types for each schema).
Switching to database > export > generate schema script does this task for 7 schemas in around 2 to 3 minutes. Again, amazing stuff.
The issue with the second (much faster) method is it doesn't generate the scripts the same as the first method and there are no options to make it so.
What I am missing is:
schema name in front of the generated script file
package spec and body is in 2 files (1st method puts them both in 1 script) preferred
the grants and synonyms are in their own folders (preferred in their own object script)
I guess the frustration here is that the options are available (in the first method) but unavailable in the 2nd method.
Basically, I want to generate these scripts so that all scripts have the schema name in the script name and include the DB object code plus the synonyms and grants if they exist, Same as the way the first method does but being able to perform the work for the entire schema.
Is this at all possible? or is there any kind of workaround?
If there are dependencies between your packages, then having the files combined like this is likely to cause compile errors because dependent objects may not exist yet.
That's why they are separated by default. Sometimes we need to create objects like this:
package A
package B
package C
package body A
package body B
package body C
.... for cases where package body A references something in package C, for example. Of course it can get a lot more complicated in real life.
You can always re-compile the schema after everything is created. Just FYI.
Thanks John, I totally agree with what you have written.
I first started out using method1 (create scripts) for one schema, which was not so bad timewise.
Running the create script on each group of db objects in that schema wasn't such a heavy task.
However, running the same for 7 schemas is much different and very time-consuming, especially when I could do as many schemas as I'd like for way under 5 minutes.
Unfortunately, the format of the package of existing schemas for the receivers is set in stone .
So creating the package of all schemas I need, in under 5 minutes, with the formatting I require, would be such a time saver.
I'm assuming that the code for these options is already there, embedded in the 'create script' method.
Anyway, good luck with this and thanks for your help.
I've added this for next beta. I added a few other "combine" options that I thought others might also want, after seeing the ones you've asked for. If you can think of others, let me know and I can add them.
