Oracle DBA

In Oracle, a tablespace is a logical storage container used to manage and organize the physical storage of database objects such as tables, indexes, and partitions. When working with tablespaces, you may come across several important attributes, including MAXBYTES, AUTOEXTENSIBLE, and INCREMENT_BY. Let’s break down each of these attributes:

1. MAXBYTES:
   • MAXBYTES is an attribute that defines the maximum size to which a data file or tempfile associated with a tablespace can grow.
   • It specifies the maximum allowable size in bytes for a data file. Once a data file reaches this size, it cannot grow any larger, even if it has AUTOEXTENSIBLE enabled.
   • MAXBYTES is typically set to a value that represents the upper limit for the data file’s size to prevent it from growing uncontrollably and potentially filling up the file system or storage device.
2. AUTOEXTENSIBLE:
   • AUTOEXTENSIBLE is a tablespace attribute that determines whether a data file or tempfile can automatically grow in size when it becomes full.
   • When AUTOEXTENSIBLE is set to “YES,” Oracle will automatically increase the size of the data file when it reaches its maximum size (as defined by MAXBYTES) and more space is needed.
   • The growth increment for the file is determined by the INCREMENT_BY attribute.
3. INCREMENT_BY:
   • INCREMENT_BY is an attribute that specifies the size by which a data file or tempfile will grow when AUTOEXTENSIBLE is enabled and more space is needed.
   • It represents the growth increment in bytes or megabytes, depending on how it’s specified.
   • For example, if INCREMENT_BY is set to 100MB and a data file needs to grow because it’s reaching its maximum size, Oracle will add 100MB of space to the file.
   • The value of INCREMENT_BY should be chosen carefully to balance between minimizing fragmentation and efficiently using storage resources.

Let’s walk through the steps to create a demonstration of the MAXBYTES, AUTOEXTENSIBLE, BYTES, and INCREMENT_BY attributes in Oracle tablespaces:

Step 1: Create a Bigfile Tablespace: In this step, we create a bigfile tablespace named “my_big_ts” with an initial size of 100MB. It is set to AUTOEXTEND, increasing in size by 1MB each time, and the maximum size is capped at 150MB (as specified).

SQL> create bigfile tablespace my_big_ts datafile '+DATA' size 100m autoextend on next 1m maxsize 150m;

Tablespace created.

Step 2: Check Parameter Values: You can check the parameter values of the newly created tablespace using the following SQL query:

SQL> select MAXBYTES,AUTOEXTENSIBLE,BYTES,INCREMENT_BY from dba_data_files where tablespace_name='MY_BIG_TS';

  MAXBYTES AUT      BYTES INCREMENT_BY
---------- --- ---------- ------------
 157286400 YES  104857600          128

Step 3: Calculate and Verify INCREMENT_BY: The INCREMENT_BY value can be calculated based on the database block size. Multiply value of db_block_size with INCREMENT_BY will give size that we specified in create tablespace statement (next 1m).

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> select 8192*128/1024/1024 from dual;

8192*128/1024/1024
------------------
                 1

Step 4: Resize the Datafile: Below command resizes the datafile associated with the tablespace to 200MB.

SQL> select file_name from dba_data_files where tablespace_name='MY_BIG_TS';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDBONE/FD3ABFEBBDD704B1E0537A0BA8C0133F/DATAFILE/pawan.279.1138534237

SQL> alter database datafile '+DATA/CDBONE/FD3ABFEBBDD704B1E0537A0BA8C0133F/DATAFILE/pawan.279.1138534237' resize 200m;

Database altered.

SQL> select MAXBYTES,AUTOEXTENSIBLE,BYTES,INCREMENT_BY from dba_data_files where tablespace_name='MY_BIG_TS';

  MAXBYTES AUT      BYTES INCREMENT_BY
---------- --- ---------- ------------
 157286400 YES  209715200          128

Step 6: Create Table and Move to the New Tablespace This step creates a table named “abc” and moves it to the “my_big_ts” tablespace. After table is moved to newly created tablespace insert data and check the limit.

SQL> create table abc as select * from dba_objects where 1=2;

Table created.

SQL> alter table abc  move tablespace MY_BIG_TS;

Table altered.

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='MY_BIG_TS';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> insert into abc select * from dba_objects;

72390 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='MY_BIG_TS';

SUM(BYTES)/1024/1024
--------------------
                  12

SQL> insert into abc select * from dba_objects;

72390 rows created.

SQL> insert into abc select * from abc;

144780 rows created.

SQL> /

289560 rows created.

SQL> /

579120 rows created.

SQL> /
insert into abc select * from abc
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.ABC by 1024 in tablespace MY_BIG_TS


SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name='MY_BIG_TS';

SUM(BYTES)/1024/1024
--------------------
                 192

SQL>

Conclusion: When you create a tablespace with AUTOEXTENSIBLE set to “ON” and specify a maximum size limit using the MAXSIZE parameter, Oracle will automatically extend the tablespace as needed to accommodate data growth up to the specified maximum size. However, if the tablespace reaches its MAXSIZE limit and you extend it beyond that limit due to space issues, Oracle does not enforce the MAXSIZE constraint anymore. Instead, it continues to allocate space as needed within the available storage resources, effectively nullifying the MAXSIZE limit for that tablespace.

In other words, once you exceed the MAXSIZE limit of a tablespace with AUTOEXTENSIBLE enabled, Oracle prioritizes accommodating data growth over enforcing the originally defined maximum size constraint. This behavior allows the database to continue functioning despite space constraints, but it’s essential to monitor and manage storage resources carefully to avoid unexpected space exhaustion issues.