ORA-04043: object DR$SESSION_STATE_T does not exist

Took me a while to sort this one out, posting my notes in case Google thinks it worthy and it helps someone.

I got this error trying to enable Oracle Text, which provides full text search capabilities. Oracle provides a SQL script (in $ORACLE_HOME/ctx/admin) to enable this capability.

The issue was simple, once I figured it out ... I was running the script as the SYSTEM user and not the SYS user. Doh!

Here's the script that worked:

$ cat configure_ctx.sql
spool configure_ctx.log
@?/ctx/admin/catctx.sql oracle sysaux tempts1 NOLOCK
spool off
$ sqlplus sys/oracle as sysdba @configure_ctx
[snip!]
PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

Note: the ? in the @? above is a SQLPlus shortcut for ORACLE_HOME

Useful Links on Oracle Text

The 11.2g VirtualBox appliance that Oracle provides does not come with Oracle Text (full-text search) configured. The ArsDigita system uses this, so I needed to set it up.

Cluestick on how to login as SYS (hint: as sysdba)

http://toddlerdba.blogspot.com/2011/04/install-oracle-text-oracle-database.html

How to drop Oracle Text before re-installing

http://blog.it-iresource.be/?p=260

SQL you can use to see if Text is intalled

https://forums.oracle.com/thread/2169244

Script to Drop Oracle Text

While debugging this problem, I had to drop before re-installing.

$ cat drop_ctx.sql
spool drop_ctx.log
@?/ctx/admin/catnoctx.sql
drop procedure sys.validate_context;
exit;
$ sqlplus sys/oracle as sysdba @drop_ctx
[snip!]
Session altered.

dropping user ctxsys...

Role dropped.


User dropped.


Procedure dropped.