I want to export all the 100 tables with data from one schema at once from oracle sql developer... Like we export one table and that table get's saved where we want to save as an excel file. Is there any way to do this?... Instead of exporting one table at a time with data.
3 Answers
There's Data Pump Export (and Import) which does that. However, the result is a DMP file which is certainly not recognizable by Excel; think of it as of a binary file readable only by Data Pump Import.
So, if you want Excel files (actually, a CSV format), you'll have to either export them one-by-one (what a tedious job!) or write your PL/SQL procedure which would use UTL_FILE package. Note that (generally speaking) the result resides in a directory located on the database server, not your local PC, so you'll have to talk to your DBA about it. Shouldn't be a problem (in my opinion), you should be granted read/write access to a directory designed for such purposes.
Tools, Database Export
Select your tables. Select your output method (Excel), hit go.
Bigger question, what are you gonna do with these 100 Excel files?
Also, how big are these tables? Exporting to CSV might be better, but again we don't know why you want Excel files...
Finally, if you want to take this data and use it to put in another Oracle Database at some point, you should be using Data Pump.
You can try writing a scheduler for following task using PL/SQL
Use oracle documentation for help
Related