Configuration of full-text retrieval in oracle

After you manually create a database, you only have basic functions, and some still need to use the full-text retrieval function. Therefore, you need to configure full-text retrieval. Follow these steps: 1. Check whether the database has the full-text retrieval function (this is for a database that has been built and used) to check whether the user has a ctxsys user and whether the role has a ctxapp role.

After you manually create a database, you only have basic functions, and some still need to use the full-text retrieval function. Therefore, you need to configure full-text retrieval. Follow these steps: 1. Check whether the database has the full-text retrieval function (this is for a database that has been built and used) to check whether the user has a ctxsys user and whether the role has a ctxapp role.

After you manually create a database, you only have basic functions, and some still need to use the full-text retrieval function. Therefore, you need to configure full-text retrieval.

Follow these steps:

1. Check whether the database has the full-text retrieval function (this is for the database that has been built and used)

Check whether a ctxsys user exists in the user and whether the ctxapp role exists in the role. If one of the preceding two does not meet (does not exist), it indicates that the full-text search function has not been installed.

When using the contains function, if full-text search is not available, an error is returned.

2. If not, you must manually create a space for full-text search.

Sqlplus/as sysdba -- enter the Console

Create tablespace Idx_ctxsys datafile '/oradata/sg186fx/ctxsys01.dbf size 10240 M autoextend on next 32 M maxsize 20480 M; -- create a tablespace for full-text search

3. To create the users and roles used for full-text search and corresponding packages, run the script cd $ ORACLE_HOME/ctx/admin/catctx. SQL that comes with oracle.

Or execute in sqlplus:

@? /Ctx/admin/catctx. SQL ctxsys Idx_ctxsys temp nolock

When executing this script, enter several parameters. The first parameter ctxsys is the password of the ctxsys user.

The second parameter Idx_ctxsys is the tablespace to be used by the ctxsys user.

The third parameter temp is the temporary tablespace used by the ctxsys user.

The fourth parameter nolock indicates that the ctxsys user is unlocked.

4. After creation, log on to the ctxsys user

Connect ctxsys/ctxsys

Run the following Script :@? /Ctx/admin/defaults/drdefus. SQL (this is a very important script. The information created by this script will be used to create the index later)

5. Create a full-text index syntax analyzer

First, you must specify the users who use full-text indexing. The sgpm users who want to use full-text indexing

Therefore, grant execute on ctxsys. ctx_ddl to sgpm with grant option;

Connect sgpm/sgpm

Set the syntax analyzer: exec ctx_ddl.drop_preference ('chinalexer ');

Exec ctx_ddl.create_preference ('chinalexer ', 'Chinese _ lexer ');

Set the lexical attribute: exec ctx_ddl.drop_preference ('idx _ c_store ');

Begin

Ctx_ddl.create_preference ('idx _ c_store', 'Basic _ store ');

Ctx_ddl.set_attribut ('idx _ c_store ',' I _ TABLE_CLAUSE ', 'tablespaces Idx_ctxsy ');

Ctx_ddl.set_attribute ('idx _ c_store', 'I _ INDEX_CLAUSE', 'tablespace Idx_ctxsy compress 2 ');

End;

/

6. Create an index

Create index sgpm. idx_c_cons_name on sgpm. c_cons (cons_name) indextype is ctxsys. context parameters ('lexer chinalexer storage idx_c_store ');

7. Synchronize Indexes

Variable jobno number;

Begin

Dbms_job.submit (: jobno, 'pkg _ sp_tools.p_cont_sys_index (); ', sysdate, 'trunc (sysdate) + 19/24 + 1'); -- executes a personalized method.

End;

/

Normally, exec ctx_ddl.sync_index ('idx _ c_cons_name ') is used ');

At this point, the full-text search is successfully created, and the contains function can be used normally.

Note: The ORA-29879: cannot create multiple domain index on a column listusing same indextype appears during creation, which indicates that the index has been created for another user.