Solved

CLOB Configurtion

Posted on 2001-07-31
8
770 Views
Last Modified: 2006-11-17
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..
0
Comment
Question by:realclusters
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:ramkb
ID: 6339248

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
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6339976
Probably u dont have the Quota in the Index tablespace.

0
 

Author Comment

by:realclusters
ID: 6342640
Hi,

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

Thanks in Advance


0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 3

Accepted Solution

by:
ramkb earned 50 total points
ID: 6342741

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

You cannot override it.

- Ramesh
0
 

Author Comment

by:realclusters
ID: 6346472
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
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6349083
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
 
LVL 3

Expert Comment

by:ramkb
ID: 6349161

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
 

Author Comment

by:realclusters
ID: 6350303
Hi Guys,

Thanks for all your time.

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question