图片中 涉及的脚本在图片下方。
![](http://img.blog.itpub.net/blog/attachment/201803/30/29267792_1522392168nkyR.png?x-oss-process=style/bb)
【脚本1】 .
select df.tablespace_name "表空间名",
totalspace "总空间M",
freespace "剩余空间M",
round((1 - freespace / totalspace) * 100, 2) "使用率%"
from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name
order by df.tablespace_name;
create tablespace test_space datafile 'E:\APP\ADMINISTRATOR\ORADATA\DBSERVER\test_space.dbf'size 3G;
【脚本3】.
--以下生成脚本(根据需要来调整需要生成的脚本)
SQL> set heading off
SQL> set echo off
SQL> set feedback off
SQL> set termout on
SQL> spool d:\move_test.sql
--要移动的表
select DISTINCT 'alter table '||||segment_name || ' move tablespace test_space;' from dba_extents where segment_type='TABLE' and file_id=37;
--要移动的索引
select DISTINCT 'alter index ' || owner||'.'||segment_name || ' rebuild tablespace test_space;'
from dba_extents
where segment_type = 'INDEX'
and file_id = 37;
--移动分区表
SQL>select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace test_space;' from dba_extents where segment_type='TABLE PARTITION' and file_id=18;
--移动分区索引
SQL>select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace test_space;' from dba_extents where segment_type='INDEX PARTITION' and file_id=18;
SQL>spool off
--ps:然后执行move_test.sql,注意保证test_space有足够的空间容纳这些数据。
【脚本4】.
--以下生成脚本(根据需要来调整需要生成的脚本)
SQL> set heading off
SQL> set echo off
SQL> set feedback off
SQL> set termout on
SQL> spool d:\move_test2.sql
--表
select DISTINCT 'alter table '||segment_name || ' move tablespace TSP_ORDADM;' from dba_extents where segment_type='TABLE' and file_id=43;
--索引
select DISTINCT 'alter index ' || owner||'.'||segment_name || ' rebuild tablespace TSP_ORDADM;'
from dba_extents
where segment_type = 'INDEX'
and file_id = 43;
SQL>spool off
--移到中转表空间TEST_SPACE
ALTER TABLE LAB.LAB_IMAGE_RESULT MOVE TABLESPACE TEST_SPACE LOB(IMAGE) STORE AS(TABLESPACE TEST_SPACE);
--移回原表空间TSP_LAB
ALTER TABLE LAB.LAB_IMAGE_RESULT MOVE TABLESPACE TSP_LAB LOB(IMAGE) STORE AS(TABLESPACE TSP_LAB);
--1.在sqlplus工具中可以help copy
--先创建对象
select dbms_metadata.get_ddl('TABLE','TEST1','TEST') from dual;
--2.可以通过dblink来插入
--本地以网络登录的形式test/oracle@king "king"连接字符串
--然后插入数据
COPY FROM test/oracle@king INSERT test.TEST1 (SERIES_NO,TABLE_CODE,TABLE_NAME,MANAGER_CLASS,REMARK)USING SELECT * FROM comm.FEE_TABLE_DICT@tsj--取数源 tsj 是dblink
3.TEST.TEST1表在所指定的表空间同步数据之后,
后续alter table...rename to...,然后drop源表空间上的老表(表空间resize之后,就做个逆向操作)。
dbtiger
good luck!
2018,3,30 于海南三亚
![](http://img.blog.itpub.net/blog/attachment/201803/30/29267792_15223938007V7z.jpg?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29267792/viewspace-2152431/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29267792/viewspace-2152431/