oralce数据库逻辑导出跳过某些表（EXP）_oracle用exp排除部分表导出-CSDN博客

本文链接：https://blog.csdn.net/Oracle_zsq/article/details/89668881

1、使用exp导出部分数据：

法一：修改exu10tabs视图：

不同版本数据库，可能视图名称和语句有一定出入，请别照搬,exu10tabs在$ORACLE_HOME/rdbms/admin/catexp.sql中

修改脚本如下：

CREATE OR REPLACE VIEW exu10tabs (
objid, dobjid, name, owner, ownerid, tablespace, tsno, fileno,
blockno, audit$, comment$, clusterflag, mtime, modified, tabno,
pctfree$, pctused$, initrans, maxtrans, degree, instances,
cache, tempflags, property, deflog, tsdeflog, roid, recpblk,
secondaryobj, rowcnt, blkcnt, avgrlen, tflags, trigflag,
objstatus, xdbool)
AS /* Heap tables */
SELECT
o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
t$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
NVL(t$.bobj#, 0), o$.mtime,
DECODE(BITAND(t$.flags, 1), 1, 1, 0), NVL(t$.tab#, 0),
MOD(t$.pctfree$, 100), t$.pctused$, t$.initrans, t$.maxtrans,
NVL(t$.degree, 1), NVL(t$.instances, 1),
DECODE(BITAND(t$.flags, 8), 8, 1, 0),
MOD(TRUNC(o$.flags / 2), 2), t$.property,
DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
t$.flags, t$.trigflag, o$.status,
(SELECT COUNT(*)
FROM sys.opqtype$ opq$
WHERE opq$.obj# = o$.obj# AND
BITAND(opq$.flags, 32) = 32 )
FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
sys.com$ c$
WHERE t$.obj# = o$.obj# AND
t$.ts# = ts$.ts# AND
u$.user# = o$.owner# AND
o$.obj# = c$.obj#(+) AND
c$.col#(+) IS NULL AND
BITAND(o$.flags,128) != 128 AND /* Skip recycle bin */
BITAND(t$.property, 64+512) = 0 AND /*skip IOT and ovflw segs*/
BITAND(t$.flags, 536870912) = 0 /* skip IOT mapping table */
and o$.name not in('aaa','bbb') --跳过 aaa,bbb表
UNION ALL /* Index-only tables */
SELECT o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
i$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
NVL(t$.bobj#, 0), o$.mtime,
DECODE(BITAND(t$.flags, 1), 1, 1, 0),
NVL(t$.tab#, 0), 0, 0, 0, 0,
NVL(t$.degree, 1), NVL(t$.instances, 1),
DECODE(BITAND(t$.flags, 8), 8, 1, 0),
MOD(TRUNC(o$.flags / 2), 2), t$.property,
DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
t$.flags, t$.trigflag, o$.status,
(SELECT COUNT(*)
FROM sys.opqtype$ opq$
WHERE opq$.obj# = o$.obj# AND
BITAND(opq$.flags, 32) = 32 )
FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
sys.com$ c$, sys.ind$ i$
WHERE t$.obj# = o$.obj# AND
u$.user# = o$.owner# AND
o$.obj# = c$.obj#(+) AND
c$.col#(+) IS NULL AND
BITAND(o$.flags,128) != 128 AND /* Skip recycle bin */
BITAND(t$.property, 64+512) = 64 AND /* IOT, but not overflow*/
t$.pctused$ = i$.obj# AND/* For IOTs, pctused has index obj# */
i$.ts# = ts$.ts#