DBMS_JOB Running At Wrong Time
Jan 7, 2013
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;
View 5 Replies
ADVERTISEMENT
Jul 30, 2012
I've defined DBMS_JOB in Oracle it is not starting on time. As per query it should start at 09:00 PM as given below.
SQL> SELECT TRUNC(SYSDATE) + 21/24 FROM DUAL;
TRUNC(SYSDATE)+21/24
--------------------
7/31/2012 9:00:00 PM
But instead it was started on 7/31/2012 1:14:10 AM. Which is wrong.
Following is script which I am using to submit this job.
DECLARE
X NUMBER;
BEGIN
[Code]....
View 6 Replies
View Related
Feb 26, 2012
We are doing RMAN Duplicate set until time to refresh daily our test database for our developers and it taking long time to finish. We noticed on the restore log that RMAN was using a day old old backup pieces to refresh the test database and don't immediately use the latest backup pieces instead.
For additional details here's the rman duplicate command we are using which we run daily(mon-sat) at 4am once daily full backup on production completed.
RMAN Duplicate commands:
run
{
set until time "to_date(to_char(sysdate,'Mon DD YYYY') || ' 04:00:00', 'Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel ch1 type disk;
duplicate target database to testdb;
}
exit
Is there a way on how to let RMAN use the latest backup pieces instead?
View 1 Replies
View Related
Jul 13, 2008
Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.
View 16 Replies
View Related
Apr 27, 2012
I have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.
I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.
But now i still have the same problem, don't know what could be the problem.
Table_nameNum_RowsBlocks
PRPSL_LST_T5866107159
PRPSL_WKFLW_ACTVTY_T5829904030
ITEM_CHR_VAL_T5134340104049020
ITEM_RGN_ASSN_T8571220137215
Also attached 2 screen shots of OEM Plans..
View 2 Replies
View Related
Feb 11, 2011
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
View 6 Replies
View Related
Jun 22, 2012
MY DB Version: 10.2.o
OS: Windows Server 2003
I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host
by using below command:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump
Command i am using to do the import is :
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
But the query of import is still runing even not showing any amount of rows to be imported.
i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03": cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log
[code]....
i check streams_pool_size it will show zero and then i make it to 48M and after that
SQL> show parameter streams_pool_size;
NAME TYPE VALUE
-----------
streams_pool_size big integer 48M
But still it takes time
View 13 Replies
View Related
Dec 20, 2011
How to extract DDL of DBMS_JOB in sqlplus ?
View 30 Replies
View Related
Jul 5, 2013
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.
View 1 Replies
View Related
Jul 26, 2012
I have total 8 procedure to run in parallel . and after that my 9th procedure should run.
below is my job submission procedure
create or replace procedure DURATION_ALARM_WEEKLY as
l_job number ;
begin
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_OUT ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_IN ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_DURATIN_OUT ; end;');
[code].......
what is the syntax I have to do in my FINALE procedure . using DBMS_ALERT.REGISTER , DBMS_ALERT.WAITANY .....?
View 9 Replies
View Related
Jun 30, 2011
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).
View 1 Replies
View Related
Jun 17, 2010
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..
View 9 Replies
View Related
Mar 9, 2010
What is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.
View 1 Replies
View Related
Jan 2, 2011
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?
View 3 Replies
View Related
Jun 7, 2013
how to enable dbms_job to run on every day at 4am excluding Fridays.
View 7 Replies
View Related
Aug 10, 2010
'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
procedure analyze_error_152_ll
is
vJob varchar2(400);
vJobNum binary_integer;
v_db_ssa_dtl2 varr_ocv_rec := varr_ocv_rec();
ext_ids varr_ext_id_rec;
ext_id_type varchar2(5) := '(1)';
/*OTHER DECLARATONS*/
[code]......
View 7 Replies
View Related
Jun 20, 2010
Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?
View 3 Replies
View Related
Oct 11, 2012
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';
SID SERIAL# PADDR
---------- ---------- ----------------
626 753
i suggested a developer to drop job and reschedule it,now question was raised from them why process was not allocated to job.
View 6 Replies
View Related
Aug 8, 2010
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..
View 8 Replies
View Related
Mar 28, 2012
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?
View 1 Replies
View Related
Dec 14, 2010
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%.
View 9 Replies
View Related
Jan 26, 2013
I have database jobs that upload the data in my applications.My problem is while copying the record of one application to another, the format of dates goes wrongFor example:
The date in one column is 01-JAN-1941 but in the other record while copying ot goes as 01-JAN-2041.
View 4 Replies
View Related
Jan 13, 2011
I've written the code (see below) and after run I get an error:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
I don't know how to find out the wrong value from table. How to find it out?
DECLARE
TYPE rowids IS TABLE OF ROWID;
r1 rowids;
type t_varchar is table of varchar2(50);
n1 t_varchar ;
cursor c1 is select e.rowid rid,msisdn_displayed
from the_table
where contract_id is not null;
BEGIN
OPEN c1;
[code].........
View 3 Replies
View Related
Jun 18, 2013
My production DB has a couple of datafiles that were created in the wrong place, plus they are tiny - 100mb each. What is best way to get rid of them?
View 3 Replies
View Related
Jul 21, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');
[code]....
I get this:
K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected
When i change datatype in column to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01
[code]....
View 7 Replies
View Related
May 24, 2012
I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.
In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true
echo $TZ variable is returning nothing.
sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.
This command "./emctl config agent getTZ" is also returning timezone as europe/paris
Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris
Oracle version= 11.2.0
Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.
View 9 Replies
View Related
Jun 20, 2013
I would like to need export data to csv file, but I got problems with diacritics.The simply PLSQL looks like:
declare
f utl_file.file_type;
cursor c1 is Select ACTIVITY_SUB_TYPE
from the_table;
begin
[code]...
After run of plsql the record in the csv file looks like "Vypršanie skuš.lehoty kontakt"So there is a problem with that diacritics.
View 7 Replies
View Related
Apr 12, 2013
I am using pipelined functions. I've written a few with no problem this one seems to be giving an error when I am using techniques that appear very similar to ones that work.
I am doing this all in a package;
The type definition is;
TYPE SUSPECT_LINKAGES_FAC_RECORD IS RECORD
(
PATIENT_ID TUMOR.TUMOR_PATIENT_ID%TYPE,
CENTRAL_SEQ TUMOR.TUMOR_CENTRAL_SEQ%TYPE,
MP_REVIEW_FLAG NUMBER(1),
FACILITY_FLAG NUMBER
);
The variable definition is;
OUT_REC SUSPECT_LINKAGES_FAC_RECORD;
The line with the error is;
PIPE ROW(OUT_REC);
This is the entire function;
FUNCTION GET_SUSPECT_LINKAGE_FAC_FLAGS RETURN SUSPECT_LINKAGES_TABLE PIPELINED AS
CURSOR CURS_SUSPECT_LINKAGES IS
SELECT * FROM TABLE(TUMOR_UTILITIES.GET_SUSPECT_LINKAGE_FLAGS())
order by 1,2,3 DESC;
TEMP_REC SUSPECT_LINKAGES_RECORD;
MATCH_COUNT NUMBER;
OUT_REC SUSPECT_LINKAGES_FAC_RECORD;
[code].....
I get "PL-00382 expression is of wrong type" on both pipe row (out_rec); lines.
View 1 Replies
View Related
Mar 19, 2013
i got this error 'PLS-00382: expression is of wrong type'
--declaration
l_recipe_detail_tbl apps.gmd_recipe_detail.recipe_detail_tbl;
begin
ln_recipe_id := NULL;
[Code].....
View 1 Replies
View Related
Jun 28, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- SELECT * FROM NLS_SESSION_PARAMETERS;
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
[code]....
When i change datatype to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d3d64aupz4bb5, child number 2
-------------------------------------
select * from TEST1 where k1 like N'Ł%'
[code]....
Note - dynamic sampling used for this statement (level=2)
View 2 Replies
View Related
