Solved

CLOB Configurtion

Posted on 2001-07-31
8
768 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
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
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.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL 6 75
Oracle DBLINKS From 11g to 8i 3 49
best datatype for oracle table email creation 8 58
capture vmstat info and insert it into an oracle table 31 39
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

830 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