Archive

Posts Tagged ‘Database’

Oracle CTXSYS LEXER issues

January 17th, 2011 Comments off

Hello,

I recently tried to troubleshoot an application issue that was related to a text index. The index itself appeared in valid state but there was obviously some kind of a problem with it.
I started with the following

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> DROP INDEX USER5.INDX71;
Index dropped.
SQL>CREATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
REATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
*
ERROR at line 1:
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

There was something wrong with the CTXSYS schema and especially with the DEFAULT_LEXER.
I fixed this by reapplying the correct drdefXX.sql script for the database locale.

Read more…

Categories: Database, Oracle Tags: , , ,

Gather Schema and Index Statistics in Oracle

January 28th, 2010 2 comments

In this post I would like to draw your attention to the Optimizer in Oracle 10g.
The best place to start is the DBMS_STATS package reference page.

The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.
Once you get familiar with the documentation of the package, you will actually know which approach is best for your database and schemas. In my case I have to gather schema statistics once per week with a specific estimate_percent value and I have to gather schema index statistics every night. For this purpose I have created the following simple PL/SQL. I recommend if you create jobs and schedule them as appropriate.

-- Gathering Schema Statistics
execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
-- Gathering Index Statistics
set serveroutput on
begin
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
)
loop
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
end loop;
end;
/

Read more…

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 10

February 20th, 2009 3 comments

When I finished the clusterware install in the lab environment I have continued with the database software itself but it appears that I needed a VMware ESXi Update 3 required! The kernel version of SLES failed verification and upon an upgrade of the kernel through YAST both machines were unusable since they would not boot.

This is an obstacle until completed since I can’t continue with the How-to until this is done.

In Part 9 of the Oracle RAC How-to we have completed successfully the installation of the Oracle Clusterware services on the shared storage for the two Suse Linux Enterprise Server 9 SP5 nodes.

Read more…

Oracle alter table change column datatype

February 17th, 2009 Comments off

Let’s say you had a field CITIZEN in table called EMPLOYEE that was created as VARCHAR2(10) but this has to be changed to VARCHAR2(3). The syntax to change the the column datatype in Oracle is as follows:


ALTER TABLE table_name MODIFY ( column_name VARCHAR2(3));


If there is existing column data you should first export that data and then import it after the datatype change. Make sure the exported data will be suitable for import upon the change (You can’t import a row with CITIZEN column of 5 after you have changed the column to VARCHAR2(3)).

Categories: Database, Oracle Tags: , ,

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 8

February 16th, 2009 3 comments

Oracle Clusterware Installation

Install the xntpd service and configure it.
You can use the Yast management console to do so.
It is extremely important that both nodes are configured to use ntp server and that they are regularly being updated.
If there is any difference at all within the date of all nodes this could result into inoperable cluster.

1. Copy the cpio.gz file to the first node and unzip the contents of the cpio file


#gunzip 10201_clusterware_linux_x86_64.cpio.gz
#cpio -idmv < 10201_clusterware_linux_x86_64.cpio

Read more…