---- In the Oracle database, DBA can understand the usage status of the current space by observing a certain table or view, which in turn makes a possible adjustment decision.
---- One. Free space of table space
---- Through the observation of the free space of the table space, there is too much space that can be used to determine the space allocated to a table space is still not enough. Please see 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 in the tablespace RBS of Fileno 12, only 0.19% of the allocation space is not used, this ratio is too small, and in the SYSTEM and TEMP, it is more than 80% space. Unused, for the production database, the settings of this table space are somewhat high.
---- About the management of free space, some suggestions below:
Using the export and import commands to unload and load a table space to release a large amount of space, thereby alleviating the requirements of adding additional data files.
If the specific gravity of free space in table spaces containing high insertion (INSERT) and update (UPDATE) activities have dropped below 15%, add more space to this table space.
For a table space that is substantially static table data, if there are more than 20% free space, you can consider the amount of file space allocated to it.
Reducing the amount of space in the SYSTEM table space is more difficult because it is to rebuild the database. ---- Two tables and index expansions
---- A. In order to prevent the table or index from being expanded, the adjustment of the database is realized in time, and the user should often observe the relevant objects.
---- We can think that the extended area is more than 5 tables or indexes for excessive expansion. Please see the following statement:
SQL> SELECT SUBSTR (segment_name, 1, 15)
Segment_name, segment_type,
? 2 subside_name, 1, 10)
Tablepace_name, extents, max_extents
3From DBA_SEGMENTS
? 4where eXtents> 5 and oer = '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.
---- By observation, DBA can promptly find problems and perform corresponding processes.
---- We can use the export to remove the table, then delete the table, and then load the table with the import command, so that the discontinuous area can be combined into a consecutive space.
---- B. If the user wants to optimize the spatial settings of the table, for example, you need to change the initial parameters of the table EMP, the following method can be used:
---- 1. Use the indexfile parameter when the EMP table is removed and deleted, execute the Imp command:
---- Imp Userid = Scott / Tiger File = Emp.dmp IndexFile = Emp.sql Oracle Write the creation of the table and index to the specified file instead of writing the 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 maxExtents121 Pctincrease 50 Freeelists
REM? 1 FreeElist Groups 1 Buffer_Pool Default)
TABLESPACE "User_Data";
REM? ... 14 rows
---- Edit it, remove information such as "REM", find the initial parameter, change it as needed.
---- 3. Execute EMP.SQL in SQL * Plus.
---- 4. Load data:
---- IMP userid = scott / tiger ignore = y file = Emp.dmp
---- What to note is that the Ignore parameter must be set to Y.
---- C. You can use the following statement to observe the status of the maximum extension, "unuse" is the maximum extended value, in the user_extents table, extent_id is the number of descriptions 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;
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
Rows SELECTED.
---- If "unuse" is small to a certain degree, we should pay attention to appropriate adjustment processing.
---- Three Three Connect Space
---- You can use the following statements 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 true quantity of adjacent free space through the results of the command. For each row, the number of free blocks (block_id) plus the number of free blocks (block_id) is used, and the two lines are continuous if it is equal to the block ID (block_id) of the next line. As in the second row and third lines, 133719 126 = 133845, and 1338456 130! = 135275, since the Block_ID is 133719, 126 130 = 256 blocks are continuous. ---- In the background of Oracle Database, the System Monitor (SMON) periodically combines the free space adjacent blocks to obtain a larger continuous block. And DBA can use the SQL command to complete this work:
---- ALTER TABLESPACE TABLESPACE_NAME COALESCE;
---- Oracle spatial management has an important impact on the work performance of the database, and its management method deserves our serious exploration.
