Home » Server Options » Text & interMedia » Privileges to view DR$ tables (10.2.0.4)
Privileges to view DR$ tables [message #487954] Mon, 03 January 2011 16:44 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
I am getting "table or view doesn't exist" error while executing following SQL on oracle text. I already have CTXAPP role assigned. Also I have execute privileges on CTX_DDL package. What privileges do I need to access DR$ tables?


SQL> SELECT COUNT (DISTINCT token_text) FROM dr$BS_ORGNL_SBMSN_IX2$i WHERE INSTR (token_text, 'IN') > 0;
SELECT COUNT (DISTINCT token_text) FROM dr$BS_ORGNL_SBMSN_IX2$i WHERE INSTR (token_text, 'IN') > 0
                                        *
ERROR at line 1:
ORA-00942: table or view does not exist





Re: Privileges to view DR$ tables [message #487958 is a reply to message #487954] Mon, 03 January 2011 17:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
If you created the index in your schema, then the dr$...$... doamin index tables should be in your schema and you should be able to select from them. Try confirming that the index and dr$...$... tables exist. If you have enclosed anything in quotes, then it is case-sensitive. Also bear in mind that the structure is different for context and ctxcat indexes. Please see the demonstration below. If that does not help, then please post a run of your create index, select from user_indexes, describe dr$...$i, then your original select.

SCOTT@orcl_11gR2> create user test identified by test
  2  /

User created.

SCOTT@orcl_11gR2> grant connect, resource, ctxapp to test
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> create table test_tab
  2    (test_col  varchar2 (10))
  3  /

Table created.

TEST@orcl_11gR2> insert into test_tab values ('kind mind')
  2  /

1 row created.

TEST@orcl_11gR2> -- context:
TEST@orcl_11gR2> create index BS_ORGNL_SBMSN_IX2
  2  on test_tab (test_col)
  3  indextype is ctxsys.context
  4  /

Index created.

TEST@orcl_11gR2> select index_name
  2  from   user_indexes
  3  where  index_name = 'BS_ORGNL_SBMSN_IX2'
  4  /

INDEX_NAME
------------------------------
BS_ORGNL_SBMSN_IX2

1 row selected.

TEST@orcl_11gR2> desc dr$BS_ORGNL_SBMSN_IX2$i
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(3)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB

TEST@orcl_11gR2> SELECT COUNT (DISTINCT token_text)
  2  FROM   dr$BS_ORGNL_SBMSN_IX2$i
  3  WHERE  INSTR (token_text, 'IN') > 0
  4  /

COUNT(DISTINCTTOKEN_TEXT)
-------------------------
                        2

1 row selected.

TEST@orcl_11gR2> drop index BS_ORGNL_SBMSN_IX2
  2  /

Index dropped.

TEST@orcl_11gR2> -- ctxcat:
TEST@orcl_11gR2> create index BS_ORGNL_SBMSN_IX2
  2  on test_tab (test_col)
  3  indextype is ctxsys.ctxcat
  4  /

Index created.

TEST@orcl_11gR2> select index_name
  2  from   user_indexes
  3  where  index_name = 'BS_ORGNL_SBMSN_IX2'
  4  /

INDEX_NAME
------------------------------
BS_ORGNL_SBMSN_IX2

1 row selected.

TEST@orcl_11gR2> desc dr$BS_ORGNL_SBMSN_IX2$i
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DR$TOKEN                                  NOT NULL VARCHAR2(64)
 DR$TOKEN_TYPE                             NOT NULL NUMBER(3)
 DR$ROWID                                  NOT NULL ROWID
 DR$TOKEN_INFO                             NOT NULL RAW(2000)

TEST@orcl_11gR2> SELECT COUNT (DISTINCT dr$token)
  2  FROM   dr$BS_ORGNL_SBMSN_IX2$i
  3  WHERE  INSTR (dr$token, 'IN') > 0
  4  /

COUNT(DISTINCTDR$TOKEN)
-----------------------
                      2

1 row selected.

TEST@orcl_11gR2> drop index BS_ORGNL_SBMSN_IX2
  2  /

Index dropped.

TEST@orcl_11gR2> drop table test_tab
  2  /

Table dropped.

TEST@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> drop user test cascade
  2  /

User dropped.

SCOTT@orcl_11gR2>








Re: Privileges to view DR$ tables [message #487965 is a reply to message #487958] Mon, 03 January 2011 21:31 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Barbara,

You are right.The domain index is created in an application schema. I am trying to access DR$ table using my own account. What privileges do I need in order to be able to access these tables?
Re: Privileges to view DR$ tables [message #487966 is a reply to message #487965] Mon, 03 January 2011 21:39 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
While connected as the application schema, grant select on the dr$...$i table to the user trying to select from it. Then, when selecting from it, make sure you preface it with the application schema.


SCOTT@orcl_11gR2> create user application identified by application
2 /

User created.

SCOTT@orcl_11gR2> create user test identified by test
2 /

User created.

SCOTT@orcl_11gR2> grant connect, resource, ctxapp to application, test
2 /

Grant succeeded.

SCOTT@orcl_11gR2> connect application/application
Connected.
APPLICATION@orcl_11gR2> create table test_tab
2 (test_col varchar2 (10))
3 /

Table created.

APPLICATION@orcl_11gR2> insert into test_tab values ('kind mind')
2 /

1 row created.

APPLICATION@orcl_11gR2> create index BS_ORGNL_SBMSN_IX2
2 on test_tab (test_col)
3 indextype is ctxsys.context
4 /

Index created.

APPLICATION@orcl_11gR2> GRANT SELECT ON dr$BS_ORGNL_SBMSN_IX2$i TO test
2 /

Grant succeeded.

APPLICATION@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> SELECT COUNT (DISTINCT token_text)
2 FROM application.dr$BS_ORGNL_SBMSN_IX2$i
3 WHERE INSTR (token_text, 'IN') > 0
4 /

COUNT(DISTINCTTOKEN_TEXT)
-------------------------
2

1 row selected.

TEST@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> drop user test cascade
2 /

User dropped.

SCOTT@orcl_11gR2> drop user application cascade
2 /

User dropped.

SCOTT@orcl_11gR2>
Previous Topic: Using Near clause for text search (2 Merged)
Next Topic: Oracle Query with AND clause
Goto Forum:
  


Current Time: Thu Dec 18 18:29:51 CST 2014

Total time taken to generate the page: 0.12094 seconds