Pages

Monday, October 13, 2014

Understanding bytes, max_bytes, and user_bytes in DBA_DATA_FILES by example

General:
Consider following scenario:
Tablespace has one datafile.

Datafile was defined as AUTOEXTENSIBLE=NO, with size 100M
Tablespace has reached in maximum size of 100M
Now, resize the datafile to 300Mb.

Steps
1. Initial situation
SELECT tablespace_name, 
       autoextensible, 
       bytes/1024/1024 AS BYTES_MB, 
       maxbytes/1024/1024 AS MAXBYTES_MB, 
       user_bytes/1024/1024 AS USER_MB
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'MY_TABLESPACE'

TABLESPACE_NAME      AUTOEXTENSIBLE   BYTES_MB MAXBYTES_MB    USER_MB
-------------------- -------------- ---------- ----------- ----------
MY_TABLESPACE        NO                    100           0    99.9375


SELECT (SUM(bytes))/1024/1024 AS bytes_MB
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME = 'MY_TABLESPACE'

  BYTES_MB
----------
    98.875

2. Now add another 200M to the same datafile:

ALTER DATABASE DATAFILE '/oracle_db/db1/db_inst/MY_DATAFILE_01.dbf' RESIZE 300M;

3. Check new situation.
SELECT tablespace_name, 
       autoextensible, 
       bytes/1024/1024 AS BYTES_MB, 
       maxbytes/1024/1024 AS MAXBYTES_MB, 
       user_bytes/1024/1024 AS USER_MB
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'MY_TABLESPACE';

TABLESPACE_NAME       AUTOEXTENSIBLE   BYTES_MB MAXBYTES_MB    USER_MB
--------------------- -------------- ---------- ----------- ----------
MY_TABLESPACE         NO                    300           0   299.9375


SELECT bytes/1024/1024 AS FREE_MB
  FROM DBA_FREE_SPACE;


TABLESPACE_NAME                   FREE_MB
------------------------------ ----------
MY_TABLESPACE                     200.125

Conclusion.
The data in DBA_DATA_FILES is misleading.
A. MAXBYTES for non autoextensible files is always zero.
B. USER_BYTES represent the max available space for oracle, not the actual used space.
C. The BYTES represent the used space, from OS prospective, i.e. same values as in USER_BYTES plus some small overhead.
D. To get the actual used space, need to do
DBA_DATA_FILE.user_bytes - DBA_FREE_SPACE.bytes

No comments:

Post a Comment