Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, rahul.

Asked: May 27, 2008 - 6:09 pm UTC

Last updated: December 14, 2012 - 2:17 pm UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Tom,

Here is my table definition

create table test(
id number not null,
day_of_week number not null,
testdata clob
) partition by list (day_of_week)
partition p1 values(1) tablespace t1,
partition p2 values(2) tablespace t2,
partition p3 values(3) tablespace t3,
partition p4 values(4) tablespace t4,
partition p5 values(5) tablespace t5,
partition p6 values(6) tablespace t6,
partition p7 values(7) tablespace t7;

I am trying to partition data by day of the week and I only store data for 5 days. Clob column size will be more than 4k. I would drop partition and re-create the partition two days before that day. I would drop & recreate the partition p5 on 3rd day of the week, p6 on 4th day of the week, p7 on 5th day, p1 on 6th day....

Couple of questions here:

1. I understand that LOB storage will go into its own tablespace. How do I utilize lob partitioning here to store the testdata column? can I just use LOB (test_data) STORE AS (TABLESPACE lob_tablespace) and try to subpartition it?

2. When I drop and re-create a partition, will oracle use the fastest way to clean-up my lobs?

Please clarify.

Thanks,
rahul


and Tom said...

why drop and recreate. just truncate please.


1) lob data is equipartioned already...

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    z   clob
  7  )
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 100
  5          loop
  6                  insert into t (dt,x,y,z) values ( to_date('12-mar-2003')+mod(i,2), i, i, l_data );
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select object_name, object_type,data_object_id from user_objects;

OBJECT_NAME                    OBJECT_TYPE         DATA_OBJECT_ID
------------------------------ ------------------- --------------
T                              TABLE PARTITION             158868
T                              TABLE
SYS_IL0000158867C00004$$       INDEX PARTITION             158875
SYS_IL0000158867C00004$$       INDEX PARTITION             158874
SYS_LOB0000158867C00004$$      LOB PARTITION               158872
SYS_LOB0000158867C00004$$      LOB PARTITION               158871
SYS_LOB0000158867C00004$$      LOB                         158870
T                              TABLE PARTITION             158869

8 rows selected.

ops$tkyte%ORA10GR2> alter table t truncate partition part1;

Table truncated.

ops$tkyte%ORA10GR2> select object_name, object_type,data_object_id from user_objects;

OBJECT_NAME                    OBJECT_TYPE         DATA_OBJECT_ID
------------------------------ ------------------- --------------
T                              TABLE PARTITION             158877
T                              TABLE
SYS_IL0000158867C00004$$       INDEX PARTITION             158875
SYS_IL0000158867C00004$$       INDEX PARTITION             158874
SYS_LOB0000158867C00004$$      LOB PARTITION               158872
SYS_LOB0000158867C00004$$      LOB PARTITION               158876
SYS_LOB0000158867C00004$$      LOB                         158870
T                              TABLE PARTITION             158869

8 rows selected.


if you truncate, we truncate the lob as well (note the data object id changed..)

2) do not drop, truncate. We'll just release the storage. no redo, no undo except that which we need for the data dictionary updates.

Rating

  (1 rating)

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

Comments

LOB Partition and Tablespace

A reader, December 07, 2012 - 4:17 pm UTC

In the example below, why did the LOB partition get creatd in the users tablespace (the default)? In the CREATE TABLE

statement, I explicitly specify the LOB partition to be created in the LOB_tablespace tablespace.

"LOB(LOB_Column) STORE AS (TABLESPACE lob_tablespace ENABLE STORAGE IN ROW)"

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> 
SQL> SELECT default_tablespace
  2  FROM   user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

SQL> 
SQL> CREATE TABLE t (
  2     ID           INTEGER NOT NULL,
  3     LOB_Column   CLOB,
  4     created_date DATE
  5  )
  6  PARTITION BY RANGE (created_date)
  7  (
  8     PARTITION p_1 VALUES LESS THAN (TO_DATE('2012-11-01', 'yyyy-mm-dd'))
  9        TABLESPACE lob_tablespace
 10        LOB(LOB_Column) STORE AS (TABLESPACE lob_tablespace ENABLE STORAGE IN ROW),
 11     PARTITION p_2 VALUES LESS THAN (TO_DATE('2012-12-01', 'yyyy-mm-dd'))
 12        TABLESPACE lob_tablespace
 13        LOB(LOB_Column) STORE AS (TABLESPACE lob_tablespace ENABLE STORAGE IN ROW)
 14  );

Table created.

SQL> 
SQL> INSERT INTO t VALUES (1, 'abc', TO_DATE('2012-10-01', 'yyyy-mm-dd'));

1 row created.

SQL> INSERT INTO t VALUES (2, 'def', TO_DATE('2012-11-01', 'yyyy-mm-dd'));

1 row created.

SQL> 
SQL> DECLARE
  2     l_data LONG := RPAD('*', 32000, '*');
  3  
  4  BEGIN
  5     INSERT INTO t VALUES (3, l_data, TO_DATE('2012-10-01', 'yyyy-mm-dd'));
  6     INSERT INTO t VALUES (4, l_data, TO_DATE('2012-11-01', 'yyyy-mm-dd'));
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> SELECT segment_name, partition_name, segment_type, tablespace_name
  2  FROM   user_segments
  3  WHERE  segment_name = 'T';

SEGMENT_NAME              PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------- ------------------------------ ------------------ ------------------------------
T                         P_1                            TABLE PARTITION    LOB_TABLESPACE
T                         P_2                            TABLE PARTITION    LOB_TABLESPACE

SQL> 
SQL> SELECT tablespace_name, table_name, column_name
  2  FROM   user_lobs
  3  WHERE  table_name   = 'T';

TABLESPACE_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------------
USERS                          T                              LOB_COLUMN

Tom Kyte
December 14, 2012 - 2:17 pm UTC

look at the segments - not at the "thing that is not a segment" - user lobs isn't showing partition level stuff - there is no single "lob" here - there are lob partitions just like table paritions:

ops$tkyte%ORA11GR2> CREATE TABLE t (
  2     ID           INTEGER NOT NULL,
  3     LOB_Column   CLOB,
  4     created_date DATE
  5  )
  6  segment creation immediate
  7  PARTITION BY RANGE (created_date)
  8  (
  9     PARTITION p_1 VALUES LESS THAN (TO_DATE('2012-11-01', 'yyyy-mm-dd'))
 10        TABLESPACE assm
 11        LOB(LOB_Column) STORE AS (TABLESPACE assm ENABLE STORAGE IN ROW),
 12     PARTITION p_2 VALUES LESS THAN (TO_DATE('2012-12-01', 'yyyy-mm-dd'))
 13        TABLESPACE assm
 14        LOB(LOB_Column) STORE AS (TABLESPACE assm ENABLE STORAGE IN ROW)
 15  );

Table created.

ops$tkyte%ORA11GR2> select segment_name, segment_type, tablespace_name
  2    from dba_segments
  3   where owner = user;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
T                              TABLE PARTITION    ASSM
T                              TABLE PARTITION    ASSM
SYS_IL0000132475C00002$$       INDEX PARTITION    ASSM
SYS_IL0000132475C00002$$       INDEX PARTITION    ASSM
SYS_LOB0000132475C00002$$      LOB PARTITION      ASSM
SYS_LOB0000132475C00002$$      LOB PARTITION      ASSM

6 rows selected.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.