Solved

CLOB Configurtion

Posted on 2001-07-31
8
769 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create file system directory from Oracle 10g 4 46
capture vmstat info and insert it into an oracle table 31 93
SQL query for highest sequence 4 75
SQL Syntax Question 9 57
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

752 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