RE: EM, capacity planning and monitoring

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

repost

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

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

Other related posts:

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