Re: Import of single storeproc from export file?

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 04 Sep 1998 06:12:33 GMT
Message-ID: <35ef84b5.319018@news.siol.net>

On 3 Sep 1998 03:23:29 GMT, jpmakar_at_aol.com (Jpmakar) wrote:
>Does anyone know how to import a single storedproc from a export file of an
>entire database that consists of many schemas? I can restore a single table,
>but have only been able to get storedprocs if I restore an entire schema.
>Right now I have a dummy schema that I do a 'touser' import into and cut and
>past the storedproc back to the original schema. This is a pain in the
>butarsky. Anybody got a better way?

Here is a clumsy aproximation of what you want to achieve:

ALTER USER to_user QUOTA 0 ON <all tablespaces>; REVOKE UNLIMITED TABLESPACE FROM to_user; This way you prevent import to be able to create any new tables in to_user's schema.
Import the dmp file with the following settings: FROMUSER=orig_owner TOUSER=to_user (if the to_user is different from the orig_user) IGNORE=n (to prevent inserting new rows into existing tables

                 and to prevent recreating existing PL/SQL objects,
                 views and sequences)

3. Restote the original to_users's tablespace quotas.

As a result, the import will create all the nonexisting procedures, functions, packages, package bodies, sequences and views (i.e. all the objects that exist only in the data dictionary) from orig_owner schema in the to_user's schema, while leaving all the existing objects intact.

So it's not the "SINGLE_PROCEDURE_IMPORT" like you wan't it, but it is "NONEXISTING_VIEW_SEQ_PLSQL_IMPORT". You can make a table listing all the procedures, functions, packages, package bodies, sequences and views prior to the above import. Then after the import, you can dynamicaly create the script (by querying this table and the catalog) for droping all the new objects except the procedure you wanted to import originaly.

This should be much quicker than importing the whole schema into a dummy schema and then cut-and-pasting the procedure definition into the to_user schema. And you'll avoid tablespace fragmentation (by not creating creating/dropping objects in the dummy schema).

HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Sep 04 1998 - 01:12:33 CDT