Re-organisation of table using CLOB datatype column

I need to move some tables from the current tablespace(schema) to another tablespace. I also need to poupalte these data back to original tablespace as a part of table re-oragnisation using

ALTER TABLE tablename MOVE TABLESPACE

It may be re-oranised in the same tablepsace or another.

But one of my table contains CLOB datatype. I wanted to know this type of data type would be handled using Oracle 8.1.7.

I wish to follow steps, if CLOB datatype handelded

A. Create a new tablespace TEMP_DATA with autoextend option of datafile

B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily

C. Truncate all the tables from schema

D. Resize the current tablespace, if required.

E. Move the data from TEMP_DATA back to the current
tablespace

F. Re-build all the concern unusable or invalidated indexes

G. Drop the TEMP_DATA tablespace

If the whole idea is to reorg these tables...

May I suggest:
1-- Export whole schema
2-- Truncate all schema tables
3-- Coalesce tablespaces
4-- Import fromuser/touser skip=y

Quote:

---

Originally Posted by dbajay

I need to move some tables from the current tablespace(schema) to another tablespace. I also need to poupalte these data back to original tablespace as a part of table re-oragnisation using

ALTER TABLE tablename MOVE TABLESPACE

It may be re-oranised in the same tablepsace or another.

But one of my table contains CLOB datatype. I wanted to know this type of data type would be handled using Oracle 8.1.7.

I wish to follow steps, if CLOB datatype handelded

A. Create a new tablespace TEMP_DATA with autoextend option of datafile

B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily

C. Truncate all the tables from schema

D. Resize the current tablespace, if required.

E. Move the data from TEMP_DATA back to the current
tablespace

F. Re-build all the concern unusable or invalidated indexes

G. Drop the TEMP_DATA tablespace

---

I'm not sure where the question is here, you seem to have it all in hand...

Are you asking if this is possible in 8.1.7?:

Code:

---

ALTER TABLE tab_with_lob
    MOVE LOB(c/blob_column) STORE AS (
        TABLESPACE new_tablespace
    )
/

---

I wanted to know that I should provide the follwing statement or suggested by you.

I mean it is possible to handel and to move data for CLOB datatype using

ALTER TABLE tablename MOVE TABLESPACE

I need to deal the follwing statement only for CLOB.

I am not cleared about this one.

I will appreciate if you could explain me,

ALTER TABLE tab_with_lob
MOVE LOB(c/blob_column) STORE AS (
TABLESPACE new_tablespace
)

Statement below will move table to new tablespace BUT will not move LOB segments from current tablespace...

ALTER TABLE table_name MOVE TABLESPACE target_tablespace
/


Statement below will move segments that make up specific LOB column to new tablespace...

ALTER TABLE table_name
MOVE LOB(clob_column) STORE AS (
TABLESPACE target_tablespace
)
/

Hope this helps.

Thanks for your reply. But, I wanted to know any impact or missingd data by just my moving table to another tablepspace which contatns LOB type of column.

ALTER TABLE tablename MOVE TABLEPSCAE tablespace name

Your clarification would be greatly appreciated.

LOB data is stored outside the table.

Your alter-table-move statement will move your "table" to the new tablespace while keeping your LOB data in the same place where it was before alter-table-move.

You can always conduct a test and see how nice it behaves.

If I use the tabespace approch for moving table from one tablespace to another then is it required to truncate all schema tables.

Please make your comment on it.

Quote:

---

Originally Posted by PAVB

If the whole idea is to reorg these tables...

May I suggest:
1-- Export whole schema
2-- Truncate all schema tables
3-- Coalesce tablespaces
4-- Import fromuser/touser skip=y

---

Could you define "tablespace approach"?

Are you planing to "exp/imp"?
or
are you planing to "alter table move"?

I'm planning 'alter table move' approch as one of my table is having CLOB datatype so that I can not go for exp/imp approch.

Could you please expalin me an exact steps to move table J55 having incuding CLOB datatype including index

1) alter table j35 move tablepsace target tablepscae

2) alter table j35 move tablespace source tablespace

3) alter index indexname re-build

Please let me know alos I need to take any extra step to be invloved for CLOB daat type.

Your help would be highly appreciated.

1-- Drop all FKs pointing to table j35
2-- Alter table j35 move tablespace target_tablespace
3-- Coalesce original_tablespace
4-- Alter table j35 move tablespace original_tablespace
5-- (if needed or wanted) Alter table j35 move lob(lob_column) store as (tablespace tablespace_name)
6-- Rebuild all j35 indexes including PK
7-- Build all FKs dropped on step #1
8-- Get fresh performance stats