Oracle Database space management skills (reference yesky)

---- In Oracle databases, DBAs can observe tables or views to understand the current space usage and make possible adjustments.

---- 1. Free Space of the 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
10 JFSJTS 2.146E + 09 1.359E + 09 787431424 36.685546
2 RBS523239424 359800832 163438592
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 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 two tables and Indexes
---- A. In order to prevent tables or indexes from being excessively expanded and adjust the database in a timely manner, 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
? 3 from dba_segments
? 4 where extents> 5 and owner = 'jfcl'
? 5 order 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:

---- Imp userid = scott/tiger ignore = y file = emp. dmp

---- 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
? 4 where 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 less than a certain degree, 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 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 estimate the real number of adjacent free spaces through the command results. 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.