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/shORACLE_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
BEGINDBMS_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
BEGINdbms_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
