oracle表空间创建与数据库导入导出

1、创建表空间（---以实例名为“zyzl”的实例为例，一般ORACLE11gR2默认实例名为“hszl”---）

CREATE SMALLFILE TABLESPACE NNC_DATA01 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA01.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE NNC_DATA02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE NNC_DATA03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_DATA03.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;



CREATE SMALLFILE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\Administrator\virtual\oradata\orcl\orclpdb\NNC_INDEX01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE NNC_INDEX02 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX02.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
 
CREATE SMALLFILE TABLESPACE NNC_INDEX03 DATAFILE '/u01/app/oracle/oradata/orcl/NNC_INDEX03.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


CREATE SMALLFILE TABLESPACE EPRK_LOB01 DATAFILE '/u01/app/oracle/oradata/orcl/EPRK_LOB01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


CREATE SMALLFILE TABLESPACE YLS_BLOB DATAFILE '/u01/app/oracle/oradata/orcl/YLS_BLOB.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
--------------------------------------------------------------------------------------------------

---以用户名为“hszl3”的用户为例---


2、创建用户hszl7,给用户授权（---以用户名为“hszl3”的用户为例---）

---创建用户---
create user cimc01 identified by cimc01 default tablespace NNC_DATA01 temporary tablespace temp;
grant connect,resource,dba to cimc01;
grant read,write on directory db to cimc01;

-----------------------------------------------------------------------
---删除用户---
select select sid,serial# from v$session where username=‘hxzl';
alter system kill session 'sid,serial'; ---删除进程---
-----------------------------------------------------------

3、创建数据文件目录并将目录授权与之前创建的用户hszl3（---以目录“D:\db_dir”为例---）

select db from dba_directories;

create directory db as 'D:\oracle';

grant read,write on directory db to zhongji_1205;
------------------------------------------------------------------------------------------------------
4、导入数据文件

impdp zhongji_1205/zhongji_1205@127.0.0.1/orclpdb directory=db dumpfile=uat_20171205.dmp remap_schema=cimc01:zhongji_1205
imp cimc01/cimc01@orclpdb file=D:\oracle\uat_20171205.dmp full=y;

-------------------------------------------------------------------------------------
5、创建strcat函数，执行《创建strcat函数.sql》,提交。

6、数据库备份：

expdp zjjz_01/zjjz_01@orcl directory=db dumpfile=zjjz_0120170628.dmp logfile=zjjz_0120170628.log job_name=zjjz_0120170628_job
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp