| Privileges to view DR$ tables [message #487954] |
Mon, 03 January 2011 16:44  |
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  |
 |
Barbara Boehmer
Messages: 9102
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 |
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 |
|
Barbara Boehmer
Messages: 9102
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
