Many times, DBA operation and maintenance personnel will encounter some wonderful needs and strange problems. Recently, they have also encountered several wonderful data export needs. Here is a simple summary to prevent them from being overwhelmed when they encounter the same needs in the later stage.
1, A table that is part of a comparison rule, such as t_ Table beginning with price *
T_ The price table can be from dba_tables view or user_ like't 'in the tables view_ Price% 'found that this kind of expdp export is more convenient. In the following project, you need to use t under a user of oracle database_ There are about 90 tables at the beginning of price for backup. Under 11.2.0.4, rman can't do single table backup. More than 90 tables in CTAs are also a matter. They can only be exported through expdp. However, it is rarely necessary to export so many tables. They are usually exported according to schema or the whole database. Considering that the include parameter in expdp can be accompanied by query statements, the proposed idea of exporting a large number of specific tables is as follows:
1) Use the include parameter of the expdp export command with the select statement to query DBAs_ Tables table to obtain the table name to be exported; Of course, if you use ordinary user export, you need to query user_tables.
select TABLE_NAME from dba_tables where owner='T3_CCBSCF' and table_name like 'T_PRICE%';
2) Edit exptable Par file, start exporting
The specific process is as follows:
First look at the SCN
select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 3523577018
Then view the export directory
set linesize 9999 col OWNER for a10 col DIRECTORY_NAME for a30 col DIRECTORY_PATH for a60 select * from dba_directories; --If the directory is too small or does not exist,be create directory expdp_dir as '/u01/app/backup/expdp_dir'; grant read,write on directory expdp_dir to public;
Edit par file
more exptable.par dumpfile=T3_CC_93Tables_20210908_%U.dmp logfile=T3_CC_93Tables_20210908.log schemas=T3_CC directory=DUMP_DIR #exclude=statistics flashback_scn=3523577018 PARALLEL=4 COMPRESSION=all include=TABLE:"IN (select TABLE_NAME from dba_tables where owner='T3_CC' and table_name like 'T_PRICE%')" --be careful: exclude and include It cannot be used at the same time, otherwise an error will be reported --UDE-00011: parameter include is incompatible with parameter exclude . Until 21 c These two parameters in the new feature can be used at the same time,Data pump export parameters in best practices exclude=statistics Is an indispensable parameter.
expdp background export data
nohup expdp \'/ as sysdba\' parfile=exptable.par &
view log
more T3_CCBSCF_93Tables_20210908.log ;;; Export: Release 11.2.0.4.0 - Production on Wed Sep 8 17:29:51 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_SCHEMA_04": "/******** AS SYSDBA" parfile=exptable.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 338.1 MB . . exported "T3_CC"."T_PRICE_SHARE_DETAIL" 6.953 MB 63781 rows . . exported "T3_CC"."T_PRICE_SNAPSHOT_DETAIL" 12.58 MB 27957 rows . . exported "T3_CC"."T_PRICE_RETRYABLE_TASK" 9.148 MB 74225 rows . . exported "T3_CC"."T_PRICE_SHARE_PLAN" 4.293 MB 63811 rows . . exported "T3_CC"."T_PRICE_SNAPSHOT_MPS" 2.339 MB 36888 rows . . exported "T3_CC"."T_PRICE_FEE_PLAN_MPS" 2.798 MB 36888 rows Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE . . exported "T3_CC"."T_PRICE_BIZ_EXTENSION_ABS" 1.704 MB 36888 rows ............Omit the middle part . . exported "T3_CC"."T_PRICE_SHARE_TIME_POINT" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is: /oracle/dump_dir/T3_CC_93Tables_20210908_01.dmp /oracle/dump_dir/T3_CC_93Tables_20210908_02.dmp /oracle/dump_dir/T3_CC_93Tables_20210908_03.dmp /oracle/dump_dir/T3_CC_93Tables_20210908_04.dmp Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Wed Sep 8 17:32:23 2021 elapsed 0 00:02:31
Note: View SCN Refer to the following statements for the time of and when exporting some data in the table.
View the time corresponding to SCN
select to_char(scn_to_timestamp(3523577018), 'yyyy-mm-dd hh24:mi:ss') scndate from dual; SCNDATE ------------------- 2021-09-08 17:24:03
Export some data in the table (query):
expdp scott/tiger dumpfile=scott_20211230_%U.dmp directory=exp_dir tables =test query=test:'"where show_date <= 20211230"'
be careful: query There are single quotes and double quotes.
Export multiple tables:
expdp scott/tiger dumpfile=scott_20211230_T_%U.dmp directory=exp_dir tables=EMP,DEPT,BONUS,SALGRADE,TEST,T1,T2
2, Some irregular tables, such as EMP,DEPT,TEST,T1,T2, etc
If hundreds of tables listed above are irregular, you can only export some tables through the last example in the previous section. However, sometimes you may not know the business account password and cannot export using ordinary users, so you can only export using SYS users.
expdp scott/tiger dumpfile=scott_20211230_T_%U.dmp directory=exp_dir tables=EMP,DEPT,BONUS,SALGRADE,TEST,T1,T2
However, use SYS When exporting, you should pay attention to the need to add a user name before the table name, such as: tables=SCOTT.EMP,SCOTT.DEPT,...... Then list all the tables.
1. Export partial tables directly from the command line
expdp \'/ as sysdba\' dumpfile=T4_20211230.dmp directory=PUBLIC_DUMP exclude=statistics PARALLEL=4 COMPRESSION=all cluster=n logfile=T4_100tables.log tables = PROD_CC.T_VCHR_INVOICE_HISTORY, PROD_CC.T_SYS_XZQH, PROD_CC.T_SYS_USER_ROLES, PROD_CC.T_SYSTEM_FILE, PROD_CC.T_PRODUCT_RELATION, PROD_CC.T_PRODUCT_LOGISTICS, PROD_CC.T_PRODUCT_CORP, PROD_CC.T_PRODUCT_CORE_CREDIT, PROD_CC.T_PRODUCT_CC, PROD_CC.T_PRODUCT_CASH_FLOW, PROD_CC.T_PRICE_SNAPSHOT_DETAIL, PROD_CC.T_PRICE_SNAPSHOT, PROD_CC.T_PRICE_SHARE_PLAN, PROD_CC.T_PRICE_SHARE_DETAIL, PROD_CC.T_PRICE_FEE_SNAPSHOT, PROD_CC.T_PRICE_FEE_PLAN, PROD_CC.T_PRICE_CONFIG, PROD_CC.T_POND_DRAWDOWN, PROD_CC.T_ORG_STAFF_PREFERENCE, PROD_CC.T_ORG_STAFF_DEPT_TEMP
When hundreds or thousands of tables are listed as shown above, some netizens say that sometimes there are parameter errors.
But I just tested exclude and excluded 724 user names and table names. There was no problem exporting expdp. I don't know how he wrote the command at that time.
The original description is as follows: use the data pump to export some tables under a user, but 200 of them should be eliminated. If you write the table name directly with exclude, you will report the error that the exclude parameter is invalid because too many table names are eliminated: UDE-00014: invalid value for parameter, 'exclude'
expdp \'/ as sysdba\' dumpfile=T4_2021123022.dmp schemas=PROD directory=PUBLIC_DUMP exclude=statistics PARALLEL=4 COMPRESSION=all cluster=n logfile=T4_100tables.log exclude = PROD.T_SYS_ROLE,PROD_PERMISSION_TEMP,......
If there is a problem, you can try to use the parfile parameter file to export all the exported contents, tables, logs, directories, etc
Write to a file and directly replace it with parfile = file name. Many can be omitted from the command line. It looks intuitive and not easy to make mistakes. Many export and import errors are caused by too long command line parameters.
vim exp100table.par dumpfile=T4_100Tables_20211230_%U.dmp logfile=T4_100Tables_20211230.log #schemas=PROD_CC directory=PUBLIC_DUMP exclude=statistics PARALLEL=4 tables=PROD_CC.T_HISTORY,PROD_CC.T_HI，......Wait a thousand tables COMPRESSION=all cluster=n #include=TABLE:"IN (select TABLE_NAME from dba_tables where table_name like 'T_PRICE%')"
2. Exporting partial tables using parfile
$ expdp \'/ as sysdba\' parfile=exp100table.par Export: Release 11.2.0.4.0 - Production on Thu Dec 30 15:38:05 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" parfile=exp100table.par Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.594 GB Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE . . exported "PROD_CC"."T_PRICE_SNAPSHOT_DETAIL" 47.28 MB 113825 rows Processing object type TABLE_EXPORT/TABLE/TABLE ...............Omit partial output 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/CONSTRAINT/REF_CONSTRAINT Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /nfs/T4_100Tables_20211230_01.dmp /nfs/T4_100Tables_20211230_02.dmp /nfs/T4_100Tables_20211230_03.dmp /nfs/T4_100Tables_20211230_04.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 30 15:40:24 2021 elapsed 0 00:02:14
3. Export some tables by viewing the table name
This method is actually the same as the top method to check dba_tables is the same principle. Create a new table and write the table name of the table to be exported into a table, which is the same as the previous export scheme. Here is a brief sentence.
Use the export user to create a table. I use SYS to create and export here.
create table T4_100Tables (TABLE_NAME varchar2(40));
Splice SQL statements to convert the 94 table names to be exported into the following insert SQL
insert into T4_100Tables (table_name) values('T_VCHR_HISTORY');
insert into T4_100Tables (table_name) values('T_SYS_ZQH');
insert into T4_100Tables (table_name) values('T_SYS_ROLE');
insert into T4_100Tables (table_name) values('T_SYS_FILES');
commit;Edit par file to export 94 tables under financing
vim exp94table.par dumpfile=T4_100Tables_20211202_%U.dmp logfile=T4_100Tables_20211202.log schemas=PROD_CC directory=PUBLIC_DUMP #exclude=statistics flashback_scn=10836454986 PARALLEL=4 COMPRESSION=all cluster=n #include=TABLE:"IN (select TABLE_NAME from dba_tables where table_name like 'T_PRICE%')" include=TABLE:"IN (select TABLE_NAME from T4_100Tables)"
Background export partial tables
nohup expdp \'/ as sysdba\' parfile=exp94table.par &
