首页 / ORACLE / oracle释放空间到OS

oracle释放空间到OS

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle释放空间到OS，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5877字，纯文字阅读大概需要9分钟。

内容图文

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;

oracle释放空间到OS - 文章图片

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

/* 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;

oracle释放空间到OS - 文章图片

从这里可以看出，此时如果要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‘;

oracle释放空间到OS - 文章图片

 

 查看表使用的实际块数

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

oracle释放空间到OS - 文章图片

 

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

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

DELETE FROM TESTTAB WHERE ROWNUM < 4000000;

再次收集统计信息

oracle释放空间到OS - 文章图片

查看表实际使用的块数

oracle释放空间到OS - 文章图片

 

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

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

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

此时再次收集统计信息

oracle释放空间到OS - 文章图片

 

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

再次查看数据文件的变化

oracle释放空间到OS - 文章图片

 

此时，我们就可以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的数据文件和可操作的数据库对象

oracle释放空间到OS - 文章图片

 

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;

oracle释放空间到OS - 文章图片

 

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

 

oracle释放空间到OS

标签：size   技术   存在   实验   weight   taf   group   统计信息   png   

本文系统来源：https://www.cnblogs.com/monkey6/p/12918781.html

内容总结

以上是互联网集市为您收集整理的oracle释放空间到OS全部内容，希望文章能够帮你解决oracle释放空间到OS所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/519005.html

来源：【匿名】