Oracle Execute list of jobs in parallel

2013-04-14  Source: Original Site  Category:Oracle  Views:1 


I am using DBMS_FILE_TRANSFER.PUT_FILE as part of my transportable tablesspace shell script from
my staging dwh instance to my report dwh instance.

The problem is that i am coping the datafiles one after one.
There are about 20 datafiles , and each one is 10 giga size.
I would like to copy the files in parallel and not one after one.

I thought to create dynamically 20 jobs/scheduler (based on the number of the datafiles) and to execute all of them togther,
this way i could actualy run it in parallel.
Bellow is part of my TTS shell script.
The for loop is generating the list of datafiles that i need to copy.
the DBMS_FILE_TRANSFER.PUT_FILE copy them one after one.

Could one suggest how can i changed the plsql block bellow in order to create
and submit in parallel a job for each datafile ?

Thank You.

sqlplus -s "sys/${SourceSysPass}@${SOURCE_ORACLE_SID} as sysdba" << EOF whenever sqlerror exit 1 declare v_link varchar2(30); begin select db_link into v_link from dba_db_links where db_link like '%TTS%'; for x in ( select fname, ltrim(rtrim(substr(fname,1,instr(fname,'.')-1)))||'_'||rownum nf_name from (select substr(file_name,instr(file_name,'/',-1)+1) fname from dba_data_files where tablespace_name in ('${TableSpacesList}') order by file_id) ) loop DBMS_FILE_TRANSFER.PUT_FILE('source_tts', x.fname, 'target_tts', x.nf_name, v_link); end loop; exception when others then raise; end; / EOF

Related articles
  • Oracle Execute list of jobs in parallel 2013-04-14

    I am using DBMS_FILE_TRANSFER.PUT_FILE as part of my transportable tablesspace shell script from my staging dwh instance to my report dwh instance. The problem is that i am coping the datafiles one after one. There are about 20 datafiles , and each o

  • Oracle Executing Procedure through Jobs 2012-11-08

    I have a UI where user can enter a sql statement as well as a procedure (somewhat ADHOC query type)..The sql statement or the procedure is stored in a table in a column and a job is created to execute the statement on a particular day. whenever the j

  • Oracle Executing after-submit components in parallel 2014-06-28

    Is there a way to execute after-submit components (especially processes) in parallel? Right now, all after-submit components on a page are executed one after the other. First all validations fire in sequence number order, then all computations fire (

  • Oracle execute script with job 2015-04-29

    is there a way to execute a sql script with a job? And when how? I only want to run this script during the night because it takes a lot of time. Thanks for help. With best

  • Oracle How to see the list of jobs set? 2013-04-30

    Guys, Not sure if this is possible, but how can I see a list of jobs set by dbms_job? I'm using oracle 9i.

  • Oracle issue in running multiple job in parallel 2012-07-23

    I want to run six steps in parallel. I schedule it using following code. DBMS_SCHEDULER.define_chain_rule (chain_name => 'pscrm_chain', condition => 'TRUE', action => 'START my_step1,my_step3,my_step5,my_step6,my_step9,my_step10', rule_name

  • Oracle execute priv on dbms_scheduler job 2012-07-31

    I need to allow multiple users to execute a particular job owned by another user. This seems to work if I grant alter on the job to specific users, but I would like to do this with a role (grant alter on <job> to <role>). But this does no

  • Oracle getting error while trying to execute a external job using dbms_scheduler 2014-03-17

    I create a job using alpha account. begin dbms_scheduler.create_job( job_name => 'jps_test_executable', job_type => 'EXECUTABLE', job_action => '/usr/bin/ksh', number_of_arguments => 2 ); dbms_scheduler.set_job_argument_value ( job_name =

  • Oracle SQL Access Advisor Jobs fails 2014-06-21

    (oracle 11g release 2, enterprise edition) i tried to get sql access advisor job running. i made a sql tuning set with a few selected statements in it and tried to get some recomendations for them. for some reason the jobs fails every time i try to s

  • Oracle SQL Output from Job Scheduler 2013-06-04

    We are currently doing our ETL for a data warehouse via a scheduler called Opalis. This scheduler is no longer supported within our organization. This is why I am trying to convert to the Oracle Job Scheduler, which makes far more sense anyway as all

  • Execute multiple procedures in a parallel state 2013-09-29

    I am wondering how can I execute all individual procedures in parallel state in following script using sqlplus. I know I can create individual script for each procedure and execute it in different oracle sqlplus session but that solution is not viabl

  • Converting server job to parallel 2014-07-12

    We are planning to convert an existing server job to parallel. It performs a source extract from Oracle db, then looks up for dimension surrogate keys in 6 different hash lookups and then performs an upsert intarget oracle table. What all things shou

  • timesten or oracle executed the query ? 2012-01-17

    i have created a user managed cache with INTERVAL 2 MINUTES I updated the cache from my application .how should we know timesten or oracle executed the query as the table is updated in oracle database within milliseconds ? whether this 2 minutes is m

  • How to produce a list of job names 2012-01-23

    I have two questions related to this: Is there a way of producing a list of jobs in a project? I have a requirement to keep a list of jobs separate from DataStage in a spreadsheet. I could filter a dsx or xml file from a backup but there must be a mo

  • Convert server jobs to parallel? 2012-09-17

    We created server jobs in DS 7.5 but recently installed DS 7.5 Enterprise Edition. Is there a conversion step/process to allow us to convert the existing server jobs to parallel jobs? We want to continue development but with the additional functional

  • Executing an ETL job via JobControlWebService 2012-09-18

    Anyone ever execute another ETL job within a server job using the webservice client? I tried using the webservice client to call the RunJob method. I got an error saying I didn't attach to a DataStage project. In a way I know what they are saying but

  • How to generate list of jobs with category (folder) names 2012-10-12

    I'm trying to write a script that will export all jobs, one job per export file, in a particular category or folder. I know the 'dsjob' command can be used to list all jobs in a project, but the problem is, it just displays the job names, with the fu

  • Ubuntu executing some nohup command in parallel 2012-12-10

    I want to write a script to execute some nohup commands in parallel. Normally I put my command in background using nohup ./run arg1 > arg1.log & Now I want to run these command in parallel: nohup ./run arg1 > arg1.log & nohup ./run arg2

  • Oracle Execute Immediate and Multiple-Rows 2013-03-01

    what happens exactly when I execute a SQL-Statement which returns multiple columns and multiple rows with execute immediate? does oracle execute the statement completely? for example: execute immediate 'select a, b, c from x';

  • how does Oracle Scheduler determine External Job has fail? 2013-03-27

    Anyone knows how oracle determine that external job has fail? Does it only look on the content of stderr from the OS? or the exit code? Because I have a case whereby my script print the stderr to a logfile, and oracle scheduler see it as it has been