RE: Large Export Problem ......

[Janardhana Babu Donga]

Title: RE: Large Export Problem ......

Jacques,

 

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.

 

Thanks,

-- Babu

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 6:19 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Large Export Problem ......

(see comments below)

> -----Original Message-----
> From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]]
>
> 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
 select
    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
 select
    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
exit