RE: Truncating the table

From: Jenkins, Michael <Michael.Jenkins_at_Nextel.com>
Date: Tue, 29 Jan 2002 13:57:25 -0800
Message-ID: <F001.003FFA30.20020129135652@fatcity.com>

Actually, technically speaking is does not destroy any data. It simply makes it unrecoverable because the high water mark has been moved. That is why is completes so quickly as opposed to a delete statement. To recover the unused space you need to "alter table <table_name> deallocate unused"

--Michael

-----Original Message-----From: Adams, Matthew (GEA, 088130) [mailto:MATT.ADAMS_at_APPL.GE.COM]Sent: Tuesday, January 29, 2002 4:28 PMTo: Multiple recipients of list ORACLE-LSubject: Truncating the table
 That is incorrect JoJo. When a table is truncated, all rows are removed. In addition this statement does not generated Undo Blocks, and therefore cannot be rolled back. Use with care.
 The OracleX SQL Reference manual has more information (where X is either 8, 8i or 9 depending on the version you are using)

Matt Adams - GE Appliances - matt.adams_at_appl.ge.com <Discussing a black rectangle silhouetted on a martian landscape> "That is the top of the calibration target, that is NOT in fact a monolith" - NASA TV Commentator - 7/5/1997
> -----Original Message----- >
From: JoJo Al-Zawawi [<A href="mailto:jojo_at_jojo-zawawi.com">mailto:jojo_at_jojo-zawawi.com]
> Sent: Tuesday, January 29, 2002 3:16 PM <FONT
size=2>> To: Multiple recipients of list ORACLE-L > Subject: RE: RTFM questions (formally RE: PL/SQL) > > > I'm a complete newbie, so I may be off-base here, but I believe that
> truncating a table does not delete any data from the
table. > It moves the > cursor up, thereby closing up the "empty space" where data
> has previously > been
deleted. > > --JoJo > >
> -----Original Message----- <FONT
size=2>> Sent: Tuesday, January 29, 2002 10:46 AM > To: Multiple recipients of list ORACLE-L > > > To our resident Oracle Expert who just 6 months (July 26 > 2001) ago posted > the following e-mail
> > <FONT
size=2>> If I remember rightly, deleting rows from the table does NOT free up > tablespace. In order to do that you have to trunctate the > table (although > this of course deletes all data from the table)...I can't for
> the life of me >
remember how you adjust the space the table is actually using > after doing a > delete...(to everyone else) would an analyze work? > > Kev. > > "would and analyze work", leave it out kev(or thomas), my sides are
> splitting Im laughing that much" <FONT
size=2>> "Dude you really are a sage" > I agree dude, you tell them beginners how u became such an expert in 6
> months and didn't come out with stupid ****** comments
like > above, and dude, > explain more about this defrag,space adjusting tool "the
> analyze command": >
> Unfortunately I only got dragged into this
because I just saw > one of your > rude replies and I was having a bad day and didn't like your
> attitude., I > have no
idea about the guy you were slagging off as I don't > get time to read > all the posts, but it looks like 6 months ago you were no > better than him, > and nobody on the list, I recall, treated you with the > contempt that you
> actually deserved. >
> As somebody on the list said "opinions are like
arseholes, > everybody's got > one and they all stink" (classic ,classic quote): > > Anyhow I rest my case Kev (or thomas): > >
> > ----- Original
Message ----- > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> > Sent: Tuesday, January 29, 2002 8:12 PM >
> > > And your point
caller?? > > > > TIC ;o) > > > > -----Original Message----- > > Sent: 29 January 2002 15:41 > > To: Multiple recipients of list ORACLE-L > > >
> > > No offense to anybody, but I think this
is rather arrogant. > The list is
> for > > anybody (unless
the list owner decides it's just for > experienced dba's). I > > am brand new to Oracle, having just started learning it > this week. I can > > easily foresee having what some might consider stupid or > RTFM questions,
> and > > being possibly
unable to find the answer or having problems > deciphering > the > > manual. I myself am somewhat of a Word pro (and my vba > code samples page > is > > the #1 ranked site of its kind on most major search
> engines) and I help >
all > > kinds of newbies out on a Word list of which I am a member, > as do all the
> > other members of the list. Everybody on that
list is > extremely gracious > and > > nobody complains -- and the people with questions get the > help they need, > > with nobody sitting back going "boy are you stupid / lazy". > I am hoping
> > that I can get the same kind of help here, when I
am ready > for it, that I > > and others give on other lists. >
> > > --JoJo >
> > > > >
-----Original Message----- > > Sent: Monday, January 28, 2002 10:20 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > The problem is that if you always ask the group for help,
> you never learn > >
where the information is within the manuals or other reference
> documentation > > and the
group also gets cluttered with trivia. >
> > > I agree with you that the list is here
to help people and I > personally do
> > not mind the odd naive question from a new DBA, but
it is > the "...can't be > > bothered to look up the manual.." attitude that is most
> frustrating. When <FONT
size=2>> > you ask the format of a command (for example) to the list, you are > > potentionally asking 1000's of people the same question who > will all have
> to > > spend the time to
filter these questions - we are all busy > people and I > > personally do not want to use my time in this way. I'm > sure you can see
> > that this is a much more expensive option than
asking the > person next to > > you. > > > > > > Cheers, > > Craig. > > > > > > > > -----Original Message----- > > <<A href="mailto:rabbit_at_emirates.net.ae">mailto:rabbit_at_emirates.net.ae> ] > > Sent: Tuesday, 29 January 2002 2:55 PM > > To: Multiple recipients of list ORACLE-L > > >
> > > Why do you bother being on this List:
The list is here to > help fellow > > DBA'S. Have you never said to one of your collegues , what
> is the format >
of > > this command, or how can I do that, when you simply cant be > bothered to > > look it up in the manual (either your busy or just down right lazy): > > Sometimes this list is useful for just that. So why dont u > lighten up
> dude. > > <FONT
size=2>> > > > -- > > Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com
> > <<A href="http://www.orafaq.com"
target=_blank>http://www.orafaq.com> > >
- > > Author: > > INET: rabbit_at_emirates.net.ae >
 > > > Fat City Network
 Services -- (858) 538-5051 FAX: (858) 538-5051
 > > San Diego,
 California -- Public Internet access / > Mailing Lists > >
 
 > > To REMOVE yourself from this mailing list, send an
 E-Mail message > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). >
 > > > >
 > > > -- > >
 Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com > >
- > > Author: Thomas, Kevin > > INET: Kevin.Thomas_at_calanais.com > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
 > > San Diego,
 California -- Public Internet access / > Mailing Lists > >
 
 > > To REMOVE yourself from this mailing list, send an
 E-Mail message > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). >
 > -- > Please see the
 official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com > --
 > Author: Sam Roberts <FONT
 size=2>> INET: rabbit_at_emirates.net.ae > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
 > San Diego,
 California -- Public Internet access / Mailing Lists >
 
 > To REMOVE yourself from this mailing list, send an
 E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > --
 > Please see the official ORACLE-L FAQ: <A
 href="http://www.orafaq.com" target=_blank>http://www.orafaq.com
 > -- > Author: JoJo
 Al-Zawawi > INET: jojo_at_jojo-zawawi.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists >
 
 > To REMOVE yourself from this mailing list, send an
 E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >

Received on Tue Jan 29 2002 - 15:57:25 CST