Oracle in World: When datafile bytes size greater than maxbytes

From dba_data_files we can check the size of the datafile, the maximum size of the datafile it can grows if AUTOEXTENSIBLE option is set and their status with many other fields.

Describing the dba_data_files yield following result.

SQL> desc dba_data_files;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_NAME                                          VARCHAR2(513)
FILE_ID                                            NUMBER
TABLESPACE_NAME                                    VARCHAR2(30)
BYTES                                              NUMBER
BLOCKS                                             NUMBER
STATUS                                             VARCHAR2(9)
RELATIVE_FNO                                       NUMBER
AUTOEXTENSIBLE                                     VARCHAR2(3)
MAXBYTES                                           NUMBER
MAXBLOCKS                                          NUMBER
INCREMENT_BY                                       NUMBER
USER_BYTES                                         NUMBER
USER_BLOCKS                                        NUMBER
ONLINE_STATUS                                      VARCHAR2(7)

Where,
Bytes represent the size of the data file in bytes.

Status can be AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace was dropped)

Maxbytes represent maximum file size in bytes.

User_Bytes represent the actual size of the datafile that can be usable to store user data. In fact,
USER_BYTES= Actual size of the datafile - Size used to store file related metadata.

Online_status can have following values,
Online status of the file:
- SYSOFF
- SYSTEM
- OFFLINE
- ONLINE
- RECOVER

From the column description we see MAXBYTES should be greater than BYTES.

But let's have a look at the following query.

SQL> select bytes/1024/1024, maxbytes/1024/1024, user_bytes/1024/1024 from dba_data_files where tablespace_name='DATA';

BYTES/1024/1024 MAXBYTES/1024/1024 USER_BYTES/1024/1024
--------------- ------------------ --------------------
400                300             399.9375

We see in the query it shows BYTES size is greater then USER_BYTES which is acceptable. Because 400-399.9375 = 0.0625 Megabytes size is used to store metadata information. But why the MAXBYTES is greater than the size BYTES?

Well, this happens if after creating tablespace and datafile later we resize the datafile to a bigger size than the maxsize.
Following example will make you clear.

1)Create a tablespace whose datafile size is less than maxsize

SQL> create tablespace data datafile 'data01.dbf' size 40M autoextend on maxsize 300M;
Tablespace created.

2)Let's have a query to see bytes and maxbytes size from dba_data_files.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where tablespace_name='DATA';
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
40                300

3)Resize the datafile to a bigger size than MAXBYTES.

SQL> alter database datafile 'data01.dbf' resize 400M;

Database altered.

4)Let's have a query to see BYTES and MAXBYTES information.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where tablespace_name='DATA';

BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
400                300

2 comments:

Nadera Sultana TanyNovember 11, 2009 at 5:27 AM
Great observation. Thank you. Everyday I learn a new thing from your site.
AnonymousNovember 11, 2009 at 9:15 AM
Good observation but why would someone wants to have datafile size set higher to autoextend size when you could set it to autoextend?

Wednesday, November 11, 2009

When datafile bytes size greater than maxbytes

2 comments: