Home » RDBMS Server » Server Administration » Index Organized Table (Oracle 12)
Index Organized Table [message #635307] Thu, 26 March 2015 09:37 Go to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
I have two oracle environment with similar configuration.

Creating index organized table is working in one environment but not in another environment.

I know that including OVERFLOW clause will solve this. But I want to know exact root cause why same IOT command working in one db and not in another db. These Dbs are similar and their tablespace blocksize too same.

Let me know where I need to check.

Thanks
Re: Index Organized Table [message #635308 is a reply to message #635307] Thu, 26 March 2015 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


we don't know what you are doing wrong since you neglected to actually show us what exactly you did & how Oracle responded.

In the future use COPY & PASTE, please
Re: Index Organized Table [message #635309 is a reply to message #635308] Thu, 26 March 2015 09:47 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Find below the output from two DBs:

From Working evnironment:


SQL> CREATE TABLE SAMPLE_INDEX
(
ID NUMBER NOT NULL PRIMARY KEY,
INDEX_NAME VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(30) NOT NULL,
COLUMN_EXPRESSION VARCHAR2(1024)
)
ORGANIZATION INDEX ;

Table created.

SQL>


From Problematic Environment.

SQL> CREATE TABLE SAMPLE_INDEX
(
ID NUMBER NOT NULL PRIMARY KEY,
INDEX_NAME VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(30) NOT NULL,
COLUMN_EXPRESSION VARCHAR2(1024)
)
ORGANIZATION INDEX ;

Table created.

SQL>


CREATE TABLE SAMPLE_INDEX
*
ERROR at line 1:
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Re: Index Organized Table [message #635310 is a reply to message #635309] Thu, 26 March 2015 09:48 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
From Problematic Environment.

SQL> CREATE TABLE SAMPLE_INDEX
(
ID NUMBER NOT NULL PRIMARY KEY,
INDEX_NAME VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(30) NOT NULL,
COLUMN_EXPRESSION VARCHAR2(1024)
)
ORGANIZATION INDEX ;

CREATE TABLE SAMPLE_INDEX
*
ERROR at line 1:
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces


Let me know where I need to check.
Re: Index Organized Table [message #635311 is a reply to message #635310] Thu, 26 March 2015 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from query below from both databases.

SELECT owner, object_type from all_objects where object_name = 'OIDINDEX';
Re: Index Organized Table [message #635313 is a reply to message #635311] Thu, 26 March 2015 10:09 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Problematic Area:

SQL> SELECT owner, object_type from all_objects where object_name = 'SAMPLE_INDEX';

no rows selected

SQL>


Working Area:

SQL> SELECT owner, object_type from all_objects where object_name = 'SAMPLE_INDEX';

OWNER OBJECT_TYPE
-------------------------------------------------------------------------------- -----------------------
DBSIT1 TABLE

SQL>
Re: Index Organized Table [message #635315 is a reply to message #635313] Thu, 26 March 2015 10:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What is the setting for the deferred_segment_creation parameter in each database, and what is the schema's default tablespace?
Re: Index Organized Table [message #635318 is a reply to message #635315] Thu, 26 March 2015 10:50 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
In both the database this 'deferred_segment_creation' parameter is set to 'TRUE'
Re: Index Organized Table [message #635324 is a reply to message #635318] Thu, 26 March 2015 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post
select * from v$version;
for both databases.

Before, Please How to use [code] tags and make your code easier to read.
Re: Index Organized Table [message #635600 is a reply to message #635307] Thu, 02 April 2015 03:22 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Both are same.

From Working DB


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Solaris: Version 12.1.0.2.0 - Production                                          0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL>



From Problematic DB

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Solaris: Version 12.1.0.2.0 - Production                                          0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL>




I know this is some environment setup issue. But I need to identify the exact root cause. The same create table command works fine in one DB and not in another DB.

Tell me where I need to check.


--update: I've corrected your code tags. Please read the guidelines again, and this time do it properly.

[Updated on: Thu, 02 April 2015 03:31] by Moderator

Report message to a moderator

Re: Index Organized Table [message #635601 is a reply to message #635600] Thu, 02 April 2015 03:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Perhaps your databases have a different block size. There is a limit on the length of an index key in proportion to a block which you could be hitting:
orclz> CREATE TABLE SAMPLE_INDEX
  2  (
  3  ID NUMBER NOT NULL PRIMARY KEY,
  4  INDEX_NAME VARCHAR2(30) NOT NULL,
  5  TABLE_NAME VARCHAR2(30) NOT NULL,
  6  COLUMN_EXPRESSION VARCHAR2(4000)
  7  )
  8  ORGANIZATION INDEX ;
CREATE TABLE SAMPLE_INDEX
*
ERROR at line 1:
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces


orclz> CREATE TABLE SAMPLE_INDEX
  2  (
  3  ID NUMBER NOT NULL PRIMARY KEY,
  4  INDEX_NAME VARCHAR2(30) NOT NULL,
  5  TABLE_NAME VARCHAR2(30) NOT NULL,
  6  COLUMN_EXPRESSION VARCHAR2(1000)
  7  )
  8  ORGANIZATION INDEX ;

Table created.

orclz>
Re: Index Organized Table [message #635608 is a reply to message #635307] Thu, 02 April 2015 07:12 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Finally narrow down the root cause.

difference is in 'nls_length_semantics' parameter.

In working db, it is set to 'BYTE' but in problematic db, it is set to 'CHAR'.

Changed this parameter to 'BYTE' in problematic DB and now I can able to create the same table.

Many thanks for all your support.
Re: Index Organized Table [message #635612 is a reply to message #635608] Thu, 02 April 2015 08:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case, the root is still the field length, as 1024 CHAR means 2048 or 4000 bytes depending on your character set.

Generally speaking, NLS_SEMANTICS_LENGTH should always been set to CHAR. I never saw requirements talking about string space size but always about number of characters of strings.

Previous Topic: running out of TEMP space
Next Topic: user droped
Goto Forum:
  


Current Time: Thu Mar 28 16:34:50 CDT 2024