Re: HELP: export database to a script file

From: Richard Hull <rich_at_work_at_bigfoot.com>
Date: Fri, 8 Jan 1999 16:48:48 -0500
Message-ID: <775usq$oeq$1@lore.csc.com>

I've used the "exp rows=no..." approach, and it works pretty good for initial deployments. If you're deploying multiple schemas and/or have public synonyms (and can't do a full database level export), you may run into snags with import warnings such as views or db procedure code that didn't compile due to the missing synonyms. These situations can usually be cleared up by establishing the missing synonyms, and recompiling the objects. Here's a quick script that will find and recompile all invalid objects:

---

rem MAKEM_VALID - Find and recompile invalid user objects.

rem Author: Richard Hull

prompt Here is a summary of the invalid objects:

select object_type, count(*)

from user_objects

where status <> 'VALID'

group by object_type;

accept v_ok prompt "Press [Return] to continue..."

set feedback off

set heading off

set pagesize 9999

define v_what="decode(object_type,'PACKAGE BODY','PACKAGE',object_type)"

define v_body="decode(object_type,'PACKAGE BODY','BODY ',null)"

spool makem_valid_guts.sql

select 'alter ' || &v_what || ' ' || object_name || ' compile ' || &v_body || ';'

from user_objects

where status <> 'VALID'

order by object_type, object_name;

spool off

undefine v_what

undefine v_body

set feedback on

set heading on

prompt makem_valid_guts.sql created.

accept v_ok prompt "Press [Return] to continue and run the _guts file..."

@makem_valid_guts

---

 You might also look at the TOAD product (http://www.toadsoft.com) from which has some ability to create schema scripts from an existing database.

-Rich.

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:36967377.5878F2DF_at_sybrandb.demon.nl...
>You could consider using exp to export the complete database without
exporting the
>rows. The utility imp will reverse the process on the target database.
>
>Hth,
>
>Sybrand Bakker, Oracle DBA
>
>mcui_at_cc.usu.edu wrote:
>
>> Hello, Oracle exports,
>> We would like to generate script file for an existing Oracle database so
that
>> we can deliver this script file to our customer with our
>> application. In MS SQL server, we can get a script file for a database
(only
>> for building database objects, records not included).
>> Do we have this type of utility in Oracle? Thanks in advance,
>>
>> Muyi
>
Received on Fri Jan 08 1999 - 15:48:48 CST