Home » SQL & PL/SQL » SQL & PL/SQL » How to Find the Parent of Index Organized Table (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
How to Find the Parent of Index Organized Table [message #671940] Tue, 25 September 2018 07:41 Go to next message
wtolentino
Messages: 320
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 Go to previous messageGo to next message
joy_division
Messages: 4949
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 #671942 is a reply to message #671941] Tue, 25 September 2018 07:59 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
right but if I had to give grants on single or individual tables and that happens to be an index organized table. how will I find the parent of the index organized table?
Re: How to Find the Parent of Index Organized Table [message #671944 is a reply to message #671940] Tue, 25 September 2018 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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.

Re: How to Find the Parent of Index Organized Table [message #671945 is a reply to message #671942] Tue, 25 September 2018 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wtolentino wrote on Tue, 25 September 2018 14:59
right but if I had to give grants on single or individual tables and that happens to be an index organized table. how will I find the parent of the index organized table?
The error comes from an OVERFLOW part of an IOT not the IOT itself, you can't grant anything on the OVERFLOW part.

Re: How to Find the Parent of Index Organized Table [message #671946 is a reply to message #671942] Tue, 25 September 2018 08:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
IOT overflow and mapping tables are auxiliary objects Oracle creates when you create IOT and are never referenced in user code, so they do not need any grants.

SY.
Re: How to Find the Parent of Index Organized Table [message #671952 is a reply to message #671946] Tue, 25 September 2018 13:01 Go to previous message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thank you all.
Previous Topic: SQL code syntax Understanding
Next Topic: In Class with sub query
Goto Forum:
  


Current Time: Sun Sep 20 19:55:15 CDT 2020