移动表空间内数据两种方式

最新推荐文章于 2022-11-27 16:31:23 发布

朝闻道-夕死可矣

最新推荐文章于 2022-11-27 16:31:23 发布

阅读量597

点赞数

分类专栏： -- Oracle BAK&REC

本文链接：https://blog.csdn.net/jc_benben/article/details/53580604

版权

-- Oracle BAK&REC 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

1,手动迁移对象
select 'alter table '||owner||'.'||segment_name||' move tablespace '||'TBS_NEW;'
from dba_segments 
where tablespace_name='TBS_OLD' and segment_type='TABLE' and owner='U1';

select 'alter index '||owner||'.'||segment_name||' rebuild tablespace '||'TBS_NEW;'
from dba_segments where tablespace_name='TBS_OLD' and segment_type='INDEX' and owner='U1';

select 'alter table '||t1.owner||'.'||t2.TABLE_NAME||' move lob ('||t2.COLUMN_NAME||') store as (tablespace '||'TBS_NEW);'
from dba_segments t1, dba_lobs t2
where t1.owner=t2.OWNER and t1.tablespace_name=t2.TABLESPACE_NAME and t1.segment_name=t2.SEGMENT_NAME
and t1.tablespace_name='TBS_OLD' and t1.segment_type='LOBSEGMENT' and t1.owner='U1';
2，整体迁移表空间
alter tablespace TBS_1 offline;
os mv '/ora01/oradata/tbs_1_data01.dbf' TO '/ora02/oradata/tbs_1_data01.dbf'
alter tablespace TBS_1 RENAME DATAFILE '/ora01/oradata/tbs_1_data01.dbf' TO '/ora02/oradata/tbs_1_data01.dbf';
alter tablespace TBS_1 online;