Space management tips for Oracle databases

The ORACLE tutorial you are looking at is: space management tips for Oracle databases. In the Oracle database, DBA can observe a definite table or view to see how the space is being used and make possible adjustment decisions.

1. Free space for table Spaces

By observing the free space of a table space, you can determine whether there is too much or not enough space allocated to a table space. Look at the following statement

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 is not used in RBS, a table space with FileNo of 12, which is too small, while in SYSTEM, TEMP and other table Spaces, up to 80% of the space is not used. For a production database, this table space is a little high.

-- here are some Suggestions for managing free space:

Unloading and loading tablespaces using the Export and Import commands can free up a large amount of space, relieving the need to add additional data files.

If the share of free space in the table space containing tables with high insert (insert) and update (update) activities drops below 15%, add more space to the table space.

For a table space that is basically static table data, if you have more than 20% free space, consider reducing the amount of file space allocated to it.

It is difficult to reduce the amount of space in the SYSTEM table space, which requires rebuilding the database.

Extension of tables and indexes

-- A. In order to prevent tables or indexes from being overextended and to adjust the database in a timely manner, the user should observe the relevant objects frequently.

We can assume that a table or index with an extended area greater than 5 is overextended (overextended). Look at 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, DBA can find problems in time and deal with them accordingly.
We can unmount the table with export, then delete the table, and then load the table with the import command. In this way, we can combine the discontinuous areas into one continuous space.

-- B. If the user wishes to optimize the space Settings of the table, for example, if the initial parameter of the table EMP needs to be changed, the following method can be used:

-- 1. Use the indexfile parameter when executing the imp command after unloading and deleting the EMP table:

-- 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 the data back.

-- 2. Open emp.sql:

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 "REM" and other information, find the Initial parameter, and change it as needed.
-- 3. Execute emp. sql in SQL*plus.

-- 4. Load data:

---- imp 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 expansion of the table or index distance. "UNUSE" is the value of the maximum expansion of the distance.

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 the "UNUSE" is small enough, we should pay attention to it and make appropriate adjustments.

-- 3 for continuous space

-- you can view the free space in the database with the following statements:

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 estimate the true amount of adjacent free space by the result of the command. For each row, the first id(BLOCK_ID) plus the number of free blocks (BLOCKS) is used, and if the sum is equal to the block id(BLOCK_ID) in the next row, the two rows are continuous. Lines 2 and 3, as in the example above, 133719+126=133845, while 1338456+130! =135275, so from block_id is 133719, there are 126+130=256 block contiguous Spaces.
Behind the oracle database, the system monitor (SMON) periodically merges contiguous blocks of free space to produce larger contiguous blocks. DBA can do this with the SQL command:

---- alter tablespace tablespace_name coalesce;

-- oracle space management has an important impact on the performance of the database, and its management method is worthy of our careful exploration and study.