Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Large Export Problem ......

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 25 Mar 2003 18:18:41 -0800
Message-ID: <F001.00572C80.20030325181841@fatcity.com>


(see comments below)

> -----Original Message-----
> From: Janardhana Babu Donga [mailto:jbdonga_at_ucdavis.edu]
>
> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Tue Mar 25 2003 - 20:18:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US