oracle数据库导入

–连接到Oracle

sqlplus / as sysdba

详解：以sqlplus / as sysdba方式登录时，以SYS用户登录采用的是操作系统验证的方式，所以用户名/密码输与不输入是一样的

– 查询表空间目录
select File_Name from dba_data_files;

– 删除表空间
Drop tablespace TS_ECP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace TS_HIS_TMS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace TS_TABLE_TMS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace TS_INDEX_TMS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

– 创建表空间
CREATE BIGFILE TABLESPACE “TS_TABLE_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_TABLE_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE “TS_INDEX_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_INDEX_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE “TS_ECP” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_ECP.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE “TS_HIS_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_HIS_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

详解：.表空间有一个数据文件*.dbf，大小为100MB；
允许表空间自动扩展(autoextends)，每次增长100MB(next 100M)，并且不限制最大大小；
说明表空间本地(local)管理，并自动分配范围(autoallocate)，用户不能指定范围的大小；
段空间(segment)的空间管理上使用bitmaps(auto)来管理数据块。使用AUTO会比使用MANUAL有更好的空间利用率，与效能上的提升。

– 创建角色
create user jats003 identified by jats003 default tablespace TS_TABLE_TMS temporary tablespace TEMP profile DEFAULT quota unlimited on TS_ECP quota unlimited on TS_HIS_TMS quota unlimited on TS_INDEX_TMS quota unlimited on TS_TABLE_TMS;

详解：quota unlimited on 对指定表空间不做限额， 用户名 identified by 密码

– 给角色赋权
grant connect to jats003;
grant dba to jats003;
grant resource to jats003;

详解：CONNECT角色是授予最终用户的典型权利，最基本的；RESOURCE角色是授予开发人员的 ；DBA角色是授予数据库维护人员的

–设置oracle临时目录
CREATE OR REPLACE DIRECTORY dump_dir AS ‘D:\app\dump_dir’;

详解：dmp文件放在dump_dir文件下

–导入
impdp jats003/jats003 directory=dump_dir schemas=jats003 dumpfile=JATS003_201911191419.DMP logfile=impats003.log version=10.2.0

详解： full=y ignore=y 那就是全部导入，把dmp里的所有数据插入到表里面；impdp、imp两个不同的数据泵，schemas用户

–导入时如果用户名不一致：
impdp jats001/jats001 directory=dump_dir REMAP_SCHEMA=jats002:jats001 dumpfile=JATS001_201807041429.dmp logfile=impats001.log version=10.2.0
REMAP_SCHEMA=导出时的用户名:现在要导入的用户名

– oracle代码编译命令：
mvn clean install -Dmaven.test.skip=true -P oracle
– mysql代码编译命令：
mvn clean install -Dmaven.test.skip=true -P mysql
详解：一般在idea的控制台使用

具体操作流程打开cmd:

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 22 09:57:48 2019

Copyright © 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select File_Name from dba_data_files;

FILE_NAME

D:\APP\HASEE\ORADATA\CHAN\USERS01.DBF
D:\APP\HASEE\ORADATA\CHAN\UNDOTBS01.DBF
D:\APP\HASEE\ORADATA\CHAN\SYSAUX01.DBF
D:\APP\HASEE\ORADATA\CHAN\SYSTEM01.DBF

SQL> CREATE BIGFILE TABLESPACE “TS_TABLE_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_TABLE_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间已创建。

SQL> CREATE BIGFILE TABLESPACE “TS_INDEX_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_INDEX_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间已创建。

SQL> CREATE BIGFILE TABLESPACE “TS_ECP” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_ECP.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间已创建。

SQL> CREATE BIGFILE TABLESPACE “TS_HIS_TMS” DATAFILE ‘D:\APP\HASEE\ORADATA\CHAN\TS_HIS_TMS.ora’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间已创建。

SQL> create user jats003 identified by jats003 default tablespace TS_TABLE_TMS temporary tablespace TEMP profile DEFAULT quota unlimited on TS_ECP quota unlimited on TS_HIS_TMS quota unlimited on TS_INDEX_TMS quota unlimited on TS_TABLE_TMS;

用户已创建。

SQL> grant connect to jats003;

授权成功。

SQL> grant dba to jats003;

授权成功。

SQL> grant resource to jats003;

授权成功。

SQL> CREATE OR REPLACE DIRECTORY dump_dir AS ‘D:\app\dump_dir’;

目录已创建。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Windows\system32>impdp jats003/jats003 directory=dump_dir schemas=jats003 dumpfile=JATS003_201911191419.DMP logfile=impats003.log version=10.2.0

Import: Release 11.2.0.1.0 - Production on 星期五 11月 22 10:04:28 2019

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 “JATS003”.“SYS_IMPORT_SCHEMA_01”
启动 “JATS003”.“SYS_IMPORT_SCHEMA_01”: jats003/******** directory=dump_dir schemas=jats003 dumpfile=JATS003_201911191419.DMP logfile=impats003.log version=10.2.0
处理对象类型 SCHEMA_EXPORT/USER
ORA-31684: 对象类型 USER:“JATS003” 已存在
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT…