How to Find the Parent of Index Organized Table [message #671940] |
Tue, 25 September 2018 07:41 |
wtolentino
Messages: 404 Registered: March 2005
|
Senior Member |
|
|
i build a script to generate a DDL for all table owned by a schema to provide grants to a role. for example:
select 'grants select, update, delete, insert on '||
owner||'.'||object_name || 'to EM_ROLE;'
from all_objects
where owner = 'CONN';
then when i run the DDL i came across this error because one of the table is an index organized table.
ORA-25191: cannot reference overflow table of an index-organized table..
i searched about the error and found some of these answers:
-----
Cause: An attempt to directly access the overflow table of
an index-organized table
Action: Issue the statement against the parent
index-organized table containing the specified overflow table.
-----
how do i find the parent of the index organized table CONN.SYS_IOT_OVER_283122?
the metadata does not tell anything about the parent table for example:
CREATE TABLE CONN.SYS_IOT_OVER_283122
LOGGING
TABLESPACE CONN_INDEX
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL
please help thank you.
|
|
|
Re: How to Find the Parent of Index Organized Table [message #671941 is a reply to message #671940] |
Tue, 25 September 2018 07:56 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Does it matter? You are issuing grants on all the objects, so why do you care to know the parent? You don't grant select, insert, update and delete on indexes, procedures, functions, etc., so let them fail and you'll have your grants on the objects that you want.
There is a column IOT_TYPE in DBA_TABLES that may help you.
[Updated on: Tue, 25 September 2018 07:58] Report message to a moderator
|
|
|
|
Re: How to Find the Parent of Index Organized Table [message #671944 is a reply to message #671940] |
Tue, 25 September 2018 08:08 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:how do i find the parent of the index organized table CONN.SYS_IOT_OVER_283122?
You do not need it to do what your original query intends to do, just query all_tables (all_objects will also give you procedure, function, package, type, java..., all things you can't grant "select, update, delete, insert"):
select 'grant select, update, delete, insert on '||
owner||'.'||table_name || ' to EM_ROLE;'
from all_tables
where owner = 'CONN'
AND IOT_TYPE != 'IOT_OVERFLOW';
or query ALL_TABLES and ALL_VIEWS instead of ALL_OBJECTS.
Note this is "grant" not "grants" and a space is missing before "to" in your query.
|
|
|
|
|
|