Oracle Database space management skills

The ORACLE tutorial is about space management of Oracle databases. In Oracle databases, DBAs can observe tables or views to understand the usage of the current space and make possible adjustments.

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


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

[NextPage]

Expansion of two tables and Indexes

A. In order to prevent tables or indexes from being excessively expanded and timely adjust the 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:


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

[NextPage]

2. Open the emp. SQL file:


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:


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.


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

[NextPage]

3. continuous space

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


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:


Oracle space management has an important impact on the performance of the database. Its management method deserves our careful research.

Previous Page