以下操作在测试服务器进行:标识黄色为替换点
1、备份正式数据库:
# su - oracle
$ expdp system/passwd schemas=jingyadirectory=EXPDP_DUMP dumpfile=jingya20130918.dmp logfile=expdp20130918.log parallel=4Cluster=n exclude=table:\" like \'VT%\'\",STATISTICS
注:备份文件名及日志文件名需要变动
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_STANDARDDATAFILE
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD01.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD02.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD03.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD04.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD05.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD06.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD07.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD08.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD09.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD10.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD11.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD12.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD13.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD14.ora'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
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK OFF;
6、创建临时表空间之一
CREATE smallfile TABLESPACE EAS_D_jingya7_TEMP2DATAFILE
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP201.dbf' SIZE5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP202.dbf' SIZE5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP203.dbf' SIZE5G 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 TABLESPACEEAS_T_jingya7_STANDARD TEMPFILE
'/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD01.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD02.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
'/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD03.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
8、创建用户
CREATE USER jingya7
IDENTIFIED BY VALUES 'passwd'
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;
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:jingya7REMAP_TABLESPACE=EAS_D_JINGYA_STANDARD:EAS_D_jingya7_STANDARD,EAS_D_JINGYA_TEMP2:EAS_D_jingya7_TEMP2,EAS_T_JINGYA_STANDARD:EAS_T_jingya7_STANDARDdirectory=EXPDP_DUMP dumpfile=jingya20131213.dmp logfile=impdp20131218.log parallel=4
10.删除数据库的表空间和用户
DROPTABLESPACE EAS_D_JINGYA2_STANDARDINCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROPTABLESPACE EAS_D_JINGYA2_TEMP2INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROPTABLESPACE EAS_T_JINGYA2_STANDARDINCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROPUSER JINGYA2CASCADE;