Re: Method for migrating schema from PROD to DEV

why not disable all constraints before dropping tables? there should be dozens of scripts help you to drop all objects owned by one schema,
you can google can try.

And , to my experience, while we get DEV refresh from production, the old schema in DEV need not be backuped :)

Thanks a lot.

On Tue, Nov 25, 2008 at 8:33 AM, Bradd Piontek <piontekdd_at_gmail.com> wrote:

> You can issue a 'drop table <tablename> cascade constraints;' and then not
> worry about the constraint orders.
>
> I know I have an anonymous PL/SQLscript I wrote a while ago to clear a
> schema (excluding some objects like database links).
>
> Bradd Piontek
> "Next to doing a good job yourself,
> the greatest joy is in having someone
> else do a first-class job under your
> direction."
> -- William Feather
>
>
> On Mon, Nov 24, 2008 at 4:31 PM, SHEEHAN, JEREMY <JEREMY.SHEEHAN_at_fpl.com>wrote:
>
>> I started on the route of just dropping objects, but I came across some
>> constraint issues. I know that I could get around it by dropping the tables
>> in a particular sequence, but I'm in kind of a rush. I thought that just
>> dropping the user and cascading it's objects it would work just as well.
>>
>>
>>
>> I didn't think about the TS Quotas. I'll be sure to keep that in mind
>> before I migrate this. I'll just recreate the user as best as I can, then
>> rerun any grants/privs that were missed due to missing objects.
>>
>>
>>
>> Thanks!
>>
>>
>>
>> Jeremy
>>
>> P *Consider the environment. Please don't print this e-mail unless you
>> really need to.*
>>
>>
>>
>> *From:* Baumgartel, Paul [mailto:paul.baumgartel_at_credit-suisse.com]
>> *Sent:* Monday, November 24, 2008 4:27 PM
>> *To:* SHEEHAN, JEREMY; oracle-l_at_freelists.org
>> *Subject:* RE: Method for migrating schema from PROD to DEV
>>
>>
>>
>> 1. Make sure all of the production tablespaces exist in dev, and with
>> sufficient space
>>
>>
>>
>>
>>
>> 2. Make sure all production roles exist in dev
>>
>>
>>
>>
>>
>> 3. Make sure to create dev user with same system privileges, role
>> grants, and tablespace quotas as production
>>
>>
>>
>> OR
>>
>>
>>
>> 3. Don't drop dev user; just drop all its schema objects
>>
>>
>>
>>
>>
>> *Paul Baumgartel*
>> *CREDIT SUISSE*
>> Information Technology
>> Prime Services Databases Americas
>> One Madison Avenue
>> New York, NY 10010
>> USA
>> Phone 212.538.1143
>> paul.baumgartel_at_credit-suisse.com
>> www.credit-suisse.com
>>
>>
>>
>>
>> ------------------------------
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *SHEEHAN, JEREMY
>> *Sent:* Monday, November 24, 2008 4:12 PM
>> *To:* oracle-l_at_freelists.org
>> *Subject:* Method for migrating schema from PROD to DEV
>>
>> Does anyone have a proven method for migrating a schema from PROD to DEV?
>> I plan on using export/import, but I've got a few reservations about the
>> migration steps.
>>
>>
>>
>> I think the best method would be to do the following
>>
>>
>>
>> 1. Export Schema in PROD
>>
>> 2. Export Schema in DEV (for backup)
>>
>> 3. Drop user in DEV
>>
>> 4. Create basic user in DEV
>>
>> 5. Import user in DEV
>>
>> 6. Verify object count - PROD vs DEV
>>
>> 7. Compile schema
>>
>>
>>
>> Does this sound right?
>>
>>
>>
>> Jeremy
>>
>> P *Consider the environment. Please don't print this e-mail unless you
>> really need to.*
>>
>> ==============================================================================
>>
>> Please access the attached hyperlink for an important electronic communications disclaimer:
>>
>>
>>
>> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>>
>> ==============================================================================
>>
>>
>

-- 

("'-''-/").___..--''"'-._
 '7_ 7  )   '_.  (     ).'-.__.')
 (_Y_.)   ._   )  '._ '. ''-..-'
_.'--'_..-_/  /--'_.' ,'
(il),-''  (li),'  ((!.-'

Best regards，
Yours sincerely House
baojiejie_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l