测试：
建表空间
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 = 7GROUP BY E.SEGMENT_TYPE, E.OWNER, E.SEGMENT_NAME, F.FILE_NAMEORDER BY 4 DESC;
向表中插入数据，查看数据文件的变化
/* Formatted on 2020/5/19 下午 04:31:24 (QP5 v5.163.1008.3004) */DECLAREBEGIN 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 = 7GROUP BY E.SEGMENT_TYPE, E.OWNER, E.SEGMENT_NAME, F.FILE_NAMEORDER 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 = 89GROUP BY E.SEGMENT_TYPE, E.OWNER, E.SEGMENT_NAME, F.FILE_NAMEORDER BY 4 DESC;
重建上面的索引，之后RESIZE数据文件即可。
