RE: Quick way to drop all objects in a schema
- From: "Go-Faster Consultancy Ltd." <info@xxxxxxxxxxxxxxx>
- To: <mwf@xxxxxxxx>
- Date: Thu, 19 Nov 2009 23:19:11 -0000
There are no referential integrity constraints in PeopleSoft.
There no primary key constraints in PeopleSoft - they create unique indexes
instead.
Every character and numeric column and most date column are not nullable -
so lots of NOT NULL constraints.
If you use Unicode on application v8, character columns are still defined
with byte semantics, so PeopleSoft puts a length checking constraint on each
and every character column - as well as causing a huge parse overhead, this
will make drop table commands very slow. Only from Application v9 do they
use character semantics.
Most of the tens of thousands of tables and indexes come from the multiple
'non-shared' tables created for each PeopleSoft temporary record (these are
permanent tables used for temporary working storage during batch programs -
different concurrent instances of the same program use different tables).
If you take the time and trouble to set the number of temporary table
instances on an Application Engine to the actual number of concurrent
instances of the program that you will run, you can significantly reduce the
number of tables that you have to deal with. However, almost nobody does
this, because it is a lot of work.
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
>Sent: Thursday, November 19, 2009 10:42 PM
>To: stmontgo@xxxxxxxxx; JDunn@xxxxxxxxx
>Cc: oracle-l@xxxxxxxxxxxxx
>Subject: RE: Quick way to drop all objects in a schema
>Importance: High
>
>I'd wager somewhere between a nickel and a donut that you can
>additionally speed it up if you drop the constraints first,
>then the indexes, then the tables, and then, as you say, drop
>user cascade for completeness. Now while I have not run a
>speed test since before we had local extent management and the
>I doubt the difference is still as much it was with dictionary
>management. It probably only matters with local extent
>management when you have huge numbers of tables and indexes. A
>big part of the old advantage (meaning dictionary managed) was
>if you could take the tablespaces off line for the drop,
>avoiding the "pecimal" (my made up antonym for optimal)
>algorithm to juggle putting bits back on fet$ when others
>could be grabbing bits from fet$ for the same tablespace. But
>it also relieves Oracle of a lot of recursive sql elbow grease
>to figure out what order to drop things in the cascade. If you
>whack the constraints, then the indexes, and then the tables,
>that leaves a lot less for Oracle to figure out. I wouldn't be
>surprised if it about cut that in half again. (but that wasn't
>a guess Alex
>- I just said I wouldn't be surprised.) If you try it, please
>let us know whether I'm right.
>
>mwf
>
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>On Behalf Of steve montgomerie
>Sent: Thursday, November 19, 2009 5:25 PM
>To: JDunn@xxxxxxxxx
>Cc: oracle-l@xxxxxxxxxxxxx
>Subject: Re: Quick way to drop all objects in a schema
>
>We do a little of both where we drop tables and views through
>a cursor and then drop user cascade. I've found this to be
>about twice as fast as a simple drop user.
>
>It's peoplesoft so we have about 23,000 tables, 25,000
>indexes. Takes about 45 mins for us.
>
>Steve
>
>On Thu, Nov 19, 2009 at 4:25 AM, John Dunn <JDunn@xxxxxxxxx> wrote:
>> Is there a quick way to drop all objects in a schema without
>dropping
>> the user itself?
>>
>> This is with Oracle 10.
>>
>>
>>
>>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
