Shulou(Shulou.com)06/01 Report--

Today, the developer said that the table space in a test library is full and needs to be cleaned. When connected to the test library, 99.98% has indeed been used.

TABLESPACE_NAME SIZE_G FREE_G USED_PCT---E2TEST 96.00 0.02 99.98%

The deletion strategy is determined by communicating with the developer. First, some history tables are deleted but the cleaning effect is not great. Directly through the DBA_SEGMENTS view, the largest table of bytes in the ESOP2TEST tablespace is E_CONT, accounting for 28G. I'm going to clean up this form directly.

OWNER SEGMENT_NAME SUM (BYTES) / 1024amp 1024Murray-- E2TEST E_CONT 28983.3125

Developers are required to keep the last month's data, so they plan to build a temporary table to import the most recent month's data, delete the original table, and rename the temporary table to the original table. The process is actually very simple, and the steps are not complicated, but after all the steps, it is found that the tablespace usage is still very high, and the cleanup is not effective.

TABLESPACE_NAME SIZE_G FREE_G USED_PCT---E2TEST 96.00 0.89 99.07%

Check again to see the space usage through DBA_SEGMENTS.

SQL > select * from (select owner,segment_name,sum (bytes) / 1024 from dba_segments where tablespace_name='E2TEST' group by owner,segment_name order by 3 desc) where rownum

< 50; OWNER SEGMENT_NAME SUM(BYTES)/1024/1024------------------------------ ------------------------------ --------------------E2TEST 20.962 28981 segment_name居然变为了数字。。。 查看这个段的类型为TEMPORARY SQL>

Select * from (select owner,segment_name,segment_type,sum (bytes) / 1024 from dba_segments where tablespace_name='E2TEST' group by owner,segment_name,segment_type order by 4 desc) where rownum

< 50；OWNER SEGMENT_NAME SEGMENT_TYPE SUM(BYTES)/1024/1024------------------------------ ------------------------------ ------------------ --------------------E2TEST 20.962 TEMPORARY 28981 在网上查资料，先看到惜分飞的一篇blog，介绍type为TEMPORARY,name为file#.block#对象重现和清理 http://www.xifenfei.com/2015/12/type%E4%B8%BAtemporaryname%E4%B8%BAfile-block%E5%AF%B9%E8%B1%A1%E9%87%8D%E7%8E%B0%E5%92%8C%E6%B8%85%E7%90%86.html 跟他里边描述的不太一样，按他写的方法无法进行清理。 继续查发现一篇文章介绍的情况跟我的比较相似，http://blog.chinaunix.net/uid-22948773-id-3758510.html 重启数据库或用下面的方法清理： alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'; level - tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only segments in a tablespace whose number is equal to the LEVEL specification are dropped. 1、查询所在表空间的编号 SQL>

Select ts# from v$tablespace where name='E2TEST'; TS#- 5

2. Use the above method to clean up

SQL > alter session set events' immediate trace name DROP_SEGMENTS level 6 session altered.

3. Check again that the largest TEMPORY segment is gone, and the tablespace has been completely freed

SQL > select * from (select owner,segment_name,segment_type,sum (bytes) / 1024 from dba_segments where tablespace_name='E2TEST' group by owner,segment_name,segment_type order by 4 desc) where rownum < 50 OWNER SEGMENT_NAME SEGMENT_TYPE SUM (BYTES) / 1024amp 1024Murray-- -E2TEST T_CONT TABLE 12332E2TEST SYS_LOB0000017851C00008 $$LOBSEGMENT 9834E2TEST ERMSGLOG TABLE 5974TABLESPACE_NAME SIZE_G FREE_G USED_PCT -- E2TEST 96.00 31.68 67.00%

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.