RE: How NOT to EXPort a specific table when FULL=Y?

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Mon, 23 May 2005 18:38:39 -0400
Message-ID: <B30C2483766F9342B6AEF108833CC84E0450BBA1@ecogenemld50.Org.Collegeboard.local>

This seems to work, but the situation has to be right...I have the big table ONLY in this tablspace. EXPort OFFLINE TABLESPACE Test;

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces where TABLESPACE_NAME='SCOTT_BLOB01';

TABLESPACE_NAME                STATUS
------------------------------ ---------
SCOTT_BLOB01                    ONLINE

SQL> alter tablespace SCOTT_BLOB01 offline; Tablespace altered.

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces where TABLESPACE_NAME='SCOTT_BLOB01';

TABLESPACE_NAME                STATUS
------------------------------ ---------
SCOTT_BLOB01                    OFFLINE

SQL> exp USERID=/ PARFILE=ts_offline_test.par

FILE=ts_offline_test.dmp
LOG=ts_offline_test.log
CONSTRAINTS=Y
GRANTS=N
INDEXES=N
TRIGGERS=N
ROWS=Y
STATISTICS=ESTIMATE
BUFFER=26214400
COMPRESS=N
CONSISTENT=Y
DIRECT=Y
TABLES=SCOTT.IMAGE, SCOTT.ENV
/> exp USERID=/ PARFILE=ts_offline_test.par

Export: Release 9.2.0.3.0 - Production on Mon May 23 18:23:42 2005

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported

About to export specified tables via Direct Path ... Current user changed to SCOTT
Table IMAGE will be exported in conventional path.

. . exporting table                    IMAGE
EXP-00056: ORACLE error 376 encountered

ORA-00376: file 15 cannot be read at this time ORA-01110: data file 15: '/oracle/data/orcl3/d04/SCOTT_blob01_03.dbf'

. . exporting table                            ENV          6 rows exported

EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Reardon, Bruce (CALBBAY) Sent: Mon 5/23/2005 6:33 PM
To: oracle-l_at_freelists.org
Subject: How NOT to EXPort a specific table when FULL=Y?

We use a method for updating our test instance whilst excluding the = biggest few tables.

It probably isn't supported by Oracle and make sure you test it and = understand the gotchas - eg you don't get the table but you do get the = views based on the table which will be invalid - so you need to recreate = the table & indexes and then recompile everything (or pre-create the = table).

Anyway, create a custom version of catexp.sql (ie = ?\rdbms\admin\catexp.sql).
-- Update views exu81obj + exu81tabs - add o$.name NOT IN ('x','y') -- View exu8usr would need altering if wanted to exclude a particular = user.

Note that I got the original idea from Nov 2002 Oracle-L postings by = Arup Nanda + Kirti Deshpande - subject "export in full mode but exclude = particular user".

Then have a script that does the export
run your custom catexp-nobig.sql
run export
run standard catexp.sql

Obviously don't do this at the same time someone else (scheduled job?) = is trying to do a normal export (eg full) that involves those tables.

HTH,
Bruce Reardon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Marquez, Chris Sent: Tuesday, 24 May 2005 7:59 AM
To: oracle-l_at_freelists.org
Subject: How NOT to EXPort a specific table when FULL=3DY?

Have a HUGE table that I do NOT want to EXPort,=20 but other that I would like *everything* else...FULL=3DY.

Any ideas?

Thanks in advance,

Chris Marquez
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l


NOTICE
This e-mail and any attachments are private and confidential and may =
contain privileged information. If you are not an authorised recipient, =
the copying or distribution of this e-mail and any attachments is =
prohibited and you must not read, print or act in reliance on this =
e-mail or attachments.
This notice should not be removed.
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 23 2005 - 18:47:10 CDT