8、收缩表空间

[TOC]

### 1. 查看空闲比较大的数据文件

你提供的查询语句用于检查表空间中哪些数据文件具有大量未使用空间。这一步没有问题，可以正常使用。

```sql
SET LINES 400 PAGES 1000
column TABLESPACE_NAME for a10
column filenname for a80
column used for 999,9999
column realused for 999,999
column freeused for 999,999

WITH E AS (
    SELECT FILE_ID, SUM(BYTES) REAL_USED_BYTES 
    FROM DBA_EXTENTS 
    WHERE TABLESPACE_NAME = 'TBS_BASE' 
    GROUP BY FILE_ID
), 
F AS (
    SELECT FILE_ID, SUM(BYTES) FREE_BYTES 
    FROM dba_free_space 
    WHERE TABLESPACE_NAME = 'TBS_BASE'  
    GROUP BY FILE_ID
)
SELECT 
    D.TABLESPACE_NAME TABLESPACE_NAME,
    D.FILE_NAME filenname,
    ROUND(D.USER_BYTES/1024/1024/1024,2) used,
    ROUND(E.REAL_USED_BYTES/1024/1024/1024,2) realused,
    ROUND(F.FREE_BYTES/1024/1024/1024,2) freeused
FROM 
    dba_data_files D 
JOIN 
    E ON D.FILE_ID = E.FILE_ID
LEFT JOIN 
    F ON D.FILE_ID = F.FILE_ID 
ORDER BY 1,2;
```

### 2. 测试案例

这里的测试案例步骤非常详细，主要目标是确定哪些表或索引阻碍了数据文件的收缩。

### 2.1 查看表空间使用情况

使用类似前面查询的语句来检查特定表空间的使用情况，这一步是正常的。

### 2.2 查看表空间 `TRW` 包含的数据文件及空间使用量

查询 `TRW` 表空间中每个数据文件的实际使用情况，这一步与前面的查询基本一致，适用于不同的表空间。

```sql
SET LINES 400 PAGES 1000
column TABLESPACE_NAME for a10
column filenname for a80
column used for 999,9999
column realused for 999,999
column freeused for 999,999

WITH E AS (
    SELECT FILE_ID, SUM(BYTES) REAL_USED_BYTES 
    FROM DBA_EXTENTS 
    WHERE TABLESPACE_NAME = 'TRW' 
    GROUP BY FILE_ID
), 
F AS (
    SELECT FILE_ID, SUM(BYTES) FREE_BYTES 
    FROM dba_free_space 
    WHERE TABLESPACE_NAME = 'TRW'  
    GROUP BY FILE_ID
)
SELECT 
    D.TABLESPACE_NAME TABLESPACE_NAME,
    D.FILE_NAME filenname,
    ROUND(D.USER_BYTES/1024/1024,2) used,
    ROUND(E.REAL_USED_BYTES/1024/1024,2) realused,
    ROUND(F.FREE_BYTES/1024,2) freeused
FROM 
    dba_data_files D 
JOIN 
    E ON D.FILE_ID = E.FILE_ID
LEFT JOIN 
    F ON D.FILE_ID = F.FILE_ID 
ORDER BY 1,2;
```

### 2.3 查看数据文件包括的对象

使用以下查询检查数据文件包含哪些对象，这样可以确定需要移动哪些表或索引。

```sql
SET LINES 500 PAGESIZE 1000
COL OWNER FOR A20
COL SEGMENT_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SEGMENT_TYPE FOR A10
COL TABLESPACE_NAME FOR A40
COL FILE_ID FOR 999,99
COL NAME FOR A80

SELECT DISTINCT a.OWNER, a.SEGMENT_NAME, a.PARTITION_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME, a.FILE_ID, b.NAME 
FROM DBA_EXTENTS a  
JOIN V$DATAFILE b 
ON a.FILE_ID = b.FILE# 
WHERE b.NAME = '+DATA/att11db/datafile/trw01.dbf';
```

### 2.4 删除 `TESTTABLE`

这一步是正常的，如果 `TESTTABLE` 已经不需要，可以直接删除以释放空间。

### 2.5 再次查询对象

删除表后，重新查询表空间使用情况以确认空间是否被释放。

### 2.6 再次查看空间使用量

类似于前面的查询，重新检查数据文件的空间使用情况以确认是否可以进行收缩。

### 2.7 数据库收缩处理

#### 2.7.1 尝试将数据文件收缩到400M

这一步是关键步骤之一，直接调整数据文件大小。

```sql
ALTER DATABASE DATAFILE '+DATA/att11db/datafile/trw01.dbf' RESIZE 400M;
```

**注意**：如果遇到错误，可能是因为文件中有未移动的段（表、索引等），这时需要执行以下步骤。

#### 2.7.2 使用存储过程查询哪些对象阻碍了收缩

使用提供的存储过程 `SHRINK_DATAFILE.SQL` 确认哪些对象阻碍了文件的收缩。

```sql
SET SERVEROUTPUT ON

DECLARE
    V_FILE_ID NUMBER;
    V_BLOCK_SIZE NUMBER;
    V_RESIZE_SIZE NUMBER;
BEGIN
    V_FILE_ID := &FILE_ID;
    V_RESIZE_SIZE := &RESIZE_FILE_TO;

SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;

DBMS_OUTPUT.PUT_LINE('.');
    DBMS_OUTPUT.PUT_LINE('.');
    DBMS_OUTPUT.PUT_LINE('.');
    DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');
    DBMS_OUTPUT.PUT_LINE('===================================================================');
    DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
    DBMS_OUTPUT.PUT_LINE('===================================================================');

FOR my_record IN (
        SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
        FROM DBA_EXTENTS
        WHERE (BLOCK_ID + BLOCKS - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE 
        AND FILE_ID = V_FILE_ID
        AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
        ORDER BY 1) 
    LOOP
        DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
    END LOOP;

DBMS_OUTPUT.PUT_LINE('===================================================================');
    DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
    DBMS_OUTPUT.PUT_LINE('===================================================================');

FOR my_record IN (
        SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
        FROM DBA_EXTENTS
        WHERE (BLOCK_ID + BLOCKS - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
        AND FILE_ID = V_FILE_ID 
        AND SEGMENT_TYPE LIKE '%PARTITION%'
        ORDER BY 1) 
    LOOP 
        DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
    END LOOP;

END;
/
```

#### 2.7.3 查看 BLOCK 被哪些表使用

查询哪些表的 BLOCK 占用了文件中不想保留的空间。

```sql
SET LINES 1000 PAGESIZE 1000
COL BLOCK_ID FOR 999,99
COL SEGMENT_NAME FOR A30

SELECT BLOCK_ID, SEGMENT_NAME 
FROM DBA_EXTENTS 
WHERE FILE_ID = &FILE_ID;
```

#### 2.7.4 将表移动到其他表空间

将阻碍文件收缩的表移动到其他表空间。

```sql
ALTER TABLE TESTTABLETEST MOVE TABLESPACE USERS;
ALTER TABLE TESTTABLE111 MOVE TABLESPACE USERS;
ALTER TABLE TEST MOVE TABLESPACE USERS;
```

#### 2.7.5 再次执行查询检查数据文件是否包含表

确认所有阻碍收缩的表都已经移动，可以再次执行 `SHRINK_DATAFILE.SQL` 或查看数据文件的使用情况。

#### 2.7.6 执行数据文件收缩

重新执行数据文件收缩操作。

```sql
ALTER DATABASE DATAFILE '+DATA/att11db/datafile/trw01.dbf' RESIZE 400M;
```

#### 2.7.7 重建索引

在将表移动后，可能需要重建索引以优化性能。

```sql
ALTER INDEX index_name REBUILD;
```

FYF

导航1234

最近发表

友情链接