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?
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
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
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.
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?
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
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.
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.
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
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.
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
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.
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?
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.
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.
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.
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
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
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
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
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!
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
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
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?
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
October 10, 2005 - 8:50 am UTC
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...
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>
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
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.
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
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
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
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
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
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
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,
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)?
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
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.
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
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
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
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
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.
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
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
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!
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
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
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
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.
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.
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,
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,
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,
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.
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
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?
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
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>
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.