Tablespace Monitoring

AUTOEXTEND ON Next Size
In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component.

In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace).

But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater.

Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occassion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.

In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get's created with both the initial and increment at 100MB and AutoExtend ON. However, for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile. If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not specify the incremental size, Oracle defaults it to 1 Oracle Block.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;


SQL> alter system set db_create_file_dest='/var/tmp';
SQL> create tablespace an_omf_tbs;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf';
create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'


SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' size 100M;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

SQL> create tablespace ANOTHER_TBS datafile '/oracle_fs/Databases/ORT24FS/another_tbs.dbf' size 100M autoextend on ;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

You would be well-adviced to remember that when you create or add a non-OMF datafile, you should specify the Increment size with the AutoExtend ON. Else, you might suffer the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)

SQL> create tablespace LAST_TBS datafile '/oracle_fs/Databases/ORT24FS/last_tbs.dbf' size 100M autoextend on next 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

Tablespace LAST_TBS's datafile is created with a meaningful Increment for AutoExtend.


otes on Specifying Datafiles and Tempfiles

*

For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.
*

You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.
*

If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.


http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/statements_7003.htm
---------------------------------------------------------------------------
CREATE TABLESPACE
DATAFILE SIZE sayı K|M [AUTOEXTEND OFF | {AUTIOEXTEND
ON | NEXT sayı K|M | MAXSIZE UNLIMITED | sayi K|M |}
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY ]

Kullanılan ifadelerin açıklamaları :
Tablespace ismi : Oluşturulacak tablespace verilecek addır.
DATAFILE : Tablespace’i oluşturan veri dosyasının tanımlandığı bölümdür.
Dosya ismi : DATAFILE’ın path’ini belirtildiği bölümdür.
AUTOEXTEND OFF : Datafile otomatik artışını iptal etmekte kullanılan komuttur.

AUTOEXTEND ON : Datafile otomatik artışını sağlayan komuttur.
NEXT : Datafile’ ın genişleme miktarı.
MAXSIZE : Datafile’ın genişleyebileceği en son miktardır.
UNLIMITED : Datafile’ın sınırsız büyüyebileceğini göstermektedir. Belirtilmese dahi standart olarak seçilidir.
ONLINE : Tablespace’i oluşturduktan hemen sonra aktif olacağını belirten bölümdür. Standart olarak seçilidir.

OFFLINE : Tablespace’i oluşturduktan hemen sonra pasif olacağını belirten bölümdür.

PERMANENT : Tablespace içinde tutulacak olan nesnelerin kalıcı olarak saklanacağını belirten bölümdür. Standart olarak seçilidir.

TEMPORARY : Tablespace’te geçici nesnelerin tutulacağının belirtildiği bölümdür.

NOT : Tablespace oluşturmak ve yönetmek için STORAGE MANAGER kullanılmasını tavsiye ederiz.