PL/SQL :: Enable Dbms_job To Run On Every Day At 4am Excluding Fridays?

Jun 7, 2013

how to enable dbms_job to run on every day at 4am excluding Fridays.

View 7 Replies


ADVERTISEMENT

Excluding Records That Contains Certain Text

Jan 30, 2012

I'm very new to sql. I'm using rapid sql to query an oracle db. I need to exclude records that contain the following text: reissuescholasticmedia tie-inmovie tie-in

Currently, I'm using the following query, which returns records that include the text I'd like to exclude (reissue, scholastic, media tie-in, movie tie-in).

Select
ISBN,
BOOK_TITLE,
PUBLISHER_DESCRIPTION,
AUTHOR_BYLINE,

[Code]....

this query so that it excludes records containing the text that I've indicated?

View 5 Replies View Related

SQL & PL/SQL :: How To Calculate Between 2 Dates Excluding Saturday And Sunday

May 3, 2012

select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt
from dual
connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1;

The above query returning the following output,

DT
04/25/2012
04/26/2012
04/27/2012
04/28/2012
04/29/2012
04/30/2012
05/01/2012
05/02/2012
05/03/2012
05/04/2012
05/05/2012

here I need to exclude the Dates which comes on 'saturday' and 'sunday' and also the common holiday..Here it is '01-May-2012' and I need the output like the following,

DT
04/25/2012
04/26/2012
04/27/2012
04/30/2012
05/02/2012
05/03/2012
05/04/2012

I need the common query to calculate between any two dates.

View 2 Replies View Related

Server Utilities :: Excluding Data Of Some Tables In EXPDP

Oct 5, 2013

i want to exclude only data of some particular tables not complete table object when exporting using expdp.

View 13 Replies View Related

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

Oct 29, 2012

I want to remove more than one space from a string by excluding double quotes.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

View 17 Replies View Related

Export/Import/SQL Loader :: EXPDP Excluding Tablespaces

Sep 9, 2013

I am using EXPDP to export a schema (Oracle 11g R2), and I need to exclude all the tablespaces that the schema is using. I have seen exluding Oracle objects like functions, tables, packages, indexes...etc. But I have not seen excluding tablespaces.Iis it possible to exclude tablespaces while creating the export dump?

View 5 Replies View Related

SQL & PL/SQL :: Extract DDL Of DBMS_JOB

Dec 20, 2011

How to extract DDL of DBMS_JOB in sqlplus ?

View 30 Replies View Related

SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday

Feb 3, 2011

I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.

My Requirement:

For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.

Table Name : ABC

Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date

Sample Record in the Table :

Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08

OUTPUT:

Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08

View 5 Replies View Related

Server Utilities :: Faster Impdp Excluding Indexes And Constraints

May 24, 2012

We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints

Steps
Import schema excluding constraints,rf_constraints,indexes with metadata only
Import schema with data only
spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes
execute on Destination the Index creation ddl
execute on Destination constraints and rf_constraints ddl

View 1 Replies View Related

PL/SQL :: What Kind Of Exception Can Raise A Select Statement Excluding NO_DATA_FOUND

Oct 23, 2013

what kind of exception can raise a select statement excluding NO_DATA_FOUND; For example i try to run the following: select * from departments where departments_id=11; In a situation like that what kind of error oracle can raise?I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.

View 25 Replies View Related

Backup & Recovery :: Restore And Recover Database Excluding Tablespace

Jul 27, 2011

I am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.

1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;

3) I take a separate Backup of DP_TS_LOB tablespace.

RMAN> BACKUP TABLESPACE DP_TS_LOB;

4) I backup all the archived redo logs.

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;

Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.

RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup;
RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup;
RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";

Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.

RMAN> startup nomount pfile=D:appdivaproduct11.1.0db_1databasepfile;
RMAN> restore database skip tablespace 'dp_ts_lob';
RMAN> alter database mount;
RMAN> sql 'alter database datafile 8 offline'; ==> datafile of DP_TS_LOB tablespace.

First I assumed "Recover Database" will only recover online database files. But got the following error.

RMAN> recover database;

Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored

Then I tried recovering data file separately, but same error.

RMAN> recover datafile 1;

Starting recover at 27-JUL-11
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file

View 4 Replies View Related

SQL & PL/SQL :: DBMS_JOB Not Executing On Time?

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

SQL & PL/SQL :: ORA-31603 When Using DBMS_JOB.SUBMIT

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

PL/SQL :: How To Run DBMS_JOB In Parallel And Serial

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

Starting A Function In Parallel Using DBMS_JOB

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

Server Administration :: DBMS_JOB Using Sys Account

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

SQL & PL/SQL :: DBMS_JOB / Maximum Number Of Failures A Job Can Allow

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

SQL & PL/SQL :: Dbms_job.submit How To Receive Output

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

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 View Related

DBMS_JOB.SUBMIT Can Pass Dynamic Argument Procedure

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

Server Administration :: Find Last Run Duration Of Job Which Was Scheduled Using DBMS_JOB

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

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 View Related

Server Administration :: DBMS_JOB Restarting / Database Shutdown

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

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

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

Performance Tuning :: Can Limit Dbms_job To Use Defined Amount Of CPU%

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

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

SQL & PL/SQL :: Enable To Run Procedure Through Job?

Mar 30, 2011

I am trying to execute procedure using dbms scheduler.but i am getting below errors

ORA-06550: line ORA-06550: line 1, column 407:
PLS-00103: Encountered the symbol "AMANORATEST" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "AMANORATEST" to continue.
, column :

but procedure is executing fine with sql commmand line.

View 1 Replies View Related

SQL & PL/SQL :: Enable Row Movement On IOT?

Nov 11, 2011

I have the table is partitioned.

Can I set the table table to "alter table x enable row movement" even when it is index organized table?

How do I know if the table is INDEX ORGANIZED TABLE.

View 3 Replies View Related

Enable / Disable Auditing From PHP

Dec 15, 2010

In PL/SQL Plus, i can enable/disable auditing when i connect as sysdba by using these command:

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE
SQL> shutdown
SQL> startup

I've done it successfully with PL/SQL Plus command line. But in PHP, how can i do that?How to execute "shutdown" and "startup" from PHP?
I've found this code for connect to oracle as sysdba:

oci_connect("/", "", null, null, OCI_SYSDBA);

From the following link:

[URL]......

But, i still can't execute "shutdown", "startup";

View 4 Replies View Related

Unable To Enable The Audit

Jul 5, 2012

I facing the below issue while enable the audit on table.

SQL> audit delete on test;
audit delete on test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE")
ORA-06512: at line 2

===
Related Information
===
SQL> select version from v$instance;

VERSION
-----------------
11.2.0.2.0

SQL> show parameter audit;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /orasw/product/11.2.0
/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB

SQL> SELECT TABLESPACE_NAME,owner from dba_tables where table_name='AUD$';

TABLESPACE_NAME OWNER
------------------------------ ------------------------------
AUDIT_DATA SYS

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved