Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Large Export Problem ......

RE: Large Export Problem ......

From: Jacques Kilchoer <>
Date: Wed, 26 Mar 2003 09:58:44 -0800
Message-ID: <>

Yes, that was a typographical error in my response. I meant IGNORE=N.

-----Original Message-----

Your response is helpful. Thanks for your suggestions. I think I should import with IGNORE=N and not "Y". I think it is typo in your response. If not, please clarify. I would utilize your script as an extra precaution.

-----Original Message-----

(see comments below)
> -----Original Message-----
> From: Janardhana Babu Donga []
> Thanks for the caution. Does any one know if I export with
> owner=<schema
> Name> rows=N, then drop a package and import from the export file with
> IGNORE=N (which is default), will it restore the dropped
> package and error
> out on all other objects as they are already exist?
> Is this the right way of restoring the dropped package or
> will it have any
> problems? There are plenty of the tables in the database and
> error list will
> be too long in this case. Any side effects? or Is there any
> other way to
> import a dropped pakage?
> A little bit uncomfortable seeing big error list for
> restoring one package.
> Any ideas or will I have to live with this in case the need
> arises? Iam
> afraid of any side effects.

Answering your question: if you import a schema with IGNORE=Y after dropping only one package, I would think it should work (meaning only import the missing package and show errors for every other object), but I haven't tried it. However, have you considered the following? Are these packages written in-house or part of some third-party software? If they are written in-house then I would suggest some version control system to keep track of which version in the source code is in the database. Then you should be able to recreate any dropped package from the latest version in your version control system. If they are part of some third-party software and you want to back them up, you could always try a custom "select ... from dba_source" script to back them up.
example: save_source.sql
set linesize 4000
set pagesize 0
set echo off
set feedback off
set verify off
set trimspool on
column sort_column noprint
column owner noprint
column name noprint
column type noprint
column line noprint
accept username char prompt "Enter schema name (case sensitive) " set termout off
spool c:\mydir\db_source_&username..sql  select

    1 as sort_column,
    object_name as name,
    object_type as type,
    to_number (null) as line,
    'create ' as sql_text
  from dba_objects
  where owner = '&&username'

        and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
union all

    2 as sort_column,

    name as name, 
    type as type, 
    line as line, 

    text as sql_text
  from dba_source
  where owner = '&&username'
union all

    3 as sort_column,
    object_name as name,
    object_type as type,
    to_number (null) as line,
    '/' as sql_text
  from dba_objects
  where owner = '&&username'

        and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
order by name, type, sort_column, line ; spool off

Please see the official ORACLE-L FAQ:
Author: Jacques Kilchoer

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Wed Mar 26 2003 - 11:58:44 CST

Original text of this message