In the Oracle database, DBA can understand the usage status of the current space by observing a certain table or view, in turn makes a possible adjustment decision. I. The free space of the table space can be used to judge that there is too much space that is allocated to a table space by observing the free space of the table space. 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) .betes, 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.46E 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.228 E 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 row 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 and other tablespaces, the space is not Used, for the production database, the settings of this table space are high. Regarding the management of free space, some suggestions: Use the export and import commands to unload and load table spaces to release a large amount of space, thereby alleviating the requirements of 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. Expansion of the second table and index A. In order to prevent the table or index from being excessively expanded, the adjustment to 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 extension (Overextended). Please 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 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. by observation, DBA can identify problems in a timely and appropriate treatment. We can use the export to remove the table, then delete the table, then load the table with the import command, which can be combined into a continuous space. B. If the user wants to optimize the space settings of the table, for example, you need to change the INITIAL parameters of the table EMP, you can use the following method: 1. Use the indexfile parameter when the EMP table is removed and deleted: IMP UserId = Scott / Tiger File = Emp.dmp IndexFile = Emp.sql Oracle writes the creation information of the table and index to the specified file instead of writing the data back. 2. Open Emp.SQL file: Rem Create Table "Scott". "EMP" ("Empno" Number (4, 0), "Ename" Rem varcha2 (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, removing the "REM" and other information, to find parameters Initial Alter it as needed. 3. Execute Emp.SQL in SQL * Plus. 4. Load data:
MP Userid = Scott / Tiger ignore = y File = Emp.dmp To 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 the index distance, "unuse" is a distance To achieve the maximum extension, 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 8 rows selected. If "unuse" is small to a certain level, we should pay attention to proper adjustment processing. Third, the continuous space can be used to view the 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 the 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.
