SQL> SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
# 与客户描述的一致，TS_DATA表空间量大，但实际使用量小。
2、查看表空间上的用户
SQL> select owner,tablespace_name ,sum(bytes)/1024/1024/1024 G from dba_segments where tablespace_name = 'TS_DATA' group by owner,tablespace_name;
4、查看表空间TOP 10大索引
SQL> select * from (
2 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024/1024 "G"
3 from dba_segments where segment_type = 'INDEX' and tablespace_name = 'TS_DATA' order by bytes desc)
4 where rownum <= 10;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G
----------------- ---------------------------- ------------------ ------------------ ----------
TMC TMC_LOG_PK INDEX TS_DATA 13.03125
BWPLATFORM SOA_SYSTEM_LOG INDEX TS_DATA .433837891
TMC INDEX_USER INDEX TS_DATA .419921875
TMC INDEX_ORGAN INDEX TS_DATA .390625
BWPLATFORM PK_PLAT_USER_LOGIN_LOG INDEX TS_DATA .267150879
TMC SEI_IX_NAME INDEX TS_DATA .216796875
TMC FTVC_IX_EXPIRE_DATE INDEX TS_DATA .1953125
FDCYS I_TR_YWBJSJ INDEX TS_DATA .1953125
BWPLATFORM SYS_C0066879 INDEX TS_DATA .1484375
TMC FTVC_UK_CERT_NO_FO_ID INDEX TS_DATA .1328125
10 rows selected.
5、查看表空间管理方式
SQL> select tablespace_name, block_size,contents, extent_management, segment_space_management, allocation_type, segment_space_management from dba_tablespaces where tablespace_name='TS_DATA';
TABLESPACE_NAME BLOCK_SIZE CONTENTS EXTENT_MAN SEGMEN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ---------- ------ --------- ------
TS_DATA 8192 PERMANENT LOCAL AUTO SYSTEM AUTO
6、查看数据文件使用情况
SQL> col file_name for a50
SQL> select file_name, tablespace_name, bytes/1024/1024/1024 "used G", maxbytes/1024/1024/1024 "total G" from dba_data_files where tablespace_name = 'TS_DATA' order by bytes;
FILE_NAME TABLESPACE_NAME used G total G
-------------------------------------------------- ------------------------------ ---------- ----------
/Oradata/2nd_fdc/TS_DATA74.dbf TS_DATA 5 0
/Oradata/2nd_fdc/TS_DATA76.dbf TS_DATA 5 0
/Oradata/2nd_fdc/TS_DATA75.dbf TS_DATA 5 0
...................
/Oradata/2nd_fdc/TS_DATA18.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA19.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA11.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA14.dbf TS_DATA 31.9921875 31.9999847
76 rows selected.
7、查看表空间高水位情况
SQL> select max(block_id)*8/1024/1024 "G size" from dba_extents where tablespace_name='TS_DATA';
G size
----------
31.9920731
降低高水位
1、检查失效索引
（建议处理高水位前和后都要检查。并及时处理失效索引）--检查分区索引（包括分区表的分区本地索引，分区表的分区全局索引），如果存在重建，并修改并行度
select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name ||' parallel 10;' from
dba_ind_partitions a
where a.status='UNUSABLE';
select 'alter index '||a.index_owner||'.'||a.index_name||' parallel 1;' from
dba_ind_partitions a
where a.status='UNUSABLE’;
--检查普通索引(包括普通表的索引，分区表的普通全局索引)，如果存在重建，并修改并行度
select 'alter index '||a.owner||'.'||a.index_name||' rebuild parallel 10;' from
dba_indexes a
where a.status='UNUSABLE';
select 'alter index '||a.owner||'.'||a.index_name||' parallel 1;' from
dba_indexes a
where a.status='UNUSABLE’;
2、查询指定用户中的高水位
# 比较表的行数和表的大小关系。如果行数为0，而表的当前占用大小减去初始化时的大小（INITIAL_EXTENT）后依然很大，那么说明该表有高水位。=====为了保证结果准确，建议先对表进行统计信息收集。
# 查看TMC用户高水位
SQL> SELECT D.OWNER,
2 ROUND(D.NUM_ROWS / D.BLOCKS, 2),
3 D.NUM_ROWS,
4 D.BLOCKS,
5 D.TABLE_NAME,
6 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
7 FROM DBA_TABLES D
8 WHERE D.BLOCKS > 10
9 AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
10 AND d.OWNER = 'TMC' ;
# 查看表上边的索引
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'JG_TBL_FDC_EST_REGISTE';
INDEX_NAME STATUS
------------------------------ --------
EST_REGISTE_PARCEL_NO VALID
EST_REGISTE_PROJ_NO VALID
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'FDC_YS_CONTRACT_APPENDIX';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000060570C00006$$ VALID
SYS_IL0000060570C00020$$ VALID
FYCA_IX_FYC_ID VALID
FYCA_IX_YS_FYB_ID VALID
SYS_IL0000060570C00026$$ VALID
FYCA_PK VALID
6 rows selected.
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'REMC_WS_LOG_REQUEST';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000142741C00026$$ VALID
SYS_IL0000142741C00027$$ VALID
SYS_IL0000142741C00028$$ VALID
# 查看FDCYS用户高水位线
SELECT D.OWNER,
ROUND(D.NUM_ROWS / D.BLOCKS, 2),
D.NUM_ROWS,
D.BLOCKS,
D.TABLE_NAME,
ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER = 'FDCYS' ;
# 查看表上的索引
SQL> select index_name,status from dba_indexes where owner = 'FDCYS' and table_name = 'SYS_20191171126389483_TEMP';
INDEX_NAME STATUS
------------------------------ --------
SYS_C0060562 VALID
3、收缩指定表的高水位
# TMC用户收缩高水位线
alter table TMC.JG_TBL_FDC_EST_REGISTE enable row movement;
alter table TMC.JG_TBL_FDC_EST_REGISTE shrink space;
alter index TMC.EST_REGISTE_PARCEL_NO rebuild;
alter index TMC.EST_REGISTE_PROJ_NO rebuild;
alter table TMC.JG_TBL_FDC_EST_REGISTE disable row movement;
alter table TMC.FDC_YS_CONTRACT_APPENDIX enable row movement;
alter table TMC.FDC_YS_CONTRACT_APPENDIX shrink space;
alter index TMC.FYCA_IX_FYC_ID rebuild parallel 4 nologging;
alter index TMC.FYCA_IX_YS_FYB_ID rebuild parallel 4 nologging;
alter index TMC.FYCA_PK rebuild parallel 4 nologging;
alter index TMC.FYCA_IX_FYC_ID parallel 1 ;
alter index TMC.FYCA_IX_YS_FYB_ID parallel 1 ;
alter index TMC.FYCA_PK parallel 1 ;
alter index TMC.FDC_YS_CONTRACT_APPENDIX disable row movement;
alter table TMC.REMC_WS_LOG_REQUEST enable row movement;
alter table TMC. REMC_WS_LOG_REQUEST shrink space;
alter table TMC.REMC_WS_LOG_REQUEST disable row movement;
# FDCYS用户收缩高水位线
alter table FDCYS.SYS_20191171126389483_TEMP enable row movement;
alter table FDCYS.SYS_20191171126389483_TEMP shrink space;
alter index SYS_C0060562 rebuild;
alter table FDCYS.SYS_20191171126389483_TEMP disable row movement;