Monday, October 03, 2011

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?

2 comments:

AGuidingLife said...

hey - thanks for this.

sunny88 said...

thanks bro !