Oracle Database 12c: How to use full-text search with Oracle Database 12c?
After created a PDB, let's run an full-text search example. In this example, we will use Oracle Database 12c to index file names so that we can perform full-text search to find the relevant documents.
Let's get started. We will use the c##dba1 and demo user created in How to create users in a multitenant database?. Step 1: Grant the necessary search role/privileges to the demo user. In the following process, we use the common DBA user c##dba1 to perform the SYS adminstration operations. |
Related Tutorials |
SQL> connect c##dba1/dba as sysdba Connected. SQL> alter session set container=pdbdemo; Session altered. SQL> execute ctxsys.ctx_adm.set_parameter('FILE_ACCESS_ROLE','ctxapp'); PL/SQL procedure successfully completed. SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE'; PAR_VALUE --------------------------------------------------------------------- CTXAPP SQL> grant ctxapp to demo; Grant succeeded.
Why I got the "DRG-10764: only SYS can modify FILE_ACCESS_ROLE" when running the command." error?
Details: When running the "ctxsys.ctx_adm.set_parameter('FILE_ACCESS_ROLE','ctx_app')command, I got the following error:
ORA-20000: Oracle Text error:
DRG-10764: only SYS can modify FILE_ACCESS_ROLE" when running the command " execute "?
Answer: You have to login as SYS user to run the the command.
Step 2: Create the new tablespace for search applications.
SQL> col file_name format A65 col TABLESPACE_NAME heading 'TBS_NAME' format A8 col FILE_ID format 9999999 select FILE_NAME, TABLESPACE_NAME from cdb_temp_files; FILE_NAME TBS_NAME -------------------------------------------------------------- -------- C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBDEMO\PDBDEMO_TEMP01.DBF TEMP SQL> create tablespace TBS_SEARCH datafile 'C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBDEMO\tbs_search_01.dbf' size 10M autoextend on; Tablespace created. SQL> col tablespace_name format a30 SQL> select tablespace_name from dba_tablespaces; TBS_NAME ------------------------------ SYSTEM SYSAUX TEMP TBS_SEARCH SQL>alter user demo default tablespace TBS_SEARCH quota unlimited on TBS_SEARCH; User altered.
SQL> connect demo/demo@localhost:1522/pdbdemo.us.oracle.com SQL> select sys_context('USERENV', 'CON_NAME') from dual; SYS_CONTEXT('USERENV','CON_NAME') ---------------------------------------------------------------------- PDBDEMO SQL> -- Create Example Table SQL> -- drop table oracledb_doc_tbl; SQL> create table oracledb_doc_tbl(file_name varchar2(60)); SQL> insert into oracledb_doc_tbl values('Exadata_and_Oracle12c.pptx'); 1 row created. SQL> insert into oracledb_doc_tbl values('Oracle_RAC_12c_Overview.pptx'); 1 row created. SQL>insert into oracledb_doc_tbl values('OracleDatabase12c_Application_Development.pptx'); 1 row created.
-- Create Oracle Text Index with File Store Preference SQL> exec ctx_output.start_log('file_store.log'); SQL> begin ctx_ddl.drop_preference('file_search_pref'); end; / PL/SQL procedure successfully completed. SQL> begin ctx_ddl.create_preference('file_search_pref', 'FILE_DATASTORE'); ctx_ddl.set_attribute('file_search_pref', 'PATH', 'c:\shared\12c'); end; / PL/SQL procedure successfully completed. SQL>--drop index oracledb_doc_tbl_idx; SQL> create index oracledb_doc_tbl_idx on oracledb_doc_tbl(file_name) 2 indextype is ctxsys.context 3 parameters ('DATASTORE file_search_pref'); Index created. -- alter index oracledb_doc_tbl_idx rebuild; SQL>SELECT SCORE(1), file_name FROM oracledb_doc_tbl WHERE CONTAINS(file_name, 'exadata',1) > 0; SCORE(1) FILE_NAME ---------- ------------------------------------------------------------ 100 Exadata_and_Oracle12c.pptx 4 Oracle_RAC_12c_Overview.pptx SQL> select token_text from dr$oracledb_doc_tbl_idx$i fetch first 30 rows only; TOKEN_TEXT ---------------------------------------------------------------- 0 0.5 01 03 1 1.5 10 10.5 100 1000S 100S ... exec ctx_output.end_log;
How to check errors when creating Oracle Text indexes?
Run "select * from ctx_user_index_errors" command.
Why I get the "DRG-11513: unable to open or write to file" error?
Make sure the user running the Oracle instance has the right to read/write the files specified. On Windows, for example, by default, the service set to be ".\oracle_user". After you change to a local use, the error goes away.