Bo's Oracle Station › 博客首页 ›Oracle技术› 开发和实施数据仓库 › 查看内容

【博客文章2013】极为奇葩的一条命令，绝对正确能执行。

2016-5-13 11:13| 发布者: admin| 查看: 2088| 评论: 0|原作者: Bo Tang

摘要: 在导入导出时，尽量使用parfile，否则在unix系统里，命令就会变得极为复杂。

极为奇葩的一条命令，绝对正确能执行。

Author: Bo Tang

SQL> create user u1 identified by oracle_4U default tablespace users;

User created.

SQL> create table t04218_a ( a number ) ;

Table created.

SQL> insert into t04218_a values ( 1 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> create table t04218_b ( a number ) ;

Table created.

SQL> insert into t04218_b values ( 1 ) ;

1 row created.

SQL> insert into t04218_b values ( 50 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function func04218_a
2 return number
3 is
4 begin
5 return 1 ;
6 end;
7 /

Function created.

SQL> create or replace function func04218_b
2 return number
3 is
4 begin
5 return 2;
6 end;
7 /

Function created.

SQL> select to_char( sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2022-03-28:00:05:53

SQL> insert into t04218_b values ( 100 ) ;

1 row created.

SQL> commit;

Commit complete.

expdp system/oracle_4U directory=dir1 dumpfile=schemas6.dmp schemas=u1 job_name=schemas4_job EXCLUDE=TABLE:\" in \(\'T04218_A\'\)\",function:\" in \(\'FUNC04218_A\'\)\" "query='T04218_B:where a>=50'" flashback_time=2022-03-28:00:22:43

Export: Release 19.0.0.0.0 - Production on Mon Mar 28 00:24:02 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
Starting "SYSTEM"."SCHEMAS4_JOB": system/******** directory=dir1 dumpfile=schemas6.dmp schemas=u1 job_name=schemas4_job EXCLUDE=TABLE:" in ('T04218_A')",function:" in ('FUNC04218_A')" query='T04218_B:where a>=50' flashback_time=2022-03-28:00:22:43
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
. . exported "U1"."T04218_B" 5.054 KB 1 rows
Master table "SYSTEM"."SCHEMAS4_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SCHEMAS4_JOB is:
/home/oracle/dir1/schemas6.dmp

Job "SYSTEM"."SCHEMAS4_JOB" successfully completed at Mon Mar 28 00:24:37 2022 elapsed 0 00:00:34

parfile.txt:

directory=dir1

dumpfile=schemas7.dmp

schemas=u1

job_name=schemas7_job

EXCLUDE=TABLE:" in ('T04218_A')", function:" in ('FUNC04218_A')"

query="T04218_B:where a>=50"

flashback_time=2022-03-28:00:22:43

expdp system/oracle_4U parfile=parfile.txt