Oracle exp/imp数据导出和导入
包含内容: 源码,全套工具
作者QQ549710689
下载Demo
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
启动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.创建表空间
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.创建用户
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.创建用户
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. 导入数据
imp gpj1/ajqnhwvia@127.0.0.1:1521/MYORACLE ignore=y full=y file=C:\Oracle(Export+Imp)\gpj.dmp
下面附加其它知识点
10.删除表空间
DROP TABLESPACE gpj_data INCLUDING CONTENTS AND DATAFILES;
11.删除用户和用户在表空间的数据
DROP USER gpj CASCADE;
删除用户前请确保此用户没有登录，如果有多个用户登录，请按照下面的方式结束session
select username,sid,serial# from v$session where username='gpj'; alter system kill session '1062,2394'; --这两个数字对应的值可变 alter system kill session '1082,1366'; alter system kill session '1074,7974'; alter system kill session '1077,5118';