Re: table reorganisation question?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 17 Dec 2001 06:50:54 -0800
Message-ID: <9vl0oe01nsn@drn.newsguy.com>

In article <3c1df601$0$225$ed9e5944_at_reading.news.pipex.net>, "Niall says...
>
>Is it possible to change lob storage parameters online or do I have to do
>export/recreate tables/import? specifically I want to allocate lobs to their
>own tablespace but can see I might want to change pctversion etc at a later
>date. DBA Studio and sqlplus don't seem to allow it but I can't find the
>info for sure in TFM.
>
>--
>Niall Litchfield
>Oracle DBA
>Audit Commission UK
>*****************************************
>Please include version and platform
>and SQL where applicable
>It makes life easier and increases the
>likelihood of a good answer
>
>******************************************
>
>

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE TABLE t   2 (x int, y clob)
  3 LOB ("Y")
  4 STORE AS (TABLESPACE "USERS"

  5            ENABLE STORAGE IN ROW
  6            CHUNK 8192
  7            PCTVERSION 10
  8            NOCACHE
  9           )

 10 /

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from user_lobs where table_name = ''T'' ' );

TABLE_NAME                    : T
COLUMN_NAME                   : Y
SEGMENT_NAME                  : SYS_LOB0000024119C00002$$
INDEX_NAME                    : SYS_IL0000024119C00002$$
CHUNK                         : 8192
PCTVERSION                    : 10
CACHE                         : NO
LOGGING                       : YES
IN_ROW                        : YES
-----------------

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter table t move lob(y) store as ( disable storage in row

  2                                       chunk 16k
  3                                       pctversion 25
  4                                       cache )

  5 /

Table altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from user_lobs
where table_name = ''T'' ' );
TABLE_NAME                    : T
COLUMN_NAME                   : Y
SEGMENT_NAME                  : SYS_LOB0000024119C00002$$
INDEX_NAME                    : SYS_IL0000024119C00002$$
CHUNK                         : 16384
PCTVERSION                    : 25
CACHE                         : YES
LOGGING                       : YES
IN_ROW                        : NO
-----------------

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Dec 17 2001 - 08:50:54 CST