Five-minute master of Oracle tablespace

Before giving you a detailed description of free space for Oracle tablespaces, let us first understand the free space for Oracle tablespaces, and then fully introduce the free space for Oracle tablespaces. In Oracle databases, DBAs can observe tables or views to understand the usage of the current space and make possible adjustments.

Free Space for Oracle tablespace

By observing the free space of the Oracle tablespace, we can determine whether the space allocated to a tablespace is too much or not. See the following statements:

 
 

  
  
SQL > select a.file_id "FileNo"，a.tablespace_name  
  
  
"Tablespace_name"，  
  
  
a.bytes "Bytes"，a.bytes-sum(nvl(b.bytes，0)) "Used"，  
  
  
sum(nvl(b.bytes，0)) "Free"，  
  
  
sum(nvl(b.bytes，0))/a.bytes*100 "%free"  
  
  
from dba_data_files a， dba_free_space b  
  
  
where a.file_id=b.file_id(+)  
  
  
group by a.tablespace_name ，  
  
  
a.file_id，a.bytes order by a.tablespace_name;  
  
  
File Tablespace  
  
  
No _nameBytes Used Free %free  
  
  
IDX_JF .146E+09 849305600 1.297E+09 60.431806  
  
  
JFSJTS 2.146E+09 1.803E+09 343793664 16.016961  
  
  
JFSJTS 2.146E+09 1.359E+09 787431424 36.685546  
  
  
RBS523239424 359800832 163438592 31.235909  
  
  
RBS1.610E+09 1.606E+09 3104768 .19289495  
  
  
RBSJF 3.220E+09 2.716E+09 504356864 15.662396  
  
  
SFGLTS 2.146E+09 1.228E+09 918159360 42.776014  
  
  
SFSJTS 2.146E+09 1.526E+09 620093440 28.889457  
  
  
SYSTEM 523239424 59924480 463314944 88.547407  
  
  
TEMP 523239424294912 522944512 99.943637  
  
  
TOOLS 15728640 12582912 314572820  
  
  
USERS 7340032 81927331840 99.888393  
  
  
rows selected. 
 
 

It can be seen that only 0.19% of the allocated space in the tablespace RBS with 12 FileNo is not used. This proportion is too small, and in the tablespace such as SYSTEM and TEMP, up to 80% of the space is not used. For production databases, the Oracle tablespace settings are somewhat high.

For free space management, we have the following suggestions: using the Export and Import commands to unload and load tablespaces can release a large amount of space, so as to alleviate the need for additional data files. If the proportion of free space in the tablespace that contains tables with high insert and update activities falls below 15%, more space is required for the tablespace. For a tablespace that is basically a static table data, if there is more than 20% free space, you can consider reducing the amount of file space allocated to it. It is difficult to reduce the space of the SYSTEM tablespace because it is necessary to recreate the database. The preceding section describes free space for Oracle tablespace.

1. In-depth Oracle user management and specifications
2. Two implementation methods for Oracle user unlocking commands
3. The founder of MySQL suggested selling MySQL to Oracle
4. Oracle Financial Management System: Accounting Business Cycle
5. Step 4: Change the Oracle user name