Oracle数据库中如何传输表空间和数据泵
2023-09-14 09:13:28 时间
导出表空间
exp system/system@PDB1 tablespaces=data01 file=/home/oracle/expbk/exp_data01.dmp buffer=1000000 log=/home/oracle/expbk/exp_data01.log
[oracle@oracle-db-19c ~]$ exp system/system@PDB1 tablespaces=data01 file=/home/oracle/expbk/exp_data01.dmp buffer=1000000 log=/home/oracle/expbk/exp_data01.log
Export: Release 19.0.0.0.0 - Production on Wed Dec 7 09:38:57 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace DATA01 ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
[oracle@oracle-db-19c ~]$ 删除表空间和创建表空间
SQL>
SQL> select owner,table_name from dba_tables where tablespace_name ='DATA01';
no rows selected
SQL> drop tablespace data01 including contents and datafiles;
Tablespace dropped.
SQL> create tablespace data01 nologging datafile '/u02/oradata/CDB1/pdb1/data01.dbf' size 50M autoextend on next 50M maxsize 500M extent management local;
Tablespace created.
SQL>
导入表空间：导入时原来的表空间若不存在，则表会被导入到用户的默认永久表空间
imp system/system@PDB1 full=y file=/home/oracle/expbk/exp_data01.dmp buffer=10000000 log=/home/oracle/expbk/imp_data01.log
传输表空间模式：数据库版本必须一致，字符集必须一致
create tablespace teach10 datafile '/u02/oradata/CDB1/pdb1/teach10.dbf' size 10m;
create table blake.crm (x int) tablespace teach10;
insert into blake.crm values （1990）；
commit;
表空间需要在只读模式下：
alter tablespace teach10 read only;数据泵： oracle 10g之后的版本可用，只有在服务端可用
1. 创建逻辑目录，使用逻辑目录保存数据泵的备份文件
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL> conn system/system@PDB1;
Connected.
SQL> select * from all_directories;
create or replace directory expbk as '/home/oracle/expbk';
2.授予scott用户读写逻辑目录的权限
grant read,write on directory expbk to scott;3.使用数据泵备份数据
expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_metadata.dmp content=metadata_only logfile=ob1_metadata.log
expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_data.dmp
content=data_only logfile=ob1_data.log
expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_all.dmp content=all logfile=ob1_all.log
impdp scott/tiger@PDB1 directory=expbk dumpfile=ob1.dmp logfile=imp_ob1.log
total 0
[oracle@oracle-db-19c expbk]$ pwd
/home/oracle/expbk
[oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1.dmp content=metadata_only logfile=ob1.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:06:10 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_OB1": scott/********@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1.dmp content=metadata_only logfile=ob1.log
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SCOTT"."EXP_OB1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_OB1 is:
/home/oracle/expbk/ob1.dmp
Job "SCOTT"."EXP_OB1" successfully completed at Sun Jan 1 14:06:35 2023 elapsed 0 00:00:20
[oracle@oracle-db-19c expbk]$ [oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_metadata.dmp content=metadata_only logfile=ob1_metadata.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:10:59 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_OB1": scott/********@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_metadata.dmp content=metadata_only logfile=ob1_metadata.log
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SCOTT"."EXP_OB1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_OB1 is:
/home/oracle/expbk/ob1_metadata.dmp
Job "SCOTT"."EXP_OB1" successfully completed at Sun Jan 1 14:11:15 2023 elapsed 0 00:00:14
[oracle@oracle-db-19c expbk]$ ls -ltr
total 400
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
[oracle@oracle-db-19c expbk]$ [oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_data.dmp content=data_only logfile=ob1_data.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:14:09 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_OB1": scott/********@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_data.dmp content=data_only logfile=ob1_data.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "SCOTT"."OB1" 9.650 MB 73271 rows
Master table "SCOTT"."EXP_OB1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_OB1 is:
/home/oracle/expbk/ob1_data.dmp
Job "SCOTT"."EXP_OB1" successfully completed at Sun Jan 1 14:14:16 2023 elapsed 0 00:00:06
[oracle@oracle-db-19c expbk]$ ls -ltr
total 10428
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
[oracle@oracle-db-19c expbk]$ [oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_all.dmp content=all logfile=ob1_all.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:17:10 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_OB1": scott/********@PDB1 job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_all.dmp content=all logfile=ob1_all.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."OB1" 9.650 MB 73271 rows
Master table "SCOTT"."EXP_OB1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_OB1 is:
/home/oracle/expbk/ob1_all.dmp
Job "SCOTT"."EXP_OB1" successfully completed at Sun Jan 1 14:17:25 2023 elapsed 0 00:00:14
[oracle@oracle-db-19c expbk]$ ls -ltr
total 20512
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
[oracle@oracle-db-19c expbk]$ [oracle@oracle-db-19c expbk]$ impdp scott/tiger@PDB1 directory=expbk dumpfile=ob1.dmp logfile=imp_ob1.log
Import: Release 19.0.0.0.0 - Production on Sun Jan 1 14:18:52 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@PDB1 directory=expbk dumpfile=ob1.dmp logfile=imp_ob1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."OB1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sun Jan 1 14:18:57 2023 elapsed 0 00:00:04
[oracle@oracle-db-19c expbk]$ ls -ltr
total 20516
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
-rw-r--r--. 1 oracle oinstall 850 Jan 1 14:18 imp_ob1.log
[oracle@oracle-db-19c expbk]$ 导用户：
[oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 directory=expbk dumpfile=scott.dmp parallel=8 job_name=scott_job schemas=scott content=all logfile=scott.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:22:28 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."SCOTT_JOB": scott/********@PDB1 directory=expbk dumpfile=scott.dmp parallel=8 job_name=scott_job schemas=scott content=all logfile=scott.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."OB1" 9.650 MB 73271 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "SCOTT"."EMP_AGGR_MV" 5.992 KB 3 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."T01" 0 KB 0 rows
. . exported "SCOTT"."TAB1" 0 KB 0 rows
. . exported "SCOTT"."TAB2" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Master table "SCOTT"."SCOTT_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SCOTT_JOB is:
/home/oracle/expbk/scott.dmp
Job "SCOTT"."SCOTT_JOB" successfully completed at Sun Jan 1 14:23:18 2023 elapsed 0 00:00:49
[oracle@oracle-db-19c expbk]$ ls -ltr
total 30928
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
-rw-r--r--. 1 oracle oinstall 850 Jan 1 14:18 imp_ob1.log
-rw-r--r--. 1 oracle oinstall 2265 Jan 1 14:23 scott.log
-rw-r-----. 1 oracle oinstall 10657792 Jan 1 14:23 scott.dmp
[oracle@oracle-db-19c expbk]$ 导表空间：
[oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 directory=expbk dumpfile=users.dmp parallel=8 job_name=users_job tablespaces=users content=all logfile=users.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 14:50:16 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."USERS_JOB": scott/********@PDB1 directory=expbk dumpfile=users.dmp parallel=8 job_name=users_job tablespaces=users content=all logfile=users.log
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."OB1" 9.650 MB 73271 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "SCOTT"."EMP_AGGR_MV" 5.992 KB 3 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."TAB1" 0 KB 0 rows
. . exported "SCOTT"."TAB2" 0 KB 0 rows
Master table "SCOTT"."USERS_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.USERS_JOB is:
/home/oracle/expbk/users.dmp
Job "SCOTT"."USERS_JOB" successfully completed at Sun Jan 1 14:50:48 2023 elapsed 0 00:00:30
[oracle@oracle-db-19c expbk]$ ls -ltr
total 41180
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
-rw-r--r--. 1 oracle oinstall 850 Jan 1 14:18 imp_ob1.log
-rw-r--r--. 1 oracle oinstall 2265 Jan 1 14:23 scott.log
-rw-r-----. 1 oracle oinstall 10657792 Jan 1 14:23 scott.dmp
-rw-r--r--. 1 oracle oinstall 2067 Jan 1 14:50 users.log
-rw-r-----. 1 oracle oinstall 10493952 Jan 1 14:50 users.dmp
[oracle@oracle-db-19c expbk]$ 支持模糊匹配，备份
[oracle@oracle-db-19c expbk]$
[oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_e directory=expbk include=table:\" like \'E%\'\" dumpfile=e.dmp content=all logfile=e.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 15:11:55 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_E": scott/********@PDB1 job_name=exp_e directory=expbk include=table:" like 'E%'" dumpfile=e.dmp content=all logfile=e.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."E01" 8.773 KB 14 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."EMP_AGGR_MV" 5.992 KB 3 rows
Master table "SCOTT"."EXP_E" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_E is:
/home/oracle/expbk/e.dmp
Job "SCOTT"."EXP_E" successfully completed at Sun Jan 1 15:12:22 2023 elapsed 0 00:00:25
[oracle@oracle-db-19c expbk]$ ls -ltr
total 41572
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
-rw-r--r--. 1 oracle oinstall 850 Jan 1 14:18 imp_ob1.log
-rw-r--r--. 1 oracle oinstall 2265 Jan 1 14:23 scott.log
-rw-r-----. 1 oracle oinstall 10657792 Jan 1 14:23 scott.dmp
-rw-r--r--. 1 oracle oinstall 2067 Jan 1 14:50 users.log
-rw-r-----. 1 oracle oinstall 10493952 Jan 1 14:50 users.dmp
-rw-r-----. 1 oracle oinstall 397312 Jan 1 15:12 e.dmp
-rw-r--r--. 1 oracle oinstall 1588 Jan 1 15:12 e.log
[oracle@oracle-db-19c expbk]$
排除以E打头的表，对其它表进行备份
[oracle@oracle-db-19c expbk]$ expdp scott/tiger@PDB1 job_name=exp_e directory=expbk exclude=table:\" like \'E%\'\" dumpfile=scott_not_e.dmp content=all logfile=scott_not_e.log
Export: Release 19.0.0.0.0 - Production on Sun Jan 1 15:15:38 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."EXP_E": scott/********@PDB1 job_name=exp_e directory=expbk exclude=table:" like 'E%'" dumpfile=scott_not_e.dmp content=all logfile=scott_not_e.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "SCOTT"."OB1" 9.650 MB 73271 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."T01" 0 KB 0 rows
. . exported "SCOTT"."TAB1" 0 KB 0 rows
. . exported "SCOTT"."TAB2" 0 KB 0 rows
Master table "SCOTT"."EXP_E" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.EXP_E is:
/home/oracle/expbk/scott_not_e.dmp
Job "SCOTT"."EXP_E" successfully completed at Sun Jan 1 15:16:37 2023 elapsed 0 00:00:57
[oracle@oracle-db-19c expbk]$ ls -ltr
total 51912
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:06 ob1.dmp
-rw-r--r--. 1 oracle oinstall 882 Jan 1 14:06 ob1.log
-rw-r-----. 1 oracle oinstall 200704 Jan 1 14:11 ob1_metadata.dmp
-rw-r--r--. 1 oracle oinstall 909 Jan 1 14:11 ob1_metadata.log
-rw-r-----. 1 oracle oinstall 10264576 Jan 1 14:14 ob1_data.dmp
-rw-r--r--. 1 oracle oinstall 847 Jan 1 14:14 ob1_data.log
-rw-r-----. 1 oracle oinstall 10321920 Jan 1 14:17 ob1_all.dmp
-rw-r--r--. 1 oracle oinstall 1016 Jan 1 14:17 ob1_all.log
-rw-r--r--. 1 oracle oinstall 850 Jan 1 14:18 imp_ob1.log
-rw-r--r--. 1 oracle oinstall 2265 Jan 1 14:23 scott.log
-rw-r-----. 1 oracle oinstall 10657792 Jan 1 14:23 scott.dmp
-rw-r--r--. 1 oracle oinstall 2067 Jan 1 14:50 users.log
-rw-r-----. 1 oracle oinstall 10493952 Jan 1 14:50 users.dmp
-rw-r-----. 1 oracle oinstall 397312 Jan 1 15:12 e.dmp
-rw-r--r--. 1 oracle oinstall 1588 Jan 1 15:12 e.log
-rw-r-----. 1 oracle oinstall 10584064 Jan 1 15:16 scott_not_e.dmp
-rw-r--r--. 1 oracle oinstall 1835 Jan 1 15:16 scott_not_e.log
[oracle@oracle-db-19c expbk]$
相关文章
- Oracle数据库使用出现错误-状态: 失败 ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
- 【oracle 】如何估算即将创建的索引大小
- Oracle转化成为百分比
- 2013年7月14日-Java连接Oracle数据库
- jdbc连接rac的oracle数据库
- 从远程Oracle服务器上同步复制数据到本地备份库
- 使用E-MapReduce集群sqoop组件同步云外Oracle数据库数据到集群hive
- oracle参数文件
- 【学亮IT手记】oracle集合运算
- oracle 判断列是否在数据库中存在
- Oracle 数据库（表）的逻辑备份与恢复
- ORACLE 官方文档
- Oracle下的ArcSDE创建的空间数据库的备份与恢复
- ORACLE中的Net Configuration Assistant 点击后无反应, sqlplus登录数据库提示Oracle11g ORA-12560: TNS: 协议适配器错误
- Oracle数据库 External component has thrown an exception
- 未能加载文件或程序集“Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"
- 完美完全卸载Oracle 11g数据库
- 常用的Oracle数据库语句 (待更新完毕)
- Oracle中添加虚拟列（emp是oracle自带表）
- 安装Oracle时可能碰到的常见问题-1
- Oracle DB 体系机构
- Oracle的学习心得和知识总结（二十）|Oracle数据库Real Application Testing之DBMS_SQLTUNE包技术详解
- CentOS 7 下 静默方式安装 Oracle 11g