Analysis of Oracle Database space management techniques

Source: Internet
Author: User

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

  1. SQL > select a.file_id "FileNo",a.tablespace_name   
  2. "Tablespace_name",   
  3. 2 a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",   
  4. 3 sum(nvl(b.bytes,0)) "Free",   
  5. 4 sum(nvl(b.bytes,0))/a.bytes*100 "%free"   
  6. 5 from dba_data_files a, dba_free_space b   
  7. 6 where a.file_id=b.file_id(+)   
  8. 7 group by a.tablespace_name ,   
  9. 8 a.file_id,a.bytes order by a.tablespace_name;   
  10. File Tablespace   
  11. No _nameBytes Used Free %free   
  12. 11IDX_JF .146E+09 849305600 1.297E+09 60.431806   
  13. 9 JFSJTS 2.146E+09 1.803E+09 343793664 16.016961   
  14. 10JFSJTS 2.146E+09 1.359E+09 787431424 36.685546   
  15. 2 RBS523239424 359800832 163438592 31.235909   
  16. 12RBS1.610E+09 1.606E+09 3104768 .19289495   
  17. 8 RBSJF 3.220E+09 2.716E+09 504356864 15.662396   
  18. 7 SFGLTS 2.146E+09 1.228E+09 918159360 42.776014   
  19. 6 SFSJTS 2.146E+09 1.526E+09 620093440 28.889457   
  20. 1 SYSTEM 523239424 59924480 463314944 88.547407   
  21. 3 TEMP 523239424294912 522944512 99.943637   
  22. 4 TOOLS 15728640 12582912 314572820   
  23. 5 USERS 7340032 81927331840 99.888393   
  24. 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:

  1. SQL > select substr(segment_name,1,15)   
  2. Segment_name,segment_type,   
  3. 2 substr(tablespace_name,1,10)   
  4. Tablepace_name,extents,Max_extents   
  5. 3from dba_segments   
  6. 4where extents >5 and owner='JFCL'   
  7. 5order by segment_name;   
  10. _TYPE   
  16. GSMFYB TABLE JFSJTS 11121   
  17. JFDHTABLE JFSJTS 14500   
  18. JFDH_DHHM INDEX IDX_JF 61500   
  19. JFDH_JZHM INDEX IDX_JF 64500   
  23. YHHZFYB_12 TABLE JFSJTS 10500   
  24. 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:

  2. NUMBER(4, 0), "ENAME"   
  3. REM VARCHAR2(10), "JOB" VARCHAR2(9),   
  4. "MGR" NUMBER(4, 0), "HIREDATE" DATE,   
  5. REM "SAL" NUMBER(7, 2), "COMM" NUMBER   
  6. (7, 2), "DEPTNO" NUMBER(2, 0))   
  13. 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:

  1. 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.

  1. SQL >select a.table_name "TABLE_NAME",max   
  2. (a.max_extents) "MAXEXTENTS" ,   
  3. 2 max(b.extent_id)+1 "IN USE", MAX   
  4. (a.max_extents)-(max(b.extent_id)+1) "UNUSE"   
  5. 3 from user_tables a, user_extents b   
  6. 4where a.table_name=b.segment_name   
  7. 5 group by a.table_name ORDER BY 4;   
  9. YZPHB 98 1 97   
  10. SHJYB 121 1 120   
  11. SHFYB 121 1 120   
  12. RCHDB 121 1 120   
  13. SJTXDZB121 1 120   
  14. SJTXDAB121 1 120   
  15. CHYHB 121 1 120   
  16. JFDH 50014 486   
  17. 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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.