oracle 将表从一个表空间移动到另一个表空间

1,使用下面的命令移动:
alter table table_name move tablespace tablespace_name;

2,如果有索引的话必须重建索引:
alter index index_name rebuild tablespace tablespace_name;

 

然,可以使用spool来帮助实现多个表的操作.
set header off;
spool /export/home/oracle/alter_tables.sql;
select 'alter table   ' || object_name || '  move tablespace users'
from dba_object
where owner = 'XXX' and object_type = 'TABLE';
spool off;
之后执行此sql脚本即可.
同样对于index也做同样的操作.

查看system表空间下每个对象的大小：

select * from (
select bytes/1024/1024 M,segment_name,segment_type,owner from dba_segments
where tablespace_name = 'SYSTEM'
order by bytes desc
) where rownum < 10

SQL> select OWNER,INDEX_NAME,INDEX_TYPE, TABLE_OWNER, TABLE_TYPE, TABLESPACE_NAME from  dba_indexes where TABLE_NAME='T_TROUBLEINFO_TMP' ;


OWNER      INDEX_NAME           INDEX_TYPE TABLE_OWNER  TABLE_TYPE  TABLESPACE_NAME
---------- -------------------- ---------- ------------ ----------- ------------------------------
TELECOM    PK_TEMP_KEYID        NORMAL     TELECOM      TABLE       USERS
TELECOM    T_TROUBLEINFO_TMP_IN NORMAL     TELECOM      TABLE       USERS

SQL> set   pagesize  49999
SQL> select 'alter table '||segment_name||' move tablespace users;' from dba_segments where owner='TELECOM' and TABLESPACE_NAME='SYSTEM' and segment_type='TABLE';


'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACEUSERS;'
--------------------------------------------------------------------------------
alter table T_FIX_TERMINALTYPE move tablespace users;
alter table T_FIX_COI_LOG move tablespace users;
alter table T_FIX_BUSIUSER move tablespace users;
alter table T_FIX_BUSITYPE move tablespace users;
alter table T_FIX_TABLE3 move tablespace users;
alter table T_FIX_TABLE1 move tablespace users;
alter table T_FIX_TABLE2 move tablespace users;
alter table T_CIRUIT_DELIVERY move tablespace users;
alter table IP_MASK_IPNUM move tablespace users;
alter table IP_RESOURCE move tablespace users;
alter table IP_BLOCK move tablespace users;
alter table T_OPERATION_INFO_TMP move tablespace users;
alter table T_OPERATION_INFO move tablespace users;
alter table T_STAT_JOBLIST move tablespace users;
alter table T_STAT_JOBQUERY move tablespace users;
alter table T_TROUBLE_ID_TMP move tablespace users;
alter table T_QUERY_TYPE move tablespace users;
alter table T_QUERY_DICT move tablespace users;
alter table T_QUERY_FIELD move tablespace users;
alter table T_QUERY_INFO move tablespace users;
alter table T_QUERY_MODULE move tablespace users;
alter table T_QUERY_SQES move tablespace users;
alter table T_TROUBLE_WEEK_DIFF move tablespace users;
alter table T_FIX_MEETINGPHONE move tablespace users;
alter table T_HURRY move tablespace users;
alter table T_LINK_LOG move tablespace users;


26 rows selected.


SQL> select 'alter index '||segment_name||' rebuild tablespace users;' from dba_segments where owner='TELECOM' and TABLESPACE_NAME='SYSTEM' and segment_type='INDEX';


'ALTERINDEX'||SEGMENT_NAME||'REBUILDTABLESPACEUSERS;'
--------------------------------------------------------------------------------
alter index PK_GT_W_OPERATION_INFO_WSID rebuild tablespace users;
alter index PK_GT_OPERATION_INFO_GTID rebuild tablespace users;
alter index BAT_IN rebuild tablespace users;
alter index IDX_IP rebuild tablespace users;
alter index IDX_COMMUN_PHONENUMBER rebuild tablespace users;
alter index IDX_COMMUN_PORTNUMBER rebuild tablespace users;
alter index PK_T_OPERATION_INFO rebuild tablespace users;
alter index PK_T_OPERATION_INFO_NEW_TTID rebuild tablespace users;
alter index PK_T_QUERY_TYPE rebuild tablespace users;
alter index PK_T_QUERY_DICT rebuild tablespace users;
alter index PK_T_QUERY_FIELD rebuild tablespace users;
alter index PK_T_QUERY_MODULE rebuild tablespace users;
alter index IDX_SECT_BATCHID rebuild tablespace users;


13 rows selected.