Surachart Opun's Blog: How To? Recreate CTXSYS after dropped it

Nothing special, Someone asked me How to recreate CTXSYS after dropped it. Good !!! it's only system development. Actually i don't know much about Oracle Text. However I just tested and wrote .

SQL> show user;
USER is "DEMO"

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;

SCORE(1) ID TEXT
---------- ---------- ----------------------------------------
4 2 <HTML>Paris is a city in France.</HTML>
4 3 <HTML>France is in Europe.</HTML>

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/

FIRST 40 CHARS ARE:<HTML><<<France>>> is in Europe.</HTML>

PL/SQL procedure successfully completed.

- Drop CTXSYS schema.

SQL> connect / as sysdba
Connected.
SQL> drop user CTXSYS cascade;

User dropped.

- Go to "demo" user and try again

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END; /
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00201: identifier 'CTX_DOC.MARKUP' must be declared
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored

Oops!!! Idea to recreate CTXSYS schema again by catctx.sql script.

SQL> connect / as sysdba
Connected.
SQL> @?/ctx/admin/catctx.sql password sysaux temp NOLOCK
SQL> connect CTXSYS/password
Connected.
SQL> @?/ctx/admin/defaults/drdefus.sql
SQL> connect / as sysdba
Connected.
SQL> alter user CTXSYS account lock;

User altered.

SQL> @?/rdbms/admin/utlrp

SQL> select STATUS from dba_registry where COMP_ID='CONTEXT' and SCHEMA='CTXSYS';

STATUS
--------------------------------------------
VALID

and granted:
GRANT CTXAPP to demo;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO demo;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO demo;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO demo;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO demo;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO demo;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO demo;
GRANT EXECUTE ON CTXSYS.CTX_THES TO demo;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO demo;

- Go to "demo" user, check what's happened? and fix!!!

SQL> show user;
USER is "DEMO"

SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CTXSYS.CTX_DOC" has been invalidated
ORA-04065: not executed, altered or dropped package "CTXSYS.CTX_DOC"
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.CTX_DOC"
ORA-06512: at line 6

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0
*
ERROR at line 1:
ORA-20000: Oracle Text error: DRG-10599: column is not indexed

*** check data, index status and etc ... ***

SQL> SELECT id, text FROM docs;

ID TEXT
---------- ----------------------------------------
1 <HTML>California is a state in the US.</
HTML>
2 <HTML>Paris is a city in France.</HTML>
3 <HTML>France is in Europe.</HTML>

SQL> select object_name, status from user_objects where object_type='INDEX' and status='INVALID';

OBJECT_NAME STATUS
------------------------------ -------
IDX_DOCS INVALID

SQL> ALTER INDEXTYPE IDX_DOCS compile;
ALTER INDEXTYPE IDX_DOCS compile
*
ERROR at line 1:
ORA-29833: indextype does not exist

SQL> select dbms_metadata.get_ddl('INDEX','IDX_DOCS') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_DOCS')
--------------------------------------------------------------------------------

CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT")
INDEXTYPE IS ""."" PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXS
YS.HTML_SECTION_GROUP')

I can get ddl, but INDEXTYPE IS ""."" PARAMETERS...
check more....

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CTXSYS.DRIPARSE" has been invalidated
ORA-04065: not executed, altered or dropped package "CTXSYS.DRIPARSE"
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIPARSE"
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 81

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

Index created.

- Check again ...

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;

SCORE(1) ID TEXT
---------- ---------- ----------------------------------------
4 2 <HTML>Paris is a city in France.</HTML>
4 3 <HTML>France is in Europe.</HTML>

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/

FIRST 40 CHARS ARE:<HTML><<<France>>> is in Europe.</HTML>

PL/SQL procedure successfully completed.

Reference (example)
Not sure IDEA - How about your idea?

Surachart Opun's Blog

Monday, October 03, 2011

How To? Recreate CTXSYS after dropped it

2 comments:

Search This Blog

Blog Archives