Three Tips for Oracle database space management

We all know that in Oracle databases, DBAs can make possible adjustments and decisions by observing tables or related views to understand the specific usage of the current space. The following describes the main content of the article.

I. Free Space for tablespace

By observing the free space of a table space, you 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",   
  
  
2 a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",   
  
  
3 sum(nvl(b.bytes,0)) "Free",   
  
  
4 sum(nvl(b.bytes,0))/a.bytes*100 "%free"   
  
  
5 from dba_data_files a, dba_free_space b   
  
  
6 where a.file_id=b.file_id(+)   
  
  
7 group by a.tablespace_name ,   
  
  
8 a.file_id,a.bytes order by a.tablespace_name;   
  
  
File Tablespace   
  
  
No _nameBytes Used Free %free   
  
  
11IDX_JF .146E+09 849305600 1.297E+09 60.431806   
  
  
9 JFSJTS 2.146E+09 1.803E+09 343793664 16.016961   
  
  
10JFSJTS 2.146E+09 1.359E+09 787431424 36.685546   
  
  
2 RBS523239424 359800832 163438592 31.235909   
  
  
12RBS1.610E+09 1.606E+09 3104768 .19289495   
  
  
8 RBSJF 3.220E+09 2.716E+09 504356864 15.662396   
  
  
7 SFGLTS 2.146E+09 1.228E+09 918159360 42.776014   
  
  
6 SFSJTS 2.146E+09 1.526E+09 620093440 28.889457   
  
  
1 SYSTEM 523239424 59924480 463314944 88.547407   
  
  
3 TEMP 523239424294912 522944512 99.943637   
  
  
4 TOOLS 15728640 12582912 314572820   
  
  
5 USERS 7340032 81927331840 99.888393   
  
  
12 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 Oracle databases, the tablespace settings are somewhat high.

There are some suggestions for free space management:

Using the Export and Import commands to unload and load tablespaces can free up 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 reconstruct the Oracle database.

Expansion of two tables and Indexes

A. In order to prevent tables or indexes from being overly expanded and timely adjust the Oracle database, users should observe related objects frequently.

We can think that tables or indexes with more than five extended areas are overextended ). See the following statement:

 
 

  
  
SQL > select substr(segment_name,1,15)   
  
  
Segment_name,segment_type,   
  
  
2 substr(tablespace_name,1,10)   
  
  
Tablepace_name,extents,Max_extents   
  
  
3from dba_segments   
  
  
4where extents >5 and owner='JFCL'   
  
  
5order by segment_name;   
  
  
SEGMENT_NAMESEGMENT TABLEPACE_   
  
  
EXTENTS MAX_EXTENTS   
  
  
_TYPE   
  
  
CHHDFYB TABLE JFSJTS 11121   
  
  
CHHDFYB_DHHMINDEX JFSJTS9121   
  
  
DJHZFYB_BF TABLE JFSJTS 17500   
  
  
DJHZFYB_DJHMINDEX IDX_JF6500   
  
  
DJHZFYB_JZHMINDEX IDX_JF7500   
  
  
GSMFYB TABLE JFSJTS 11121   
  
  
JFDHTABLE JFSJTS 14500   
  
  
JFDH_DHHM INDEX IDX_JF 61500   
  
  
JFDH_JZHM INDEX IDX_JF 64500   
  
  
XYKFYB TABLE JFSJTS7121   
  
  
YHDATABLE JFSJTS6500   
  
  
YHDA_BAKTABLE JFSJTS6500   
  
  
YHHZFYB_12 TABLE JFSJTS 10500   
  
  
13 rows selected.   
 
 

Through observation, DBAs can promptly discover and handle problems. We can use export to unload the table, delete the table, and then use the import command to load the table. In this way, we can combine discontinuous areas into a continuous space.

B. If you want to optimize the table space settings, for example, you need to change the initial parameter of the table EMP, you can use the following method:

1. Use the indexfile parameter when executing the imp command after the EMP table is detached and deleted:

 
 

  
  
imp userid=scott/tiger file=emp.dmp indexfile=emp.sql 
 
 

Oracle writes the table and index creation information to the specified file instead of writing data back.

2. Open the emp. SQL file:

 
 

  
  
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO"   
  
  
NUMBER(4, 0), "ENAME"   
  
  
REM VARCHAR2(10), "JOB" VARCHAR2(9),   
  
  
"MGR" NUMBER(4, 0), "HIREDATE" DATE,   
  
  
REM "SAL" NUMBER(7, 2), "COMM" NUMBER   
  
  
(7, 2), "DEPTNO" NUMBER(2, 0))   
  
  
REM PCTFREE 10 PCTUSED 40 INITRANS 1   
  
  
MAXTRANS 255 LOGGING STORAGE(INITIAL   
  
  
REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS   
  
  
121 PCTINCREASE 50 FREELISTS   
  
  
REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   
  
  
TABLESPACE "USER_DATA" ;   
  
  
REM ... 14 rows  
 
 

Edit it, remove information such as "REM", find the Initial parameter, and change it as needed.

3. Execute emp. SQL in SQL * plus.

4. load data:

 
 

  
  
mp userid=scott/tiger ignore=y file=emp.dmp  
 
 

Note that the ignore parameter must be set to Y.

C. you can use the following statement to observe the maximum extension of the table or index distance. "UNUSE" indicates the maximum extension of the distance. In the User_extents table, extent_id is the number of records starting from 0.

 
 

  
  
SQL >select a.table_name "TABLE_NAME",max   
  
  
(a.max_extents) "MAXEXTENTS" ,   
  
  
2 max(b.extent_id)+1 "IN USE", MAX   
  
  
(a.max_extents)-(max(b.extent_id)+1) "UNUSE"   
  
  
3 from user_tables a, user_extents b   
  
  
4where a.table_name=b.segment_name   
  
  
5 group by a.table_name ORDER BY 4;   
  
  
TABLE_NAME MAXEXTENTS IN USEUNUSE   
  
  
YZPHB 98 1 97   
  
  
SHJYB 121 1 120   
  
  
SHFYB 121 1 120   
  
  
RCHDB 121 1 120   
  
  
SJTXDZB121 1 120   
  
  
SJTXDAB121 1 120   
  
  
CHYHB 121 1 120   
  
  
JFDH 50014 486   
  
  
8 rows selected.   
 
 

If "UNUSE" is small enough, we should pay attention to it and make appropriate adjustments.

3. continuous space

You can use the following statement to view free space in the Oracle database:

 
 

  
  
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 the database. Its management method deserves our careful research.

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