Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ade.

Asked: June 30, 2000 - 10:59 am UTC

Last updated: January 25, 2022 - 6:40 am UTC

Version: 8.1.6.0.0

Viewed 100K+ times! This question is

You Asked

How do I move lob indexes and lob segments from one tablespace to another.

and Tom said...

if you have a Table T and a lob column Y, it would be:

alter table t move lob(y) store as ( tablespace users )
/

....

Rating

  (78 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Lobs in a separate tbs

Alex Daher, June 12, 2003 - 10:46 am UTC

Would you recommend putting the log segments in a separate tablespace?

Tom Kyte
June 12, 2003 - 11:16 am UTC

supposing you mean "lob" not log...

it is up to you. lobs make sense in their own tablespace since they use a "versioning" scheme that is a little different from table data (they use space very differently)

Not log segments, LOB segments, sorry...

Alex, June 12, 2003 - 10:48 am UTC


A reader, June 12, 2003 - 12:50 pm UTC


Move LOB of partition table

Braniko, August 14, 2003 - 8:13 am UTC

Hi,

The above example works fine with nonpartition tables. What if we have partition table with LOB column?

After execution

ALTER TABLE PartitionTableName
EXCHANGE PARTITION PartitionName WITH TABLE NonPartitionTableName
WITHOUT VALIDATION;

And

ALTER TABLE PartitionTableName
MOVE PARTITION PartitionName TABLESPACE NewTableSpace

LOB segment and LOB Index segment still exist in tablespace where nonpartition table was created.


ALTER TABLE PartitionTableName MOVE LOB (LobColumnName) STORE AS (TABLESPACE NewTablespace)

Gives error: ORA-14511: cannot perform operation on a partitioned

Tom Kyte
August 14, 2003 - 8:43 am UTC

alter table <tname> move partition <pname> lob (<cname>) store as ( tablespace <tablespace_name> )



you sort of have to specify the partition you want to operate on.

Braniko, August 14, 2003 - 10:55 am UTC

Thank you very much

how do i find lob's tablespace

Umesh Kasturi, February 24, 2004 - 5:14 am UTC

Tom
Without using the export import( show=y) Is there any query to find out in which Tablespace the LOB column is stored
Thanks in advance

Tom Kyte
February 24, 2004 - 6:52 am UTC

select * from user_segments

you can join user_segments to user_lobs if you like as well.


user_segments will give you tablespace info.
user_lobs will give you the lob segment name.

Blob, Import

Ignatius, March 02, 2004 - 6:53 am UTC

Hi Tom,
I have a dump file containing blob datatypes, when i import the dump file in a schema it gives an error stating that the tablespace for Blob datatype does not exists. My question is how do i import the dump file in the default tablespace of the importing user.

Tom Kyte
March 02, 2004 - 7:49 am UTC

You'll have to precreate the table.

do this:

imp userid=u/p tables=that_table indexfile=that_table.sql


edit that_table.sql, fix up the tablespace references to be whatever you want to be, run that sql.

then imp with ignore=y


for any MULTI-SEGMENT object (iot's with overflows, tables with lobs, partitioned tables, for example), you have to do this -- imp will not rewrite ALL of the tablespaces in that multi-tablespace create -- hence you either need to have the same tablespaces in place or precreate the object with the proper tablespaces.

Only for single tablespace segments will imp rewrite the create to go into the default if the requested tablespace does not exist.

Trying to move lob, getting error message

Mike, March 03, 2004 - 2:19 pm UTC

As part of moving lobs in an anonymous block (not sure if that's relevant), we are running into the following error:

...
cLine := 'ALTER TABLE DOCS move lob(DOCTEXT) store AS (TABLESPACE doclobs)';
dbms_output.put_line(cLine);
execute immediate cLine;
...

ALTER TABLE DOCS move lob(DOCTEXT) store AS (TABLESPACE Doclobs)
DECLARE
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 1
ORA-01650: unable to extend rollback segment ROLLB1 by 256 in tablespace
ROLLBACKTBS
ORA-06512: at line 14

I know, the solution is to increase the rollback space. But how can I determine how much rollback space this command will take?

Tom Kyte
March 03, 2004 - 4:19 pm UTC

well, the undo is going to be all about the LOBINDEX, not the lob segment itself, so I would look at the size of the lobindex segment (dba_segments) in order to see how big it is an consider 3-4 times that as a start

Export, Blob Datatype

Ignatius, March 05, 2004 - 12:52 am UTC

Thank you very much Tom, Thank you for your help and support.
So you mean to say that to import tables with Blob datatype (in different tablespace - non existent in the importing Database) i have to pre-create the tablespace name containing in the dump file or edit the dump file and remove the tablespace pointer. Is there no way to import the dump file in the default tablespace of the importing user without editing the dump file or precreating the tablespace in new Database.
Thank you very much

Tom Kyte
March 05, 2004 - 7:57 am UTC

you have to precreate the EMPTY table.


never never never edit a dmp file -- they are binary.

You do not have to precreate the tablespace -- rather, you would create the empty table to import into (try the steps I outlined above -- you'll see that I extracted the create table statement from the dmp file, then said "edit that create table" to fix up the tablespace references to be whatever you want, create the table and import as normal)

missing left parens

Jim, March 10, 2004 - 2:36 pm UTC

I am having a problem using your example. I enter:
alter table t modify lob(y) store as ( tablespace lrg_tbl01)

and sql returns:

missing left parenthesis and points to the beginning of the word store.
this is not a partitioned table.
any suggestions? I am trying to rebuild the tablespace and this is the last item in it.
Thanks for your help.


Tom Kyte
March 10, 2004 - 4:28 pm UTC

hmm, interesting, looks like the above syntax was "invalid" but accidently accepted in older releases (it should never have worked)


ops$tkyte@ORA9IR2> alter table t move lob(y) store as ( tablespace users  )
  2  /
 
Table altered.

works BUT it has the side effect that the table is also moved.  There does not seem to be a way to move the lob but not the table as well.

one that won't move the table could be:

ops$tkyte@ORA9IR2> create table t ( x int, y clob )
  2  tablespace t2_demo
  3  lob(y) store as (tablespace t1_demo)
  4  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data long := rpad('*',30000,'*');
  3  begin
  4          for i in 1 .. 100
  5          loop
  6                  insert into t values ( i, l_data );
  7          end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select tablespace_name, segment_name, segment_type, blocks from user_segments;
 
TABLESPA SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
-------- ------------------------------ ------------------ ----------
T2_DEMO  T                              TABLE                       8
T1_DEMO  SYS_IL0000035562C00002$$       LOBINDEX                    8
T1_DEMO  SYS_LOB0000035562C00002$$      LOBSEGMENT                512
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add z clob lob(z) store as (tablespace t3_demo);
 
Table altered.
 
ops$tkyte@ORA9IR2> lock table t in exclusive mode;
 
Table(s) Locked.
 
ops$tkyte@ORA9IR2> update t set z = y;
 
100 rows updated.
 
ops$tkyte@ORA9IR2> alter table t set unused column y;
 
Table altered.
 
ops$tkyte@ORA9IR2> alter table t rename column z to y;
 
Table altered.
 
ops$tkyte@ORA9IR2> select tablespace_name, segment_name, segment_type, blocks from user_segments;
 
TABLESPA SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
-------- ------------------------------ ------------------ ----------
T2_DEMO  T                              TABLE                       8
T1_DEMO  SYS_IL0000035562C00002$$       LOBINDEX                    8
T3_DEMO  SYS_IL0000035562C00003$$       LOBINDEX                    8
T1_DEMO  SYS_LOB0000035562C00002$$      LOBSEGMENT                512
T3_DEMO  SYS_LOB0000035562C00003$$      LOBSEGMENT                512
 
ops$tkyte@ORA9IR2> alter table t drop unused columns;
 
Table altered.
 
ops$tkyte@ORA9IR2> select tablespace_name, segment_name, segment_type, blocks from user_segments;
 
TABLESPA SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
-------- ------------------------------ ------------------ ----------
T2_DEMO  T                              TABLE                       8
T3_DEMO  SYS_IL0000035562C00002$$       LOBINDEX                    8
T3_DEMO  SYS_LOB0000035562C00002$$      LOBSEGMENT                512
 





 

Moving a LOB works fine

Jon Waldron, March 20, 2004 - 9:08 pm UTC

Unless I'm missing something here, I think you may have made a mistake:

column table_name format a20
column column_name format a20
column lob_tablespace format a20
column table_tablespace format a20
drop table jw
Table dropped

create table jw (x int, y clob) tablespace users
Table created

insert into jw values(1, rpad('*',30000,'*'))
1 row inserted


insert into jw values(2, rpad('*',30000,'*'))
1 row inserted


insert into jw values(3, rpad('*',30000,'*'))
1 row inserted


commit
Commit complete

select l.table_name, l.column_name, s.tablespace_name lob_tablespace, t.tablespa
from user_lobs l, user_segments s, user_segments t
where l.table_name = 'JW'
and l.segment_name = s.segment_name
and t.segment_name = 'JW'
and t.segment_type = 'TABLE'

TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE
-------------------- -------------------- -------------------- -----------------
JW Y USERS USERS
1 row selected


alter table jw move lob(y) store as (tablespace data_lob)
Table altered

select l.table_name, l.column_name, s.tablespace_name lob_tablespace, t.tablespa
from user_lobs l, user_segments s, user_segments t
where l.table_name = 'JW'
and l.segment_name = s.segment_name
and t.segment_name = 'JW'
and t.segment_type = 'TABLE'

TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE
-------------------- -------------------- -------------------- -----------------
JW Y DATA_LOB USERS
1 row selected

Moving a LOB, without moving the table works just fine.

Tom Kyte
March 21, 2004 - 9:55 am UTC

post your version, it USED to work (I demonstrated that) but they fixed the "bug" that allowed it to work "by accident"

as I said:
...
hmm, interesting, looks like the above syntax was "invalid" but accidently
accepted in older releases (it should never have worked)

.....




Yea of little faith...

Jon, March 21, 2004 - 9:49 pm UTC

column table_name format a20
column column_name format a20
column lob_tablespace format a20
column table_tablespace format a20
set linesize 100
select * from v$version
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
5 rows selected


drop table jw
Table dropped

create table jw (x int, y clob) tablespace users
Table created

insert into jw values(1, rpad('*',30000,'*'))
1 row inserted


insert into jw values(2, rpad('*',30000,'*'))
1 row inserted


insert into jw values(3, rpad('*',30000,'*'))
1 row inserted


commit
Commit complete

select l.table_name, l.column_name, s.tablespace_name lob_tablespace,
t.tablespace_name table_tablespace
from user_lobs l, user_segments s, user_segments t
where l.table_name = 'JW'
and l.segment_name = s.segment_name
and t.segment_name = 'JW'
and t.segment_type = 'TABLE'
TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE
-------------------- -------------------- ------------------ -------------------
JW Y USERS USERS
1 row selected


alter table jw move lob(y) store as (tablespace csee_lob)
Table altered

select l.table_name, l.column_name, s.tablespace_name lob_tablespace,
t.tablespace_name table_tablespace
from user_lobs l, user_segments s, user_segments t
where l.table_name = 'JW'
and l.segment_name = s.segment_name
and t.segment_name = 'JW'
and t.segment_type = 'TABLE'
TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE
-------------------- -------------------- ------------------ -------------------
JW Y CSEE_LOB USERS
1 row selected






Hello...you still out there?

Jon, March 24, 2004 - 7:21 am UTC


Tom Kyte
March 24, 2004 - 9:29 am UTC

ahh, I see -- yes, that one with MOVE works (i said as much above)

...<quote>
hmm, interesting, looks like the above syntax was "invalid" but accidently 
accepted in older releases (it should never have worked)


ops$tkyte@ORA9IR2> alter table t move lob(y) store as ( tablespace users  )
  2  /
 
Table altered.

works BUT it has the side effect that the table is also moved.  There does not 
seem to be a way to move the lob but not the table as well.
</quote>


It is:
alter table t modify lob(y) store as ( tablespace lrg_tbl01)
              ^^^^^^

that does NOT
 

Yes, but...

Jon, March 24, 2004 - 7:43 pm UTC

the point being that MOVE does not move the table along with the lob. You can in fact move lob and table segments independently of each other. Your quote was saying that this is not possible.

Tom Kyte
March 25, 2004 - 8:50 am UTC

yes it most certainly does!

ops$tkyte@ORA817DEV> create table t ( x int , y clob );
 
Table created.
 
ops$tkyte@ORA817DEV> insert into t values ( 1, 'hello world' );
 
1 row created.
 
ops$tkyte@ORA817DEV> select rowid from t;
 
ROWID
------------------
AAAOOPAAHAAAJdKAAA
 
ops$tkyte@ORA817DEV> alter table t move lob(y) store as ( tablespace users  )
  2  /
 
Table altered.
 
ops$tkyte@ORA817DEV> select rowid from t;
 
ROWID
------------------
AAAOOSAAHAAAJkKAAA
 


<b>see how the rowid changed, the table MOVED (in the same tablespace)

You can just:

alter table t move;

and it just moves the table.  alter table t move <lob junk> moves the table AND the lob -- your test didn't show that the table doesn't move, it just shows that move will leave the table in the same tablespace -- but it certainly "moved" it</b>
 

Updating LOBS

Vikas Bagga, March 25, 2004 - 8:48 am UTC

Great opportunity to hear and meet you at the GOUSER.
I have a hash partitioned table, with LOBS.The way the application works is they should have the ability to update LOBS with a new LOB image as and when required. The issue I am having is that the updates take place fine, but the storage does not get released. This will make the tablespace to grow indefinitely!!
Second question is every month the application is supposed to delete data greater than a certain holding period. With a hash partition what special maintenance issues will I need to perform any performance issues I need to be aware of.
Thanks
Vikas

Tom Kyte
March 25, 2004 - 9:28 am UTC

give me a test case to work with here -- that is not my experience.

your purge won't affect the partitioning here since you'll just be "deleting". You could have used partitioning to allow you to make for an easier purge, but hash won't do that.

so, hash partitioning and your purge are really "unrelated". no different than if you had a single table...

hash

reader, March 25, 2004 - 3:35 pm UTC

<quote>You could have used partitioning to allow you to make for an easier purge, but hash won't do that.<quote>

Does it mean one cannot drop a hash partition? Thanks.


Tom Kyte
March 25, 2004 - 9:28 pm UTC

you can, but the data in there is "uncontrollable"

to do a rolling window of data for a purge, you need RANGE or LIST partitioning -- where you control the data going into each partition. hash means "whatever oracle feels like stuffing in there..."

Alter table <x> move <lob>

Jon, March 27, 2004 - 8:30 am UTC

Too Sharp... 5 stars for that one! I think I'll call it the phantom move :-)

Moving a LOB

A reader, April 28, 2004 - 4:48 pm UTC

This is one of the best (if not the best site) I have used, better than orafaq.com

GTTs and LOB storage

Dušan, June 21, 2004 - 10:35 am UTC

Tom,

select * from v$version;

Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

create global temporary table tom(x number, y clob)
on commit delete rows
lob (y) store as l$tom$y (index i$tom$y)
/

Table created.

select user_lobs.index_name, index_type, tablespace_name
from user_lobs, user_indexes
where user_LOBS.table_name = 'TOM'
and user_lobs.INDEX_NAME = user_indexes.INDEX_NAME
/

INDEX_NAME INDEX_TYPE TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
I$TOM$Y LOB SYSTEM

Tablespace SYSTEM?

No, let's provide explicit storage (bizarre thing to do, given that the table is GTT, but then again...)

According to the documentation, I shouldn't be allowed to try this ("You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, logging_clause, MONITORING or NOMONITORING, or LOB_index_clause.") but here it goes:


drop table TOM
/

Table dropped.

create global temporary table tom(x number, y clob) on commit delete rows
lob (y) store as l$tom$y (tablespace temp index i$tom$y (tablespace temp))
/

Table created.

select user_lobs.index_name, index_type, tablespace_name
from user_lobs, user_indexes
where user_LOBS.table_name = 'TOM'
and user_lobs.INDEX_NAME = user_indexes.INDEX_NAME
/

INDEX_NAME INDEX_TYPE TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
I$TOM$Y LOB SYSTEM

SYSTEM again...

But wait,

select * from user_segments
where segment_type in ('LOBSEGMENT','LOBINDEX')
and segment_name = 'L$TOM$Y'
/

no rows selected

Hmmm... I'd better ask Tom.

I regularly use GTTs as "an interface" between the application and real tables (for bulk loads, merging, transformation in an on-line reporting data store) and one of them happens to have a LOB column for a string that's slightly bigger than max varchar2.

SYSTEM tablespace in this context sounds worrying, even with the "on commit delete rows" table.

Am I just somehow being misled by Oracle implementation here, or am I in trouble with big LOBs messing with my SYSTEM tablespace?

Will I have to review my GTT-based design with respect to LOB handling?

Tom Kyte
June 21, 2004 - 1:54 pm UTC

gtts have no tablespace really, they should have left it null.

they go into the temporary tablespace of the current schema.

they do NOT go into system.

Many thanks

Dušan, June 22, 2004 - 5:16 am UTC


index on a LOB column

David, July 17, 2004 - 12:38 pm UTC

Does oracle create an index on a LOB column automatically? If yes, what info the LOB index contains and how does the structure of LOB index differ from a regular B-tree index? Thanks.

Tom Kyte
July 17, 2004 - 2:51 pm UTC

Yes it does.

An out of line lob (over 4,000 bytes of lob data is always out of line) consists of a lob pointer (think "primary key"), a series lob index entries that point to the lob chunks, and the lob chunks.

Think of a lob as a master/detail. master has primary key (lob locator), detail has lob locator + sequence (chunk number). we derefernece the lob locator -- via the lob index to find the chunks of the lob out there in lob land.

index on a LOB coulmn

Sam, July 17, 2004 - 8:03 pm UTC

Request clarification of your reply above. Suppose I have stored resume in a CLOB column. Does the locator point to the location of the LOB segment (ex file_id, object_id, extent_id and Block_id something like that)? Also, when you say the sequence of chunks in LOB index, if the chunk size is 1 oracle block, does the LOB index contain info for each chunk where it is located? thanks.

Tom Kyte
July 18, 2004 - 11:50 am UTC

lob locator points to lob index, lob index points to lob chunks.


lobs are stored in chunks, which are integer multiples of blocks -- whether that integer is 1 or 100 matters not, they would be stored the same -- lob locator used as key into a lob index used to locate the chunks where ever they may be.




A reader, November 04, 2004 - 11:12 am UTC


lobindex fragmentation

reader, December 02, 2004 - 10:56 pm UTC

Hello Tom,

How does Oracle manage the lobindex? Since it's not possible to really rebuild like a B-tree (except for, I suppose, moving the lobsegment to another tablespace -or- exp/imp) I was just wondering what goes on under the covers with all the pointers to lob segments and such. I'm assuming it's some kind of bitmap to the rest of the lobsegment extent, but I don't know for sure.

Is it possible to get a lobindex out of whack with lots of LOB segment growth and/or deletes from the lob? What happens internally with the lobindex during dml? Can a lot of DML on the lobsegment cause unnecessary lobindex growth and possibly fragmentation?

In this case I'm referring to out of line lobs with an 8k chunk (since we have 8k blocks, this is the smallest we can go, correct?) BLOBS.

Tom Kyte
December 03, 2004 - 7:41 am UTC

since b*trees rarely and only under degenerative situations "get out of whack" and lob segment indexes are really sort of "simple beasts", you need not rebuild them. That is in part why they are "invisible" to you -- you cannot touch them.

yes, a single block is the lower limit for the chunksize.

Problem moving partitioned lob columns (9.2.0.5)

Flemming Andersen, February 10, 2005 - 8:15 am UTC

Hi Tom
I'm trying to move lob columns from a partitioned table to another tablespace, but I'm not having any success

This is my statement:

ALTER table sae.audit_column MOVE PARTITION WEEK_01 LOB(old_value_xml) STORE AS (TABLESPACE SAELOBT)

This is the result:

ERROR at line 1:
ORA-22997: VARRAY | OPAQUE stored as LOB is not specified at the table level

This is my table:

CREATE TABLE AUDIT_COLUMN
(TABLE_NAME VARCHAR2(30) NOT NULL
,RECORD_ID VARCHAR2(4000) NOT NULL
,COLUMN_NAME VARCHAR2(30) NOT NULL
,AUDIT_TIME TIMESTAMP NOT NULL
,WEEK VARCHAR2(2) NOT NULL
,ACTION VARCHAR2(15) NOT NULL
,ORACLE_USER VARCHAR2(30) NOT NULL
,OLD_VALUE_CHAR VARCHAR2(4000)
,NEW_VALUE_CHAR VARCHAR2(4000)
,OLD_VALUE_XML XMLTYPE
,NEW_VALUE_XML XMLTYPE
) TABLESPACE SAESYSLT
PARTITION BY RANGE (WEEK)
(PARTITION WEEK_01 VALUES LESS THAN ('02')

I hope you can help me out on this !

Best Regards Flemming

Moving xmltype columns

Flemming Andersen, February 18, 2005 - 8:39 am UTC

Hi Tom
I found the answer. The method is described in note 231599.1 on metalink.

Use the XMLDATA argument of the static function of the sys.XMLTYPE.createXML
used to manipulate data in an XMLTYPE column.

alter table testxml move lob (spec.XMLDATA) store as (tablespace users);

/Flemming

A reader, February 21, 2005 - 2:15 pm UTC


Is it applicable to CLOB, BLOB etc ....??

raman, April 21, 2005 - 2:10 pm UTC

Hello TOM,

Version: 9.2.0.4.0
Windows 2003 prof.
Development Box ....

sql>select DISTINCT data_type from dba_tab_columns WHERE OWNER NOT IN ('SYS','SYSTEM','TEMP');

BLOB
CLOB
LONG
LONG RAW
LRSX_KEY2_FIELD_ARRAY
LRSX_LRS2_FIELD_ARRAY
LRSX_MARKERINFO_ARRAY
SDO_DIM_ARRAY
SDO_GEOMETRY
UNDEFINED
WM$ED_UNDO_CODE_TABLE_TYPE
XMLTYPE

After filtering few datatypes, I listed above some ...
Could you please tell me which ones can I move to LOBSEGMENT/LOBINDEX tablespace ...?

or

Should I move only LOBs to LOBSEGMENT/LOBINDEX ?

regards,
-raman

Tom Kyte
April 22, 2005 - 9:18 am UTC

you can move any segment that is a lob you want to. You want to look at the segment types, not the data types I think

...

raman, April 21, 2005 - 4:32 pm UTC

Terribly sorry ... my question was not put into proper words .... I was supposed to ask you like this ....

If I had created a seperate tablespace intending to keep LOB objects ... the list I gave you confuses me ...

Under USER_LOBS:
...
...
SYS_LOB0000086486C00003$$
SYS_LOB0000086503C00003$$
SYS_LOB0000086510C00022$$
SYS_LOB0000086510C00023$$
...
...
Can I send all of them into new tablespace ....??

regards,
-raman

Tom Kyte
April 22, 2005 - 10:11 am UTC

yes, you probably have to rebuild the entire table -- so you really have to ask yourself "is it really worth doing this"

LOB performance

atul, May 23, 2005 - 6:08 am UTC

Hi,

We have a clob columns in out transaction table,Which gets insert/update every second.

Could you tell us something to improve performance
1)will keeping clob column in other tablespace improve performance?
2)Whats about in-line & out-line lobs?
3)Will CACHE options help performance?
4)OUR chunck & DB-BLOCK-SIZE IS 8k,WHAT CHUNK SIZE will help in performance..it generally has 10k bytes inserted per row.

Thanks




Tom Kyte
May 23, 2005 - 11:10 am UTC

1) not just "another tablespace", for a tablespace is for ease of administration, not performance. You could have 100 tablespaces -- all on the same disk (leaving 50 other disks idle) for example.

It is about even IO distribution, not tablespaces.

2) inline lobs, if the lob is 4000 bytes or less, store it like a varchar2, right in the database block. if the lob is >4000 bytes -- store a pointer to the lob index which points to the lob chunks.

Inline is generally OK -- stores small lobs similar to varchar2, long lobs out of line. you'd only want the out of line if you full scan the table frequently and wanted to avoid reading over 4000 byte strings...

3) yes it can, but it depends on the size of your clobs, if you CACHE them, they will be cached, eating block buffers in the cache that used to cache other stuff.

4) 16k chunk would be ok then.

Hi

atul, May 24, 2005 - 6:38 am UTC

Hi,

1)How to keep out of line lobs only,is there any parameter to alter or how it happens.
As our data will be generally around 8k each time.

2)If we opt for partitioning how clob will work?

Thanks



Tom Kyte
May 24, 2005 - 8:12 am UTC

you specify "disable storage in row" as an attribute of the lob.  See the sql reference manual, or the 

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm

or, what I do to see all of the options:

ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
 
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
 
  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("X") STORE AS (
  TABLESPACE "USERS" <b>ENABLE STORAGE IN ROW CHUNK</b> 8192 PCTVERSION 10
  NOCACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 


you would change the enable to disable... 

Works as indicated

DBA Dan, August 09, 2005 - 3:22 pm UTC

I was trying to clean up a tablespace for removal and needed to move a lob index. The first thread addressed my needs but the subsequent questions by other users added valuable knowledge

problems moving blobs...

Craig, August 19, 2005 - 12:14 pm UTC

Tom,

I was trying to reorg. several database objects and ran into the following problem:

$ sqlplus /

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 19 10:50:46 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> desc export_blob_space
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 THE_BLOB                                  NOT NULL BLOB
 FILENAME                                  NOT NULL VARCHAR2(128)
 XFER_ID                                            VARCHAR2(15)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TOOLS
MRDSS
MRDSSUSERS
ORATMP
UNDOTBS
MRDSSIND
MRDSSDAT
MRDSSTEMP

9 rows selected.

SQL> alter table export_blob_space move lob(the_blob) store as(tablespace mrdssdat);
alter table export_blob_space move lob(the_blob) store as(tablespace mrdssdat)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Any suggestions?

Thanks! 

Tom Kyte
August 20, 2005 - 4:31 pm UTC

please contact support.

reader

A reader, September 23, 2005 - 3:57 pm UTC

Does oracle provide us with a command to move tables with
column as LOB datatype which would move
lobsegment to one tablespace and lobindex to
another tablespace. Else, do we move the table
and lobsegment first, and then recreate lobindex


Tom Kyte
September 23, 2005 - 8:52 pm UTC

nope, lobindex and lobsegment are "one in the same" these days, they travel together.

you cannot recreate a lobindex separately

Hi

atul, October 03, 2005 - 7:13 am UTC

Hi,

If i move clob to there own tablespace.
What type of performnace improvement could i expect?

1)Will insert be slow?
2)Will update that doesn't include lob column be fast?

Thanks,
Atul

Tom Kyte
October 03, 2005 - 7:51 am UTC

One of three things will happen:

a) it will go faster
b) it will go slower
c) it will run at exactly the same speed.

In general, tablespaces are *NOT* a performance thing, they are a "make my life as an admin easier" thing. They are a tool you can use to organize data.

A separate tablespace doesn't mean "separate IO system".

by default LOBS are not cached, depending on the size of your lobs - you might look at that detail. By default, when you insert a lob, you wait for the direct IO to the lob segment to complete. By default, when you read a lob, you wait for the direct IO to complete.

Move LOB Indexes

EBer, October 07, 2005 - 12:09 pm UTC

Actually, lob index is separated from the lobsegment if this is partitioned. I moved lob partitions in tableapace B but the lob index with name SYS_IL0000108564C00002$$ is still in the tablespace A !

I used the sintax:
alter table t MOVE lob(y) store as ( tablespace users )

but lob index is still there!

How can I move it?


Tom Kyte
October 07, 2005 - 9:54 pm UTC

give full example please. create table, inserts and move.

Move partitioned lobs

EBer, October 10, 2005 - 5:24 am UTC

Hi Tom.
I tried again this morning.
I created the same table in EUDSCORE_ARC tablespace and then I moved every segment in USERS tablespace.

This is the output.

CREATE TABLE SCES1INPUTS
(
CODREQUEST VARCHAR2(9 BYTE) NOT NULL,
LOBS1INPUT CLOB NOT NULL,
CODLAYOUT VARCHAR2(20 BYTE) NOT NULL,
DATINSERTION DATE DEFAULT SYSDATE NOT NULL,
CODINSERTIONUSER VARCHAR2(10 BYTE) NOT NULL,
CODINSERTIONFUNCTION VARCHAR2(5 BYTE) NOT NULL,
DATHISTORY DATE DEFAULT SYSDATE NOT NULL,
LOBS1INPUT_GZ BLOB
)
TABLESPACE EUDSCORE_ARC
PCTUSED 0
PCTFREE 0
INITRANS 5
MAXTRANS 255
LOGGING
LOB (LOBS1INPUT) STORE AS LOB1_SCES1INPUTS
( TABLESPACE EUDSCORE_ARC
ENABLE STORAGE IN ROW
CHUNK 8k
PCTVERSION 0
NOCACHE
)
LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS
( TABLESPACE EUDSCORE_ARC
ENABLE STORAGE IN ROW
CHUNK 8k
PCTVERSION 0
NOCACHE
)
PARTITION BY RANGE ( DATINSERTION )
(
PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy')) TABLESPACE EUDEVARC_DATA,
PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy')) TABLESPACE EUDEVARC_DATA,
PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy')) TABLESPACE EUDEVARC_DATA,
PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy')) TABLESPACE EUDEVARC_DATA,
PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','ddmmyyyy')) TABLESPACE EUDEVARC_DATA
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

-----


select SEGMENT_NAME from dba_segments
where TABLESPACE_name = 'EUDSCORE_ARC'
and segment_name not like 'BIN$%'
SEGMENT_NAME
-------------------------------------------------
SCES1INPUTS
SCES1INPUTS
SCES1INPUTS
SCES1INPUTS
SCES1INPUTS
SYS_IL0000054124C00008$$
SYS_IL0000054124C00008$$
SYS_IL0000054124C00008$$
SYS_IL0000054124C00008$$
SYS_IL0000054124C00008$$
LOB2_SCES1INPUTS
LOB2_SCES1INPUTS
LOB2_SCES1INPUTS
LOB2_SCES1INPUTS
LOB2_SCES1INPUTS
SYS_IL0000054124C00002$$
SYS_IL0000054124C00002$$
SYS_IL0000054124C00002$$
SYS_IL0000054124C00002$$
SYS_IL0000054124C00002$$
LOB1_SCES1INPUTS
LOB1_SCES1INPUTS
LOB1_SCES1INPUTS
LOB1_SCES1INPUTS
LOB1_SCES1INPUTS
25 rows selected


select * from dba_lobs
where TABLESPACE_name = 'EUDSCORE_ARC'
and table_name not like 'BIN$%'
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
OTTIX SCES1INPUTS LOBS1INPUT
OTTIX SCES1INPUTS LOBS1INPUT_GZ
2 rows selected

----
----

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200508 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
7 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200509 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
7 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200510 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
7 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200511 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
7 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200512 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
7 /

Tabella modificata.



select * from dba_lobs
where TABLESPACE_name = 'EUDSCORE_ARC'
and table_name not like 'BIN$%'
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
OTTIX SCES1INPUTS LOBS1INPUT
OTTIX SCES1INPUTS LOBS1INPUT_GZ
2 rows selected



ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 modify lob (LOBS1INPUT) store as LOB1_SCES1INPUTS (tablespace USERS)
3 /
modify lob (LOBS1INPUT) store as LOB1_SCES1INPUTS (tablespace USERS)
*
ERRORE alla riga 2:
ORA-00906: parentesi aperta mancante



ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS
2 move lob (LOBS1INPUT) store as LOB1_SCES1INPUTS (tablespace USERS)
3 /
alter table SCES1INPUTS
*
ERRORE alla riga 1:
ORA-14511: impossibile eseguire l'operazione su un oggetto partizionato




ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS move partition SCES1INPUTS_200508 tablespace USERS

2 lob (LOBS1INPUT) store as (tablespace USERS)
3 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS move partition SCES1INPUTS_200509 tablespace USERS

2 lob (LOBS1INPUT) store as (tablespace USERS)
3 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS move partition SCES1INPUTS_200510 tablespace USERS

2 lob (LOBS1INPUT) store as (tablespace USERS)
3 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS move partition SCES1INPUTS_200511 tablespace USERS

2 lob (LOBS1INPUT) store as (tablespace USERS)
3 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>
ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter table SCES1INPUTS move partition SCES1INPUTS_200512 tablespace USERS

2 lob (LOBS1INPUT) store as (tablespace USERS)
3 /

Tabella modificata.

ottix@LOCALE.REGRESS.RDBMS.DEV.US.ORACLE.COM>



select * from dba_lobs
where TABLESPACE_name = 'EUDSCORE_ARC'
and table_name not like 'BIN$%'
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
OTTIX SCES1INPUTS LOBS1INPUT
OTTIX SCES1INPUTS LOBS1INPUT_GZ
2 rows selected



Move partitioned lobs

EBer, October 10, 2005 - 9:47 am UTC

So you are telling me:
"sorry, you can't move that lob-index, you must create another column and drop the first one".

Right?

That move DOES work for NOT-partitioned lobs but it doesn't work for partitioned ones...



Tom Kyte
October 10, 2005 - 11:09 am UTC

sorry - read too fast - I thought you were failing trying to move just the lob and not the associated segment.  

But, the lobs are moving, here is an example:


ops$tkyte@ORA9IR2> CREATE TABLE SCES1INPUTS
  2  (
  3    CODREQUEST            VARCHAR2(9 BYTE)        NOT NULL,
  4    LOBS1INPUT            CLOB                    NOT NULL,
  5    CODLAYOUT             VARCHAR2(20 BYTE)       NOT NULL,
  6    DATINSERTION          DATE                    DEFAULT SYSDATE               NOT NULL,
  7    CODINSERTIONUSER      VARCHAR2(10 BYTE)       NOT NULL,
  8    CODINSERTIONFUNCTION  VARCHAR2(5 BYTE)        NOT NULL,
  9    DATHISTORY            DATE                    DEFAULT SYSDATE               NOT NULL,
 10    LOBS1INPUT_GZ         BLOB
 11  )
 12  LOB (LOBS1INPUT) STORE AS LOB1_SCES1INPUTS
 13  LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS
 14  PARTITION BY RANGE ( DATINSERTION )
 15  (
 16   PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy')) ,
 17   PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy')) ,
 18   PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy')) ,
 19   PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy')) ,
 20   PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','ddmmyyyy'))
 21  )
 22  /
 
Table created.
 
ops$tkyte@ORA9IR2> select segment_name, segment_type, tablespace_name from user_segments
  2  order by tablespace_name, segment_name, segment_type;
 
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
 
25 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table SCES1INPUTS
  2    move partition SCES1INPUTS_200508 tablespace USERS
  3     lob (LOBS1INPUT)
  4       store as  (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  5     lob (LOBS1INPUT_GZ)
  6       store as  (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  7  /
 
Table altered.
 
ops$tkyte@ORA9IR2> select segment_name, segment_type, tablespace_name from user_segments
  2  order by tablespace_name, segment_name, segment_type;
 
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB1_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
LOB2_SCES1INPUTS               LOB PARTITION      TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SCES1INPUTS                    TABLE PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00002$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS
SYS_IL0000041989C00008$$       INDEX PARTITION    TOOLS<b>
LOB1_SCES1INPUTS               LOB PARTITION      USERS
LOB2_SCES1INPUTS               LOB PARTITION      USERS
SCES1INPUTS                    TABLE PARTITION    USERS
SYS_IL0000041989C00002$$       INDEX PARTITION    USERS
SYS_IL0000041989C00008$$       INDEX PARTITION    USERS</b>
 
25 rows selected.


The table, lob segment and lob index partitions moved - is that what you were trying? 

Move partitioned LOBS

EBer, October 10, 2005 - 11:40 am UTC

Sorry, but look in USER_LOBS....
They are STILL THERE !!!
(version 10.1.0.4)

<output>

SQL> CREATE TABLE SCES1INPUTS
  2    (
  3     CODREQUEST            VARCHAR2(9 BYTE)        NOT NULL,
  4      LOBS1INPUT            CLOB                    NOT NULL,
  5      CODLAYOUT             VARCHAR2(20 BYTE)       NOT NULL,
  6      DATINSERTION          DATE                    DEFAULT SYSDATE              NOT NULL,
  7      CODINSERTIONUSER      VARCHAR2(10 BYTE)       NOT NULL,
  8      CODINSERTIONFUNCTION  VARCHAR2(5 BYTE)        NOT NULL,
  9      DATHISTORY            DATE                    DEFAULT SYSDATE               NOT NULL,
 10      LOBS1INPUT_GZ         BLOB
 11    )  tablespace EXAMPLE
 12    LOB (LOBS1INPUT) STORE AS LOB1_SCES1INPUTS  (tablespace EXAMPLE)
 13    LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS  (tablespace EXAMPLE)
 14    PARTITION BY RANGE ( DATINSERTION )
 15    (
 16     PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy'))  tablespace EXAMPLE,
 17     PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy'))  tablespace EXAMPLE,
 18     PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy'))  tablespace EXAMPLE,
 19     PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy'))  tablespace EXAMPLE,
 20     PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','ddmmyyyy'))  tablespace EXAMPLE
 21    );

Table created

SQL> select * from user_lobs;

TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING IN_ROW FORMAT          PARTITIONED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------ --------------- -----------
SCES1INPUTS                    LOBS1INPUT                                                                       LOB1_SCES1INPUTS               EXAMPLE                        SYS_IL0000054330C00002$$             8192         10        900            NO         NONE    YES    ENDIAN NEUTRAL  YES
SCES1INPUTS                    LOBS1INPUT_GZ                                                                    LOB2_SCES1INPUTS               EXAMPLE                        SYS_IL0000054330C00008$$             8192         10        900            NO         NONE    YES    NOT APPLICABLE  YES

SQL> 
SQL> alter table SCES1INPUTS
  2      move partition SCES1INPUTS_200508 tablespace USERS
  3       lob (LOBS1INPUT)
  4         store as  (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  5       lob (LOBS1INPUT_GZ)
  6         store as  (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );

Table altered

SQL> 
SQL> 
SQL> select * from user_lobs;

TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING IN_ROW FORMAT          PARTITIONED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------ --------------- -----------
SCES1INPUTS                    LOBS1INPUT                                                                       LOB1_SCES1INPUTS               EXAMPLE                        SYS_IL0000054330C00002$$             8192         10        900            NO         NONE    YES    ENDIAN NEUTRAL  YES
SCES1INPUTS                    LOBS1INPUT_GZ                                                                    LOB2_SCES1INPUTS               EXAMPLE                        SYS_IL0000054330C00008$$             8192         10        900            NO         NONE    YES    NOT APPLICABLE  YES

SQL>  

Tom Kyte
October 10, 2005 - 12:10 pm UTC

user lobs is at the aggregate level - probably should not even have a tablespace there at all.


Look at your SEGMENTS - things that occupy SPACE - they are *not* there, they are where you said to put them.


Look at user-lobs this way - you have a PARTITIONED LOB, how can a single row tell you really where the data is when each partition can be in a separate tablespace??


look at segments, look at extents. Look at things that actually *occupy* space.

Move lobs + Drop tablespace

EBer, October 10, 2005 - 12:53 pm UTC

Hi Tom.
You are right (as always).

Now I understand what problem was:

I was trying to drop the tablespace with INCLUDING CASCADE.
So I received ORA-00604 error...
Look below.

SQL> drop tablespace ex2 including contents;

drop tablespace ex2 including contents

ORA-00604:  error occurred at recursive SQL level 1
ORA-22864: cannot ALTER or DROP LOB indexes

SQL> drop tablespace ex2 ;

Tablespace dropped

I wish this helps someone else.
Thank you again!
 

Drop tablespace and Lob Segments

EBer, October 25, 2005 - 6:34 am UTC

I dropped that tablespace, but now I can't reorg that tables and their lob segments (as I used to do).


SQL> alter table EURISCARC.SCES1OUTPUTS
  2      move partition SCES1OUTPUTS_200508
  3       lob (LOBS1OUTPUT)
  4        store as (tablespace EUDSCORE_ARC ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  5       lob (LOBS1OUTPUT_GZ)
  6        store as (tablespace EUDSCORE_ARC ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );
alter table EURISCARC.SCES1OUTPUTS
                      *
ERRORE alla riga 1:
ORA-00959: tablespace 'EUDSCORE_ARC_LOB' does not exist



SQL> select * from user_lobs where tablespace_name = 'EUDSCORE_ARC_LOB';

TABLE_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
LOGGING IN_ FORMAT          PAR
------- --- --------------- ---
SCEREQUESTS
LOBXMLINPUT
LOB_SCEREQUESTS                EUDSCORE_ARC_LOB               SYS_IL0000108966C00013$$             8192          0        900            NO
NONE    YES ENDIAN NEUTRAL  YES

SCES1INPUTS
LOBS1INPUT
LOB_SCES1INPUTS                EUDSCORE_ARC_LOB               SYS_IL0000108984C00002$$             8192          0        900            NO
NONE    YES ENDIAN NEUTRAL  YES

SCES1OUTPUTS
LOBS1OUTPUT
LOB_SCES1OUTPUTS               EUDSCORE_ARC_LOB               SYS_IL0000109002C00002$$             8192          0        900            NO
NONE    YES ENDIAN NEUTRAL  YES

SCSREQUESTRESULTS
LOBOUTPUTMESSAGE
LOB_SCSREQUESTRESULTS          EUDSCORE_ARC_LOB               SYS_IL0000109020C00002$$             8192          0        900            NO
NONE    YES ENDIAN NEUTRAL  YES

 

Tom Kyte
October 25, 2005 - 6:58 am UTC

I'm not sure what you did?

Move Lobs

EBer, October 25, 2005 - 7:24 am UTC

Hi Tom,
please look at previous messages.

Summary:
I moved table partitions and lobs to another tbs.
Something seemed to remain in the first tbs 
(dba_lobs still shows some object there) so I couldn't drop firs tbs. You told me that segments actually moved in the 2nd tbs so I dropped the 1st tbs WITHOUT "including contents" clause. 
Now I could NOT move that table because Oracle wants the 1st tbs. 

I created it again but....

SQL> alter table EURISCARC.SCES1OUTPUTS
  2      move partition SCES1OUTPUTS_200508
  3       lob (LOBS1OUTPUT)
  4        store as (tablespace EUDSCORE_ARC ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  5       lob (LOBS1OUTPUT_GZ)
  6        store as (tablespace EUDSCORE_ARC ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );
alter table EURISCARC.SCES1OUTPUTS
                      *
ERRORE alla riga 1:
ORA-22922: nonexistent LOB value  


Thank you in advance. 

Tom Kyte
October 26, 2005 - 7:03 am UTC

can you reproduce this with a script? say using a single lob or something - just to let us see the step by steps you took.

Have you contacted support (because this is starting to sound "not correct")

...continues

EBer, October 25, 2005 - 8:55 am UTC

I lost something with that tablespace...
I shouldn't have to drop it out!
I won't drop that tablespace again...

select * from SCEREQUESTS
ORA-22922: nonexistent LOB value

W.T.H.
Bye
EBer



MOVE Partitioned LOBS strange behaviour

EBer, October 26, 2005 - 4:49 am UTC

Hi Tom.
It's me again!
Look at this curious behaviour!
(I translated ORA- messages in english for you)

I tried again with the previous example (tablespace EX2)
I created table SCES1INPUTS in tbs EX2 and then I moved it in tbs USERS.

I dropped tbs EX2 (without INCLUDING CONTENTS because Oracle didn't want to do it).

After I dropped empty tablespace EX2, in dba_lobs you can find lobindexes still there.

If I try to reorg the table:

OTTIX@LOC> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200508 tablespace USERS
3 lob (LOBS1INPUT)
4 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
5 lob (LOBS1INPUT_GZ)
6 store as (tablespace USERS ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );
alter table SCES1INPUTS
*
ERRORE alla riga 1:
ORA-00959: tablespace 'EX2' doesn't exist


SYSTEM@LOC> select * from dba_lobs where TABLESPACE_NAME='EX2' ;

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
------------------------------
SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETE
NTION FREEPOOLS CACHE
------------------------------ ------------------------------ ------------------------------ ---------- ---------- -----
----- ---------- ----------
LOGGING IN_ FORMAT PAR
------- --- --------------- ---
OTTIX SCES1INPUTS
LOBS1INPUT_GZ
LOB2_SCES1INPUTS EX2 SYS_IL0000054365C00008$$ 8192 10
900 NO
NONE YES NOT APPLICABLE YES

OTTIX SCES1INPUTS
LOBS1INPUT
LOB1_SCES1INPUTS EX2 SYS_IL0000054365C00002$$ 8192 10
900 NO
NONE YES ENDIAN NEUTRAL YES


---


OTTIX@LOC> create table t as select * from SCES1INPUTS;

Tabella creata.

OTTIX@LOC> drop table SCES1INPUTS;

Table created.

OTTIX@LOC> select * from user_lobs;

TABLE_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
LOGGING IN_ FORMAT PAR
------- --- --------------- ---
BLOB_TABLE
BIN
SYS_LOB0000057061C00001$$ USERS SYS_IL0000057061C00001$$ 8192 900 NO
YES YES NOT APPLICABLE NO

CLOB_TABLE
TEXT
SYS_LOB0000056812C00001$$ USERS SYS_IL0000056812C00001$$ 8192 900 NO
YES YES ENDIAN NEUTRAL NO

T
LOBS1INPUT_GZ
SYS_LOB0000057627C00008$$ USERS SYS_IL0000057627C00008$$ 8192 10 NO
YES YES NOT APPLICABLE NO

T
LOBS1INPUT
SYS_LOB0000057627C00002$$ USERS SYS_IL0000057627C00002$$ 8192 10 NO
YES YES ENDIAN NEUTRAL NO

BIN$oC0Y7zqJRsWhjPRICMq8DQ==$0
LOBS1INPUT_GZ
BIN$a0KmnPCxTXSgIBkpJRW5Iw==$0 EX2 SYS_IL0000054365C00008$$ 8192 10 900 NO
NONE YES NOT APPLICABLE YES

BIN$oC0Y7zqJRsWhjPRICMq8DQ==$0
LOBS1INPUT
BIN$3A/GCsdqTEO6VOkm3+y4bA==$0 EX2 SYS_IL0000054365C00002$$ 8192 10 900 NO
NONE YES ENDIAN NEUTRAL YES

---

Oracle uses recyclebin names, but tablespace EX2 doesn't exist !!! ;)


OTTIX@LOC> purge recyclebin
2 ;

Empty bin.

OTTIX@LOC> select * from user_lobs;

TABLE_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
LOGGING IN_ FORMAT PAR
------- --- --------------- ---
BLOB_TABLE
BIN
SYS_LOB0000057061C00001$$ USERS SYS_IL0000057061C00001$$ 8192 900 NO
YES YES NOT APPLICABLE NO

CLOB_TABLE
TEXT
SYS_LOB0000056812C00001$$ USERS SYS_IL0000056812C00001$$ 8192 900 NO
YES YES ENDIAN NEUTRAL NO

T
LOBS1INPUT_GZ
SYS_LOB0000057627C00008$$ USERS SYS_IL0000057627C00008$$ 8192 10 NO
YES YES NOT APPLICABLE NO

T
LOBS1INPUT
SYS_LOB0000057627C00002$$ USERS SYS_IL0000057627C00002$$ 8192 10 NO
YES YES ENDIAN NEUTRAL NO


Is it normal for you?

Bye, and thank you for your time.
I have your 2 books and I appreciate them very much.

EBer


Tom Kyte
October 26, 2005 - 11:59 am UTC

i'll file this one away for when I get back home - looks "not right entirely", but I'd like to reproduce from start to finish...

How does lob effect full table scan?

A reader, November 02, 2005 - 8:56 am UTC

Tom,
If a table has a lob column, how would this effect full table scan? Will the FTS scan all the table blocks including lob?

The second question is if lobs are direct IO, what is the effect of cache and nocache?
Thanks


Tom Kyte
November 03, 2005 - 5:08 am UTC

lobs over 4000 characters are stored "out of line" and pointed to.

so, if you have a 400mb lob stored, the table will have a pointer to it, not the lob itself - 400mb will not be scanned during the full tablescan since we read the lob locator (pointer) and not the lob data (not until you ask us to)


if you are nocache, we can use direct IO (no buffer cache)

if you are cache, we use 'regular' IO via the buffer cache.

How does lob effect full table scan?

A reader, November 03, 2005 - 8:48 am UTC

Thanks for the answer. Really appreciate it with you being so busy with UKOUG. What is the impact on performance of using cache/nocache? In other words, in what situations should I use cache/nocache?

Thanks


Tom Kyte
November 04, 2005 - 2:22 am UTC

I cover this in some detail in my new book - chapter on datatypes. Here is a snippet on that:

<quote>
CACHE Clause
The CREATE TABLE statement returned from DBMS_METADATA previously included the following:
LOB ("TXT") STORE AS (… NOCACHE … )
The alternative to NOCACHE is CACHE or CACHE READS. This clause controls whether or not the LOBSEGMENT data is stored in the buffer cache. The default NOCACHE implies that every access will be a direct read from disk and every write/modification will likewise be a direct read from disk. CACHE READS allows LOB data that is read from disk to be buffered, but writes of LOB data will be done directly to disk. CACHE permits the caching of LOB data during both reads and writes.
In many cases, the default might not be what you want. If you have small- to medium-sized LOBS (e.g., you are using them to store descriptive fields of just a couple of kilobytes), caching them makes perfect sense. If they are not cached, when the user updates the description field the user must also wait for the I/O to write the data to disk (an I/O the size of a CHUNK will be performed and the user will wait for this I/O to complete). If you are performing a large load of many LOBs, you will have to wait for the I/O to complete on each row as they are loaded. It makes sense to enable caching on these LOBs. You may turn caching on and off easily:
ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );
ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );
to see the effect this may have on you. For a large initial load, it would make sense to enable caching of the LOBs and allow DBWR to write the LOB data out to disk in the background while your client application keeps loading more. For small- to medium-sized LOBs that are frequently accessed or modified, caching makes sense so as to not have the end users wait for physical I/O to complete in real time. For a LOB that is 50MB in size, however, it probably does not make sense to have that in the cache.
Bear in mind that you can make excellent use of the Keep or Recycle pools here. Instead of caching the LOBSEGMENT data in the default cache with all of the “regular” data, you can use the Keep or Recycle pools to separate it out. In that fashion, you can achieve the goal of caching LOB data without affecting the caching of existing data in your system.
</quote>

lob cache

atul, November 06, 2005 - 2:51 am UTC

Hi,

I did cacheing for my CLOB.
My buffer cache is 250M.
When data gets inserted,my total lobsegment size goes beyond 250M.

I calculated this by select sum(bytes) from user_segments where segment_type='LOBSEGMENT';

After running load test,i started getting slow performace.
Max waiting was for direct path read(LOB).

I am surprised even after caching how can i get this wait event.

Could you please help

Tom Kyte
November 06, 2005 - 8:24 am UTC

show us how you cached the lob.

Cache CLOB

atul, November 07, 2005 - 1:01 am UTC

Hi,

Given cache option for CLOB in create table syntax

+++++++++
CREATE TABLE "VRTSTST"."VRTS_CUF_ERR_ERPXX002" ("ID" VARCHAR2(50) NOT
NULL ENABLE, "TRANSACTIONID" VARCHAR2(255) NOT NULL ENABLE,
"DOCUMENTID" VARCHAR2(255) NOT NULL ENABLE, "OBJECT_ID" VARCHAR2(50),
"OBJECT_TYPE" VARCHAR2(50), "ERROR_TYPE" VARCHAR2(10), "ERROR_CODE"
VARCHAR2(50), "PROCESS_STACK" VARCHAR2(2048), "ERROR_DESC"
VARCHAR2(2048), "STACK_TRACE" CLOB, "LAST_UPDATE_BY" VARCHAR2(255),
"LAST_UPDATE_DATETIME" DATE, "DATA" CLOB, "DESTINATION"
VARCHAR2(255), "RETRY_COUNTER" VARCHAR2(10), "VERSION_NUM"
VARCHAR2(10)) PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
"VRTSTSTD" LOGGING NOCOMPRESS LOB ("STACK_TRACE") STORE AS
(TABLESPACE "ERR_LOB" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 1)) LOB
("DATA") STORE AS (TABLESPACE "ERR_LOB" DISABLE STORAGE IN ROW CHUNK
8192 PCTVERSION 10 CACHE STORAGE(INITIAL 524288 FREELISTS 1
FREELIST GROUPS 1)) ;

+++++++++++++

Thanks,
Atul

Tom Kyte
November 07, 2005 - 8:49 am UTC

I cannot reproduce with this:

drop table t;

CREATE TABLE "T" (
"ID" VARCHAR2(50) NOT NULL ENABLE,
"STACK_TRACE" CLOB,
"DATA" CLOB
)
LOGGING NOCOMPRESS
LOB ("STACK_TRACE") STORE AS
(TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 1))
LOB ("DATA") STORE AS (TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK
8192 PCTVERSION 10 CACHE STORAGE(INITIAL 524288 FREELISTS 1
FREELIST GROUPS 1)) ;


@trace
declare
l_data long := rpad( '*', 32760, '*' );
begin
for i in 1 .. 100
loop
insert into t values( i, l_data, l_data );
end loop;
end;
/
set autotrace traceonly
select * from t;
set autotrace off



Are you sure the table you are using actually used that create (dbms_metadata.get_ddl for example - or query the dictionary)


give me a test case that reproduces and I'll look at it.

In Line or out of line

atul, November 09, 2005 - 6:10 am UTC

Hi,

We have a clob cloumns in a table.
We are not sure if we keep them In-Line or Out-of-line.

Could you please help.

1)We are moving CLOB's to its own tablespace.
2)Our's is OLTP,where insert rate is high,and we do update query through our scripts which updates Non CLOB column and for that we have indexes which are not on any CLOB column.

3)So if we keep clob out of line,Will inserts & updates be fast in our situation

4)Our Avg CLOB size for some tables is > 4k and for maximum tables < 4k.

5)We are using 9.2.0.6 ,Is it possible to move LOBINDEX in different tablespace.

Thanks

Tom Kyte
November 10, 2005 - 5:22 pm UTC

3) maybe yes, maybe no, maybe no difference.


It depends. If you access the row to update via an index, it likely will make no differences.

moving the lobs out of line will potentially decrease the performance for inserts and updates that modify lobs of less than 4000 characters (under 4000 characters the lob is similar to a varchar2 - over 4000 characters it gets moved out of line - and remember by default they are NOT cached when out of line)


5) the lobsegment and lobindex will ALWAYS be together, ignore the lobindex, you cannot touch it.

Move BLOBs to another DB on another box

Wayne, December 09, 2005 - 2:49 pm UTC

Tom,
We are researching on the best way(s) to move blobs from one db to another (both DBs are 9.2.6, but on different Unix server), For example, fax server got a fax from a DB in the DMZ area and we want to move it into our application area behind a fire wall. Data could be stored in-row or in OS BFILE).
Do you have some recommendations?



Thanks,


Tom Kyte
December 10, 2005 - 4:39 am UTC

if stored in blob - dblinks would work and be fairly easy.


Move BLOBs

Wayne, December 11, 2005 - 11:46 am UTC

Thanks,
Do you mean DB links works on moving BLOBs that are stored both in-row and on OS (using BFILEs)?



Tom Kyte
December 11, 2005 - 5:25 pm UTC

I referenced BLOBS specifically, store them in bfiles and you are on your own to move the FILES from system A to system B.

Move BLOBS

Wayne, December 12, 2005 - 12:42 pm UTC

Thanks!

Move CLOB/BLOB Columns in Diff Tablespace in separate Disk.

Rahul Dutta, February 10, 2006 - 2:50 pm UTC

Hi Tom,

Context:
========
We are using 10g Rel:1 on production for a Content Management Application. The size of the Database is around 1GB and is growing at a rate of 3MB a day. The main data that is growing is CLOB/BLOB. We have not created any Index for CLOB/BLOB columns as of now.
Question
=========
What will be our Next steps in order to prevent us from getting into any Performance bottlenecks:
1) Move BLOB/CLOB columns in different Tablespace in different disk to increase IO.
2) Create Index for columns after referencing costly queries.

Please suggest us the good approach to handle this situation. Also, Does Oracle has any documents to support this as we have to recomment this to our clients.

Thanks a lot

Rahul



Tom Kyte
February 11, 2006 - 1:44 pm UTC

1) in order to "increase IO"?

A tablespace is a collection of one or more files in general. A tablespace is not a "tuning device" really (just because byte A and byte B are in different tablespaces does NOT mean that bytes A and B are on different devices at all)

Generally, you tune IO via striping, which either takes place at the OS level, or at the database level by having multiple datafiles from different devices in a single tablespace or at the database level using ASM in 10g (automatic storage management).

You'll really want to understand how lobs are implemented, there is a full document on nothing but lobs:
</code> http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10796/toc.htm

and if you have access to my latest book Expert Oracle: Database Architecture, I cover LOBs and all of their parameters - what might make sense and when - in the chapter on datatypes.


2) you don't say what your searching needs are.  In general, if you have documents (say like this site does - each page being a document), you might consider using Text to index and search them:

http://docs.oracle.com/docs/cd/B14117_01/text.101/b10730/toc.htm <code>


Your situation is somewhat vague here - not sure where to point you exactly.

replicating lobs...

Craig, March 01, 2006 - 1:23 pm UTC

We have a separate tablespace for all large objects. We are looking at Oracle Streams to replace our current replication methods. The Oracle 9i Streams documentation states:

"LOB WRITE, LOB ERASE, and LOB TRIM are the only valid command types for out-of-line LOBs."

Since our lob columns are stored out-of-line in a separate tablespace, are these the only three commands that will replicate, or does this limitation relate to something else?

Thanks.

Tom Kyte
March 02, 2006 - 8:29 am UTC

those are the "commands" included in the LCR (logical change record) used by the APPLY process - it is just saying "these are the three commands you'll see"

eg:

...
Each row LCR contains the following information:

* The name of the source database where the row change occurred
* The type of DML statement that produced the change, either INSERT, UPDATE, DELETE, LOB ERASE, LOB WRITE, or LOB TRIM
......




Large lob segments

Bobby, March 09, 2006 - 6:12 am UTC

I am moving a lob segment which is of 7.5GB from one tablespace DATA to other LGDATA. LGDATA is having 13Gb as free space. But still I am unable to move the lob segment. The system occupies all the free space in LGDATA tablespace and in the end gives the error ORA-1652.

Please suggest some solution. Thanks in Advance

Tom Kyte
March 09, 2006 - 2:26 pm UTC

well, what are your storage options here. "need slightly more detail"

recursive CPU

A reader, October 11, 2006 - 1:35 pm UTC

Hi Tom,
what is your way to dignose a case when the recursive CPU is about 60% of the total cpu time ?

i have statspack where the "recursive cpu usage" is about 70% of the "CPU used by this session"

i am using 9iR2 9.2.0.6
i used the SAR command and it show that the cpu utilization was about 90% , the user part only reach 80% , a statspack during that interval shows high CPU time but the surprising is that the most was recursive CPU , could you please help me in that

is there anyway to reduce that?
thanks


Tom Kyte
October 11, 2006 - 4:05 pm UTC

depends on where the recursive cpu comes from - do you do lots of plsql, that is all "recursive" sql in there.


you cannot ever get an anwser to "how to reduce cpu" short of "do less stuff"?? You need to identify where the cpu is being used.

thanks very much

A reader, October 11, 2006 - 11:33 pm UTC

Hi Tom,

thanks for clearing that , i have never thought that plsql will be counted as recursive , i thought recursive is for space and parsing stuff

i have some plsql.

should i work in tuning them to reduce the number of logical I/Os ? is that the right approach ?

thanks

Tom Kyte
October 12, 2006 - 8:11 am UTC

I would trace the application
then look for the "low hanging fruit"

the sql that does the most work. (cpu, logical io)

will you please explain about the plsql and recursive

A reader, October 12, 2006 - 11:25 am UTC

Hi Tom,
thank you for this intersting thread

will you please give us more details about the plsql that is considered recursive calls

i understood that all sql statments included in any plsql code will be counted as a recursive call , am i right ?

but what about the time this statment consume , will it be reported as a "recursive CPU time" in the statspack ? or will it be reported as the other cpu time ( other cpu time = total cpu used by this session - recusive cpu time - parse time cpu )

thanks in advance

Tom Kyte
October 12, 2006 - 11:52 am UTC

pretty easy to measure:

ops$tkyte%ORA10GR2> create or replace procedure do_something
  2  as
  3  begin
  4          for x in ( select count(*) c from
  5                      (select * from all_objects, (select level l from dual connect by level <= 5))
  6                                  )
  7          loop
  8                  dbms_output.put_line( x.c );
  9          end loop;
 10  end;
 11  /

Procedure created.
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> @mystat "recursive c"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
recursive calls                81
recursive cpu usage             0

ops$tkyte%ORA10GR2> @mystat "CPU used"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
CPU used when call sta          2
rted

CPU used by this sessi          2
on

IPC CPU used by this s          0
ession

global enqueue CPU use          0
d by this session

gc CPU used by this se          0
ssion


ops$tkyte%ORA10GR2> exec do_something
204315

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @mystat "recursive c"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
recursive calls              5155
recursive cpu usage           164

ops$tkyte%ORA10GR2> @mystat "CPU used"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
CPU used when call sta        167
rted

CPU used by this sessi        167
on

IPC CPU used by this s          0
ession

global enqueue CPU use          0
d by this session

gc CPU used by this se          0
ssion

 

life is easier

A reader, October 12, 2006 - 12:16 pm UTC

Hi Tom,

i can see it is all went in the recursive CPU time , very interesting. therefore when i have plsql running the recursive time isn't a waste by oracle doing stuff but it is our statments in the plsql that may be need tuning, am i right ?
Thank you very much , for not only answering our quiestions but to show us how to measure and learn new things.



Tom Kyte
October 13, 2006 - 6:39 am UTC

well, I would hardly call it "waste" at all, occasionally you can reduce it by better implementation techniques, but it is not "waste"



Move lob data in diffrent tablespace

Atul, October 13, 2006 - 2:25 pm UTC

Hi Tom,
I have a question related to this tophic...

Currently my database version is 9.2.0.6 and block size is 4k and I am planning to migrate in 16k block size tablespace. Due to the time constraints, I don’t want to accomplish this job using export and import.
My planning is to
1. Create separate data buffer for 16k
2. Create a new tablespace with 16k block size
3. Relocate the table from 4k block size tablespace to new 16k block size tablespaces
4. In my database some tables hold CLOB and BLOB data and all data and index are exists in one tablespace.

My concern is, can I move LOB contains table from one tablespace to another tablespace (16k block size).
If yes, then
Is below command move the table with lobs column to new tablespace ?

“alter table t move lob(y) store as (16_k tablespace )”

If not, then what will be the correct command for moving?

Thanks in advance


Not able to move the log segment of a hash partitioned table

Logan Palanisamy, November 04, 2006 - 2:13 pm UTC

Tom,

How do I move the lob segment of a hash partitioned table to its own tablespace? Here is a simple test case. Would appreciate if you could help me. 

SQL> create table t
  2  (
  3  my_id        number primary key,
  4  my_lob      clob
  5  )
  6  partition by hash(my_id)
  7  (partition t_p1,
  8  partition t_p2);

Table created.

SQL> insert into t select object_id, object_type from all_objects;

54509 rows created.

SQL> commit;

Commit complete.

SQL>

SQL>
SQL> alter table t move partition t_p1 lob(my_clob) store as (tablespace t_p1_tbs);
alter table t move partition t_p1 lob(my_clob) store as (tablespace t_p1_tbs)
                                                                       *
ERROR at line 1:
ORA-22877: invalid option specified for a HASH partition or subpartition of a
LOB column


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> spool off
 

Tom Kyte
November 04, 2006 - 3:34 pm UTC

contact support and reference bug 4583442

Not able to move log segments for hash partitioned tables

Logan Palanisamy, November 04, 2006 - 2:46 pm UTC

Tom,

My previous posting had a typo (my_clob instead of my_lob in the alter table command). Still the same error even after correcting it. But it seems to work for RANGE partitioned tables.

SQL> drop table t;

Table dropped.

SQL> create table t
  2  (
  3  my_id      number primary key,
  4  my_lob     clob
  5  )
  6  partition by hash(my_id)
  7  (partition t_p1,
  8  partition t_p2);

Table created.

SQL> insert into t select object_id, object_type from all_objects;

54518 rows created.

SQL> commit;

Commit complete.

SQL> alter table t move partition t_p1 lob(my_lob) store as (tablespace t_p1_tbs);
alter table t move partition t_p1 lob(my_lob) store as (tablespace t_p1_tbs)
                                                                      *
ERROR at line 1:
ORA-22877: invalid option specified for a HASH partition or subpartition of a 
LOB column 


SQL> 
SQL> drop table t;

Table dropped.

SQL> create table t
  2  (
  3  my_id      number primary key,
  4  my_lob     clob
  5  )
  6  partition by range(my_id)
  7  (partition t_p1 values less than (1000),
  8  partition t_p2 values less than (maxvalue));

Table created.

SQL> insert into t select object_id, object_type from all_objects;

54518 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> alter table t move partition t_p1 lob(my_lob) store as (tablespace t_p1_tbs);

Table altered.

SQL> spool off 

Tom Kyte
November 04, 2006 - 3:36 pm UTC

see above...

A reader, November 04, 2006 - 10:09 pm UTC

Apply patch# 4583442 for 10.2.0.1

A reader, November 04, 2006 - 10:11 pm UTC

Opps Tom already replied missed that.

Changing Freelists and Freelists Groups for a LOB Segment

a reader, December 08, 2006 - 9:37 am UTC

Tom,

Is there a way to change the number of freelists and groups for a table and its lob segment. I can change the table with:

alter table t storage(freelists 4);

I have tried the following on the lob segment:

alter table t modify lob(x) (freelists 4);

But it did not work. I think someone said in this topic or another topic on this site that lobs don't use freelists. Is this true? If so, then why can I specify the # of freelists when creating the table? Thanks for all help!

Tom Kyte
December 09, 2006 - 12:27 pm UTC

ops$tkyte%ORA10GR2> create table t ( x clob ) tablespace manual;

Table created.

ops$tkyte%ORA10GR2> select segment_name, segment_type, freelists from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE        FREELISTS
------------------------------ ------------------ ----------
SYS_IL0000059822C00001$$       LOBINDEX                    1
SYS_LOB0000059822C00001$$      LOBSEGMENT                  1
T                              TABLE                       1

ops$tkyte%ORA10GR2> alter table t storage ( freelists 4 ) modify lob(x) (storage(freelists 4));

Table altered.

ops$tkyte%ORA10GR2> select segment_name, segment_type, freelists from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE        FREELISTS
------------------------------ ------------------ ----------
SYS_IL0000059822C00001$$       LOBINDEX                    4
SYS_LOB0000059822C00001$$      LOBSEGMENT                  4
T                              TABLE                       4

 

Inline and Out-of-line LOB

Citrus, December 23, 2006 - 8:48 am UTC

Hi Tom,

How do I know how many of my records are stored inline and how many are stored out-of-line????

Thanks a lot
Tom Kyte
December 24, 2006 - 9:11 am UTC

select x, count(*) 
  from (
select case 
       when dbms_lob.getlength(x) < 4000 
       then 'inline' 
       else 'outline' 
        end x
  from t
       )
 group by x;


presuming that "enable storage in row is true" will give you a good idea.

Seperatin LOB Indexes and Lob segments

Ram, May 02, 2007 - 7:38 am UTC

Hi Tom,
Thanks again for the excellent answers.

You were mentioning above that LOB Indexes cannot be touched and its always managed internally , i had a question in this regard.

When i move the LOB segement to a seperate tablespace , the LOB index is also moved along to the same tablespace.

If this is the case then how will i or will i be able to move Lob Index and Lob segement to Two Different tablespaces.

Thanks in advance
Tom Kyte
May 02, 2007 - 9:09 am UTC

you do not, "LOB Indexes cannot be touched and its always managed internally"

Michael, November 09, 2007 - 5:42 pm UTC

I tried to move a lob using the syntax above, and I get an ora-00997 error -illegal use of long.

alter table t move lob (lob_name) store as (tablespace new_ts)

The column isn't a long, but there is a long in the table. Does having a long in the table, prevent you from moving the lob column.

BTW it is version 9.2
Tom Kyte
November 10, 2007 - 11:28 am UTC

you cannot move a table with a long in it and to move the lob, you have to move the table....


blob insert from one table to another

raju, February 12, 2008 - 3:11 pm UTC

hi tom

I am trying to insert data from non partition table to partition table having size about 60 GB mostly data is blob data. It is taking very long time. What is best way to do this kind of data move fast within same database. New lob tablespace i created is with 32k block size and current one is 8k block size.

Appriciate your help.
Tom Kyte
February 13, 2008 - 12:25 pm UTC

what is a very long time, are your expectations reasonable? what is the current time and what is your desired time

do you have the IO bandwidth capable of doing this.
can you do it in parallel.
what about unrecoverable with a backup scheduled right after
direct path it (/*+ append */)


moving a lob with a sys-generated name?

Stephan, February 17, 2008 - 5:30 pm UTC

Hi Tom,

I've got a lob segment that got created in SYSTEM and I'm trying to move it out, but am having no luck

opera@OPERA> select segment_type, segment_name from user_segments where tablespace_name = 'SYSTEM';

SEGMENT_TYPE       SEGMENT_NAME
------------------ ---------------------------------------------------------------------------------
LOBINDEX           SYS_IL0000065139C00002$$
LOBSEGMENT         SYS_LOB0000065139C00002$$

Elapsed: 00:00:00.24
opera@OPERA> select table_name from user_lobs where segmenT_name = 'SYS_LOB0000065139C00002$$';

TABLE_NAME
------------------------------
CREATE$JAVA$LOB$TABLE

Elapsed: 00:00:00.16
opera@OPERA> select table_name from user_lobs where index_name = 'SYS_IL0000065139C00002$$';

TABLE_NAME
------------------------------
CREATE$JAVA$LOB$TABLE


opera@OPERA> alter table create$java$lob$table move lob(SYS_LOB0000065139C00002$$) store as (tablespace opera_data);
alter table create$java$lob$table move lob(SYS_LOB0000065139C00002$$) store as (tablespace opera_data)
                                           *
ERROR at line 1:
ORA-00904: "SYS_LOB0000065139C00002$$": invalid identifier


Elapsed: 00:00:00.00
opera@OPERA> alter table create$java$lob$table move lob('SYS_LOB0000065139C00002$$') store as (tablespace opera_data);
alter table create$java$lob$table move lob('SYS_LOB0000065139C00002$$') store as (tablespace opera_data)
                                           *
ERROR at line 1:
ORA-00904: : invalid identifier


Elapsed: 00:00:00.01
opera@OPERA> alter table create$java$lob$table move lob("SYS_LOB0000065139C00002$$") store as (tablespace opera_data);
alter table create$java$lob$table move lob("SYS_LOB0000065139C00002$$") store as (tablespace opera_data)
                                           *
ERROR at line 1:
ORA-00904: "SYS_LOB0000065139C00002$$": invalid identifier


Elapsed: 00:00:00.00
opera@OPERA> 



So, what do I have to do to move this lob segment?

Thanks.
Tom Kyte
February 18, 2008 - 7:26 am UTC

you use a column name, not a segment name, in the alter move. and don't forget, this is a table with indexes and so after you do the move, you'll need to rebuild that as well.

Time for you to look at your users, assign them better default tablespaces and put a 0 quota on them when possible for system....

this table appears when you use loadjava...


ops$tkyte%ORA10GR2> @dbls
Wrote file /tmp/xtmpx.sql

OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        SYS_C0026063                     USERS

JAVA CLASS   x                              *

LOB          SYS_LOB0000156818C00002$$        USERS

TABLE        CREATE$JAVA$LOB$TABLE            USERS


ops$tkyte%ORA10GR2> @desc CREATE$JAVA$LOB$TABLE
Wrote file /tmp/xtmpx.sql
Datatypes for Table CREATE$JAVA$LOB$TABLE

                               Data                 Data
Column Name                    Type                 Length     Nullable
------------------------------ -------------------- ---------- --------
NAME                           VARCHAR2             700        null
LOB                            BLOB                 4000       null
LOADTIME                       DATE                 7          null


Indexes on CREATE$JAVA$LOB$TABLE

Index                          Is
Name                           Unique COLUMNS
------------------------------ ------ --------------------------------
SYS_C0026063                   Yes    NAME


Triggers on CREATE$JAVA$LOB$TABLE
ops$tkyte%ORA10GR2> alter table CREATE$JAVA$LOB$TABLE move tablespace test lob(lob) store as (tablespace test);

Table altered.

ops$tkyte%ORA10GR2> alter index SYS_C0026063 rebuild tablespace test;

Index altered.

ops$tkyte%ORA10GR2> @dbls
Wrote file /tmp/xtmpx.sql

OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        SYS_C0026063                     TEST

JAVA CLASS   x                              *

LOB          SYS_LOB0000156818C00002$$        TEST

TABLE        CREATE$JAVA$LOB$TABLE            TEST



migrate huge LOBs

A reader, July 31, 2012 - 9:29 am UTC

Hello,

We have tables that need to be migrated to a different database. These tables have LOB Indexes. These segments are up to 4TB in size. What is the fastest and efficient way to migrate?

Thanks,

Tom Kyte
July 31, 2012 - 12:50 pm UTC

can you transport them? (yes, you can)

then it becomes a file copy, or just a restore from a backup.

follow up,

A reader, July 31, 2012 - 1:34 pm UTC

I got a bit clarity on the question from DBA. Actually the team wants to move the table that is non-partitioned to a HASH partitioned table (within the same schema/database). They tried CTAS option on the development box and it took almost 5 hours for a 200GB LOB segment. Since, on production, this is close to 4TB, we are wondering about other options.

Can we still consider transportable tablespace in this scenario?

Thanks,

Tom Kyte
July 31, 2012 - 4:22 pm UTC

if you want to reorg, you are going to have to completely read every existing bit of data and write it again - build the new lob index as you go along.

transporting is not an option.

parallel create table as select (CTAS) is likely the 'fastest' approach. You can skip redo generation which might help a bit.

you will be gated predominantly by your IO capability. If you have say a 1gb (gigabyte) connection to your SAN and you "own" that connection - you get maximum throughput it could take 12 hours to read the data, 12 hours to write the data (not counting the hours of processing).

think about your ability to perform IO, you have to read the thing and then write it again.

follow up,

A reader, August 01, 2012 - 3:22 pm UTC

Thank your for your suggestion. I think skipping a bit of redo generation isn't a choice for us since we have physical standby database synchronized by data guard.

A question: since LOB segments are stored outside the table, can we just move the table (without LOB) to a new partitioned table and later attach LOB to the new table?

Thanks,

Tom Kyte
August 01, 2012 - 3:50 pm UTC

for a 4tb move of data, you should be willing to consider everything. You can just move the datafiles over to the standby after the operation. You might find that to be a lot faster than shipping 4tb of redo and applying it.


A question: since LOB segments are stored outside the table, can we just move
the table (without LOB) to a new partitioned table and later attach LOB to the
new table?


no, and the lob data would have to be partitioned as well. every bit and byte of lob data must be rebuilt in partitions.

A reader, April 28, 2017 - 1:06 pm UTC

Why LOB data not move to other tablespace when it's table move to another tablespace?

why we need manually to fire alter command.
Connor McDonald
April 28, 2017 - 10:55 pm UTC

Because they are a *different* segment, just like an index. When you move a table, would you want the indexes moved as well ? What if you had tablespaces

DATA_2016
DATA_2017
INDEXES

and your tables are in the two DATA tablespaces, and your indexes in INDEXES.

If I move a table from DATA_2016 to DATA_2017...then wouldn't you be upset if I automatically moved the index out of INDEXES as well ?

So then we into a territory where we have all sorts of rules about when to move things, when to not etc...

Much better to give *you* explicit control...which is what we do


error during lob move

Rajeshwaran, Jeyabal, February 17, 2021 - 10:28 am UTC

Team:

we are getting this below error while moving lob segments to another tablespace.

please help us to understand what does it means and how to get it resolved.

demo@PDB1> select column_name,data_type,data_length,avg_col_len
  2  from all_tab_cols
  3  where owner ='APP_USER'
  4  and table_name ='TAB1'
  5  order by column_id ;

COLUMN_NAME          DATA_TYPE            DATA_LENGTH AVG_COL_LEN
-------------------- -------------------- ----------- -----------
C1            VARCHAR2                     255          38
C2            VARCHAR2                     255          38
C3            VARCHAR2                     255          13
C4            VARCHAR2                      10           5
C5            VARCHAR2                      10           3
C6            CLOB                        4000        2245
C7            TIMESTAMP(6)                  11          11
C8            VARCHAR2                     255          14
C9            VARCHAR2                     255           4
C10            NUMBER                        22           3
C11            NUMBER                        22           4
C12            CHAR                           1           2

12 rows selected.

demo@PDB1> alter table app_user.TAB1
  2  move partition PY
  3  tablespace TS_PY
  4  parallel 3;
alter table app_user.TAB1
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P00I, instance xxxxxx.us1.ocm.s1234567.oraclecloudatcustomer.com:XXXX (1)
ORA-12899: value too large for column ??? (actual: 4294967098, maximum: 255)


Elapsed: 00:00:54.05

Chris Saxon
February 17, 2021 - 2:56 pm UTC

Can you post a complete test case showing this issue?

error during lob move

Rajeshwaran, Jeyabal, February 18, 2021 - 6:30 am UTC

Sorry can't produce a reproducible testcase to demonstrate this error.
However the above error was from our production system running on 18c (18.10) on Exacc platform.
given this table structure and "move" command - any clue/inputs of what can be done over here to resolve this error?
Chris Saxon
February 18, 2021 - 8:40 am UTC

I don't know what the issue is here; speak with support.

LOB compress on a Hash Partitioned Tables.

Rajeshwaran Jeyabal, January 20, 2022 - 12:15 pm UTC

Team,

the below test case was from 21c XE instance.
is it not possible to compress lob on Hash Partitioned tables?

demo@XEPDB1> create table t2 (c1 int,x clob)
  2  lob(x)
  3  store as securefile( nocompress )
  4  partition by hash(c1)
  5  ( partition p1 , partition p2 );

Table created.

demo@XEPDB1> alter table t2
  2  move partition p1
  3  lob(x)
  4  store as (nocache nologging compress high);
store as (nocache nologging compress high)
                                         *
ERROR at line 4:
ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column

Connor McDonald
January 24, 2022 - 2:05 am UTC

I will confirm internally, but I suspect that for hash partitions the rule will be "all" or "none". eg

SQL> create table t2 (c1 int,x clob)
  2      lob(x)
  3      store as securefile ( nocompress )
  4      partition by list(c1)
  5      ( partition p1 values (1)  , partition p2 values(2) );

Table created.

SQL>
SQL> alter table t2
  2      move partition p1
  3      lob(x)
  4      store as (compress);

Table altered.

SQL> create table t2 (c1 int,x clob)
  2      lob(x)
  3      store as securefile ( nocompress )
  4      partition by hash(c1)
  5      ( partition p1 , partition p2 );

Table created.

SQL>
SQL> alter table t2
  2      move partition p1
  3      lob(x)
  4      store as (compress);
    store as (compress)
                      *
ERROR at line 4:
ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB co


SQL> create table t2 (c1 int,x clob)
  2      lob(x)
  3      store as securefile ( compress )
  4      partition by hash(c1)
  5      ( partition p1 , partition p2 );

Table created.



LOB compress on a Hash Partitioned Tables.

Rajeshwaran Jeyabal, January 24, 2022 - 4:38 am UTC


...
but I suspect that for hash partitions the rule will be "all" or "none". eg
...

we got an application table that got partitioned by HASH and have lobs of size 4 TB uncompressed.
our goal is to compress those lobs.
so can't we do move partition here?
as a workaround we were thinking of doing CTAS a partition to an interim-table with lobs compressed then do an exchange partition finally.

can you think of anyother better workaround to this?
demo@XEPDB1> create table t2 (c1 int,x clob)
  2  lob(x)
  3  store as securefile( nocompress )
  4  partition by hash(c1)
  5  ( partition p1 , partition p2 );

Table created.
demo@XEPDB1> create table t2_temp
  2  lob(x)
  3  store as securefile( compress high )
  4  for exchange with table t2;

Table created.
demo@XEPDB1> alter table t2
  2  exchange partition p1
  3  with table t2_temp;

Table altered.
demo@XEPDB1>

Connor McDonald
January 25, 2022 - 6:40 am UTC

In fact, from the error message manual, we are more specific

22877, 00000, "invalid option specified for a HASH partition or subpartition of a LOB column"
// *Cause:   One or more invalid options were encountered while parsing the
//           physical attributes of a LOB partition or subpartition. Either
//           the LOB partition is in a table partitioned using the HASH method,
//           or the LOB subpartition is in a table subpartitioned using the
//           HASH method. TABLESPACE is the only valid option for a HASH
//           partition or subpartition.
// *Action:  Remove the invalid option(s).
// *Comment: This error could result from omission of a
//           terminating (right) parenthesis following the list of
//           subpartition descriptions.


so its quite possible that the allowance of exchange is the bug here :-)

I'll ask around internally

Update: If you have sufficient space, you could use an online move for the whole table, eg

SQL> create table t2 (c1 int,x clob)
  2      lob(x)
  3      store as securefile ( nocompress )
  4      partition by hash(c1)
  5      ( partition p1 , partition p2 );

Table created.

SQL>
SQL> insert into t2 select rownum, rpad('x',32000)
  2  from dual connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table t2 modify lob(x) (compress);

Table altered.

SQL>
SQL> alter table t2 modify
  2  partition by hash(c1)
  3  ( partition p1 , partition p2 )
  4  online;

Table altered.