| Index Organized Table [message #635307] |
Thu, 26 March 2015 09:37  |
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 #635309 is a reply to message #635308] |
Thu, 26 March 2015 09:47  |
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 |
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 #635313 is a reply to message #635311] |
Thu, 26 March 2015 10:09 |
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 #635600 is a reply to message #635307] |
Thu, 02 April 2015 03:22 |
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 |
John Watson
Messages: 8966
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 |
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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
