Truncate with Toad and Rowid

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 23 Jan 2003 08:48:19 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70335756A@lnewton.leeds.lfs.co.uk>

Morning Crockydile,

(1) No not possible. Truncate moves the high water mark for the table
down to the lowest position it can based on the tables' MINEXTENTS setting. I use TOAD all day, every day and truncate tables, and I've never seen phantom rows. Is it possible that someone or something is inserting the rows into the table just after you have truncated it ?

(2) All DDL commands (create xxx, alter xxx, drop xxx, truncate xxx,
etc) effectively perform two commits, one at the start and one at the end, (If I remember what Tom Kyte said here a while back). So if you update some data and then attempt to do some DDL, and it fails, your updates are still committed. Here is an example :

CREATE TABLE TEST (A NUMBER)
Table created  

• Some data that will be committed ... INSERT INTO TEST VALUES (1) 1 row inserted

INSERT INTO TEST VALUES (2)
1 row inserted  

INSERT INTO TEST VALUES (3)
1 row inserted  

COMMIT
Commit complete  

• Some data that is implicitly committed ... INSERT INTO TEST VALUES (4) 1 row inserted

INSERT INTO TEST VALUES (5)
1 row inserted  

INSERT INTO TEST VALUES (6)
1 row inserted  

SELECT * FROM TEST
         A

---

         1
         2
         3
         4
         5
         6

6 rows selected    

• now cause an error CREATE TABLE TEST (B NUMBER) ORA-00955: name is already used by an existing object SELECT * FROM TEST A

  ---

  1 2 3 4 5 6 6 rows selected

ROLLBACK
Rollback complete  

SELECT * FROM TEST
         A

---

         1
         2
         3
         4
         5
         6

6 rows selected    

So, you can see that even though the create table failed, the data with values 4, 5 and 6 has been committed.  

(3) truncate table xxx reuse storage is what you do when you want to
clear out the data in a table, but keep all currently allocated extents. The default is to free up all the extents allocated except for those specified by the MINEXTENTS parameter on the table's creation. Check out http://www.jlcomp.demon.co.uk/faq/trunc_del.html for more details.

Keeping the allocated extents is useful if you are importing data into the table and you want to clear out existing data first. By specifying 'reuse storage' you avoid the recursive sql (and time and resources) deleteing from the dictionary only to have the import re-create everythiing again dynamically. Of course, if you are using LMTs then this isn't such a big problem.

HTH Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: crockydile_at_hotmail.com (BG) [mailto:crockydile_at_hotmail.com] Posted At: Wednesday, January 22, 2003 11:36 PM Posted To: server
Conversation: Truncate with Toad and Rowid Subject: Truncate with Toad and Rowid

Hello.

I am having a problem with what looks like data corruption either via poor programming or some other cause.

(1) Is it possible after a truncate and then doing inserts to have the
a ghost image of truncated data corrupt your newly inserted data.

<snip>

(2) Can someone tell me if a commit is needed after a truncate?

(3) difference between "resuse space" and free "space"

Thank you
bg Received on Thu Jan 23 2003 - 02:48:19 CST