ORACLE 导入笔记，自己MARK

最近做了一个oracle的导入，碰到的小问题，自己MARK一下

先用dba权限的用户登录 PLSQL Developer 比如sys用户

创建临时表空间

CREATE TEMPORARY TABLESPACE XL_TEMP
TEMPFILE 'D:\app\sql_db\XL_TEMP.dbf'--window系统的地址
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

创建表空间

CREATE TABLESPACE XL_DATA
LOGGING
DATAFILE 'D:\app\sql_db\XL_DATA.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

创建用户赋予表空间管理

CREATE USER xluser IDENTIFIED BY passpass
DEFAULT TABLESPACE XL_DATA
TEMPORARY TABLESPACE XL_TEMP;

给用户赋予权限，权限不足可以慢慢加上去

GRANT 
　　CREATE SESSION, 
    CREATE ANY TABLE, 
    CREATE ANY VIEW ,
    CREATE ANY INDEX, 
    CREATE ANY PROCEDURE,
    CREATE ANY TRIGGER,
    CREATE ANY sequence,
    CREATE ANY TYPE,
　　ALTER ANY TABLE, 
    ALTER ANY PROCEDURE,
    ALTER ANY TRIGGER,
    ALTER ANY sequence,
    ALTER ANY TYPE,
　　DROP ANY TABLE, 
    DROP ANY VIEW, 
    DROP ANY INDEX, 
    DROP ANY PROCEDURE,
    DROP ANY TRIGGER,
    DROP ANY sequence, 
    DROP ANY TYPE,
　　SELECT ANY TABLE, 
    INSERT ANY TABLE, 
    UPDATE ANY TABLE, 
    DELETE ANY TABLE,
    unlimited tablespace
　　TO xluser; 

--打开 CMD 命令输入 而不是进入 sqlplus

imp xluser/passpass@orcl_127.0.0.1 file=E:/cjdexp20140722_001.dmp ignore=y full=y

备注：orcl_127.0.0.1 要看 tnsnames.ora 的命名而输入

ORCL_127.0.0.1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

导入失败可以尝试删除自己的用户和表空间文件

drop user xluser cascade --删除用户
drop tablespace XL_DATA including contents and datafiles;--删除非空表空间，包含物理文件
drop tablespace XL_TEMP including contents and datafiles;

删除的参考链接 http://blog.csdn.net/oscar999/article/details/7468153