How to view the Oracle tablespace size

The method for viewing the Oracle tablespace size should be what we need to know. The following describes how to view the Oracle tablespace size for your reference.

In database management, DBA may encounter insufficient disk space, which is common.

-- 1 check the Oracle tablespace size-percentage used.

 
 

  
  
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  
  
  
from  
  
  
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,  
  
  
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b  
  
  
where   a.tablespace_name=b.tablespace_name  
  
  
order   by   ((a.bytes-b.bytes)/a.bytes)   desc 
 
 

"Sum MB" indicates the total disk space occupied by all data files in the tablespace in the operating system.
For example, if the test tablespace has two data files, datafile1 is 300 MB, and datafile2 is 400 MB, the "Sum MB" of the test tablespace is 700 MB.
"Userd MB" indicates how many tablespaces are used
"Free MB" indicates the number of tablespaces remaining.
"Percent_user" indicates the percentage used

-- 2 For example, from 1, we can see that the percentage of the MLOG_NORM_SPACE tablespace used has reached 90% or more. We can check whether the total number of data files in the tablespace is automatically extended and the maximum value can be automatically expanded.

 
 

  
  
select   file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB"   from   dba_data_files  
  
  
where tablespace_name='MLOG_NORM_SPACE'; 
 
 

-- 3 For example, the current size of the MLOG_NORM_SPACE tablespace is 19 GB, but the maximum size of each data file is only 20 GB. The data file is about to fill up, and the tablespace data file can be added.
Run the df-g command in UNIX and Linux operating systems (view the available disk space)
Obtain the statement for creating a tablespace:

 
 

  
  
select   dbms_metadata.get_ddl('TABLESPACE','MLOG_NORM_SPACE')   from   dual; 
 
 

-- 4 check whether the disk space is sufficient and add a data file

 
 

  
  
alter   tablespace   MLOG_NORM_SPACE  
  
  
add   datafile   '/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'  
  
  
size   10M   autoextend   on   maxsize   20G 
 
 

-- 5. Verify the added data file

 
 

  
  
select   file_name,file_id,tablespace_name   from   dba_data_files  
  
  
where   tablespace_name='MLOG_NORM_SPACE' 
 
 

-- 6 if you delete a tablespace data file:

 
 

  
  
alter   tablespace   MLOG_NORM_SPACE  
  
  
drop    datafile '/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf' 
 
 

Oracle tablespace creation syntax

This section describes Oracle partition indexes in detail.

Oracle COMMIT statement processing sequence

Using replacement variables to improve Oracle Interaction

ORACLE system table and data dictionary View