Oracle导出导入

一、创建用户并授权

1. 进去cmd登录sysdba账号

sqlplus/nolog

conn/ as sysdba

2. 创建临时表空间

create temporary tablespace test_temp tempfile 'D:\oradata\test\TEST_TEMP.dbf' size 100M autoextend on next 50M maxsize 20480M;

3. 创建表空间

create tablespace test datafile 'D:\oradata\test\TEST.dbf' size 1024M autoextend on next 50M maxsize unlimited EXTENT MANAGEMENT local autoallocate segment space management auto;

4. 创建用户授予表空间

create user test_user identified by test_pwd default tablespace test temporary tablespace test_temp profile default;

5. 给用户授予权限

(1) grant dba to test_user;//给用户名授予dba权限，一般情况不给dba权限

(2) grant create session to test_user;/授予⽤户创建session的权限，即登陆权限

(3) grant connect,resource to test_user;

(4) 执⾏上⾯的sql语句后⽤户包括的权限

a. CONNECT⾓⾊：

  --是授予最终⽤户的典型权利，最基本的

        ALTER SESSION --修改会话

        CREATE CLUSTER --建⽴聚簇

        CREATE DATABASE LINK --建⽴数据库链接

        CREATE SEQUENCE --建⽴序列

        CREATE SESSION --建⽴会话

        CREATE SYNONYM --建⽴同义词

        CREATE VIEW --建⽴视图

b. RESOURCE⾓⾊：

  --是授予开发⼈员的

        CREATE CLUSTER --建⽴聚簇

        CREATE PROCEDURE --建⽴过程

        CREATE SEQUENCE --建⽴序列

        CREATE TABLE --建表

        CREATE TRIGGER --建⽴触发器

        CREATE TYPE --建⽴类型

6. 校验用户名密码是否永不过期

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

如果结果显示 180 天 请执行一下语句

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

二、导出Oracle数据库dmp文件

1. 检查空表

导出之前先检查先处理没有数据的空表（Oracle 空表无法导出）

select table_name from user_tables where NUM_ROWS=0; --查询是否有空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null; --拼接处理空表语句

将查询出来拼接好的处理语句复制到新SQL窗口执行

2. 执行导出语句

以管理员运行cmd命令行，执行以下命令并等待执行完成

exp test_user/test_pwd@ORCL file=D:\dmp\test.dmp owner=test_user log=D:\dmp\ZWWHH20220528_DAOCHU.log direct=y

三、导入Oracle数据库dmp文件

1. 复制文件

将第二步中导出的文件复制到需要导入Oracle数据库的服务器上

2. 执行导入语句

以管理员运行cmd命令行，执行以下命令并等待执行完成

imp test_user/test_pwd@ORCL touser=test_user tablespaces=test file=D:\dmp\test.dmp full=y ignore=y

四、导入之后问题处理

1. 表空间修改

导入之后表空间需要修改成我们第一步创建的表空间

select * from user_tables; --查询当前所有表的表空间

--拼接修改表空间语句，查出来的数据中存在多个表空间的时候TABLESPACE_NAME条件用IN

SELECT 'alter table '||TABLE_NAME||' move tablespace test;' FROM USER_TABLES WHERE TABLESPACE_NAME = 'USERS';

将查询出来拼接好的修改语句复制到新SQL窗口执行

2. 索引处理

导入之后可能会导致索引失效需要重新处理索引

select index_name ,status from user_indexes where status = 'UNUSABLE'; --查询失效索引

select 'alter index ' || index_name || ' rebuild;' from user_indexes where Status = 'UNUSABLE'; --拼接重建索引语句

将查询出来拼接好的重建索引语句复制到新SQL窗口执行

3. 索引空间修改

导入之后索引表空间需要修改成我们第一步创建的表空间

SELECT * FROM DBA_INDEXES WHERE TABLE_OWNER = 'TEST_USER' AND INDEX_TYPE = 'NORMAL' ; --查询当前所有表的表空间

--拼接修改索引表空间语句，查出来的数据中存在多个表空间的时候TABLESPACE_NAME条件用IN

SELECT 'ALTER INDEX '|| INDEX_NAME || ' REBUILD TABLESPACE rmyy_index;' FROM DBA_INDEXES WHERE TABLE_OWNER = 'TEST_USER' AND INDEX_TYPE = 'NORMAL' AND TABLESPACE_NAME = 'USERS';

将查询出来拼接好的修改语句复制到新SQL窗口执行

4. 批量编译存储过程和函数

--拼接批量编译函数的语句

SELECT OBJECT_NAME, 'alter function ' || OBJECT_NAME || ' compile;' FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND OBJECT_TYPE ='FUNCTION' AND OWNER = UPPER('test_user');

将查询出来拼接好的语句复制到新SQL窗口执行

--拼接批量编译函数的语句

SELECT OBJECT_NAME, 'alter procedure ' || OBJECT_NAME || ' compile;' FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND OBJECT_TYPE ='PROCEDURE' AND OWNER = UPPER('test_user');

将查询出来拼接好的语句复制到新SQL窗口执行