Re: IMP/EXP accross platforms: What will be missing?

Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<35odqvgmobjn34ilsntprkra18sflrqbpm_at_4ax.com>...
> Mark.Powell_at_eds.com (Mark D Powell) wrote:
>
>
> >Rick, I am only going to add a couple of notes:
> >1- Build your new db using locally managed uniform extent tablespaces
> >for all user data tablespaces
> And for the system tablaspace too, I guess.
>
> >2- Perform the exports with compress=n so each object takes only as
> >much room as it needs when recreated and initially loaded
> Agree.
> >3- you can use an export with full=y rows=n to capture public synonyms
> >etc... that an imp with ignore=y will re-establish for you should
> >something go wrong on the first import
> I hope it will work that way.
>
> >4- I would build my new db from scratch well in advance room
> >permitting and precreate the owners with the necessary object grants.
>
> I have had the problem that at import time, some specific tables would
> import into a wrong tablespace (it should go into the target user's
> default tablespace); and the import of this particular segment will
> fail if the schema owner at the target DB has no quota on this
> tablespace. Strange. It happened with tables containing records of
> type LONG.
>
> >This would prevent errors due to the owners having objects of a type
> >for which they do not currently have create privilege plus you can
> >re-establish any grants on SYS owned objects that the imp will not
> >contain and which are needed to support stored
> >procedure/functions/packaged code that references v$ or dictionary
> >views.
>
> This is the best point: grants on sys objects won't come over.
>
> And I was right. I am afraid that I forgot something. Shouldn't I set
> the COMPATIBLE parameter in the target DB so something based on the
> source DB? Remember, I am going from 8.1.7.0 to 9.2.0.4.
>
> Thanks
> Rick Denoire
> >HTH -- Mark D Powell --

I would leave compatiable at 9.2. The new db will recognize the features in the old db.

In the past and perhaps still Oracle had an error in that when tablespaces were dropped that Oracle failed to clean up the quota entries for the dropped tablespace. The quotas on non-existent tablespaces were writen to the export file and on imp the alter user statement to add the quotas which was done in one statement for all tablespaces the user had quota on would fail due to the reference to the non-existent tablespace. Pre-creation of owner and the issueing of privileges/quotas to them avoid these kind of errors.

Also in 8.1.7 there is an imp bug that may or may not exist in 9 that when the target tablespace does not exist and the table has a LOB segment that is assigned to a non-existent tablespace the LOB is not defaulted to the owner default tablespace and the create errors off. The work around from support is to pre-create the table. If you have lobs you can post back if this problem still exists in 9.2

Good Luck.
-- Mark D Powell -- Received on Tue Nov 04 2003 - 09:28:54 CST