I had a task to create a way to save the database from clients with only Oracle Instant Client installed. As you may or may not know exporting a database is simple with exp like this:
Well i have some bad news. Imp and exp only come with a full install of Oracle and aren’t included in the Instant Client. I could’ve done it dirty by copying the imp, exp binaries and the necessary dll-s to place, but hey this is not a nice solution. I did a little research on the topic and found Datapump. Datapump is a great and modern tool in the Oracle DB and the icing on the cake is: it has a nice PL/SQL API .
So i came up with the following stored procedure ( It is not exactly the same , you know, i’m not allowed to share company information and stuff) but the code i post here will work for the typical Scott tiger Foo example:
Make sure you have the rights needed to export full schema before you run the procedure. If you haven’t got enough rights you can always run:[code]grant exp_full_database, imp_full_database to foo with admin option[/code] as sysdba.
Make sure you have created the directory called DUMP_DIR or you will not be able to run the export.
So here comes the actual stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
If everything went correctly the datapump export just started. Wait until it finishes and you can import the result into any Oracle 10 or 11 Database using impdp.