CLOB Configurtion

I am trying to store the LOB data and LOB index in separate tablespaces, for some reason the index is being created in the same tablespace as LOB data.

Here is the syntax...

CREATE TABLE MY_CLOB
(EMPNO NUMBER(10, 0) NOT NULL ENABLE,
JOB_ID NUMBER(10, 0) NOT NULL ENABLE,
DEPTNO NUMBER(3, 0) NOT NULL ENABLE,
JOB_TYPE VARCHAR2(30) NOT NULL ENABLE,
JOB_DESC VARCHAR2(2) NOT NULL ENABLE,TEST_CLOB CLOB)
LOB (TEST_CLOB)
STORE AS TEST_CLOB
(TABLESPACE TS_01 STORAGE(INITIAL 32K NEXT 32K MAXEXTENTS 999 PCTINCREASE 0)
disable storage in row CHUNK 16384 PCTVERSION 20 NOCACHE NOLOGGING
INDEX TEST_CLOB_INDEX (tablespace  INDEX_CLOB STORAGE (INITIAL 32K NEXT 32K MAXEXTENTS 999 PCTINCREASE 0)));


What am i missing here..


Thanks in advance..
realclustersAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ramkbConnect With a Mentor Commented:

Are you using Oracle 8i??  If so, this is the intended behaviour.

You cannot override it.

- Ramesh
0
 
ramkbCommented:

If you are using Oracle 8i, you cannot store the LOB Index seperately from the LOB data in a different tablespace.  Even though you may be able to specify this in your definition, Oracle ignores this and creates it with the LOB Data in the same tablespace.

You can do this in Oracle 8, however Oracle recommends not to do this.

Hope this helps.
- Ramesh
0
 
UsamaMunirCommented:
Probably u dont have the Quota in the Index tablespace.

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
realclustersAuthor Commented:
Hi,

I do have quota assigned to the user, but still it dosen't work.

Thanks in Advance


0
 
realclustersAuthor Commented:
Hi,

Oracle 8i doccumentation says that we can store the LOB data and LOB index in separate tablespaces.

Why is that so?

Thanks in Advance..
0
 
WadhwaCommented:
Oracle intends to desupport the LOB index clause in a future release of Oracle.
DO NOT put the LOB index in a different tablespace than the LOB data.
In a future Oracle release, if the LOB index is in a different tablespace than the LOB data, export/import will coalesce the LOB index with the LOB data

Hope this helps

Sameer
0
 
ramkbCommented:

Hi,

I checked the Oracle documentation for you.  Here's what it says..

Document: Oracle 8i Application Developer's Guide (LOB)
Chapter 7 - Modeling and Desing.

Here is the note about LOB Indexes..

*******************************************************
TABLESPACE and LOB Index
Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention.

The LOB index is an internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it.


--------------------------------------------------------------------------------
Note:
The LOB index cannot be altered.  

--------------------------------------------------------------------------------
 
The system determines which tablespace to use for LOB data and LOB index depending on the user specification in the LOB storage clause:

If you do not specify a tablespace for the LOB data, the table's tablespace is used for the LOB data and index.

If you specify a tablespace for the LOB data, both the LOB data and index use the tablespace that was specified.

Tablespace for LOB Index in Non-Partitioned Table
If in creating tables in 8.1 you specify a tablespace for the LOB index for a non-partitioned table, your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.

Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the table's tablespace.
***************  End Note  ****************

As long as you keep your LOBs away from the table (in a different tablespace) you should be fine in terms of performance.  Note that this is a new behaviour in Oracle 8i.

Also a note from Oracle 8i SQL Reference.
CREATE TABLE statement > under LOB_Storage clause

LOB_index_clause
 This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally.

Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data.

Regards,
Ramesh
0
 
realclustersAuthor Commented:
Hi Guys,

Thanks for all your time.

0
All Courses

From novice to tech pro — start learning today.