1

I'm trying to better understand the Oracle Data pump functionality used to export data from one database and import it into a different database in a different location. My goal is to create a SQL script so I can automate most of the work and execute the script anytime I need to transfer data over and reduce dependency on the DBA. I am hoping I would not need to get in touch with the DBA each time (I have some limited admin access over the databases).

From my research so far, I have seen examples where people were writing some PL-SQL scripts and running them in SQL Plus (I think). I read that most PL-SQL scripts can be run in SQL Developer but not sure if Data pump commands work in there. If it is possible, I wonder if I could simply just take the same script and use it in either SQL Plus or SQL Developer interchangeably or I would need to make some minor modifications. I'm not sure if I have access to SQL Plus (still learning about it) so preferably want to use SQL developer.

I did find out that there is some Data pump wizard within SQL developer where you can run it but you would need to do it from the DBA panel in the program which I don't think I have the necessary credentials/permissions for that when I try to add my limited admin account connection in there. Also saw a database copy wizard functionality under the Tools file menu which I could use but looking to see if there are commands I can call within my own script.

Thanks.

Ernest Molner
  • 90
  • 8
Rocketboy235
  • 67
  • 1
  • 6
  • you can write a script and run it in your SQL Developer's SQL worksheet, or you can use the GUI/Wizards we put into sqldev to help you with Data Pump jobs/scheduling – thatjeffsmith Apr 26 '21 at 17:56
  • To be clear: datapump can only output to files in DIRECTORY objects, pointing to file system directories on the actual database server. It will not create files on your client machine. You may need the assistance of your DBA to create and provision the directory object(s) and to access the dump files after they have been created. Alternatively, datapump can also be configured to push data over a database link to a remote database, or to pull data from a remote database. See the documentation for details. – pmdba Apr 26 '21 at 19:08

1 Answers1

2

to do that you need the right permission but after you will have the permission it's simple

Let's assume you need only to export schema , there is predefined packages from Oracle to do that, you just need to update and modify what you need.

declare
  l_dp_handle       number;
begin
  -- Open a schema export job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'The JOB NAME ( FROM YOUR CHOICE)',
    version     => 'LATEST');

Then you need Specify the dump file name and directory object name.

 dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'The Dump name (output).dmp',
    directory => 'The location of the dump ( this should be created before run the job)');

Optional if you need to create logs

dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'Name of the logs .log',
    directory => 'Same directory as above',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

Specify the schema to be exported.

dbms_datapump.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''The user should be exported''');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/

I highly recommended you to go thru the documentation to understand more about the packages and the parameter HERE

Osamazx
  • 471
  • 1
  • 5
  • 12