Oracle exp/imp数据导出和导入

2018-03-27 18:38·华瑞字节

Oracle是存储表和数据的仓库, 有时候我们需要将一个库中的对应用户下的所有表和数据等信息全部拷贝到另外的数据库中，可以使用exp将数据导出成dmp文件，使用配对命令imp将数据导入到另外的数据库中, 下面讲解所有导出导入数据的命令，并用视频讲解一个实例

demo下载地址：http://www.wisdomdd.cn/Wisdom/resource/articleDetail.htm?resourceId=1026

视频播放地址：www.wisdomdd.cn

Oracle服务器，客户端, PL/SQL 三个安装包对应的下载链接: 【下载】

Windows起停数据库

在命令窗口中输入: services.msc

启动: Listener和OracleService

Linux起停数据库


关闭Oracle
1. su - oracle
2. sqlplus / as sysdba 以DBA身份进入sqlplus
3. SHUTDOWN IMMEDIATE 关闭db 这里需要等待一段时间, 如果不行,可以强制关闭 shutdown abort
4. exit 退出oracle
1

启动Oracle
1. su - oracle 切换到oracle用户且切换到它的环境
2. lsnrctl status 查看监听及数据库状态
3. lsnrctl start 启动监听
4. sqlplus / as sysdba 以DBA身份进入sqlplus
5. startup 启动db数据库 这里需要等待一段时间

登录用户(dba): system/manager

创建用户gpj,授权相应权限,建表和数据，将数据导出dmp文件

1.创建表空间

1
2
create tablespace gpj_data logging datafile'F:\app\Administrator\oradata\orcl\gpj.dbf' size 10m
autoextend on next 3m maxsize 100m extent management local;

2.创建用户

1
create user gpj identified by ajqnhwvia default tablespace gpj_data temporary tablespace temp;

3.给用户授权

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO gpj;

GRANT RESOURCE TO gpj;
GRANT CONNECT TO gpj;
ALTER USER gpj DEFAULT ROLE ALL;
GRANT CREATE SEQUENCE TO gpj;
GRANT CREATE SESSION TO gpj;
GRANT UNLIMITED TABLESPACE TO gpj;
GRANT CREATE VIEW TO gpj;
GRANT CREATE TABLE TO gpj;
GRANT CREATE PROCEDURE TO gpj;
GRANT ALTER ANY TABLE TO gpj;
GRANT CREATE SYNONYM TO gpj;

4.创建数据表和数据

建表:


create table STUDENT
(
studentid NUMBER,
studentname VARCHAR2(30),
location VARCHAR2(30)
);
create unique index STUDENT_INDEX on STUDENT (STUDENTID);

数据:


insert into STUDENT (studentid, studentname, location) values (1, '刘强东', '宿迁沭阳');
insert into STUDENT (studentid, studentname, location) values (2, '葛筱雅', '沭阳县是我的家乡');

5.导出数据


exp gpj/ajqnhwvia@127.0.0.1:1521/MYORACLE file=C:\Oracle(Export+Imp)\gpj.dmp owner=gpj
参数: full=y --全表导出
参数: TABLES=(JSEBOTEST,NEWMAKT,TEST_ORG,TEST_SUBJECT,TEST_USER) --导出指定表
参数: TABLES=(JSEBOTEST,NEWMAKT,TEST_ORG,TEST_SUBJECT,TEST_USER) QUERY=\"WHERE rownum<11\"
--按可选条件导出表

创建用户gpj1,授权相应权限,将dmp数据导入

6.创建表空间, 创建用户, 给用户授权


create tablespace gpj1_data logging datafile'F:\app\Administrator\oradata\orcl\gpj1.dbf'
size 10m autoextend on next 3m maxsize 100m extent management local;

7.创建用户

1
create user gpj1 identified by ajqnhwvia default tablespace gpj1_data temporary tablespace temp;

8.授权


GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO gpj1;

GRANT RESOURCE TO gpj1;
GRANT CONNECT TO gpj1;
ALTER USER gpj1 DEFAULT ROLE ALL;
GRANT CREATE SEQUENCE TO gpj1;
GRANT CREATE SESSION TO gpj1;
GRANT UNLIMITED TABLESPACE TO gpj1;
GRANT CREATE VIEW TO gpj1;
GRANT CREATE TABLE TO gpj1;
GRANT CREATE PROCEDURE TO gpj1;
GRANT ALTER ANY TABLE TO gpj1;
GRANT CREATE SYNONYM TO gpj1;

9. 导入数据

1
imp gpj1/ajqnhwvia@127.0.0.1:1521/MYORACLE ignore=y full=y file=C:\Oracle(Export+Imp)\gpj.dmp

10.删除数据内容

1
DROP TABLESPACE gpj_data INCLUDING CONTENTS AND DATAFILES;

11.删除用户

1
DROP USER gpj CASCADE;
举报
评论 0
    热门：