Analysis of Oracle Database space management techniques

The following articles mainly introduce the space management skills of Oracle databases. We all know that in Oracle databases, DBAs generally use relevant observations, the table or view understands the usage of the current space and makes possible adjustments.

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 recreate the database.

Expansion of 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 the Oracle database 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;   
  
  
ABLE_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. The above content is an introduction to the space management skills of the Oracle database. I hope you will gain some benefits.
 

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