Move objects to another tablespace for tables, indexes, lobs, iots.
Table with LONG datatype can only be moved using export and import.
Note:
DBA_INDEXES – IOT1 – organization index tablespace users overflow tablespace ndat
DBA_TABLES – IOT2 – organization index tablespace ndat overflow tablespace users
Example below is to move all objects out of USERS tablespace.
LAX:(MDINH@db01)> @c
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
LAX:(MDINH@db01)> create table longt (id number, name long, junk raw(16)) tablespace users;
Table created.
LAX:(MDINH@db01)> create table tab(id int, name varchar2(30)) tablespace users;
Table created.
LAX:(MDINH@db01)> create bitmap index bm on tab(id);
Index created.
LAX:(MDINH@db01)> create table iot1(id int, name varchar2(30), constraint pk1 primary key (id))
2 organization index tablespace users overflow tablespace ndat;
Table created.
LAX:(MDINH@db01)> create table iot2(id int, name varchar2(30), constraint pk2 primary key (id))
2 organization index tablespace ndat overflow tablespace users;
Table created.
LAX:(MDINH@db01)> create table lob (id number, picture blob, resume clob)
2 lob (picture) store as picturelobs (tablespace users index (tablespace users))
3 lob (resume) store as (tablespace users index resumeidx (tablespace users));
Table created.
LAX:(MDINH@db01)> create index x on lob(id) tablespace users;
Index created.
LAX:(MDINH@db01)> create or replace procedure p( p_id in int, p_resume in varchar2 )
2 as
3 begin
4 insert into lob(id,resume) values (p_id,p_resume);
5 commit;
6 end;
7 /
Procedure created.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @o
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';
NM IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT
MDINH.TAB
MDINH.SYS_IOT_OVER_15444 IOT2 IOT_OVERFLOW
MDINH.LOB
LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';
NM TN INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
MDINH.PK1 MDINH.IOT1 IOT - TOP
MDINH.RESUMEIDX MDINH.LOB LOB
MDINH.SYS_IL0000015447C00002$$ MDINH.LOB LOB
MDINH.X MDINH.LOB NORMAL
MDINH.BM MDINH.TAB BITMAP
LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';
NM COLUMN_NAME INDEX_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
MDINH.LOB RESUME RESUMEIDX SYS_LOB0000015447C00003$$
MDINH.LOB PICTURE SYS_IL0000015447C00002$$ PICTURELOBS
LAX:(MDINH@db01)> @m
LAX:(MDINH@db01)> set serverout on
LAX:(MDINH@db01)> -- MOVE LOB FIRST
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS'
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move lob('||x.column_name||') store as '||x.segment_name||' (tablespace nlob)';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_TABLES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||iot_name nm from dba_tables where tablespace_name='USERS' and iot_name is not null
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move tablespace ndat overflow tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_INDEXES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('IOT - TOP')
6 ) loop
7 begin
8 l_sql := 'alter table '||x.tn||' move tablespace ndat overflow tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE TABLES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||table_name nm from dba_tables where tablespace_name='USERS' and iot_name is null
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
!!!!! ERROR: alter table MDINH.LONGT move tablespace ndat
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE INDEXES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('NORMAL','BITMAP')
6 ) loop
7 begin
8 l_sql := 'alter index '||x.nm||' rebuild tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @@o.sql
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';
NM IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT
LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';
no rows selected
LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';
no rows selected
LAX:(MDINH@db01)>
Thinking Out Loud 