RE: EM, capacity planning and monitoring

  • From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
  • To: "ganstadba@xxxxxxxxxxx" <ganstadba@xxxxxxxxxxx>, 'Oracle L' <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 14 Sep 2010 15:06:25 +0200

Hi,

I just did a quick test, which proved that (at least on 10.2.0.3 on linux 32 
bit), the space taken by objects in the recycle bin will be reclaimed before 
the datafile will be extended.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create tablespace bin_test
  2  extent management local
  3  segment space management auto
  4  datafile '/opt/oracle/oradata/GUNNAR/bin_test_01.dbf' size 25M autoextend 
on next 25M maxsize 100M
  5  /

Tablespace created.

SQL> alter user fdh quota unlimited on bin_test;

User altered.

SQL> connect fdh/fdh
Connected.
SQL> create table bin_test
  2  (cfield1 char(2000))
  3  tablespace bin_test
  4  /

Table created.

SQL> insert into bin_test
  2  select 'x'
  3  from all_objects
  4  where rownum <= 7000
  5  /

7000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from dba_segments where owner = 'FDH' and 
segment_name = 'BIN_TEST';

BYTES/1024/1024
---------------
             19

SQL> drop table bin_test;

Table dropped.

SQL> select object_name, ts_name, space from user_recyclebin where 
original_name = 'BIN_TEST';

OBJECT_NAME                    TS_NAME                             SPACE
------------------------------ ------------------------------ ----------
BIN$kCP86KZp8qLgQAAK9Qp1Kg==$0 BIN_TEST                             2432

SQL> create table bin_test2
  2  (cfield1 char(2000))
  3  tablespace bin_test
  4  /

Table created.

SQL> insert into bin_test2
  2  select 'x'
  3  from all_objects
  4  where rownum <= 7000
  5  /

7000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from dba_segments where owner = 'FDH' and 
segment_name = 'BIN_TEST2';

BYTES/1024/1024
---------------
             19

SQL> select object_name, ts_name, space from user_recyclebin where 
original_name = 'BIN_TEST';

no rows selected

SQL> set linesize 130
SQL> column file_name format a75
SQL> select file_name, bytes from dba_data_files where tablespace_name = 
'BIN_TEST';

FILE_NAME                                                                       
 BYTES
--------------------------------------------------------------------------- 
----------
/opt/oracle/oradata/GUNNAR/bin_test_01.dbf                                    
26214400


Regards,
 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Michael McMullen
Sent: dinsdag 14 september 2010 14:21
To: 'Oracle L'
Subject: RE: EM, capacity planning and monitoring

I just got a db that had filled up the filesystem. The server is down right
now so I can't check the version #. Anyways, one user using one tablespace,
everyday their code, does drop table and then recreates the table. The
datafiles were autoextend, the table was just a couple of meg and almost
100GB of recycle bin.
I moved the table to a new tablespace with 10Mb allocated.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of D'Hooge Freek
Sent: Monday, September 13, 2010 4:56 PM
To: s.cislaghi@xxxxxxxxx; Oracle L
Subject: RE: EM, capacity planning and monitoring

Stefano,

Not an answer to your question, but the recycle bin is supposed to be
cleaned automatically when Oracle needs space in that tablespace. I even
think that Oracle will first clean out the recycle bin and only after that
increase the datafile (if autoextend has been enabled).
In that sense it is normal that no special attention is set to the recycle
bin.

Are you sure that the errors you got where caused by the recycle bin?


Regards,



--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2010