Import excluding one table

[ZAPPAJOE52]

We currently do a full export of our production environment nightly. I need to refresh a schema in our test environment. Usually this is a straight fromuser/touser import but I need to exclude ONE VERY LARGE TABLE.

Does anybody have any tips or advice on how to do this other than listing the tables individually ? If I do that then I seem to miss other objects like procedures, sequences, etc.

[sambavan]

Roughly around how many tables do you have on the schema? What is your OS? and DB versions?

Sam

[Jr]

You may want to try this....

Malfunction that perticular Object alone ( if applicable offline TBS, Existance of the same NAMED object with different structure etc )
While doing import it throws error for that table alone and every other thing should go through.

* I assume u specify IGNORE=Y

Jr.

[ZAPPAJOE52]

1534 tables
8096 indexes

OS is HP-UX 11
Oracle ver 8.1.7

[sambavan]

You can either take that table/tablespace off line and do an export, but on a production server this is not feasible. On such case you can write a shell script that would spit out all the tables that you want and then you can use that on your in your import scripts under the clause tables(schema.tablename1, schema.tablename2 ...)


Sam

[Shestakov]

Best way in ur situation - create small script for generate parameter file.
Some thing like:
---------------------------------------
set linesize 70
set pagesize 0
set serveroutput on size 20000
set echo off
set termout off
set feedback off
set hearding off
...

spool templ_exp.par
--
-- pl/sql block for generate template file
--
declare
str varchar2(32000);
sep char(1) ;
begin
sep := '';
str := '';
for r in (select t.owner||'.'||t.table_name nm from all_tables t
where t.owner not in ('SYS', 'SYSTEM'
and
t.table_name not in ('T1','T2' ...)) loop
loop
str := str || sep || r.nm || chr(10);
sep := ',';
end loop;
--
-- write template parametr file
--
dbms_output.put_line ('USERID=system/manager@base');
dbms_output.put_line ('BUFFER=32000');
dbms_output.put_line ('FILE=../exp_data/dmp_base.dmp');
dbms_output.put_line ('LOG=../exp_log/dmp_base.log');
dbms_output.put_line ('TABLE=('||str||')');
dbms_output.put_line ('GRANTS=y');
dbms_output.put_line ('ROWS=y');
dbms_output.put_line ('COMPRESS=n');
dbms_output.put_line ('INDEXES=y');
dbms_output.put_line ('CONSISTENT=y');
dbms_output.put_line ('');

exception when others then null;
end;
/

--------------------------------------
then in command line :
> exp parfile=templ_exp.par

[sambavan]

There you go!!!!



Sam

[vanirame]

Create that table in another schema and create synonym in desired schema.

[Shestakov]

ONE IMPORTANT NOTE ABOUT SCRIPT:

dbms_output.put_line ('TABLE=('||str||')'); -- MUST BE LESS THEN 255 BYTES
ELSE U SHOULD DIVIDE str variable on substrings with size 255 bytes or less.

[jmodic]

One additional important note about that script:

I haven't tested this for a while, but in earlier releases there was (an undocumented) limitation of how many tables you can specify in your parameter file (or was it the number of lines of the parameter file?). Anyhow, I'm pretty sure you'll hit that limit with your 1500 tables!

And I simply don't see it very feesible to use such a script to exclude one single table out of 1500 from being imported. All you need to do is to drop all the objects from your TOUSER schema, except that table you don't want to be imported (that can realy be a small dummy empty table with minimal storage attributes, it only has to have the same name as the source table). And then start your import with IGNORE=N.