Re: Truncate before dropping table>

#1

Dereck L. Dietz wrote:

Quote:

The following are two segments from emails going around where I work. Is he
making any sense or is he out in left field? Both are in reference to
truncating a table before dropping it.

Do people know to truncate a table first before dropping a table?

I am not sure other people are truncating their tables first, but it may be
a good idea in order to reset the HWM's.

Depends on the version.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#2

On May 24, 9:32 pm, "Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> wrote:

Quote:

"DA Morgan" <damor... (AT) psoug (DOT) org> wrote in message

news:1180051894.989633 (AT) bubbleator (DOT) drizzle.com...

Dereck L. Dietz wrote:
The following are two segments from emails going around where I work.
Is he
making any sense or is he out in left field? Both are in reference to
truncating a table before dropping it.

Do people know to truncate a table first before dropping a table?

I am not sure other people are truncating their tables first, but it may
be
a good idea in order to reset the HWM's.

Depends on the version.
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Sorry. Oracle 10g with the latest patch.- Hide quoted text -

- Show quoted text -

As far as I know, there is a benefit to truncating instead of running
a huge delete if you're planning on deleting the entire table. no
redo. But I'm not so sure when planning on dropping anyway.

And as far as the hwm goes, if you drop the table that's going to
pretty much reset the hwm anyway, no?

In 10g, there may be some benefit to truncating before dropping. With
the new flashback table & recyclebin. But I'm not real sure, as I
would think that the recyclebin would still hold the table pre-
truncate at least until it was aged out.

#3

Dereck L. Dietz wrote:

Quote:

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote in message
news:1180051894.989633 (AT) bubbleator (DOT) drizzle.com...
Dereck L. Dietz wrote:
The following are two segments from emails going around where I work.
Is he
making any sense or is he out in left field? Both are in reference to
truncating a table before dropping it.

Do people know to truncate a table first before dropping a table?

I am not sure other people are truncating their tables first, but it may
be
a good idea in order to reset the HWM's.
Depends on the version.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Sorry. Oracle 10g with the latest patch.

Probably makes little difference given the recyclebin. But you should
run a test and verify.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

Ben wrote:

Quote:

Do people know to truncate a table first before dropping a table?

As far as I know, there is a benefit to truncating instead of running
a huge delete if you're planning on deleting the entire table.

The question was not about deletes ... it was whether to truncate before
drop.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

"Ben" <balvey (AT) comcast (DOT) net> a écrit dans le message de news: 1180104159.315489.138130 (AT) w5g200...oglegroups.com...

Quote:

And as far as the hwm goes, if you drop the table that's going to
pretty much reset the hwm anyway, no?

Not at all. Delete does not change HWM.

Regards
Michel Cadot

#6

On May 25, 8:46 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

"Ben" <bal... (AT) comcast (DOT) net> a écrit dans le message de news: 1180104159.315489.138... (AT) w5g2000hsg (DOT) googlegroups.com...
|
| And as far as the hwm goes, if you drop the table that's going to
| pretty much reset the hwm anyway, no?
|

Not at all. Delete does not change HWM.

But dropping does, there's no water left to be high. (In the context
of Ben's post, he later talks about 10g, so he's talking 9i here, no
recyclebin)

jg
--
@home.com is bogus.
http://www.mum.org/cuptastk.htm

#7

Quote:

In 10g, there may be some benefit to truncating before dropping. With
the new flashback table & recyclebin. But I'm not real sure, as I
would think that the recyclebin would still hold the table pre-
truncate at least until it was aged out.- Hide quoted text -

I use the format DROP TABLE XXXX PURGE; to avoid it going to the

recycle bin when I am dropping all the SYSADM tables in PeopleSoft.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Truncate before dropping table>

comp.databases.oracle.server comp.databases.oracle.server

Re: Truncate before dropping table> - 05-24-2007 , 07:11 PM

Re: Truncate before dropping table> - 05-25-2007 , 09:42 AM

Re: Truncate before dropping table> - 05-25-2007 , 09:52 AM

Re: Truncate before dropping table> - 05-25-2007 , 09:54 AM

Re: Truncate before dropping table> - 05-25-2007 , 10:46 AM

Re: Truncate before dropping table> - 05-25-2007 , 07:15 PM

Re: Truncate before dropping table> - 05-29-2007 , 12:39 PM