CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersqlplusdatapumpsql-pl
Rocketboy235
Rocketboy235

Reputation: 87

SQL Script using Data Pump Import/Export executing in Oracle SQL developer?

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.

Upvotes: 2

Views: 2579

Answers (1)

Osamazx
Osamazx

Reputation: 467

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

Upvotes: 2

Related Questions