ORACLE 导入笔记，自己MARK

来源：互联网发布：幼儿园学英语单词软件编辑：程序博客网时间：2024/06/05 23:01

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

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

创建临时表空间

CREATE TEMPORARY TABLESPACE XL_TEMPTEMPFILE 'D:\app\sql_db\XL_TEMP.dbf'--window系统的地址SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

创建表空间

CREATE TABLESPACE XL_DATALOGGINGDATAFILE 'D:\app\sql_db\XL_DATA.DBF'SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

创建用户赋予表空间管理

CREATE USER xluser IDENTIFIED BY passpassDEFAULT TABLESPACE XL_DATATEMPORARY 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

0 0