Oracle数据库导入导出

Oracle 数据库导入导出

1.登录数据库

用户和密码登录

sqlplus username/password@database
-- 或者
sqlplus username/password

操作系统认证登录

sqlplus / as sysdba

2.创建表空间

查看表空间

SELECT tablespace_name, status FROM dba_tablespaces;

创建表空间

CREATE TABLESPACE 表空间名 DATAFILE '数据文件路径' SIZE 大小 AUTOEXTEND ON NEXT 每次增量大小 MAXSIZE UNLIMITED;

CREATE TABLESPACE DB_TEST DATAFILE 'D:\Environment\Oracle\app\Administrator\oradata\ORCL\DB_TEST.DBF' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

3.创建用户

创建用户

CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间 TEMPORARY TABLESPACE temp;
-- 并确保temp是数据库中已经存在的临时表空间。

给用户授权

GRANT CONNECT, RESOURCE TO 用户名;

修改用户默认表空间

ALTER USER 用户名 DEFAULT tablespace 表空间;

设置表空间配额

ALTER USER 用户名 quota unlimited ON 表空间;

4. 删除表空间

确认表空间状态：

SELECT tablespace_name, status FROM dba_tablespaces;

删除表空间

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

• 其中，INCLUDING CONTENTS AND DATAFILES选项表示在删除表空间时，连同其中的数据和物理文件一并删除。

验证删除

SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name = 'tablespace_name'

5.删除用户

DROP USER 用户名 CASCADE;

6.使用数据泵

注意：导入导出的目录可以为同一个，如 backup

6.1导出(expdp)

6.1.1创建导出目录

在文件系统中创建一个目录用于存放备份文件，如 oracle_exports

6.1.2连接到数据库

sqlplus / as sysdba

6.1.3创建逻辑目录

确保已经在数据库服务器上创建了逻辑目录，并且该目录对导出操作的用户具有读写权限。可以使用 SQL 命令查询逻辑目录：

SELECT * FROM dba_directories WHERE directory_name = 'YOUR_DIRECTORY_NAME';

如果没有就创建逻辑目录

CREATE OR REPLACE DIRECTORY export_dir AS 'E:\oracle_exports';
-- 授权（可选）
GRANT READ, WRITE ON DIRECTORY export_dir TO 用户名;

删除逻辑目录（可选）

DROP DIRECTORY export_dir;

6.1.4执行导出命令

导出整个数据库

expdp username/password@dblink schemas=schema_name directory=export_dir dumpfile=export.dmp logfile=export.log

-- 例子,密码特殊符号如@
expdp SYSTEM/"""zjm123456ZJM"""@orcl schemas=DB_TEST directory=export_dir dumpfile=DB_TEST.dmp logfile=export.log

expdp SYSTEM/zjm123456ZJM@orcl schemas=DB_TEST directory=export_dir dumpfile=DB_TEST.dmp logfile=export.log

导出特定表或表集合，可以调整 TABLES 参数

expdp username/password@dblink tables=table1,table2 directory=export_dir dumpfile=tables_export.dmp logfile=tables_export.log

-- 例子
expdp SYSTEM/"""zjm123456ZJM"""@orcl tables=ZJM_TEST.SYS_USER directory=export_dir dumpfile=20240814SYS_USER.dmp logfile=tables_export.log

参数说明：

• username/password：数据库用户名和密码。
• @dblink：可选，数据库链接名，如果导出和导入在同一数据库上，可以省略。
• schemas：要导出的模式名。
• tables：要导出的表名，可以指定多个表，用逗号分隔。
• directory：逻辑目录名，用于指定导出文件的存储位置。
• dumpfile：导出的数据文件名。
• logfile：导出日志文件名，用于记录导出过程中的信息。

6.2导入(impdp)

6.2.1创建导入目录

在文件系统中创建一个目录用于存放备份文件，如 oracle_imports

6.2.2连接到数据库

sqlplus / as sysdba

6.2.3创建逻辑目录

在 Oracle 数据库中，需要有一个逻辑目录指向文件系统中用于存放导入文件的实际目录。如果尚未创建，可以使用 DBA 权限登录数据库并创建逻辑目录。

SELECT * FROM dba_directories WHERE directory_name = 'YOUR_DIRECTORY_NAME';

如果没有就创建逻辑目录

CREATE OR REPLACE DIRECTORY import_dir AS 'E:\oracle_imports';
-- 授权（可选）
GRANT READ, WRITE ON DIRECTORY import_dir TO 用户名;

删除逻辑目录（可选）

DROP DIRECTORY import_dir;

6.2.4执行导入命令

导入整个数据库

impdp username/password@dblink schemas=schema_name directory=import_dir dumpfile=import.dmp logfile=import.log

-- 例子
impdp SYSTEM/"""zjm123456ZJM"""@orcl schemas=DB_TEST directory=import_dir dumpfile=import.dmp logfile=import.log

导入特定表或表集合，可以调整 TABLES 参数

impdp username/password@dblink tables=table1,table2 directory=import_dir dumpfile=tables_import.dmp logfile=tables_import.log

-- 例子
impdp SYSTEM/"""zjm123456ZJM"""@orcl tables=DB_TEST.SYS_USER directory=import_dir dumpfile=20240814SYS_USER.dmp logfile=tables_import.log

6.2.5重命名表名导入

impdp username/password@dblink DIRECTORY=import_dir DUMPFILE=your_dumpfile.dmp LOGFILE=import.log REMAP_TABLE=OLD_SCHEMA.OLD_TABLE:NEW_TABLE

impdp SYSTEM/"""zjm123456ZJM"""@orcl DIRECTORY=import_dir DUMPFILE=20240814SYS_USER.dmp LOGFILE=import.log REMAP_TABLE=DB_TEST.SYS_USER:SYS_USER1

参数说明：

• username/password：数据库用户名和密码。
• @dblink：可选，数据库链接名，如果导出和导入在同一数据库上，可以省略。
• schemas：要导出的模式名。
• tables：要导出的表名，可以指定多个表，用逗号分隔。
• directory：逻辑目录名，用于指定导出文件的存储位置。
• dumpfile：导出的数据文件名。
• logfile：导出日志文件名，用于记录导出过程中的信息。

7.使用传统实用程序（不推荐）

7.1导出(exp)

全库导出

exp system/password@orcl file=导出文件路径 full=y log=导出日志路径

-- 例子
exp SYSTEM/zjm123456ZJM@orcl file=E:\oracle_exports\full_database.dmp full=y log=E:\oracle_exports\full_export.log

按表导出

exp system/password@orcl file=导出文件路径 tables=(表名1,表名2) log=导出日志路径

-- 例子
exp SYSTEM/zjm123456ZJM@orcl file=E:\oracle_exports\tables.dmp tables=(DB_TEST.SYS_USER) log=E:\oracle_exports\tables_export.log

7.2导入(imp)

全库导入

imp system/password@orcl file=导入文件路径 full=y log=导入日志路径

-- 例子
imp SYSTEM/zjm123456ZJM@orcl file=E:\oracle_exports\full_database.dmp full=y log=E:\oracle_exports\import_full.log

按表导入

imp system/password@orcl file=导入文件路径 tables=(表名1,表名2) ignore=y log=导入日志路径

-- 例子
imp SYSTEM/zjm123456ZJM@orcl file=E:\oracle_exports\tables.dmp tables=(emp,dept) ignore=y log=E:\oracle_exports\import_tables.log