Hi,
I’m new in toad and do not know, how can I export tables (ddl + data + triggers), stored procedures (ddl), db links, packages, … into flat files without using ‘create script’ for each object type.
Is it possible to store my settings for each export (selected objects, target file, alle settings I do using GUI) in a a file, load this file in editor and run it?
We are using Toad 12.5.1.1.
Thanks in advance,
Reza
Hi Reza,
Database->Export->Export DDL.
On the Objects/Owner’s tab:
Click ‘Add’ button.
Uncheck “Close on rows loaded”.
Select tables from the drop down.
Click ‘Load Rows’ button.
Repeat for Packages, procedures, functions, etc etc etc.
Click ‘Close’ button when done.
Check the list of objects and untick anything you don’t want including.
In the “output” area at the bottom, pick your desired options, output filename etc.
On the “Script Options” tab:
Go through the Common, Storage Objects, Roles sub tabs and choose your desired options.
On the Tables sub-tab, check the option for ‘Insert Statements’ and uncheck the two warnings, if desired.
Click the Green “>” button above the dialogue’s tab bar. That will do the export for you.
Now, saving your settings is a little more complicated.
At the bottom of the screen is a pair of buttons, one to create a snapshot (looks like a camera) and one next to it to "schedule action. Click on that one.
For the app, call it something like ‘AppExportStuff’ (or be more meaningful!) and for the name use something like ‘Export Stuff’. This is the name that you will see in the Automation Manager later.
On the next dialogue, there’s another name required, use the App name form the previous screen, AppExportStuff for example. Give a decent description of the task’s purpose. Click ‘Next’.
Click ‘Next’ again.
On the screen to “specify conditions to trigger task”, click ‘New’ and select ‘at task creation/modification’ from the drop down. Click ‘Ok’. Click ‘Next’.
Enter your account details. Click ‘Next’.
Enter the Authorisation details. When done, the task will run again. Don;t worry, unless it’s a huge task of course!
You have now saved an automated task. You can subsequently run it on demand by either changing it, in which case it will auto run, or by:
Utilities->Automation Designer.
Click on the Control tab.
Double-clik your task name (“Export Stuff”).
Click ‘Run’ button.
You may be prompted to overwrite the output file(s) as necessary.
HTH
Cheers,
Norm.
PS. Then, you can also go into the Windows Task manager in Control Panel and amend or update the task and run it on demand from there too, without needing to start Toad, if you wish. The task should do that for you.
If you have the DBA module, you might also take a look at “Generate Schema Script”. You don’t have to select every single object that you want to get DDL for - you just have to choose the object type. It will also run faster than “Export DDL”. The only limitation is that it doesn’t generate insert statements. But for that, you could use a an “export dataset” action and choose multiple tables. Insert statements are fine for small amounts of data, but if you have a lot of data, you might want to consider a faster import method such as data pump or SQL Loader.
Thanks a lot Norm. It was very useful.
Thank you John. I do not have DBA module. So I will use the solution provided by Norm.