Re: Truncate with Toad and Rowid

Thanks to everyones education. It appears my problem lies in another issue. I am currently testing to a new table to see if the problem appears there also.

thanks again
bg

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA70335756A_at_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 - 11:08:13 CST