SQL & PL/SQL

Hi Guys,

Here is a query regarding truncate statement..

first a little intro to my pc..

I have a Windows NT Server Machine with Oracle 8i installed.
(Release 8.1.5.0.0).

PL/SQL Release 8.1.5.0.0

I was simply testing delete and truncate statements and I wanted to check how much powerful the truncate statement is as compared to the ordinary delete statement.

For this I created a big table with rows 2621440.

SQL> select count(*) from dummy_table;

COUNT(*)
---------
2621440

real: 21000

Then I tried to delete the rows from this table........

SQL> delete from dummy_table;
delete from dummy_table
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 2
ORA-01628: max # extents (121) reached for rollback segment RB1

real: 314693

SQL> commit;
Commit complete.

real: 70

SQL> select count(*) from dummy_table;

COUNT(*)
---------
2621440

real: 25467

I again tried to delete, but again the same message "failed to extend rollback segment number 3 " came.

SQL> delete from dummy_table
2 where rownum < 1000000;
delete from dummy_table
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01628: max # extents (121) reached for rollback segment RB2

real: 458550

Then I tried the truncate statement.

SQL> truncate table dummy_table
2 /

Table truncated.

real: 420
SQL> commit;

Commit complete.

real: 70

During all these, I was monitoring the size of my Oracle Home Directory.
It started from 650 MB to 720 MB and like this it grew up to 860 MB !!!

Even after truncating the data in the table "dummy_table", the size 860 MB remained the same.

Then I tried to find where this data is lying..

I ran a text search giving a few of my table values as the search pattern in my windows NT PC.

And I found a file user01.dbf which has all the data i tried to delete from the dummy_table.
And the size of this file is 150 Mb.

Please give me a solution by which I can delete the data from this file..

Waiting for ur answers,

rgds,
Anagha..