Oracle 11gR2 – DBA_DATA_FILES MAXBYTES value 0

Oracle 11gR2 – DBA_DATA_FILES MAXBYTES value 0

When we query the DBA_DATA_FILES table (for scripting), we need to take extra care while getting data from MAXBYTES column. Notice the MAXBYTES value is 0 for the last 2 datafiles. The reason for this is because during the creation of the datafile, the MAXSIZE was not specified.

SQL> select TABLESPACE_NAME,FILE_NAME,MAXBYTES/1024/1024/1024 “GB” from dba_data_files;

TABLESPACE_NAME FILE_NAME GB
------------------------------ -------------------------------------------------- ----------
SYSTEM +DATA/testdb/datafile/system.269.894474543 10
AUDIT_TBS +DATA/testdb/datafile/audit_tbs.270.900889041 0
AUDIT_TBS +DATA/testdb/datafile/audit_tbs.271.909620769 0

Now, when we create the datafile is MAXSIZE specified:

SQL> alter tablespace AUDIT_TBS add datafile '+DATA' size 20G autoextend on maxsize 30G;

Let’s review the DBA_DATA_FILES output again:

SQL> select TABLESPACE_NAME,FILE_NAME,MAXBYTES/1024/1024/1024 "GB" from dba_data_files;

TABLESPACE_NAME FILE_NAME GB
------------------------------ -------------------------------------------------- ----------
SYSTEM +DATA/spa/datafile/system.269.894474543 10
AUDIT_TBS +DATA/spa/datafile/audit_tbs.270.900889041 0
AUDIT_TBS +DATA/spa/datafile/audit_tbs.271.909620769 0
AUDIT_TBS +DATA/spa/datafile/audit_tbs.272.909622999 30

That’s all for the time being!

Regards,
Wei Shan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s