【无标题】

oracle数据库表空间处理

1、查询表空间使用率

SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;

2、查询表空间中占用空间较大的表

SELECT   
    owner,   
    segment_name,   
    segment_type,   
    bytes/1024/1024 AS mb   
FROM   
    dba_segments   
WHERE   
    tablespace_name = 'NWATB_DATA'   
    AND segment_type = 'TABLE'   
ORDER BY   
    bytes DESC;

3、清理网络自动化常见大表数据

truncate table ieai_blob;

4、降水位

alter table ieai_blob enable row movement;
alter table ieai_blob SHRINK SPACE COMPACT;
alter table ieai_blob SHRINK SPACE cascade;