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

Oracle :ORA-29283: INVALID FILE OPERATION USING ORACLE USER AS SECONDARY GROUP ON UNIX

Problem :

The permissions on directory are :
drwxrwxr-x 2 oraext oraext 1392 2008-10-29 16:32 files
oraext is the secondary group of oracle user.

1. When the anonymous block is run as SYS user it runs succesfully.

create or replace directory dir_temp as '/home/oraext/files';
declare
f utl_file.file_type;
begin
f := utl_file.fopen('DIR_TEMP', 'something.txt2', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;/


2. When logged in to OS as oracle user and run the following command the file gets created .

However

When the anonymous block is run as an other schema, the following error is thrown :
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at line 4

READ and WRITE on the directory has been granted to the other schema.

Solution :

Restart the listener. The issue got resolved.

QUESTION========
What could be the reason for getting ora-29283 error , if though the oracle user can create a file using touch command on the OS ?

ANSWER======
Once you have done any changes to the folders accessed by utl_file directoiries you should always restart the listener process. Restarting the listener will inherit the recently changes permission and hence enabling oracle user to write a file from database using utl_file package succesfully.

Oracle : Recompling invalid object using utlrp.sql and utlprp.sql

Reference :
http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0".

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Oracle : Example to set the utl_file_dir parameter

Syntax :
alter system set utl_file_dir='{directory 1}', '{directory 2}' ... '{directory n}' scope=spfile;

Example :
alter system set utl_file_dir='/home/oracle/a', '/home/oracle/b' scope=spfile;

Take note that this parameter needs a restart to be effective.

Oracle : PL/SQL to test writing to an OS directory from the database.

Many time I had developer hitting errors as such
ERROR : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


Below is an sql I got from somewhere that can be used to test out writing / reading from the directory.

( Also check that you have set the parameter utl_file_dir correctly )

SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);

PROCEDURE recNgo (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' str);
UTL_FILE.FCLOSE (fid);
END;

BEGIN

/* Change the directory name to one to which you at least
THINK you have read/write access.
*/

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change existing file name here}', 'R');
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v); UTL_FILE.FCLOSE (fid);

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change new file name here}', 'W');
UTL_FILE.PUT_LINE (fid, v); UTL_FILE.FCLOSE (fid);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN recNgo ('invalid_path');
WHEN UTL_FILE.INVALID_MODE THEN recNgo ('invalid_mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ('invalid_filehandle');
WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ('invalid_operation');
WHEN UTL_FILE.READ_ERROR THEN recNgo ('read_error');
WHEN UTL_FILE.WRITE_ERROR THEN recNgo ('write_error');
WHEN UTL_FILE.INTERNAL_ERROR THEN recNgo ('internal_error');

END;/

Hi

Ok just another blog that I will be starting.

Just an short introduction about myself, I am Andrew, currently a DBA working mainly on Oracle databases. Been in IT line for approximately 8 years.

The purpose of this blog is to record down the daily problems and solutions I had encountered.

In doing so I hope to build an knowleage base which can be referenced easily by myself as well as many others who are facing similar situations. :)