SYAMS-Info: Mei 2019

Hi there i will share a little bit trick, that allow you to export oracle database using pl/sql.

I have Windows client and my oracle 10g DB base on Ubuntu(LInux),
i want create some procedure that will execute linux command to export oracle database.

ok here there are :
i will divide the method base on 2 model.

1. U will execute by calling .sh file

#Create exp.sh file

#!/bin/sh
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH
exp user/pass@orcl file=/U_re_directory/exp.dmp

#Run this script on pl/SQL

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'testjob',
job_type=>'EXECUTABLE',
job_action=>'U_re_directory/exp.sh',
enabled=>true,
auto_drop=>true);
end;
/

2. U will execute by typing the script directly

begin
   dbms_scheduler.create_job (job_name    => 'myjob',
                              job_type    => 'executable',
                              job_action => '/bin/sh',
                              number_of_arguments => 2,
                              auto_drop   => true);
   dbms_scheduler.set_job_argument_value ('myjob', 1,'-c');
   dbms_scheduler.set_job_argument_value ('myjob', 2,'ORACLE_HOME=/opt/oracle/product/10.2.0/db

PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH
exp assyifa/assyifa@orcl file=/U_re_directory/exp.dmp');
   dbms_scheduler.run_job ('myjob');
end;
/

:) Done!

Note :

How to see scheduled job status

select status,additional_info from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='Scheduled_NAME';

How to drop a scheduled job in oracle

BEGIN
dbms_scheduler.drop_job(job_name => 'Scheduled_NAME');
END;

Grant privilege tu user

SQL> conn system
SQL> grant create job to your_user;
SQL> grant create EXTERNAL job to your_user;
sources :

https://oracle-base.com/articles/misc/oracle-shell-scripting
http://rohmad.net/2008/07/24/menjalankan-os-command-atau-shell-script-dari-plsql/
https://askubuntu.com/questions/38661/how-do-i-run-sh-files
https://stackoverflow.com/questions/37458051/how-to-drop-a-scheduled-job-in-oracle
http://www.dba-oracle.com/t_execute_operating_system_os_command_oracle.htm

dbms_scheduler Ruunning OS Command or Shell Script from PL/SQL, EXPORT oracle database to .dmp file