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;
重建上面的索引，之后RESIZE数据文件即可。