We have Data Migration for our application coded in PL/SQL. The DB server has 64 Cores available (Solaris 10 OS) however running the migration code written as a function, utilizes very little CPU and CPU utilization is to max 2%. To utilize CPU power available to increase the speed of migration, we are using DBMS_JOB to schedule this function multiple times.
However scheduling the function 10 times, we are seeing that at any moment only 4/5 oracle processes are active and utilizing the CPU and CPU utilization has gone up to 5-6%. The speed of migration is increased but not to a great extend which I feel would work if we could utilize more CPU.
I see a parameter job_queue_processes is set to 10 currently in the database and am planning to increase this (currently to 25 as I don't have exact count of how many other jobs may be running in the database).
'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :
SELECT * FROM TABLE( DATA_UNLOAD( CURSOR( SELECT /*+ PARALLEL(A, 2, 1) */ TABLE_NAME || '|' || COLUMN_NAME || '|' || DATA_TYPE FROM MYTABLE A [code]....
It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work
If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?
I'm using dbms_metadata.get_ddl inside a package and I have a problem: if I execute the package directly, like "exec pkg_util.read_ddl('TA_DL_IDP', 'RMI_KUNDE') it works fine.
If I submit the same procedure using DBMS_JOB.SUBMIT, I get an error: Err_ -31603 - MSG: ORA-31603: object "RMI_KUNDE" of type TABLE not found in schema "TA_DL_IDP"
I know that in order to use dbms_metadata.get_ddl I need to have SELECT_CATALOG_ROLE and I know about the necessity to give explicit grants to objects rather than using roles when running pl/sql code from inside a package, but this is different: the different behavior is between running the same package in foreground and submitting it using DBMS_JOB.SUBMIT.
I have to change the execution of a job, I have the sys access.The job is in another user, say scott.how to use dbms_change using sys account to change the execution of a job in Scott user..
Discreet use of dbms_job.submit (to call a procedure), can be used to multi thread several instances of same proc in pl sql. I am able to do it.The problem is that dbms_output.put_line of the proc which is multi threaded does not reflects in the outer proc (the proc which parallels this proc and then waits for their completion).How do I receive the output?
We have a bunch of jobs scheduled using DBMS_JOB (yes, I know I should be using DBMS_SCHEDULER, but we haven't migrated there yet). We are running Oracle 11.2.0.3 on Windows Server 2003 x64.
For example, we have a job that is supposed to run every Wednesday at 20:00. The Interval we have set up is "NEXT_ DAY (TRUNC(SYSDATE), 'WEDNESDAY')+20/24". This has been working as intended. Today (Monday), however, the job kicked off at 11:52. It was the wrong day and the wrong time.
I don't see anything weird in my alert log. Where else should I check to figure out why this job ran today?
JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT 293 1/7/2013 11:52:46 AM 11:52:46 1/9/2013 8:00:00 PM 20:00:00 NEXT_DAY(TRUNC(SYSDATE), 'WEDNESDAY')+20/24 ACQUISITIONS.WORKLOAD_STATUS_UPDATE_NOTIF;
'Can we pass a dynamic collection variable to the procedure that is called from dbms_job.submit'
I have a package my_package with:
1. record type: ocv_rec
2. collection type: varr_ocv_rec varray(100) of ocv_rec
3. record type: ext_id_rec
4. collection type: varr_ext_id_rec table of ext_id_rec index by pls_integer
5. procedure: analyze_error_152_ll
6. procedure: get_ext_ids_152( v_db_ssa_dtl in varr_ocv_rec, ext_id_type in varchar2, ext_ids out varr_ext_id_rec)
Now here is where the issue resides:Within the definition of analyze_error_152_ll (in body of my_package), I call get_ext_ids_152 using dbms_job.submit
we had one problem one of oracle job scheduled using dbms_job was inactive for so long time,As one of our developer noticed this and reached me as i checked the dba_job was running without an process
SQL> select sid,serial#,paddr from gv$session where osuser='oracle';
I was monitoring a database job to collect statistics, it was scheduled using DBMS_JOBS..I found that it was running during business hours so i got the session ID of the job using;
select sid from dba_jobs_running where job=11;
I then i killed the job using;
select serial# from v$session where sid =232; alter system kill session '232, 10852'; select sid from dba_jobs_running where job=11;
no rows selected...After some time i again fired the same command
select sid from dba_jobs_running where job=11;
SID ---------- 232
and found that the same job is again running..This behavior was repeated again N again. i have attached the spool file for the same...
what could be the reason that the job is starting all over again even after killing the session and what should be done to stop it..I understand that once the database shuts down and if the job is still running then it will restart once the database is up..In this case, Should i remove the job and re submit it again..
I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace PROCEDURE "P_Load_Stamp" AS v_exists BOOLEAN; v_length NUMBER; v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number; variable instno number; begin select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS v_exists BOOLEAN; v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
I am creating 5 dbms_job at run time in side a stored procedure.But when I execute that procedure, all the job get 100%cpu and as a result other process does not get response. so my question is can we limit the dbms_job to use a defined amount of cpu%.
but this showing error "PROT-21: Invalid parameter", because to run this command diskgroup must be mounted. But diskgroup cannot be mounted until the CRS start. SO this is a conflicting situation.
I am getting problem while starting the CRS. Below is the necessary details:
1) I am using oracle 11g R2 RAC, with 2 node named RAC1 and RAC2
2) I have 3 diskgroup named DATA, DATA1, DATA2
3) OCR and voting disks are stored on ASM diskgroup
On node RAC1, CRS is started and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA ocrmirrorconfig_loc=+DATA1
On node RAC2, CRS is stopped and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA ocrmirrorconfig_loc=+DATA2
Please note the difference in "ocrmirrorconfig_loc". The difference was occured because I updated the OCR location on RAC1 when RAC2 was down.
Now I am trying to repair the OCR on the RAC2 using below command. This command must be run as root user when HA service is started but CRS must be stopped.
but this showing error "PROT-21: Invalid parameter", because to run this command diskgroup must be mounted. But diskgroup cannot be mounted until the CRS start. SO this is a conflicting situation.
when i start the Oracle database, the database is mounted. Then when i issue the "alter database open resetlogs" command, i got the below error.
ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data/oracle/NYP15S1D/data03/SYSTEM_01.dbf'
So i added the parameter "_allow_resetlogs_corruption=TRUE" in "init.ORA" file to force open the database but the Oracle instance crashed with following message.
1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013) 2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)
At many place I found the below solution:
select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;
But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
When I try to start in services, is returned the OS error 1067. This error starts to occur when I did the follow. I executed a PLSQL command to fill a (big) table with a lot of data. More than 1 day after and while the program still running, I broke the program. After that, Oracle was consuming lot of disk. I broke the process (using taskkill /f) and, after that, the database stopped working.
What I can do to recover my database?Below I list my listener.ora and tnsnames.ora
I have created and formatted a mini test scenario. Execute the scripts I have below?
From the column adj_second_line, I am trying to clip everything from the characters '201' all the way to the end...or the NUMERIC start value after the word 'TYPE'. Whichever way is easier for you...
