In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces how to export DATAPUMP when using exclude exclusion table, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.
I. description of the problem
There is a small requirement to import data from the production database to the test library. The problem is that there is a large table (30g) that does not need to be exported to the test environment, so use the 'exclude' parameter in expdp. But I still encounter a little bit of question in the process of using it.
II. Operation process
1. According to the expdp-help information, we know that the for example of the exclude parameter is as follows:
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA: "= 'HR'".
Of course, = can also be replaced with in () or like'%..%', such as exclude=index: "like 'emp%'", or only for object types, such as exclude=VIEW,PACKAGE, FUNCTION
The Syntax given in the official document is
EXCLUDE=object_type [: name_clause] [,...]
From the above example, I infer the use of the exclude parameter for the table
Exclude=table: "= 'table name'"
two。 The execution statement reported an error, which indicates that there is a problem with the syntax, and the final query is due to the absence of escape characters.
[oracle@rac2 ~] $expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log exclude=table: "= 'WSCT_DATA'" compression=all
LRM-00116: syntax error at 'table:' following' ='
3. Modified and executed successfully after adding escape character
[oracle@rac2 ~] $expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log EXCLUDE=TABLE:\ "=\ 'WSCT_DATA\'\" compression=all
Export: Release 11.2.0.4.0-Production on Thu Jul 13 10:17:50 2017
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=cloan_20170713.log" Location: Command Line, Replaced with: "logfile=cloan_20170713.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CLOAN". "SYS_EXPORT_SCHEMA_02": cloan/* DIRECTORY=backup DUMPFILE=cloan_20170713.dmp logfile=cloan_20170713.log EXCLUDE=TABLE: "= 'WSCT_DATA'" compression=all reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
... ...
Dump file set for CLOAN.SYS_EXPORT_SCHEMA_02 is:
/ backup/cloan_20170713.dmp
Job "CLOAN". "SYS_EXPORT_SCHEMA_02" successfully completed at Thu Jul 13 10:19:18 2017 elapsed 0 00:01:27
4. Successfully imported to the test environment
[oracle@testdb backup] $impdp cloancp/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan
Import: Release 11.2.0.4.0-Production on Thu Jul 13 10:45:30 2017
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
Master table "CLOANCP". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CLOANCP". "SYS_IMPORT_FULL_01": cloancp/* DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
... ...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CLOANCP". "SYS_IMPORT_FULL_01" completed with 4 error (s) at Thu Jul 13 10:46:34 2017 elapsed 0 00:01:04
Thank you for reading this article carefully. I hope the article "how to use exclude exclusion Table when DATAPUMP is exported" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.