Re: Datafile size - Is bigger better?

At 03:57 PM 11/14/2006, JayDBA wrote:
>Can I trigger a discussion on the pros and cons of datafile
>max-size. I am in a shop where the datafile size is limited to 2gb
>on a 64-bit platform (Raided) and I am trying to weigh the pros and
>cons of having a larger file limit.

[..]

Cons:

[...]

>3. On locally managed files, we loose header space equal to the
>extent size. E.g. on a datafile with uniform extent sizing of 128M a
>2gb file would waste 6% space / file. This number can run into
>gigabytes on systems with 100's of 1000's of files

That is not true. The bitmap(s) for managing the space are fixed at 64K. If you create the datafiles for LMTs as an integral multiple of the uniform extent size + 64K, you can use the full size of the tablespace. If not Oracle will take the first extent and use 64K of it for the bitmap but the remainder is not usable - until you extent the datafile size by 64K:

SQL> CREATE TABLESPACE "TEST" DATAFILE
'/u01/ORACLE/ora102/test01.dbf' SIZE 100M reuse

   LOGGING ONLINE PERMANENT BLOCKSIZE 8192    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT MANUAL /
Tablespace created.

SQL> CREATE TABLESPACE "TEST2" DATAFILE '/u01/ORACLE/ora102/test201.dbf' SIZE 102464K reuse   LOGGING ONLINE PERMANENT BLOCKSIZE 8192    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT MANUAL /
Tablespace created.

SQL> create table test (filler varchar2(255)) tablespace test storage(initial 100M);
create table test (filler varchar2(255)) tablespace test storage(initial 100M) *
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 9 in tablespace TEST

SQL> create table test2 (filler varchar2(255)) tablespace test2 storage(initial 100M);

Table created.

SQL> create table test (filler varchar2(255)) tablespace test storage(initial 90M);

Table created.

SQL> select tablespace_name, bytes/1024/1024 MB from dba_free_space where tablespace_name in ('TEST','TEST2');

no rows selected

As you can see (actually by not seeing anything), 10m, a full extent, is lost in tablespace TEST.

19:06:30 ora102.scott> alter database datafile '/u01/ORACLE/ora102/test01.dbf' resize 102464K 19:07:20 2 /

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB from dba_free_space where tablespace_name in ('TEST','TEST2');

TABLESPACE_NAME                        MB
------------------------------ ----------
TEST                                   10

1 row selected.

But by adding the 64K required for the bitmap to the datafile the "missing" extent magically appears.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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