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