批量move table\者重建索引

select 'alter table '||table_name||' move tablespace rpt;' from user_tables;

select 'alter index '||index_name||' rebuild tablespace RPT_IDX;' from user_indexes;

CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS

S_SQL VARCHAR2(500);

ACCOUNT NUMBER := 0;

BEGIN

FOR LINE2 IN (SELECT T.OWNER,

T.INDEX_NAME

FROM ALL_INDEXES T

WHERE T.OWNER = UPPER(USER_NAME)

AND T.TABLE_TYPE = 'TABLE'

AND T.TEMPORARY = 'N'

AND T.INDEX_TYPE = 'NORMAL') LOOP

S_SQL := 'alter index ' || LINE2.OWNER || '.' || LINE2.INDEX_NAME || ' rebuild';

ACCOUNT := ACCOUNT + 1;

EXECUTE IMMEDIATE S_SQL;

END LOOP;

DBMS_OUTPUT.PUT_LINE(ACCOUNT);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END BATCH_REBUILD_INDEX;

begin

BATCH_REBUILD_INDEX('SCOTT');

end;

多个表空间导入一个表空间：

expdp rpt/rpt dumpfile=struncuredata.dmp directory=DIR_DP logfile=structuredata.log schemas=rpt content=metadata_only

impdp rptmgr/rptmgr directory=DIR_DP dumpfile=struncuredata.dmp remap_schema=rpt:rptmgr remap_tablespace='(RPT:USERS,RPT_IDX:USERS,RPT_HISTORY_IDX:USERS,RPT_HISTORY:USERS)' logfile=struncuredata.log content=metadata_only