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: exp/imp (Transportable Tablespace)

Re: exp/imp (Transportable Tablespace)

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 12 Nov 2007 16:16:14 -0800
Message-ID: <1194912974.834993.162460@50g2000hsm.googlegroups.com>


On Nov 12, 3:51 pm, Tom76 <br..._at_hotmail.com> wrote:
> On Nov 12, 1:59 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Nov 12, 1:54 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> > wrote:
>
> > > Tom76 wrote:
> > > > Transportable Tablespace feature
>
> > > > source db: 9i
> > > > target db: 10g
> > > > OS: source and target is Linux.
> > > > Tested a migration of few application tablespaces using Transportable
> > > > Tablespace feature. Packages, procedures did not get migrated.
> > > > There is no option to specify paackages or procedures to be
> > > > exported(exp or imp). So I guess they get exported/imported
> > > > automatically.
> > > > No errors during the transportable tablespace procedure. Any idea why
> > > > the packages and procedures did not get migrated.
>
> > > Time for the concept manual athttp://tahiti.oracle.com...
>
> > > Where (what tablespace) are procedures stored?
> > > And what tablespace did you transport?
>
> > > And as A is not equal to B, no procedures, only data.
> > > Unless you transported tablespace A of course - in which
> > > case you lost your database.
>
> > > And import/export will export procedures, packages and
> > > functions of a specific user, if you do an export on that
> > > level - also documented on tahiti, but in the Utilities
> > > manual.
> > > --
> > > Regards,
> > > Frank van Bortel
>
> > > Top-posting is one way to shut me up...
>
> > The traditional export and import utilities do not have parameters
> > that allow selecting stored procedures, functions, or packages by
> > themselves.
>
> > You can obtain the code via user or full database exports. Look at
> > the rows=n option.
>
> > If you need only selected objects then it is also easy to extract
> > stored procedure code form dba_source and then just run this into the
> > target database. Oracle provides the dbms_metadata package to do this
> > but we just use a query to extact the source, grants, and synonyms on
> > the procedure.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> Dont give me the basics of exp/imp. Should not the transportable
> tablespace feature take care of package/procedure migration??- Hide quoted text -
>
> - Show quoted text -

I though my response was pretty clear in that I confirmed there are no parameters available to export/import just procedures and I also said the only way to get stored code is via a user or full export. That means you cannot do it via the transportable tablespace option. After all stored code is in the system tablespace which you cannot transport via the exp/imp transportable tablespace feature. I provided you the alternatives on how to get the source code.

If you want to duplicate an entire database you can do so using rman.

Note that the newer expdb and impdp utilities do support extracting and importing stored code but you need 10g plus for these and impdp can only process expdp output and not exp dmp files.

HTH -- Mark D Powell -- Received on Mon Nov 12 2007 - 18:16:14 CST

Original text of this message

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