ORACLE创建表空间、删除用户与建立用户及权限及导入导出脚本

一.创建表空间
CREATE SMALLFILE TABLESPACE XZSP DATAFILE 'F:\oracle\product\10.2.0\oradata\xaorcl\xzsp' SIZE 300M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 


CREATE SMALLFILE TABLESPACE XZJC DATAFILE 'F:\oracle\product\10.2.0\oradata\xaorcl\xzjc' 
SIZE 300M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT 
LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE MIDDLE DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\JXORCL\middle' 
SIZE 300M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT 
LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

二.删除用户
drop user xzsp cascade;
drop user xzjc cascade;
drop user middle cascade;
drop user wangzhan cascade;

drop tablespace xxx including contents and datafiles 

drop tablespace XZSP including contents and datafiles;
drop tablespace XZJC including contents and datafiles;

三.创建用户及分权

create user xzsp identified by xzsp 
default tablespace xzsp
temporary tablespace temp;
grant dba,connect to xzsp;

create user xzjc identified by xzjc 
default tablespace xzjc
temporary tablespace temp;
grant dba,connect to xzjc;

create user middle identified by middle
default tablespace middle 
temporary tablespace temp;
grant dba,connect to middle ;

create user sanyauser identified by sanyauser
default tablespace xzsp 
temporary tablespace temp;
grant dba,connect to sanyauser;

create user wangzhan identified by wangzhan
default tablespace wangzhan
temporary tablespace temp;
grant dba,connect to wangzhan;

四.导入数据库  在windows cmd 的环境中运行
imp system/xzsp@orcl file=D:\vingsoft\projects\xiancity\backup\oracle\xaorcl20110218.dmp log=D:\oraclelog\xaoracle.log fromuser=(xzsp) touser=(xzsp)
imp system/xzsp@orcl file=D:\vingsoft\projects\xiancity\backup\oracle\xaorcl20110218.dmp fromuser=xzsp touser=xzsp

imp system/xzsp@orcl file=D:\vingsoft\projects\xiancity\backup\oracle\xaorcl20110218.dmp fromuser=(xzsp,,) touser=(xzsp2,,)

imp system/xzsp@syorcl file=D:\sanya\middle_20120214-150001.dmp fromuser=(middle) touser=(middle)

imp system/system@syorcl file=E:\1\2010-03-07.dmp  fromuser=xzsp touser=xzsp

imp log=plsimp.log file=E:\dbuserforfs.DMP 
userid=fsuser/fsuser@orcle_11 fromuser=fsuser touser=fsuser buffer=30720 commit=no grants=yes ignore=yes indexes=yes rows=yes show=no constraints=yes

exp system/oracle@JIAN owner=xzsp,xzjc,middle file=f:/1/jian20010518.dmp log=f:/1/jian20010518.log

exp system/oracle@jxorcl owner=xzsp,xzjc,middle file=f:/1/jxorcl20010518.dmp log=f:/1/jxorcl20010518.log

exp system/oracle@xaorcl owner=xzsp,xzjc file=f:/1/xaorcl.dmp log=f:/1/xaorcl.log