Kenneth Koenraadt wrote:
> 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
Nice demo, and a very sensible point at the end, too.
Thanks, Kenneth.
Regards
HJR
Received on Fri Dec 17 2004 - 14:11:50 CST
