Re: drop table

On 17 Dec 2004 05:15:53 -0800, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

>
>The 10g recycle bin feature has significant space costs associated with
>it.

No, it hasn't.

When a table is dropped in 10g, it's space is immediately recorded as free (shown in dba_free_space).

Oracle tries to keep the space for recycling though, exactly until it needs to extend the datafile(s) of the tablespace in question, at which point it chooses to use the space of the dropped table instead.

Proof :

SQL> create tablespace test datafile
'/oracle/oradata/a/datafile/test_001.dbf' size 32m uniform size 1m;

TabelomrĠde er oprettet.

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

                  31

SQL> create table foo (c2 number) tablespace test;

Tabel er oprettet.

SQL> alter table foo allocate extent;

Tabel er µndret.

SQL> alter table foo allocate extent;

Tabel er µndret.

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

                  28

SQL> drop table foo;

Tabel er droppet.

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

                  31

And that makes the recycle bin feature even less usable : You can recover a dropped table for some time, but you seldom can predict how long, so how much is it worth in practice ?! And if it becomes a common misunderstanding to believe that the recycle bin keeps your table(s) forever after being dropped, unpleasant surprises are in the horizon....

• Kenneth Koenraadt