Thinking Out Loud

March 17, 2013

Oracle Move objects to another tablespace

Filed under: oracle — mdinh @ 4:46 am

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)>
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 300 other followers

%d bloggers like this: