oracle数据库创建用户导入导出详解

1.创建临时表空间
create temporary tablespace USERSTEMP 
tempfile 'D:\oracle11g\oradata\orcl\USERSTEMP.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

2.创建数据表空间
create tablespace EPSSITE
logging 
datafile 'F:\oracle\product\10.2.0\oradata\orcl\EPSSITE.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

3.创建用户并指定表空间
create user portal identified by portal
default tablespace USERS 
temporary tablespace USERSTEMP; 

4.给用户授予权限
grant connect,imp_full_database,resource,dba to portal; 

5.数据库导出
exp cms/cms@orcl file=d:/sss.dmp owner=portal
exp zxc/zxc@orcl file=d:/sss.dmp owner=eps_show
exp portal/portal@192.168.0.209/orcl file=d:/nong14.dmp
exp eps_show/eps_show@orcl file=/home/dbbackup/eps_show_0502.dmp


exp eps_show/eps_show@orcl file=/home/dbbackup/eps_show_0426.dmp

exp zxc/zxc@orcl file=/home/dbbackup/eps_entity_0422.dmp GRANTS=Y  owner=zxc tables=EPS_ENTITY

6.数据库导入
imp  portal/portal@orcl file=d:/sss.dmp fromuser=portal touser=portal
imp portal/portal@orcl file=d:/portal.dmp full=y

imp eps_show/eps_show@192.168.0.184:orcl file=d:/portal.dmp fromuser=portal touser=portal

imp eps_show/eps_show@orcl file=/home/dbbackup/portal.dmp   fromuser=portal touser=eps_show

imp  epstest/epstest@orcl file=/home/dbbackup/portal.dmp   fromuser=portal touser=epstest

imp  zxc/zxc@orcl file=/home/dbbackup/eps_show_20130419.dmp fromuser=eps_show touser=zxc

imp min_bank/min_bank@orcl file=d:/minsheng.dmp fromuser=min_bank touser=min_bank


imp  eps_show/eps_show@orcl file=/home/dbbackup/eps_entity_0422.dmp  fromuser=zxc touser=eps_show
7.解锁命令
su -oralce
sqlplus "/ as sysdba"
alter user portal account unlock;
ddd

sys
sys123
exp portal/portal@orcl file=/home/dbbackup/portal.dmp  owner=portal