CREATING TABLESPACE

Creating and Increase the size of tablespace by adding datafiles.(AUTOEXTEND is disabled):

Normally ,we can the increase the tablespace size by adding the datafile to the tablesapce.

Create test tablespace with auntoextend is off,

SQL> create tablespace test datafile 'E:\APP\USER\ORADATA\DEV\test01.DBF' size 1
0M;

Tablespace created.

Auto extend is off:

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO

Query the created tablespace :

SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE

Adding the datafile by increasing the tablespace,

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST02.DBF' siz
e 20M;

Tablespace altered.


SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE


FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST02.DBF
        11 TEST                             20971520       2560 AVAILABLE
          11 NO           0          0            0   19922944        2432
ONLINE

Check the free size of the tablespace

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   30         28
SYSTEM                                780        9.5

Adding the datafile to the tablespace.

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST03.DBF' siz
e 40M;

Tablespace altered.

Now check the size after adding the datafile to tablespace:

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   70         67
SYSTEM                                780        9.5


SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO
TEST                           NO
TEST                           NO

7 rows selected.

Best Regards.

How to enable tablespace autoextend on

Enabling and Disabling Automatic Extension for a Datafile:

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

• Reduces the need for immediate intervention when a tablespace runs out of space
• Ensures applications will not halt or be suspended because of failures to allocate extents

To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

SQL> select tablespace_name ,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES

You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:

• CREATE DATABASE
• ALTER DATABASE
• CREATE TABLESPACE
• ALTER TABLESPACE

You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.

The following example enables automatic extension for a datafile added to the users tablespace:

ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M;

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

The next example disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' 
    AUTOEXTEND OFF;

Manually Resizing a Datafile

You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.

Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

In the next example, assume that the datafile /u02/oracle/rbdb1/users03.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.

The following statement decreases the size of datafile /u02/oracle/rbdb1/users03.dbf:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
   RESIZE 250M;

Best Regards.

CREATE TABLESPACE WITH AUTO Extend ON

Create a permanent tablespace.

Syntax:

   CREATE [UNDO] TABLESPACE tablespace_name
      DATAFILE Datafile_Options Storage_Options;

The Autoextend Maxsize clause will default to UNLIMITED if no value is specified.

Storage_Options:

    DEFAULT [COMPRESS|NOCOMPRESS] STORAGE storage_clause
    MINIMUM EXTENT int {K|M}
    BLOCKSIZE int K
    LOGGING | NOLOGGING
    FORCE LOGGING
    ONLINE | OFFLINE
    PERMANENT | TEMPORARY
    EXTENT MANAGEMENT {DICTIONARY |
       LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }
    SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}

Examples

-- With Autoextend:

CREATE TABLESPACE ts_mydemo DATAFILE
'/data/ts_mydemo01.dbf' SIZE 50M,
'/data/ts_mydemo02.dbf' SIZE 64M
logging
autoextend on
next 32m maxsize 2048m
extent management local;

-- With specified datafile sizes:

CREATE TABLESPACE ts_myapp DATAFILE
'/data/ts_myapp01.dbf' SIZE 200M,
'/data/ts_myapp02.dbf' SIZE 500M
logging
autoextend off
extent management local;

-- Undo tablespace

CREATE UNDO TABLESPACE ts_undo01 DATAFILE
'/data/ts_undo01.dbf SIZE 50000M REUSE
autoextend on

RETENTION NOGUARANTEE;

Retention guarantee can also be enabled for UNDO tablespaces with the option RETENTION GUARANTEE, this should be used with caution - it can easily cause updates to fail due to a lack of space in the undo tablespace.

Best Regards.