oracle创建用户 创建表空间 导出表和导入表

今天整理了一下oracle怎么来创建表空间、创建用户、以及怎么把表导出成dmp文件，然后再把dmp文件导入到数据库里边。

【1,首先创建表空间】
start D:\creatOracle\createTablespaces.sql   D:\oracleTablespace2014(表空间路径【提前要新建这个文件夹】)

createTablespaces.sql内容如下:

/*-------------------------------------------------------------------------------
  Summary:  创建PI2005所需的表空间。
  Parameters: 
    1 - PI2005数据库文件的存放位置，如 'C:\ORACLE\ORADATA\MW'。
    (注意：此路径是指数据库服务器上的绝对路径。)
  Prerequests:
    数据库已经创建。
  Usage:
    在SQLPLUS中以数据库管理员账户执行。
  Examples:
    start D:\MWOra9i2_CreateTablespaces C:\ORACLE\ORADATA\MW10
-------------------------------------------------------------------------------*/


CREATE TABLESPACE "MWS_SYS" 
    LOGGING 
    DATAFILE '&1\MWS_SYS.ora' SIZE 750M 
    AUTOEXTEND 
    ON NEXT  100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT  AUTO ;



//删除表空间
drop tablespace bigoa_temp including contents and datafiles cascade constraints;
//including contents 删除表空间的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉
//cascade constraints 同时删除tablespace中表的外键参照


【2,创建用户【
start D:\creatOracle\createUsers.sql;

createUsers.sql内容如下:

/*-------------------------------------------------------------------------------
  LYCH_SYS:用户名
ADMIN:密码
MWS_SYS:表空间名称
-------------------------------------------------------------------------------*/
create user LYCH_SYS identified by ADMIN default tablespace "MWS_SYS";
grant DBA to LYCH_SYS with admin option;

//修改用户口令
alter user user_name identified by password;


【3,为用户创建权限【
start D:\creatOracle\grantPriviliges.sql  LYCH_SYS; （参数-用户名称）
 
grantPriviliges.sql内容如下:

/*------------------------------------------------------------------------------
  Summary:  此文件是对创建的用户授权
  Prerequests:
    The database user exists.
  Parameters:
    1  --   The database user that will be granted with.
  Examples:
    start MWOra9i2_GrantPriviliges.sql MW_SYS
------------------------------------------------------------------------------*/
grant ALTER ANY CLUSTER to &1 with admin option;
grant ALTER ANY DIMENSION to &1 with admin option;
grant ALTER ANY INDEX to &1 with admin option;
grant ALTER ANY INDEXTYPE to &1 with admin option;
grant ALTER ANY LIBRARY to &1 with admin option;
grant ALTER ANY OUTLINE to &1 with admin option;
grant ALTER ANY PROCEDURE to &1 with admin option;
grant ALTER ANY ROLE to &1 with admin option;
grant ALTER ANY SEQUENCE to &1 with admin option;
grant ALTER ANY SNAPSHOT to &1 with admin option;
grant ALTER ANY TABLE to &1 with admin option;
grant ALTER ANY TRIGGER to &1 with admin option;
grant ALTER ANY TYPE to &1 with admin option;
grant ALTER DATABASE to &1 with admin option;
grant ALTER PROFILE to &1 with admin option;
grant ALTER RESOURCE COST to &1 with admin option;
grant ALTER ROLLBACK SEGMENT to &1 with admin option;
grant ALTER SESSION to &1 with admin option;
grant ALTER SYSTEM to &1 with admin option;
grant ALTER TABLESPACE to &1 with admin option;
grant ALTER USER to &1 with admin option;
grant ANALYZE ANY to &1 with admin option;
grant AUDIT ANY to &1 with admin option;
grant AUDIT SYSTEM to &1 with admin option;
grant BACKUP ANY TABLE to &1 with admin option;
grant BECOME USER to &1 with admin option;
grant COMMENT ANY TABLE to &1 with admin option;
grant CREATE ANY CLUSTER to &1 with admin option;
grant CREATE ANY CONTEXT to &1 with admin option;
grant CREATE ANY DIMENSION to &1 with admin option;
grant CREATE ANY DIRECTORY to &1 with admin option;
grant CREATE ANY INDEX to &1 with admin option;
grant CREATE ANY INDEXTYPE to &1 with admin option;
grant CREATE ANY LIBRARY to &1 with admin option;
grant CREATE ANY OUTLINE to &1 with admin option;
grant CREATE ANY PROCEDURE to &1 with admin option;
grant CREATE ANY SEQUENCE to &1 with admin option;
grant CREATE ANY SNAPSHOT to &1 with admin option;
grant CREATE ANY SYNONYM to &1 with admin option;
grant CREATE ANY TABLE to &1 with admin option;
grant CREATE ANY TRIGGER to &1 with admin option;
grant CREATE ANY TYPE to &1 with admin option;
grant CREATE ANY VIEW to &1 with admin option;
grant CREATE CLUSTER to &1 with admin option;
grant CREATE DATABASE LINK to &1 with admin option;
grant CREATE DIMENSION to &1 with admin option;
grant CREATE INDEXTYPE to &1 with admin option;
grant CREATE LIBRARY to &1 with admin option;
grant CREATE PROCEDURE to &1 with admin option;
grant CREATE PROFILE to &1 with admin option;
grant CREATE PUBLIC DATABASE LINK to &1 with admin option;
grant CREATE PUBLIC SYNONYM to &1 with admin option;
grant CREATE ROLE to &1 with admin option;
grant CREATE ROLLBACK SEGMENT to &1 with admin option;
grant CREATE SEQUENCE to &1 with admin option;
grant CREATE SESSION to &1 with admin option;
grant CREATE SNAPSHOT to &1 with admin option;
grant CREATE SYNONYM to &1 with admin option;
grant CREATE TABLE to &1 with admin option;
grant CREATE TABLESPACE to &1 with admin option;
grant CREATE TRIGGER to &1 with admin option;
grant CREATE TYPE to &1 with admin option;
grant CREATE USER to &1 with admin option;
grant CREATE VIEW to &1 with admin option;
grant DEBUG ANY PROCEDURE to &1 with admin option;
grant DEBUG CONNECT SESSION to &1 with admin option;
grant DELETE ANY TABLE to &1 with admin option;
grant DROP ANY CLUSTER to &1 with admin option;
grant DROP ANY CONTEXT to &1 with admin option;
grant DROP ANY DIMENSION to &1 with admin option;
grant DROP ANY DIRECTORY to &1 with admin option;
grant DROP ANY INDEXTYPE to &1 with admin option;
grant DROP ANY LIBRARY to &1 with admin option;
grant DROP ANY OUTLINE to &1 with admin option;
grant DROP ANY PROCEDURE to &1 with admin option;
grant DROP ANY ROLE to &1 with admin option;
grant DROP ANY SEQUENCE to &1 with admin option;
grant DROP ANY SNAPSHOT to &1 with admin option;
grant DROP ANY SYNONYM to &1 with admin option;
grant DROP ANY TABLE to &1 with admin option;
grant DROP ANY TRIGGER to &1 with admin option;
grant DROP ANY TYPE to &1 with admin option;
grant DROP ANY VIEW to &1 with admin option;
grant DROP PROFILE to &1 with admin option;
grant DROP PUBLIC DATABASE LINK to &1 with admin option;
grant DROP PUBLIC SYNONYM to &1 with admin option;
grant DROP ROLLBACK SEGMENT to &1 with admin option;
grant DROP TABLESPACE to &1 with admin option;
grant DROP USER to &1 with admin option;
grant EXECUTE ANY INDEXTYPE to &1 with admin option;
grant EXECUTE ANY LIBRARY to &1 with admin option;
grant EXECUTE ANY PROCEDURE to &1 with admin option;
grant EXECUTE ANY TYPE to &1 with admin option;
grant FORCE ANY TRANSACTION to &1 with admin option;
grant FORCE TRANSACTION to &1 with admin option;
grant GLOBAL QUERY REWRITE to &1 with admin option;
grant grant ANY OBJECT PRIVILEGE to &1 with admin option;
grant grant ANY PRIVILEGE to &1 with admin option;
grant grant ANY ROLE to &1 with admin option;
grant INSERT ANY TABLE to &1 with admin option;
grant LOCK ANY TABLE to &1 with admin option;
grant MANAGE TABLESPACE to &1 with admin option;
grant ON COMMIT REFRESH to &1 with admin option;
grant QUERY REWRITE to &1 with admin option;
grant RESTRICTED SESSION to &1 with admin option;
grant SELECT ANY DICTIONARY to &1 with admin option;
grant SELECT ANY SEQUENCE to &1 with admin option;
grant SELECT ANY TABLE to &1 with admin option;
grant UNDER ANY TABLE to &1 with admin option;
grant UNDER ANY TYPE to &1 with admin option;
grant UNDER ANY VIEW to &1 with admin option;
grant UNLIMITED TABLESPACE to &1 with admin option;
grant UPDATE ANY TABLE to &1 with admin option;


【4，导入数据 直接在cmd命令下而不是sqlplus】
imp system/sys@spms full=y file=F:\20140821.dmp  log=F:\imp.log  ignore=y  （全部导入system/sys@spms 分别为用户名/密码@实体）
imp system/sys@spms full=y file=C:\187dataBackUp\2012-03-06.dmp log=C:\187dataBackUp\2012-03-06.log ignore=n 




【5，导出数据 直接在cmd命令下而不是sqlplus】
exp system/sys@spms owner=(mw_sys,mw_app,mw_rtm)  file=F:\20140821.dmp  log=F:\20140821.log  full=y  //不用加full=y，加上的意思是连同系统表一起导出 (system/sys@spms 分别为用户名/密码@配置的实体  owner表示用户)


exp mw_app/app@18SPMS file=C:\1.DMP log=C:\1.log tables=(MWT_PWCBM_SBZL,MWT_PWCBM_SBBJ,MWT_PWCBM_JXCL,MWT_PWCBM_ZTLMX,MWT_PWCBM_YJGZ,MWT_PWCBM_SBLX,MWT_PWCBM_LHCLGZ,MWT_PWCBM_BBK,MWT_PWCBM_ZTXXJKPZ,MWT_PWCBM_BJLX,MWT_PWCBM_ZTXXSJGLB,MWT_PWCBM_SBLXYPJDZGL,MWT_PWCBM_ZTLXXD,MWT_PWCBM_JXYZ,MWT_PWCBM_JXXMFL)




【4.1使用如下导入方式】，
首先在数据库中创建用户成功后，然后用system登陆PL/SQL，在Directories目录下新建DUMP_FILES，路径指向导入文件的目录，然后执行导入
impdp system/sys@sys DIRECTORY=DUMP_FILES schemas=(mw_sys,mw_app,mw_rtm) logfile=imp20090825.log dumpfile=20090825.dmp 
导出命令
expdp system/system@207 DIRECTORY=DUMP_FILES schemas=(mw_sys,mw_app,mw_rtm) logfile=expdp207.log dumpfile=expdp207.dmp