Jul 14, 2020 5:30:00 PM by Jeff Surretsky
Using Toad® for Oracle, there are many ways to export data from your Oracle databases. What is the fastest way to export data from Oracle? It all depends upon your specific use case. Of course, there is the obvious method of using the Data Pump Export Utility within TOAD but, that may not always be your best option.
Depending upon what you’re doing at the moment, whether it's browsing your tables within the Schema Browser or constructing a query within the Editor or Query Builder, you are only a click away from exporting your data in the context of your work flow.
Let’s take a closer look.
Within your Schema Browser, you can simply left click on a table and select the Data tab as seen below:
Within the data grid, you can right-click and select from the following menu:
You can then select Export Dataset.
While the default output format is Delimited Text, you have many other choices including but not limited to Excel, HTML Table, Insert Statements, JSON, Merge Statements, ODBC Connection (for output going to a non-Oracle database), SQL*LDR and XML.
Take note of the "Export Dataset" feature; you'll be seeing it appear much more as you read on!
You can even Mask portions of the output if you like.
Let’s say your data needs expand beyond a table; you have options for that too.
You can simply right-click on a table within your schema browser and select either ER Diagram or Query Builder as follows:
When you select ER diagram, you are presented with an ER diagram starting from the table you selected connecting to all tables with a Foreign key relationship (to a depth you choose):
You can then select the Query Builder icon (as highlighted above) to see the same diagram with additional capability of choosing columns and more.
Of course, you could have gone directly to the Query Builder as specified above (without having to select the ER Diagram).
Within the Query Builder, you can select your desired columns, where clause (simple or complex), group by and sort columns.
Let’s put a simple Where Clause on the Balance column where it must be greater than 7,600:
Notice how it changes the underlying query:
Let’s run this query to see the resulting data grid:
Within this data grid, you can simply right-click and choose Export Dataset with all the options as previously explained. So now you see how you can create a complex query and export the resulting data into various formats.
Your use case may require you to export the data for a group of tables regardless of their parent-child relationship. You can select the desired tables by simply shift or control clicking on them, right-click and then select Export Dataset. Again, you'll have the all the same options as mentioned above.
Those are just the options available through the Schema Browser. You also have options through the Editor.
No matter how complex your query is, you can run it and see the resulting data grid within Toad for Oracle.
I think you know what’s coming next.
Yes --- you can right-click within the data grid and select Export Dataset, again with all the capabilities as previously mentioned.
So you see there are many ways to export your data from Oracle. Which one you choose all depends upon your work flow and the amount of data you want to export.
And don't forget ...
Or should I say, last but not least ...
You can always go to the Database > Export pulldown to be presented with more choices. In addition to exporting application data, you may need to export metadata (and in various formats).
By choosing Data Pump Export, you’ll be presented with an easy to use wizard:
You can choose from Tables, Schemas, Tablespaces, Database or Transportable Tablespaces Modes. Here, we’ll select Schemas. You can choose your Schemas (under items), Use a query, filter in our out objects, choose your data pump export parameters and where the export will go (under files).
And guess what? This can all be automated through Toad for Oracle's Automation Designer!
If your data export needs extend beyond Oracle, there is always Toad Data Point where you can connect to almost 40 data sources including any source that connects via ODBC! You can even join data from different platforms.
I hope you found this blog useful and have discovered the many different options for exporting Oracle data and more!
Try Toad free for 30 days.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Written by Jeff Surretsky
Jeffrey Surretsky has been working at Quest since 2000 as a Sales Engineer focusing on a wide variety of solutions including Foglight, SharePlex and the TOAD Family of Products. Before working at Quest, he was a DBA for various organizations spanning a diverse range of industries including but not limited to Insurance and Telephony . With almost 35 years of experience in the Information Technology industry, Jeffrey has a Bachelor’s Degree in Computer Science and a Masters Degree in Management of Information Systems.