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

Home -> Community -> Usenet -> c.d.o.server -> Re: problem with import/export

Re: problem with import/export

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 28 Apr 2001 22:40:18 +0200
Message-ID: <temahkfap63s48@beta-news.demon.nl>

"Martin Pecha" <a9525749_at_unet.univie.ac.at> wrote in message news:3AEAFF87.75176FCE_at_unet.univie.ac.at...
>
> The scenario is this:
>
> I have a database (8.1.5), running on Win NT, which needs to be moved to
> another server
> (8.1.7) running on Win NT.
>
> When i make a export with "full=y" and do the import i get some
> errors:
>
> - some errors regarding violation of contraints
> - many errors that a pl/sql-procedure couln't be compiled.
> (because in the pl/sql-procedure a table/view is used, which will be
> imported later)
>
> i know that i can disable the constraints and it's also possible to do
> the import one table after the other or one user after the other.
> but with 60 users and more than hundred tables that's not an easy way to
> go.
>
> i thought the import-utility imports in the right order (=tables,
> contraints, procedures), but it seems it imports all tables of user A,
> all constraints and all procedures of user A, than the same with user B
> and ...
>
> but it's a problem when a procedure of user A uses a table/view of user
> B and procedure of user B uses a table/view of user A. so i'll always
> get an error when i do a full import.
> (i don't want to compile the procedures (with errors) on my own, because
> there are more than hundred
> procedures)
>
> it there an easy way to solve my problem?
>
> i'm only a software developer who also is responsible for the database,
> but i thought a COMPLETE backup of a database should be an easy task.
>
>
> regards,
> Martin

You are outlining one of the reasons *not* to maintain such cross-relationships in a database. No export/import procedure will be able to resolve this, and this is one of the reasons why IMO such a design is bad.
compiling manually after import shouldn't be a problem though. You can always use
begin
dbms_utility.compile_schema(<schemaname>)

You can also use
declare
sqlstr varchar2(2000);
begin
for robj in (select object_type, object_name from user_objects where status = 'INVALID') loop

    sqlstr := 'alter '||r_obj.object_type||' '||r_obj.object_name||' compile';

    execute immediate :sqlstr;
end loop;
end;
/

Actually the code should read 'alter package <package name> compile body' for package bodies, but I leave that as an exercise to you. So no need to worry and compiling 100s of procedures shouldn't be a problem, as you can do that with an utility and a very small piece of pl/sql

Regards,

Sybrand Bakker, Oracle DBA Received on Sat Apr 28 2001 - 15:40:18 CDT

Original text of this message

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