Solved

CLOB Configurtion

Posted on 2001-07-31
8
766 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
8 Comments
 
LVL 3

Expert Comment

by:ramkb
Comment Utility

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
Comment Utility
Probably u dont have the Quota in the Index tablespace.

0
 

Author Comment

by:realclusters
Comment Utility
Hi,

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

Thanks in Advance


0
 
LVL 3

Accepted Solution

by:
ramkb earned 50 total points
Comment Utility

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

You cannot override it.

- Ramesh
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:realclusters
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
Hi Guys,

Thanks for all your time.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now