# 创建新表空间
CREATE TABLESPACE TEST DATAFILE
'D:\ORADATA\PRONES2\TEST-01.DBF' SIZE 10240M AUTOEXTEND ON NEXT 1M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

# 创建用户与权限
create user TEST identified by TEST default tablespace TEST;
grant dba to TEST;

# 通过IMP导入相关数据
IMP (省略)

# 迁移数据到新表空间
sqlplus TEST/TEST
spool C:\TEST_TABLE.sql
select 'alter table '||tname||' move tablespace TEST;' from tab;
spool off;
@C:\TEST_TABLE.sql

# 迁移索引到新表空间
sqlplus TEST/TEST
spool C:\TEST_TABLE.sql
select 'alter index '||index_name||' rebuild tablespace TEST;' from user_indexes;
spool off;
@C:\TEST_INDEX.sql

# 限制用户所属的表空间
revoke unlimited tablespace from TEST;
alter user TEST quota 0 on SYSTEM;
alter user TEST quota 0 on USERS;
alter user TEST quota unlimited on TEST;

ITPub博客

迁移带LOB等大字段数据到非默认表空间

最新文章