博客访问： 218855
博文数量： 241
注册时间：
good luck.
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
This note assumes the Oracle software is installed into the $ORACLE_HOME.On Unix, the environment variable LD_LIBRARY_PATH must be set to$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib[@more@]Installation script should look as follows :============================================Note: In SQL*Plus we use '?' instead of $ORACLE_HOME ------------------- cut here ------------------------------connect SYS/password as SYSDBAset echo oncol comp_name for a30spool textinstall.logRem =======================================================================Rem Start of Text loadingRem =======================================================================EXECUTE dbms_registry.loading('CONTEXT', 'Oracle Text');Rem dr0csys.sql
start ?/ctx/admin/dr0csys ctxsys DRSYS TEMP*** NOTE: We assume that DRSYS tablespace already exists, if not create a ***
tablespace for Oracle Text data dictionary tables, for example:***
SQL> CREATE TABLESPACE tablespace_name ***
DATAFILE 'ORACLE_BASEoradatadb_namedrsys01.dbf' SIZE 40m;REM ========================================================================REM Install CTXSYS objectsREM ========================================================================connect CTXSYS/ctxsysstart ?/ctx/admin/dr0inst /ctx/lib/libctxx9.sostart ?/ctx/admin/defaults/drdefus.sqlREM ========================================================================REM Upgrade CTXSYS to the latest patchset versionREM ========================================================================connect SYS/password as SYSDBAstart ?/ctx/admin/ctxpatch.sqlselect comp_name, version, status from dba_spool off------------------- cut here ------------------------------Review the output file textinstall.log for errors.Installation of Oracle Text 9.2.0.x is complete.Explanation of installation script==================================You need to be connected as SYS to create CTXSYS userconnect SYS/password as SYSDBAEXECUTE dbms_registry.loading('CONTEXT', 'Oracle Text');This will update the DBA_REGISTRY for Oracle Text loading.start ?/ctx/admin/dr0csys ctxsys DRSYS TEMP
- is the ctxsys user password
- is the default tablespace for ctxsys
- is the temporary tablespace for ctxsys This script sets up the ctxsys user, which owns the text supporting tables.
At this point it will have no objects.Than we connect as CTXSYS user to create necessary objects.connect CTXSYS/ctxsysstart ?/ctx/admin/dr0inst /ctx/lib/libctxx9.soOn Solaris, Aix platform with $ORACLE_HOME of /u1/app/oracle/product/9.2.0this part should look like: start ?/ctx/admin/dr0inst /u1/app/oracle/product/9.2.0/ctx/lib/libctxx9.soOn HP-UX you would run:start ?/ctx/admin/dr0inst /u1/app/oracle/product/9.2.0/ctx/lib/libctxx9.sl With NT you would run with %ORACLE_HOME% of C:oracle9.2.0start ?ctxadmindr0inst C:oracle9.2.0inoractxx9.dll*** Note: The error ORA-01031: insufficient privileges while CTXSYS ***
calls the dbms_registry package can be ignored, see Last script that is called installs defaults preferences: default lexer, wordlist and stoplist.This scripts are located in $ORACLE_HOME/ctx/admin/defaults and name ofscripts is drdef.sql In above example we run US specific scriptstart ?/ctx/admin/defaults/drdefus.sqlThen we connect as SYS user to upgrade Text to the latest Patchset version.DBA_REGISTRY is also updated to the correct Oracle Text version, status.connect SYS/password as SYSDBAstart ?/ctx/admin/ctxpatch.sql*** Note: ERROR ORA-00001: unique constraint (CTXSYS.DRC$OAT_KEY) violated***
Above error shows that the insert fails as the record with unique ***
value in that table exist.***
That means the record that needs to be inserted is already existing***
and hence can be ignored.Text Installation verification-------------------------------1. Check to make sure that all Text objects were created in CTXSYS schema
and correct version is installed2. Check to make sure that there are not invalid objects for CTXSYS.
You should get: "no rows selected"
If there are then you can compile each invalid object manually.3. Check to ensure that the library is correctly installed------------------- cut here ------------------------------connect SYS/password as SYSDBAset pages 1000col object_name format a40col object_type format a20col comp_name format
a30column library_name format a8 column file_spec format a60 wrapspool text_install_verification.log-- check on setupselect comp_name, status, substr(version,1,10) as version
from dba_registry
where comp_id = 'CONTEXT';select * from ctxsys.ctx_select substr(ctxsys.dri_version,1,10) VER_CODEselect count(*)
from dba_objects where owner='CTXSYS';-- Get a summary countselect object_type, count(*)
from dba_objects where owner='CTXSYS' group by object_-- Any invalid objectsselect object_name, object_type, status
from dba_objects
where owner='CTXSYS'
and status != 'VALID'order by object_select library_name,file_spec,dynamic,status from all_libraries
where owner = 'CTXSYS';------------------- cut here ------------------------------Example output of text_install_verification.log after valid installtion of 9.2.0.5.0 on Solaris. The number of ctxsys objects might differentiate afterapplying a patchset.-------------------------------------------------------------------------------SQL>
select comp_name, status, substr(version,1,10) as version
from dba_registry
where comp_id = 'CONTEXT';
VERSION------------------------------ ----------- ------------------------------Oracle Text
9.2.0.5.0 SQL> select * from ctxsys.ctx_ VER_DICT
VER_CODE--------- ---------------------------9.2.0.5.0 9.2.0.5.0SQL> select substr(ctxsys.dri_version,1,10) VER_CODEVER_CODE
------------------------------
SQL> select count(*)
from dba_objects where owner='CTXSYS';
----------
SQL> select object_type, count(*)
from dba_objects where owner='CTXSYS'
group by object_OBJECT_TYPE
-------------------- ----------
PACKAGE BODY
14 rows selected.SQL> select object_name, object_type, status
from dba_objects
where owner='CTXSYS'
and status != 'VALID'
order by object_no rows selectedSQL> select library_name,file_spec,dynamic,status
from all_libraries
where owner = 'CTXSYS';LIBRARY_ FILE_SPEC
-------- ------------------------------------------------------------ - ------- DR$LIB
/emea/rdbms/32bit/app/oracle/product/9.2.0/lib/libctxx9.so
-------------------------------------------------------------------------------Additional configuration------------------------Oracle Text do NOT need configuration of external procedures (extproc), exceptfor one new document service function -- ctx_doc.ifilter, the on-demand INSO filtering call. If you don't use this function, you DON'T need to set upthe listener and extproc. This configuration is not covered by this document.For more information check
Installation of InterMedia Text version 8.1.xSteps to Deinstall Oracle Text Manually---------------------------------------- Note: before deinstalling Oracle Text, it is best to first drop all
Text Indexes built in schemas other than CTXSYS.------------------- cut here ------------------------------connect CTXSYS/ctxsyscol comp_name for a30set echo onspool textdeinstall.logstart ?/ctx/admin/dr0drop.sqldrop publdrop pudrop pdrop pudrop puconnect SYS/password as SYSDBARem =======================================================================Rem dba_registry removalRem =======================================================================EXECUTE dbms_registry.removed('CONTEXT');Rem =======================================================================Rem now drop CTXSYS itselfRem =======================================================================start ?/ctx/admin/dr0dsys.sqlselect comp_name, version, status from dba_spool off
阅读(1347) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。Oracle 如何创建和使用全文索引
作者：xudec
字体：[ ] 类型：转载 时间：
Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组 以dr$开头的表中，同时记下该term出现的位置、次数、hash 值等信息。
不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR函数和LIKE操作符实现。
SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') & 0;
SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';
　　有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候.然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能 建立全文检索的步骤步骤一 检查和设置数据库角色首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。 默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。 步骤二 赋权 在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例：
grant execute on ctx_
一、设置词法分析器
　　Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元（Oracle称为term）找出来，记录在一组以dr$开头的表中，同时记下该term出现的位置、次数、hash值等信息。检索时，Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分（score）,即所谓的‘匹配率'。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个：
　　basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾'处理，如if , is等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了'这句话为例，basic_lexer分析的结果只有一个term ,就是‘中国人民站起来了'。此时若检索‘中国'，将检索不到内容。
　　chinese_vgram_lexer:专门的汉语分析器，支持所有汉字字符集（ZHS16CGB231280ZHS16GBKZHT32EUCZHT16BIG5ZHT32TRISZHT16MSWIN950ZHT16HKSCSUTF8）。该分析器按字为单元来分析汉语句子。‘中国人民站起来了'这句话，会被它分析成如下几个term: ‘中'，‘中国'，‘国人'，‘人民'，‘民站'，‘站起'，起来'，‘来了'，‘了'。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽'，但效率则是差强人意。
　　chinese_lexer:这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站'，‘站起'在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8,如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer.
如果不做任何设置，Oracle缺省使用basic_lexer这个分析器。要指定使用哪一个lexer,可以这样操作：
ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
其中my_lexer是分析器名。
二、建立全文索引
在建立intermedia索引时，指明所用的lexer:
CREATE INDEX& myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');
※个人体会：全文索引建立后，用pl/sql developer工具view table，在index这一栏是看不到索引信息的。
而本人在删除全文索引时遇到过一下报错：
SQL& drop index searchkeytbl_
drop index searchkeytbl_key
ORA-29868: cannot issue DDL on a domain index marked as LOADING
解决方法：
ORA-29868: cannot issue DDL on a domain index marked as LOADING
说明：在创建索引的时候断开、重启等导致索引中断没有执行成功，之后再drop或者rebuild等操作的时候都会报此错误
解决：只能drop index ind_name force强行删除，然后再重建
三、索引同步维护
用以下的两个job来完成(该job要建在和表同一个用户下) :
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''index_name'');',
SYSDATE, 'SYSDATE + (1/24/4)');
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''myindex'',''FULL'');',
SYSDATE, 'SYSDATE + 1');
建完后手动运行下：
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);
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_
再看一下job的情况
select * from user_
关联查询： 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
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$&&&&&&&&&&&&& 查看数据文件信息
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具1.查看用户：
select * from dba_users WHERE username='CTXSYS';select * from dba_users WHERE username='CTXSYS';
select * from dba_roles WHERE ROLE = 'CTXAPP'
解锁用户：
ALTER USER CTXSYS ACCOUNT UNLOCK;
角色授权：
GRANT CTXAPP TO YLFWZB;或者GRANT EXECUTE ON ctx_ddl TO YLFWZB;
2.建立索引,并设置索引参数
& CTX_DDL.CREATE_PREFERENCE('my_datastore_prefs1', 'FILE_DATASTORE');
& CTX_DDL.SET_ATTRIBUTE('my_datastore_prefs1', 'path', 'F:\file');--&数据存储（Datastore）类
& CTX_DDL.CREATE_PREFERENCE('my_lexer', 'chinese_lexer');---&词法分析器（Lexer）类
& --& ctx_ddl.create_stoplist('my_stoplist'); --&非索引字表（Stoplist）类
& --& ctx_ddl.add_stopword('my_stoplist','有限公司');&
& --& ctx_ddl.add_stopword('my_stoplist','股份有限公司');
& --create index YU_TEST_INDEX on YU_TEST(name) indextype is CTXSYS.CONTEXT parameters('lexer my_lexer stoplist my_stoplist');&
& --create index mydocs_text_index on mydocs(thefile) indextype is ctxsys.context parameters('datastore mydatastore_prefs Filter ctxsys.inso_filter Lexer my_lexer');
--ctx_ddl.drop_preference('my_datastore_prefs1');删除索引参数
【权限报错处理方式：
ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
【查看索引使用错误信息
& & &select * from ctxsys.ctx_user_index_
4.建表，索引
create table mydocs(id number primary key,title varchar2(255),thefile varchar2(255));
create index mydocs_text_index on mydocs(thefile) indextype is ctxsys.context parameters('datastore my_datastore_prefs1 Filter ctxsys.inso_filter Lexer my_lexer');
--thefile必须是非中文
& INSERT INTO MYDOCS (ID, TITLE, THEFILE) VALUES (1, 'DOC1', '1.doc');
& INSERT INTO MYDOCS (ID, TITLE, THEFILE) VALUES (2, 'DOC1', '2.docx');
& INSERT INTO MYDOCS (ID, TITLE, THEFILE) VALUES (3, 'DOC1', '3.pdf');
【自动产生的表&DR$myindex$I，DR$myindex$K，DR$myindex$R，DR$myindex$N】
5.同步索引
&&& ctx_ddl.sync_index('mydocs_text_index');
&&& ctx_ddl.optimize_index('mydocs_text_index','full');
6.测试是否成功
& & select * from mydocs& where contains(thefile,'兴趣')&0;
oracle text 大文本检索 适合的场景：应用服务器和数据库服务器在同一个服务器中。如果不是就考虑apache 的luence技术代替
参考的文章：
比较详细：
contain用法
网页检索：
http://blog.itpub.net/271283/viewspace-1022075/
阅读(...) 评论()Oracle（28）
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 31 14:09:50 2012
Copyright (c) , Oracle. &All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL& &spool text_install.txt
SQL& @?/ctx/admin/catctx.sql oracle SYSAUX TEMP NOLOCKc
...creating user CTXSYS
old & 2: identified by &pass default tablespace &tbs temporary tablespace &ttbs
new & 2: identified by oracle default tablespace SYSAUX temporary tablespace TEMP
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
。。。。。
SQL& spool text_install.txt
SQL& @?/ctx/admin/catctx.sql oracle SYSAUX TEMP NOLOCK
...creating user CTXSYS
old & 2: identified by &pass default tablespace &tbs temporary tablespace &ttbs
new & 2: identified by oracle default tablespace SYSAUX temporary tablespace TEMP
User created.
Grant succeeded.
。。。。。
SQL& connect &CTXSYS&/&oracle&
Connected.
SQL& @?/ctx/admin/defaults/dr0defin.sql &AMERICAN&;
old & 1: SELECT DECODE('&nls_language',
new & 1: SELECT DECODE('AMERICAN',
Creating lexer preference...
PL/SQL procedure successfully completed.
Creating wordlist preference...
PL/SQL procedure successfully completed.
SQL& set pages 1000
SQL& col object_name format a40
SQL& col object_type format a20
SQL& col comp_name format a30
SQL& column library_name format a8
SQL& column file_spec format a60 wrap
SQL& spool text_install_verification.log
SQL& select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
SQL& select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
COMP_NAME & & & & & & & & & & &STATUS & & & & & & & & VERSION
------------------------------ ---------------------- --------------------
Oracle Text & & & & & & & & & &VALID & & & & & & & & &11.2.0.3.0
SQL& select * from ctxsys.ctx_
VER_DICT & & & & & & VER_CODE
-------------------- --------------------
11.2.0.3.0 & & & & & 11.2.0.3.0
SQL& select substr(ctxsys.dri_version,1,10) VER_CODE
--------------------
11.2.0.3.0
SQL& select count(*) from dba_objects where owner='CTXSYS';
& COUNT(*)
----------
& & & &388
SQL& select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_
OBJECT_TYPE & & & & & &COUNT(*)
-------------------- ----------
SEQUENCE & & & & & & & & & & &3
PROCEDURE & & & & & & & & & & 2
OPERATOR & & & & & & & & & & &6
LOB & & & & & & & & & & & & & 2
LIBRARY & & & & & & & & & & & 1
PACKAGE & & & & & & & & & & &74
PACKAGE BODY & & & & & & & & 63
TYPE BODY & & & & & & & & & & 6
TABLE & & & & & & & & & & & &50
INDEX & & & & & & & & & & & &63
VIEW & & & & & & & & & & & & 77
FUNCTION & & & & & & & & & & &2
INDEXTYPE & & & & & & & & & & 4
TYPE & & & & & & & & & & & & 35
14 rows selected.
SQL& select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_
no rows selected
SQL& spool off
原文链接：http://blog.csdn.net/lunar2000/article/details/8455350
&&相关文章推荐
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：143904次
积分：1939
积分：1939
排名：第18493名
原创：40篇
转载：51篇
(1)(5)(4)(11)(19)(13)(15)(8)(7)(12)