Server Administration :: Advantages Of Using DBMS_schedular Ovet Dbms_job
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
ADVERTISEMENT
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
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
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
Sep 16, 2010
tell me the advantages of using more than one redo log thread? Currently i am setup with 3 logfile groups and 3 members in each group.Ie
logfile group 1 ('/u01/oradata/pri/redo01a.dbf',
'/u02/oradata/pri/redo01b.dbf',
'/u03/oradata/pri/redo01c.dbf') size 30M,
group 2 ('/u01/oradata/pri/redo02a.dbf',
'/u02/oradata/pri/redo02b.dbf',
'/u03/oradata/pri/redo02c.dbf' ) size 30M,
group 3 ('/u01/oradata/pri/redo03a.dbf',
'/u02/oradata/pri/redo03b.dbf',
'/u03/oradata/pri/redo03c.dbf' ) size 30M
View 4 Replies
View Related
Aug 22, 2012
What are the advantages of writable materialized views.
View 6 Replies
View Related
Aug 6, 2013
11.2.0.1Aix 6.1 5L (quadcore, 16GRam) I am still confused how to take full advantage of these monitoring tools. Actually the our database performance is currently satisfactory, except for occasional few minutes spikes of CPU highs > 80 .I just want to catch the culprit process/program responsible for this spikes. Is it wise to run ASH, AWR, ADDM with an input from time 1AM to 1AM next day? What I mean is I will analyze a 1-day period, so that I can catch the program/process that has the higest cpu/memory usage for the day.
View 24 Replies
View Related
Apr 22, 2009
I would like to know more about RUEI's history, as well as advantages and disadvantages of RUEI and its rivals.
View 25 Replies
View Related
Jun 4, 2013
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies
View Related
Aug 31, 2011
We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?
View 2 Replies
View Related
Dec 20, 2011
How to extract DDL of DBMS_JOB in sqlplus ?
View 30 Replies
View Related
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
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
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
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
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
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
Nov 29, 2010
i'm a student currently learning database administration security.
I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.
I have attached the schema that was given to me for this assignment.
View 12 Replies
View Related
Apr 8, 2010
My manager is asking to create a SQL scrip, which will provide following information.
Our database is 9.2.0.7.0 on Windows 2000 Server
-How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
-Check database growth ?
View 10 Replies
View Related
Jan 11, 2011
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
View 1 Replies
View Related
Jun 27, 2012
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
View 3 Replies
View Related
Oct 31, 2012
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
View 22 Replies
View Related
Jan 11, 2011
after a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
View 2 Replies
View Related
Feb 13, 2013
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
View 3 Replies
View Related
Jul 26, 2010
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
View 13 Replies
View Related
