Re: Is imp/exp the best way to move one schema from one inst to another? Please help!

You could use exp/imp to do the job. I certainly do. The only real downside is that it can be slow if the schema contains large segments. Something else you might want to pursue is to use Transportable Tablespaces to move the user's segments and then reverse-engineer the DDL to create the non-segment objects (stored procedures, etc.). You can use the DBMS_METADATA package to reverse-engineer the data for you. Exp/imp with OWNER will get everything for you, nice and neatly, but it can be slow to import. The other method can be faster, but it is easier to forget something.

And if your DBA can't do the job correctly, I'm sure it won't take your company long to find one that will (provided they'll pay market $$$).

HTH,
Brian

colinlyse wrote:
>
> Using oracle 9.2.0.3.0, on two Sun Starfire Servers both running Sun Unix, 8
> Sparc CPUs, 2 TB each.
>
> We have a schema AP on an instance A1 on a UNIX server A1. The schema has
> tables, views, constraints, no triggers, functions, procs, indexes etc.
>
> We have another schema AP on an instance T1 on a UNIX server T1. The schema
> has tables, views, constraints, no triggers, functions, procs, indexes etc.
>
> We have made major changes to AP on A1 and we want to blow away AP on T1 and
> replace it completely with AP from A1.
>
> What is the best method of doing this? Will an export/import do it, export
> AP/A1, dropping the AP/T1 schema and then importing AP/A1 into T1. If so any
> things we need look out for? Is there better/different way to do this? Keep
> in mind we will delete AP/T1 because we want what is on AP/A1 to replace it
> completely.
>
> In case you may be wondering why am I asking this. Well, our DBA is having
> some issues in meeting his job performance goals, so to speak. His supervisor
> is monitoring his work, but not doing it for him. We have made requests, in
> userspeak, i.e. move this table for us from A to Z and have not been able to
> get him to do things correctly (grants not done, indexes left disabled etc).
> We have found that only way has been to give him very detailed steps (with
> enclosed scripts.he had no idea what Oracle table compression in 9i was and
> was going to take a couple of days to research the issue) as to what to do.
> While for most things that was not an issue (reading this group and Ask Tom
> have enriched my knowledge greatly), I have no experience with import/export
> just what I have read. So any feedback, maybe even a sample script would be
> greatly appreciated

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"