oracle对象的表空间迁移

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;