Re: export the schema exclude two tables?

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Fri, 25 Jan 2002 08:34:00 -0800
Message-ID: <F001.003FB416.20020125082600@fatcity.com>

I tried a different way once - with the PL/SQL extensions to export package. It isn't described in the docs, but there is a file (dbmsexp.sql) in rdbms/admin directory. Basically for each table that needs special treatment, you insert a row in sys.expact$ and identify the PL/SQL code you want to call before or after the table is exported. I had PL/SQL functions that renamed the tables (rename emp to a_emp). It sort of worked; exp renamed the tables before copying the rows and then raised an ORA-942 error because the table wasn't found and then continued with the next table in the schema. One problem was that the exp dmp file still contained the create table statement for the excluded tables. The solution to that was to pre-create dummy tables on the target system with the same names...
It was a while ago maybe in Oracle7.3. Right now I can't remember why I did it. Is there a limit on the number of tables you can list for the exp tables parameter? Maybe the issue was to speed up the exp or limit the size of the exp dump file?

With a complete export, you can still create dummy tables on the target with the same names as the tables you want to exclude and run imp with ignore=n. This at least excludes certain tables from the imp (if not from the exp).

Chaim

Rajesh.Rao_at_chase.com_at_fatcity.com on 01/24/2002 06:50:52 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table creation scripts, possibly some exutab tables to say obj$.name != <Table1, Table2>.

Hic !! Nooooooo. I did not say that ;-)

What I say is, include all the tablenames except the two that you do not need in your parfile.

Regards
Raj

CC Harvest <ccharvest_at_yahoo.com>@fatcity.com on 01/24/2002 04:55:22 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Anyone knows how to do it in Oracle8.1.7?

Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: CC Harvest
INET: ccharvest_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rajesh.Rao_at_chase.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Chaim.Katz_at_Completions.Bombardier.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message