1、备份正式数据库： # su - oracle
$ expdp system/oracle schemas=jingya directory=EXPDP_DUMP dumpfile=jingya20130918.dmp logfile=expdp20130918.log parallel=4 Cluster=n exclude=table:\\\注：备份文件名及日志文件名需要变动
以下操作在测试服务器进行：jingya7为替换点 2、创建文件夹：
mkdir /oracle/app/11g/oradata/jytest/jingya7
3、更改权限：
cd /oracle/app/11g/oradata/jytest
chown oracle:oinstall /oracle/app/11g/oradata/jytest/jingya7
4、进入sysdba #su – oracle
$sqlplus / as sysdba 5、创建表空间
CREATE smallfile TABLESPACE EAS_D_jingya7_STANDARD DATAFILE
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD01.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD02.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD03.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD04.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD05.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD06.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD07.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD08.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD09.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD10.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD11.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD12.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD13.ora' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD14.ora'
SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE 20G SIZE
20G
AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD15.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD16.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD17.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD18.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD19.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD20.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD21.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD22.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD23.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD24.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD25.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD26.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD27.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD28.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD29.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD30.ora' SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED LOGGING ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;
6、创建临时表空间之一
CREATE smallfile TABLESPACE EAS_D_jingya7_TEMP2 DATAFILE
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP201.dbf' SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP202.dbf' SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP203.dbf' SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED NOLOGGING ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;
7、创建临时表空间之二
CREATE smallfile TEMPORARY TABLESPACE EAS_T_jingya7_STANDARD TEMPFILE '/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD01.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD02.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD03.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M; 8、创建用户
CREATE USER jingya7
IDENTIFIED BY VALUES 'DCF8D58B8B578628' DEFAULT TABLESPACE EAS_D_jingya7_STANDARD TEMPORARY TABLESPACE EAS_T_jingya7_STANDARD PROFILE DEFAULT ACCOUNT UNLOCK;
-- 22 System Privileges for jingya7 GRANT CREATE VIEW TO jingya7;
GRANT DELETE ANY TABLE TO jingya7; GRANT ALTER ANY TABLE TO jingya7;
GRANT UNLIMITED TABLESPACE TO jingya7; GRANT ALTER ANY PROCEDURE TO jingya7; GRANT DROP ANY TABLE TO jingya7; GRANT INSERT ANY TABLE TO jingya7;
GRANT SELECT ANY DICTIONARY TO jingya7; GRANT CREATE ANY VIEW TO jingya7; GRANT CREATE TRIGGER TO jingya7;
GRANT DROP ANY PROCEDURE TO jingya7; GRANT UPDATE ANY TABLE TO jingya7; GRANT CREATE SESSION TO jingya7; GRANT CREATE SEQUENCE TO jingya7; GRANT CREATE ANY INDEX TO jingya7;
SIZE 2G SIZE 2G SIZE 2G GRANT CREATE PROCEDURE TO jingya7; GRANT CREATE TABLE TO jingya7;
GRANT CREATE ANY PROCEDURE TO jingya7; GRANT DROP ANY VIEW TO jingya7; GRANT DROP ANY INDEX TO jingya7; GRANT SELECT ANY TABLE TO jingya7; GRANT CREATE ANY TABLE TO jingya7; -- 1 Object Privilege for jingya7
GRANT READ, WRITE ON DIRECTORY SYS.DMPDIR TO jingya7;
9、导入备份数据： $impdp system/oracle REMAP_SCHEMA=jingya:jingya7 REMAP_TABLESPACE=EAS_D_JINGYA_STANDARD:EAS_D_jingya7_STANDARD,EAS_D_JINGYA_TEMP2:EAS_D_jingya7_TEMP2,EAS_T_JINGYA_STANDARD:EAS_T_jingya7_STANDARD
directory=EXPDP_DUMP dumpfile= jingya20131213.dmp logfile=impdp20131218.log parallel=4
10.删除数据库的表空间和用户
DROP TABLESPACE EAS_D_JINGYA2_STANDARD INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE EAS_D_JINGYA2_TEMP2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE EAS_T_JINGYA2_STANDARD INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP USER JINGYA2 CASCADE;