Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of tablespaces and table fragments in ORACLE", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let me lead you to study and learn about "sample analysis of tablespaces and table fragments in ORACLE".

Tablespace fragmentation rate idle > select a.tablespaceroomname FSFIfrom dba_free_space sqrt (max (a.blocks) / sum (a.blocks)) * (100/sqrt (sqrt (count (a.blocks) FSFIfrom dba_free_space a bwhere a.tablespace_name=b.tablespace_nameand b.contents not in ('TEMPORARY','UNDO') group by a.tablespace_name order by 2 TABLESPACE_NAME FSFI---EAM 2.57604251ALM 20.1734462SYSAUX 22.2842767SYSTEM 23.7809729USERS 53.439579RECCAT 100ARCH 1007 rows selected.idle > 123456789101112131415161718192021

The smaller the number, the more table space debris, and when it is less than 30%, the degree of fragmentation is considerable.

Displays continuous idle time by tablespace

To quote an official passage:

The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. If you have a lot of small extents (too small for any next extent size) but the total bytes of free space is large, then you may want to consider defragmentation options.

The continuous space and the sum of the continuous space are counted in the script. When the total free space in the table is very large, but there are many small blocks, the more serious the fragmentation.

= Script: tfstsfgm=SET ECHO off REM NAME:TFSTSFRM.SQL REM USAGE: "@ path/tfstsfgm" REM-REM REQUIREMENTS: REM SELECT ON DBA_FREE_SPACE REM- -REM PURPOSE: REM The following is a script that will determine how many extents REM of contiguous free space you have in Oracle as well as the REM total amount of free space you have in each tablespace. From REM these results you can detect how fragmented your tablespace is. REM REM The ideal situation is to have one large free extent in your REM tablespace. The more extents of free space there are in the REM tablespace, the more likely you will run into fragmentation REM problems. The size of the free extents is also very important. REM If you have a lot of small extents (too small for any next REM extent size) but the total bytes of free space is large, then REM you may want to consider defragmentation options. REM-REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM-REM Main text of script follows: create table SPACE_TEMP (TABLESPACE_NAME CHAR (30) CONTIGUOUS_BYTES NUMBER) / declare cursor query is select * from dba_free_space order by tablespace_name, block_id This_row query%rowtype; previous_row query%rowtype; total number; begin open query; fetch query into this_row; previous_row: = this_row; total: = previous_row.bytes; loop fetch query into this_row; exit when query%notfound If this_row.block_id = previous_row.block_id + previous_row.blocks then total: = total + this_row.bytes; insert into SPACE_TEMP (tablespace_name) values (previous_row.tablespace_name); else insert into SPACE_TEMP values (previous_row.tablespace_name, total); total: = this_row.bytes End if; previous_row: = this_row; end loop; insert into SPACE_TEMP values (previous_row.tablespace_name, total); end;. / set pagesize 60 set newpage 0 set echo off ttitle center 'Contiguous Extents Report' skip 3 break on "TABLESPACE NAME" skip page duplicate spool contig_free_space.lis rem column "CONTIGUOUS BYTES" format 999999999 column "COUNT" format 999 column "TOTAL BYTES" format 999999999 column "TODAY" noprint new_value new_today format A1 rem select TABLESPACE_NAME "TABLESPACE NAME" CONTIGUOUS_BYTES "CONTIGUOUS BYTES" from SPACE_TEMP where CONTIGUOUS_BYTES is not null order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc Select tablespace_name, count (*) "# OF EXTENTS", sum (contiguous_bytes) "TOTAL BYTES" from space_temp group by tablespace_name; spool off drop table SPACE_TEMP / 12131415171819202122324252628293031333435363839404142445464748495051535457585960616266676870172737475767880818284858686871737475767880818384858687891929495969798

For tablespaces managed by ASSM, the smon process generally organizes them automatically, provided that the pctincrement value of the tablespace is non-0, and the default storage parameter pctincrease of the tablespace can be changed to non-0, which is generally set to 1. For example, modify the pctincrease attribute of the temp tablespace: alter tablespace temp default storage (pctincrease 1); this will automatically defragment the tablespace level.

For dictionary-managed tablespaces, you can organize them with the following command:

Sql > alter tablespace collesce

Table-level defragmentation method 1. Preferred shrinkSQL > alter table T1 enable row movement;-- opening the row move table has changed. SQL > alter table T1 shrink space cascade;-compress tables and related data segments and downgrade HWMSQL > alter table T1 shrink space compact;-only compress HWMSQL > alter table T1 shrink space;-downgrade HWMSQL > alter table T1 disable row movement;-close row move 1234567891011

Can only be done in ASSM, locally managed tablespaces, after completing these do not need to rebuild the index, but the statistics had better be re-collected, the script to participate in the previous article of this blog. ^ _ ^

two。 Import and export

After exporting with exp/imp, re-import the rebuild, recreate the index, and re-collect statistics.

3.CATS technology

Create table newtable as select * from old_table

Drop old_table

Rename table newtable to old_table

Rebuild the index and collect statistics.

4.move tablespacesql > alter table move tablespace re-index and collect statistics. The above are all the contents of the article "sample Analysis of tablespaces and Table fragments in ORACLE". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.