oracle对象的表空间迁移

dwl2bp4vzk

　　IMP数据的时候，由于表空间的导入导出表空间的不同，导入数据到了system表空间，因此需要move到user表空间，具体过程如下
　　1，确定需要move的对象
select OWNER,SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_TYPE from dba_segments where OWNER='USER' order by SEGMENT_TYPE desc;
有table、index和lob segment

2，move table

　　-- for table
alter table USER.APPLICANT MOVE tablespace USER;

sqlplus -S "/ as sysdba" << EOF
@/home/oracle/dba/move.sql
exit;
EOF
　　3，move index
--for index
ALTER INDEX USER.PK_MAIL_CONFIRM REBUILD TABLESPACE USER;
...
4,move lob
--fro lob
select a.owner,a.table_name,a.column_name,b.segment_name,b.tablespace_name,index_name,b.bytes
from dba_lobs a,dba_segments b
where a.segment_name=b.segment_name
and a.SEGMENT_NAME = b.segment_name
and b.owner='USER';

alter table USER.RESUME move lob(INTRO) store as( tablespace USER);
。。。

　　5,-编译无效对象
set heading off
set pagesize 1000
set linesize 200
set verify off
set echo off
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by
OBJECT_TYPE,
OBJECT_NAME;

alter PROCEDURE USER.SP_TEMP_RESCORE_BATCH_RESUME compile;