[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6079
  • Last Modified:

How do you use the exp to exclude tables from .dmp files when you export the schema?

Hey all,

I am trying to create a dump of an Oracle 9i schema.  I know how to create a .dmp file that includes all of the tables in the schmea but the problem is I want to exclude 2 tables from the dump file.  Well that is not entirely true.  I want the table structures to come along just not the data.

Can anyone help me with this please?
0
lharrispv
Asked:
lharrispv
2 Solutions
 
slightwv (䄆 Netminder) Commented:
I believe the only option is to perform 2 exports.

one with the param: tables=(list of all except those 2)
one with: tables=(table1,table2) rows=N
0
 
lharrispvAuthor Commented:
Thanx for the quick reply slightwv..

I have a follow up question.. normally the way I do this is by calling a .ora file from a command line.  The syntax of the .ora file is as follows:

userid="sys/[syspwd] as sysdba"
FULL=N
COMPRESS=Y
FILE=[dump file name].dmp
BUFFER=40960
owner=[schema user]
GRANTS=Y
LOG=EXP_USER.LOG
FEEDBACK=1000000
statistics=none

The command line I use is as follows:

exp parfile=from_user_.ora

So in this example where would the "tables=(table1,table2) rows=N" go?  Anywhere in the .ora file?
0
 
catchmeifuwantCommented:
include it in the last line . That should do the job.

userid="sys/[syspwd] as sysdba"
FULL=N
COMPRESS=Y
FILE=[dump file name].dmp
BUFFER=40960
owner=[schema user]
GRANTS=Y
LOG=EXP_USER.LOG
FEEDBACK=1000000
statistics=none
tables=tab1,tab2,tab3,tab6
0
 
lharrispvAuthor Commented:
Thanx you guys!!!  I LOVE THIS BOARD!
0
 
Mark GeerlingsDatabase AdministratorCommented:
It is possible in Oracle8.1 and higher to exclude particular tables from a schema-level or full-database export, and that gives the affect you are looking for: the table structure is still exported, but not the data.  I'm not at work right now, so I don't have access to all of the details, but I do have this file that gives you most of what you need.  We do use this capability in our system for a couple of the larger tables.  

Notes:
1. "exp_user" is the Oracle user I created that normally runs export in our database.)
2. the last two "execute dbms_rls.add_policy..." lines have the actual values that we used in our system with a schema name and table names that are valid in our system.

CREATE or REPLACE FUNCTION exclude_table
  (obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 as
  d_predicate varchar2(4);
-- This was from a Metalink document "How to skip or bypass tables when exporting"
BEGIN
  if sys_context ('USERENV', 'SESSION_USER') = 'EXP_USER' THEN
    d_predicate := '1=2';
  else
    d_predicate := '';
  end if;
  RETURN d_predicate;
END;
/
--
-- sample of how to use:
-- execute dbms_rls.add_policy ('SCOTT','EMP','POL_EMP','EXP_DB','EXCLUDE_TABLE')
--
-- Where: 'SCOTT'         --> Owner of the table you want to skip.
--        'EMP'           --> Table you want to skip.
--        'POL_EMP'       --> The name of your policy.
--        'EXP_DB'        --> Owner of the predicate function.
--        'EXCLUDE_TABLE' --> Function which returns the predicate.
--execute dbms_rls.add_policy ('GENTEX','INV_TRANS_HISTORY_ARCHIVE','EXP_SKIP','EXP_USER','EXCLUDE_TABLE');
--execute dbms_rls.add_policy ('GENTEX','INV_TRANS_SUMMARY','EXP_SKIP','EXP_USER','EXCLUDE_TABLE');
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now