Thursday, November 26, 2009

ORA-29855 error in oracle and solution

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code


- wish this helps you too..

ORA-29855 error in oracle and solution

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code


- wish this helps you too..

Wednesday, November 11, 2009

Job scheduling in oracle 10g

The DBMS_JOB, DBMS_SCHEDULER packages are installed when the Oracle database is installed. The dbmsjob.sql script has source code of DBMS_JOB. In Oracle 10g the DBMS_JOB package functionality is replaced and/or enhanced by the DBMS_SCHEDULER package. The DBMS_JOB package is depricated and provided for backward compatibility only.

'CREATE JOB' privilege is required to create and run jobs.

There are vaious methods to do job scheduling in oracle 10g but the simple method of scheduling a job is as follows. ( I used couple of times hence thought good to post here)..


create or replace package pkg1
as
procedure makeentry;
end pkg1;
/

create or replace package body pkg1
as
procedure makeentry
is
begin
insert into test1 values (to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') );
commit;
end;
end;


BEGIN
dbms_scheduler.create_job(
job_name => 'makeentry',
job_type => 'STORED_PROCEDURE',
job_action => 'PKG1.MAKEENTRY',
repeat_interval => 'FREQ=DAILY;BYHOUR=13;BYMINUTE=0;BYSECOND=0',
comments => 'cleanup job runs every day at 1 pm',
enabled=>true);
END;


=============Notes==============================

Specifying Intervals

FREQ takes YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
FREQ=DAILY; INTERVAL=7 executes a job every 7 days
FREQ=HOURLY; INTERVAL=2 executes a job every other hour
FREQ=WEEKLY; BYDAY=FRI executes a job every Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI executes a job every other Friday.
FREQ=MONTHLY; BYMONTHDAY=1 executes a job on the last day of the month
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31 executes a job on the 31st of December.
FREQ=MONTHLY; BYDAY=2FRI executes a job every second Friday of the month

.. Hope this help you as well.....

Job scheduling in oracle 10g

The DBMS_JOB, DBMS_SCHEDULER packages are installed when the Oracle database is installed. The dbmsjob.sql script has source code of DBMS_JOB. In Oracle 10g the DBMS_JOB package functionality is replaced and/or enhanced by the DBMS_SCHEDULER package. The DBMS_JOB package is depricated and provided for backward compatibility only.

'CREATE JOB' privilege is required to create and run jobs.

There are vaious methods to do job scheduling in oracle 10g but the simple method of scheduling a job is as follows. ( I used couple of times hence thought good to post here)..


create or replace package pkg1
as
procedure makeentry;
end pkg1;
/

create or replace package body pkg1
as
procedure makeentry
is
begin
insert into test1 values (to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') );
commit;
end;
end;


BEGIN
dbms_scheduler.create_job(
job_name => 'makeentry',
job_type => 'STORED_PROCEDURE',
job_action => 'PKG1.MAKEENTRY',
repeat_interval => 'FREQ=DAILY;BYHOUR=13;BYMINUTE=0;BYSECOND=0',
comments => 'cleanup job runs every day at 1 pm',
enabled=>true);
END;


=============Notes==============================

Specifying Intervals

FREQ takes YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
FREQ=DAILY; INTERVAL=7 executes a job every 7 days
FREQ=HOURLY; INTERVAL=2 executes a job every other hour
FREQ=WEEKLY; BYDAY=FRI executes a job every Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI executes a job every other Friday.
FREQ=MONTHLY; BYMONTHDAY=1 executes a job on the last day of the month
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31 executes a job on the 31st of December.
FREQ=MONTHLY; BYDAY=2FRI executes a job every second Friday of the month

.. Hope this help you as well.....