Home » SQL & PL/SQL » SQL & PL/SQL » Question regarding time required to truncate tables have data as big as 125GB.
Question regarding time required to truncate tables have data as big as 125GB. [message #280233] Mon, 12 November 2007 22:03 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

I am having 2 tables in which are a part of a single tablespace. This tablespace is currently occupying 125Gb of space and since these tables are no longer required, i want to truncate them. Now the question is, how much time it will take to truncate tables having so much of data. Can someone please comment on this.

thanks
Re: Question regarding time required to truncate tables have data as big as 125GB. [message #280235 is a reply to message #280233] Mon, 12 November 2007 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Less than 5 seconds total for both tables combined.
The TRUNCATE just resets the High Water Mark to the start of the file.

[Updated on: Mon, 12 November 2007 22:09] by Moderator

Report message to a moderator

Re: Question regarding time required to truncate tables have data as big as 125GB. [message #280271 is a reply to message #280233] Tue, 13 November 2007 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Ana's answer.
As these tables are no more used, this will take few seconds.
If they were used, it may take a while as Oracle has to checkpoint the table, that is flush all blocks that are in cache on disk and this requires many latches and so waits.

Regards
Michel
Re: Question regarding time required to truncate tables have data as big as 125GB. [message #280360 is a reply to message #280233] Tue, 13 November 2007 05:49 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Thanks Ana and Michel
Re: Question regarding time required to truncate tables have data as big as 125GB. [message #280491 is a reply to message #280360] Tue, 13 November 2007 20:00 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I have found partitioned tables take a bit longer to truncate, but still typically less than 30sec.
Re: Question regarding time required to truncate tables have data as big as 125GB. [message #280539 is a reply to message #280233] Wed, 14 November 2007 01:04 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If the tables are no longer required, you should drop them, not just truncate.
Previous Topic: AUTHID CURRENT_USER
Next Topic: function restriction
Goto Forum:
  


Current Time: Thu Nov 07 16:51:17 CST 2024