Example of Oracle Database Management Technology

The following articles mainly describe the space-related management skills of Oracle databases. We all know that the following statements can be used to view free space in Oracle databases, the following is a detailed description of the relevant content. I hope you will find some gains.

 
 

  
  
SQL > select * from dba_free_space   
  
  
where tablespace_name='SFSJTS'   
  
  
2 order by block_id;   
  
  
TABLESPACE FILE_ID BLOCK_ID BYTESBLOCKS   
  
  
_NAME   
  
  
SFSJTS 6 133455 1064960 130   
  
  
SFSJTS 6 133719 1032192 126   
  
  
SFSJTS 6 133845 1064960 130   
  
  
SFSJTS 6 135275 1064960 130   
  
  
SFSJTS 6 135721 606208 74   
  
  
SFSJTS 6 139877 901120 110   
  
  
SFSJTS 6 143497 737280 90   
  
  
SFSJTS 6 220248 737280 90   
  
  
SFSJTS 6 246228 491520 60   
  
  
SFSJTS 6 261804 1064960 130   
  
  
10 rows selected.   
 
 

We can use the command results to estimate the real number of adjacent free spaces. Add the number of free BLOCKS (BLOCKS) to each row with the starting fast id (BLOCK_ID). If it is equal to the block id (BLOCK_ID) of the next row, the two rows are continuous. In the second and third rows of the above examples, 133719 + 126 = 133845, while 1338456 + 130! Since block_id is 135275, there is a continuous space of 133719 + 126 = 130 blocks.

In the background of the Oracle database, the system monitor periodically merges adjacent blocks in free space to obtain larger contiguous blocks. DBA can use SQL commands to do this:

 
 

  
  
alter tablespace tablespace_name coalesce;  
 
 

Oracle space management has an important impact on the performance of Oracle databases. Its management methods are worthy of our careful research.

Article by: http://www.programbbs.com/doc/class10-3.htm