Oracle : How to change the start time of an schedule.

BEGIN
sys.DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"{USER}"."{SCHEDULE_NAME}"',
attribute => 'START_DATE',
value => '28-NOV-08 12.00.00.000000 AM');
END;
/

If you want to set to another timezone :

BEGIN
sys.DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"{USER}"."{SCHEDULE_NAME}"',
attribute => 'START_DATE',
value => '28-NOV-08 12.00.00.000000 AM MET');
END;
/

http://www.dba-oracle.com/t_ora_01840_input_value_not_long_enough_for_date_format.htm

Oracle : Changing the Frequency of schedules in Oracle 10g

How to change the frequency of schedules in Oracle 10g
BEGIN
sys.DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"{OWNER}"."{SCHEDULE_NAME}"',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=TUE,WED,THU,FRI,SAT;BYHOUR=10;BYMINUTE=30;BYSECOND=0');
END;/

Oracle : Granting privileges via roles does not work ?

While working on an migration from oracle 8i to oracle 10g I discovered the following, granting of privileges via role does not work for creating procedures, functions or views that have dependencies on objects owned by other schema. It was still working in 8.1.6.

I did a small test by running the follow on 8i and 10g database seperately

create user a identified by a default tablespace DATA01 quota unlimited on DATA01;
create user b identified by b default tablespace DATA01 quota unlimited on DATA01;

grant create session to a;
grant create session to b;

create table a.t1 (v1 char);
create role test_role;
grant all on a.t1 to test_role;
grant test_role to b;
create public synonym t1 for a.t1;

create or replace procedure b.test
as
vn_Count NUMBER(10);
BEGIN
SELECT count(*) INTO vn_Count FROM t1;
END;/

In the case of 8i database

Procedure created.

In the case of 10g database

Warning: Procedure created with compilation errors.
SQL> show errorErrors for PROCEDURE B.TEST:
LINE/COL ERROR-------- -----------------------------------------------------------------5/3 PL/SQL: SQL Statement ignored
7/23 PL/SQL: ORA-00942: table or view does not exist

If the object privileges is granted directly to the user (explicitly granted ) rather than via the role in the 10g database,

grant all on a.t1 to b;

Then the procedure will be created successfully. Hence this concludes that granting via roles does not work for procedure, function and view creation for Oracle 9i onwards.


http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#CHDEDFHE http://www.adp-gmbh.ch/ora/err/ora_00942.html

Extracted from the documents

5.1.4.1 Privileges Required to Create Views

To create a view, you must meet the following requirements:
You must have been granted one of the following system privileges, either explicitly or through a role:
The CREATE VIEW system privilege (to create a view in your schema)
The CREATE ANY VIEW system privilege (to create a view in another user's schema)
You must have been explicitly granted one of the following privileges:
The SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view
The SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges
In addition, in order to grant other users access to your view, you must have received object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you have not, then grantees cannot access your view.

ORA-27486: insufficient privileges when running dbms_scheduler.add_event_queue_subscriber()

Problem :

When running dbms_scheduler.add_event_queue_subscriber() using the job owner ( not SYS )
and hitting the following error :

ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 2959
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2242
ORA-06512: at line 1

Solution :

grant CREATE JOB to '{JOB_OWNER}';

Oracle : ORA-27375: valid agent name must be specified for secure queues

When executing sys.dbms_scheduler.create_event_schedule to create event schedule I hit the following error :

ERROR at line 1:
ORA-27375: valid agent name must be specified for secure queues
ORA-06512: at "SYS.DBMS_ISCHED", line 679
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1152
ORA-06512: at line 2

Found that we need to run dbms_scheduler.add_event_queue_subscriber();
But when I run dbms_scheduler.add_event_queue_subscriber() using the job owner ( not SYS ) I hit the following error :

ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 2959
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2242
ORA-06512: at line 1

So I ran dbms_scheduler.add_event_queue_subscriber('{JOB_OWNER}'); using SYS instead and manage to create the schedule and create the job by executing sys.dbms_scheduler.create_job

but when I try to enable the job ( log on as SYS ) by running sys.dbms_scheduler.enable I once again hit the error


BEGIN
*
ERROR at line 1:
ORA-27375: valid agent name must be specified for secure queues
ORA-06512: at "SYS.DBMS_ISCHED", line 2751
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1794
ORA-06512: at line 2

Eventually I found out that the only way to subscribe to the event queue ( SYS.SCHEDULER$_EVENT_QUEUE ) is to run dbms_scheduler.add_event_queue_subscriber as the job owner.

You can verify whether the agent is correctly created or not by running the following

select * from SYS.SCHEDULER$_EVTQ_SUB;
AGT_NAME UNAME
------------------------------ ------------------------------
SYS SYS
ABCD ABCD

The AGT_NAME and UNAME should be same.

After some searching in order to solve the problem we need grant CREATE JOB to the owner so that the user can subscribe to the event queue by executing dbms_scheduler.add_event_queue_subscriber.

After running the dbms_scheduler.add_event_queue_subscriber using the job owner account, I was able to enable the job.

Problem solved.

oracle 10g dbms_scheduler explained

http://downloadwest.oracle.com/docs/cd/B13789_01/server.101/b10739/schedover.htm#sthref3230
http://www.oradev.com/dbms_scheduler.jsp
http://www.oraclebase.com/articles/10g/Scheduler10g.php#jobs
http://www.psoug.org/reference/dbms_scheduler.html

Oracle : TEMPORARY Tablespaces and TEMPFILES

Allocation of Temporary Segments for Temporary Tables and Indexes

Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.

Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref383

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#i5696

http://beginapps.blogspot.com/2008/04/how-to-use-temporary-tablespaces.html

http://www.orafaq.com/node/2