oracle 加全文索引,oracle全文索引的创建和使用，以及一些体会

ORA-29868: cannot issue DDL on a domain index marked as LOADING说明：在创建索引的时候断开、重启等导致索引中断没有执行成功，之后再drop或者rebuild等操作的时候都会报此错误

解决：只能drop index ind_name force强行删除，然后再重建

三、索引同步维护

用以下的两个job来完成(该job要建在和表同一个用户下) :

VARIABLE jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''index_name'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;           //同步

VARIABLE jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''myindex'',''FULL'');',

SYSDATE, 'SYSDATE + 1');

commit;         //优化

建完后手动运行下：

exec dbms_job.run(jobno);

※个人体会：运行job可能会有问题，此时可以单独运行索引，尝试一下

exec ctx_ddl.sync_index('index_name');

如果单独运行没有问题，则检查job是否写错或者当前操作的oracle数据库用户有无运行存储过程的权限

SQL> exec dbms_job.run(190);

begin dbms_job.run(190); end;

ORA-12011: execution of 1 jobs failed

ORA-06512: at "SYS.DBMS_IJOB", line 406

ORA-06512: at "SYS.DBMS_JOB", line 272

ORA-06512: at line 1

以上报错就是用户没有运行任何存储过程造成的，此时需要对用户加上这个权限：

SQL> grant execute any procedure to oracle_username;

再看一下job的情况

select * from user_jobs;

四、测试

关联查询： select * from table_name where contains (column_name,'keyword') >0;

SQL> select * from searchkeytbl where type='城市' and contains (key,'杨浦') >0;

USERNAME             TYPE                                     KEY

-------------------- ---------------------------------------- --------------------------------------------------------------------------------

mujian80             城市                                     上海市杨浦区

五、问题

加全文索引遇到的问题(不断更新)

SQL> create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer');

create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer')

ORA-24795: Illegal COMMIT attempt made

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvddl.IndexCreate

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvdml.MaintainKTab

ORA-24795: Illegal COMMIT attempt made

ORA-06512: at "CTXSYS.DRUE", line 160

ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

To avoid the error, please use one of the following solutions

1. Don't use a 32k-blocksized tablespace to store the internal index objects

- or -

2. Download Patch 5596325 from Metalink and apply it as described in the README file.看一下 可能是用于创建索引的表空间不够了

reports——>DBA——>total free space    pl/sql developer工具，查看表空间的剩余空间

select * from v$datafile;               查看数据文件信息