oracle释放空间到OS

测试：

建表空间

CREATE TABLESPACE TESTTBS DATAFILE '/oradata01/dfhdb/testtbs01.dbf' SIZE 2G;

在表空间上建表

CREATE TABLE TESTTAB TABLESPACE TESTTBS AS SELECT * FROM DBA_OBJECTS;

查找数据文件的编号

SELECT FILE_ID FROM DBA_DATA_FILES WHERE FILE_NAME='/oradata01/dfhdb/testtbs01.dbf';

查找数据文件上面的数据库对象

/* Formatted on 2020/5/19 下午 02:48:46 (QP5 v5.163.1008.3004) */
  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
         E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
         F.FILE_NAME AS FILE_NAME,
         SUM (E.BYTES) / 1024 / 1024/1024 AS SEGMENT_SIZE_GB
    FROM DBA_EXTENTS E INNER JOIN DBA_DATA_FILES F ON E.FILE_ID = F.FILE_ID
   WHERE F.FILE_ID = 7
GROUP BY E.SEGMENT_TYPE,
         E.OWNER,
         E.SEGMENT_NAME,
         F.FILE_NAME
ORDER BY 4 DESC;

向表中插入数据，查看数据文件的变化

/* Formatted on 2020/5/19 下午 04:31:24 (QP5 v5.163.1008.3004) */
DECLARE
BEGIN
   FOR I IN 1 .. 6
   LOOP
      INSERT INTO TESTTAB
         SELECT * FROM TESTTAB;

      COMMIT;
   END LOOP;
END;

查看数据文件的变化

/* Formatted on 2020/5/19 下午 04:14:28 (QP5 v5.163.1008.3004) */
  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
         E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
         F.FILE_NAME AS FILE_NAME,
         SUM (E.BYTES) / 1024 / 1024 / 1024 AS SEGMENT_SIZE_GB
    FROM DBA_EXTENTS E INNER JOIN DBA_DATA_FILES F ON E.FILE_ID = F.FILE_ID
   WHERE F.FILE_ID = 7
GROUP BY E.SEGMENT_TYPE,
         E.OWNER,
         E.SEGMENT_NAME,
         F.FILE_NAME
ORDER BY 4 DESC;

从这里可以看出，此时如果要resize数据文件，最小应该是0.609375G，因为这以下已经被使用

对表收集统计信息

execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TESTTAB' ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all  columns size auto' ,cascade => true ,degree=>6);

查看表占用了多少个数据块

SELECT TABLE_NAME,
       NUM_ROWS,
       BLOCKS,
       empty_blocks,
       LAST_ANALYZED
  FROM dba_tables  where owner='SYS' and table_name='TESTTAB';

 查看表使用的实际块数

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) USED_BLOCK FROM  SYS.TESTTAB;

通过对比，实际使用的块数和统计信息的差不多，没有多少空块

删除一部分数据，再次查看

DELETE FROM TESTTAB WHERE ROWNUM < 4000000;

再次收集统计信息

查看表实际使用的块数

也就是说总共79457个数据块中只使用了21167，表中存在着大量的空块，同时，也因为这些空块的存在，导致数据文件无法RESIZE。而且，这些空块在全表扫描的时候也会被扫到，影响数据库的性能

消除空块，即降低高水位线

ALTER TABLE SYS.TESTTAB ENABLE ROW MOVEMENT;
ALTER TABLE SYS.TESTTAB SHRINK SPACE;
/*也可以使用MOVE或导入导出，MOVE会导致索引失效，但是比SHRINK快*/

此时再次收集统计信息

可以看到，表占用的数据块数已经降下来了

再次查看数据文件的变化

此时，我们就可以RESIZE数据文件了

通过上述实验，我们可以得出结论，只要我们将数据文件上面的数据库对象的高水位降低（表通过move等，索引通过重建），即可将数据文件的实际使用量缩小，即可释放到OS

找到要操作的数据文件

找到空间不足的目录---》找到目录里面的数据文件---》找到这些数据文件含有的数据库对象个数---》找到数据文件中数据库对象最少的或者只有索引的数据文件（这个就是我们要操作的数据文件）

/* Formatted on 2020/5/19 下午 05:39:26 (QP5 v5.163.1008.3004) */
CREATE TABLE MONKEY.MONKEY_TABLESPACE_RESIZE
(
   FILE_ID           VARCHAR2 (100 BYTE),
   TABLE_COUNT       NUMBER,
   INDEX_COUNT       NUMBER,
   TABLE_PAT_COUNT   NUMBER,
   INDEX_PAT_COUNT   NUMBER
);

/* Formatted on 2020/5/19 下午 06:00:26 (QP5 v5.163.1008.3004) */
DECLARE
   CURSOR FIDS
   IS
      SELECT FILE_ID
        FROM DBA_DATA_FILES
       WHERE FILE_NAME LIKE '/ora21data04%';

   TABLE_COUNT       NUMBER;
   TABLE_PAT_COUNT   NUMBER;
   INDEX_COUNT       NUMBER;
   INDEX_PAT_COUNT   NUMBER;
BEGIN
   FOR FID IN FIDS
   LOOP
      WITH RES
           AS (  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
                        E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
                        F.FILE_ID AS FILE_ID
                   FROM    DBA_EXTENTS E
                        INNER JOIN
                           DBA_DATA_FILES F
                        ON E.FILE_ID = F.FILE_ID
                  WHERE F.FILE_ID = FID.FILE_ID
               GROUP BY E.SEGMENT_TYPE,
                        E.OWNER,
                        E.SEGMENT_NAME,
                        F.FILE_ID)
      SELECT COUNT (*)
        INTO TABLE_COUNT
        FROM RES
       WHERE SEGMENT_TYPE = 'TABLE';

      WITH RES
           AS (  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
                        E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
                        F.FILE_ID AS FILE_ID
                   FROM    DBA_EXTENTS E
                        INNER JOIN
                           DBA_DATA_FILES F
                        ON E.FILE_ID = F.FILE_ID
                  WHERE F.FILE_ID = FID.FILE_ID
               GROUP BY E.SEGMENT_TYPE,
                        E.OWNER,
                        E.SEGMENT_NAME,
                        F.FILE_ID)
      SELECT COUNT (*)
        INTO TABLE_PAT_COUNT
        FROM RES
       WHERE SEGMENT_TYPE = 'TABLE PARTITION';

      WITH RES
           AS (  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
                        E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
                        F.FILE_ID AS FILE_ID
                   FROM    DBA_EXTENTS E
                        INNER JOIN
                           DBA_DATA_FILES F
                        ON E.FILE_ID = F.FILE_ID
                  WHERE F.FILE_ID = FID.FILE_ID
               GROUP BY E.SEGMENT_TYPE,
                        E.OWNER,
                        E.SEGMENT_NAME,
                        F.FILE_ID)
      SELECT COUNT (*)
        INTO INDEX_COUNT
        FROM RES
       WHERE SEGMENT_TYPE = 'INDEX';

      WITH RES
           AS (  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
                        E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
                        F.FILE_ID AS FILE_ID
                   FROM    DBA_EXTENTS E
                        INNER JOIN
                           DBA_DATA_FILES F
                        ON E.FILE_ID = F.FILE_ID
                  WHERE F.FILE_ID = FID.FILE_ID
               GROUP BY E.SEGMENT_TYPE,
                        E.OWNER,
                        E.SEGMENT_NAME,
                        F.FILE_ID)
      SELECT COUNT (*)
        INTO INDEX_PAT_COUNT
        FROM RES
       WHERE SEGMENT_TYPE = 'INDEX PARTITION';

      INSERT INTO MONKEY.MONKEY_TABLESPACE_RESIZE
           VALUES (FID.FILE_ID,
                   TABLE_COUNT,
                   INDEX_COUNT,
                   TABLE_PAT_COUNT,
                   INDEX_PAT_COUNT);

      COMMIT;
   END LOOP;
END;

通过上面的SQL可以查到此目录下的数据文件上的数据库对象个数，进而判断可以RESIZE的数据文件和可操作的数据库对象

89号数据文件上面只有6个普通索引和16个分区索引，没有表，因此可以通过重建这些索引达到RESIZE数据文件的目的

查看89号文件上面的对象.

/* Formatted on 2020/5/19 上午 11:35:38 (QP5 v5.163.1008.3004) */
  SELECT E.SEGMENT_TYPE AS SEGMENT_TYPE,
         E.OWNER || '.' || E.SEGMENT_NAME AS SEGMENT_NAME,
         F.FILE_NAME AS FILE_NAME,
         SUM (E.BYTES) / 1024 / 1024 AS SEGMENT_SIZE
    FROM DBA_EXTENTS E INNER JOIN DBA_DATA_FILES F ON E.FILE_ID = F.FILE_ID
   WHERE F.FILE_ID = 89
GROUP BY E.SEGMENT_TYPE,
         E.OWNER,
         E.SEGMENT_NAME,
         F.FILE_NAME
ORDER BY 4 DESC;

重建或move上面的索引，之后RESIZE数据文件即可。

move索引请参考：https://www.cnblogs.com/monkey6/p/11221643.html