Oracle
Text Installation & Index Creation Process in Oracle 10g
1 Installation
This
article will guide you in installing Oracle Text or Context on a 10g
database. Generally if we go for full packages installation, oracle will
install Context in the first time. But if you got a requirement to install it
on a database which doesn’t have it pre-installed, you can use below steps.
1.1 Oracle Text (FTS) API INSTALLATION Steps
- Connect to sys user as sysdba by sqlplus.
Ex: sqlplus
sys/****@pdmtd5 as sysdba
NOTE: Replace
“ **** ” with proper “password” and “
pdmtd5 ” with proper “
DB Name ”.
- Run @?/ctx/admin/catctx.sql
ctxsys sysaux temp nolock
catctx.sql will create necessary packages and will
create a user CTXSYS with the same password as username and will assign SYSAUX
as default tablespace and TEMP as temporary tablespace. nolock specifies
that account should not be locked.
-
? means location of $ORACLE_HOME and catctx.sql should be readable for Linux/Unix.
Ex: @/opt/appl/oracle/product/10.2.0/db/ctx/admin/catctx.sql ctxsys sysaux temp nolock;
- Grant execute on ctxsys.ctx_ddl to public user
This is to grant permissions on the package to
public users. connect to sys user.
Ex: grant execute on ctxsys.ctx_ddl to public;
- Run @?/ctx/admin/defaults/drdefxx.sql file in ctxsys user.
This script will create necessary objects required
for index creation. The last “xx” in the script name represents region name. so must be connect to ctxsys user.
For example, for US it should be drdefus.sql and for UK it is drdefuk.sql.
For example, for US it should be drdefus.sql and for UK it is drdefuk.sql.
-
? means location of $ORACLE_HOME.and drdefus.sql should be
readable for Linux/Unix.
Ex:
conn
ctxsys/ctxsys@pdmtd5
@/opt/appl/oracle/product/10.2.0/db/ctx/admin/defaults/drdefus.sql;
NOTE: Replace
“ pdmtd5
” with proper “ DB Name ”.
1.2 Check oracle text (fts) api installation process
Verification of the context
installation , Check dba_registry and connect to sys user as sysdba by sqlplus.
And Result should be valid status of Oracle Text.
such as :-
select COMP_NAME,VERSION,STATUS from dba_registry;
Ex:
conn sys/****@pdmtd5 as sysdba
col version format a14
col comp_name format a30
select COMP_NAME,VERSION,STATUS from dba_registry;
NOTE: Replace
“ **** ” with proper “password” and “
pdmtd5 ” with proper “
DB Name ”.
1.3 Grant Oracle text (fts) to usable user/schema
This is to grant permissions on user and it must be
run after that installation( for both installation process). connect to sys user.
GRANT CTXAPP to PROD2;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO
PROD2;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO
PROD2;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO
PROD2;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT
TO PROD2;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO
PROD2;
GRANT EXECUTE ON CTXSYS.CTX_REPORT
TO PROD2;
GRANT EXECUTE ON CTXSYS.CTX_THES TO
PROD2;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER
TO PROD2;
2 oracle text (fts) index creation process
2.1 No operation on BUSINESS_OBJECTS1:
While creating this index make sure no transaction is going on table BUSINESS_OBJECTS1, better DISCONNECT DB from application.
2.2 Connect to DB
conn PROD2/ <Password>@<DBName>
<Password> - Password for
PROD2 user name in desired database
<DBName> - desired db name
2.3 Create Index:
Following script is the
index creation script that needs to be run after successful login.
SQL Script Code:
Step-1:
begin
ctx_ddl.drop_preference('SUBSTRING_PREF');
end;
/
begin
ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/
Step-2:
begin
ctx_ddl.drop_preference('LEX_BUSINESS_OBJECTS1');
end;
/
begin
ctx_ddl.create_preference('LEX_BUSINESS_OBJECTS1','BASIC_LEXER');
ctx_ddl.set_attribute('LEX_BUSINESS_OBJECTS1', 'base_letter', 'YES');
ctx_ddl.set_attribute('LEX_BUSINESS_OBJECTS1', 'mixed_case', 'YES' );
ctx_ddl.set_attribute('LEX_BUSINESS_OBJECTS1','printjoins', '-;,@_');
end;
/
Step-3:
DROP INDEX
FTS_BUSINESS_OBJECTS1 force;
create index FTS_BUSINESS_OBJECTS1 on BUSINESS_OBJECTS1(name)
indextype is ctxsys.context
parameters ('wordlist SUBSTRING_PREF
MEMORY
128M
DATASTORE CTXSYS.DEFAULT_DATASTORE
SECTION
GROUP CTXSYS.AUTO_SECTION_GROUP
STOPLIST CTXSYS.EMPTY_STOPLIST
LEXER LEX_BUSINESS_OBJECTS1
SYNC (ON
COMMIT)');
commit;
3 check size of oracle text (fts) index
After creating the
index to validate weather index is created successfully the following commands
can be executed which will return the size of the created index.
exec TXTSUP_UTIL.INDEX_SIZE('FTS_BUSINESS_OBJECTS1');
or,
SELECT CTX_REPORT.INDEX_SIZE('FTS_BUSINESS_OBJECTS1') FROM DUAL;
4 INDEX MAINTAINENCE PROCESS
4.1 Connect to DB
conn PROD2/ <Password>@<DBName>
<Password> - Password for
PROD2 user name in desired database
<DBName> - desired db name
4.2 INDEX OPTIMIZATION SCRIPT
Following script is the index
optimization script that needs to execute to rebuild and optimize the index for
in db maintenance.
SQL Script Code:
begin
ctx_ddl.optimize_index('FTS_BUSINESS_OBJECTS1','FULL');
end;
/
Note: This script need to be included with PDM DB
maintenance script.
5 alter index syntax (REQUIRED IF ANY PARAMETER or INDEX in CHANGED)
-
If any
changes done in the index or any parameter is changed after index creation like
following command, then it required to optimize the index using following
ddl_optimize_index command to get better
performance.
EX:
ALTER INDEX FTS_BUSINESS_OBJECTS1 REBUILD PARAMETERS('replace
lexer LEX_BUSINESS_OBJECTS1');
begin
ctx_ddl.optimize_index('FTS_BUSINESS_OBJECTS1','FULL');
end;
/
