RE: EM, capacity planning and monitoring

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Tue, 14 Sep 2010 15:50:34 +0200
Message-ID: <4814386347E41145AAE79139EAA39898102B653BD5_at_ws03-exch07.iconos.be>

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

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

Received on Tue Sep 14 2010 - 08:50:34 CDT