1.查看数据库表空间使用情况,确定建立多大的数据大小
SELECT A.tablespace_name AS 表空间,TOTAL AS 总计,ROUND(FREE,2) AS 空闲,ROUND(TOTAL-FREE,2) AS 使用
FROM (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 AS total
from dba_data_files
group by tablespace_name
order by tablespace_name) A,
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 AS free
from dba_free_space
group by tablespace_name
order by tablespace_name) B
2.然后在新库创建表空间,举例:
create tablespace data_large logging datafile '/database/../data_large01.dbf' size 20G autoextend on;
alter tablespace data_large add datafile '/database/../data_large02.dbf' size 20g autoextend off;
3.在新库数据还原
export ORACLE_SID=test
su - oracle
sqlplus ‘/as sysdba’
show parameter instance_name(确认是否在测试库上删除数据)
drop user user01 cascade如果是新库且没有数据用户产生,此步跳过
4.vi backup.sh
impdp system/passwd directory=dump dumpfile=20150515.dmp logfile=20150515.log schemas=user01,user02,user03,user04,user05 parallel=2 exclude=STATISTICS
nohup ./backup.sh &