随笔- 11 文章- 0 评论- 1 阅读- 3196

【数据库】Oracle数据库创建、用户、表空间、导入、导出

一、创建用户、表空间及赋权限

进入cmd 输入 sqlplus / as sysdba 注意：本命令需要在oracle安装主机执行。如果有改数据库的dba权限用户也可以登录后执行下面操作

/*分为3步 */

/*第1步：创建表空间注意表空间在服务器上的存储位置可通过以下sql查询 SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; */

create tablespace cms_DATA

logging

datafile 'D:\app\oradata\orcl\CMS_DATA.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

/*第2步：创建用户并指定表空间临时表空间用系统自带的temp即可*/

create user cms_jyz identified by cmscdtye2013

default tablespace cms_data

temporary tablespace temp;

/*第3步：给用户授予权限 */

grant connect,resource,dba to cms_jyz;

二、导出dmp、导入dmp数据脚本

注意导入前需要考虑，空表不能导出的问题：解决步骤

1、执行语句 Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null; 得到如下语句：

alter table C4_DEFECT allocate extent;
alter table C4_DEFECTANALYSISTASK allocate extent;
alter table C4_DEFECTANALYSISTASKDETAIL allocate extent;
alter table C3_TEMPERATURE allocate extent;
alter table C4_CAMERACOORDINATE allocate extent;
alter table C4_CAMERA_CONFIG allocate extent;
alter table C4_CQIDATA allocate extent;
alter table B_AICONTENT allocate extent;
alter table C4_DETECTPICTURE allocate extent;

2、将上诉语句在执行一次；

第一步导出：

exp cms_jyz/cmscdtye2013@127.0.0.1:1521/orcl file=e:\dmp\cms_sh.dmp

注：如果需要导出空表需要执行：select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 将结果在执行一次即可导出空表

导出时候排除部分表语句：

exp tmis_gt_test/tmis_gt_test@orcl file=E:\oracleexp\tmis_gt_test20230115.dmp EXCLUDE=TABLE:"IN('BDS_SUBSTATION','JC_SECLINE','JC_TUNNEL','JCW_DEVICECLASS','JCW_DEVICECLASS_CITEM','JCW_PROJECT_SECLINE','SECTION_WEEKLY_REPORT','SECTION_WEEKLY_REPORT_ANA_DETAIL','SECTION_WEEKLY_REPORT_ANA_TRIP','SECTION_WEEKLY_REPORT_ANALYSIS','SYS_CONFIG','SYS_DEPT','SYS_DEPT_JOIN_TYPE','SYS_DEPT_TYPE')"

第二步导入：

imp cms_jyz/cmscdtye2013@127.0.0.1:1521/orcl file=D:\Temp\111\cms_jyz.dmp full=y ignore=y

如果是高版本如19c的dmp数据，需要导入到11g数据库中。需要使用如下工具修改导出的dmp文件的版本号。工具下载地址（共享目录）：\\172.26.1.41\智谷耘行共享文件夹\03-常用软件包\开发相关\修改Oracle导出DMP的版本号。

操作步骤：

1、选择19c导出的dmp文件。2、将19.00.00修改成11.00.00，点击修改版本即可。

三、导出expdp、导入impdp另外一种方式

expdb和impdp相对于exp和imp来说速度更快，建议优先选择这种方式实现导入和导出

第一步导出：

1、在数据库服务器上查询导入的数据目录：

创建：create directory dpdir as 'E:\oracleexp';

查看：select * from dba_directories;

2、将导出的

expdp tmis_gt_test/tmis_gt_test@orcl owner=tmis_gt_test file=E:\oracleexp\tmis_gt_test20230115-1.dmp directory=dpdir EXCLUDE=TABLE:\"IN('BDS_SUBSTATION','JC_SECLINE','JC_TUNNEL','JCW_DEVICECLASS','JCW_DEVICECLASS_CITEM','JCW_PROJECT_SECLINE','SECTION_WEEKLY_REPORT','SECTION_WEEKLY_REPORT_ANA_DETAIL','SECTION_WEEKLY_REPORT_ANA_TRIP','SECTION_WEEKLY_REPORT_ANALYSIS','SYS_CONFIG','SYS_DEPT','SYS_DEPT_JOIN_TYPE','SYS_DEPT_TYPE')\"

第二步导入：

第一步：在数据库服务器上查询导入的数据目录：select * from dba_directories;

第二步：将导出的文件放在指定的目录

第三步：执行导入

impdp tmis_jt_dev/tmis_jt_dev@tmis directory=dpdatadir dumpfile=TMIS_GT_TEST20230115-1.DMP logfile=tmis_jt_dev.log REMAP_SCHEMA=tmis_gt_test:tmis_jt_dev version=11.2.0.0.0