1, 查询数据库各个表空间利用率:
- SELECT Upper(F.TABLESPACE_NAME) "表空间名",
- D.TOT_GROOTTE_MB "表空间大小 (M)",
- D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间 (M)",
- To_char(Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
- 2),
- '990.99') || '%' "使用比",
- F.TOTAL_BYTES "空闲空间 (M)",
- F.MAX_BYTES "最大块 (M)"
- FROM (SELECT TABLESPACE_NAME,
- Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
- Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
- FROM SYS.DBA_FREE_SPACE
- GROUP BY TABLESPACE_NAME) F,
- (SELECT DD.TABLESPACE_NAME,
- Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
- FROM SYS.DBA_DATA_FILES DD
- GROUP BY DD.TABLESPACE_NAME) D
- WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
- ORDER BY 1;
2, 根据表空间名称查询表的大小:
- select SEGMENT_NAME, TABLESPACE_NAME,SEGMENT_TYPE, sum(BYTES / 1024 / 1024 ) || 'M'
- from dba_segments
- where /*SEGMENT_TYPE = 'TABLE'
- AND */TABLESPACE_NAME='TEST'
- group by SEGMENT_NAME, TABLESPACE_NAME,SEGMENT_TYPE
- ORDER BY sum(BYTES / 1024 / 1024) DESC;
3, 根据索引查询所属表:
- select col.table_owner "table_owner",
- idx.table_name "table_name",
- col.index_owner "index_owner",
- idx.index_name "index_name",
- uniqueness "uniqueness",
- status,
- column_name "column_name",
- column_position
- from dba_ind_columns col, dba_indexes idx
- where col.index_name = idx.index_name
- and col.table_name = idx.table_name
- and col.table_owner = idx.table_owner
- /*and col.table_owner = '&owner'
- and col.table_name = '&table_name'*/
- and idx.index_name='index1'
- order by idx.table_type,
- idx.table_name,
- idx.index_name,
- col.table_owner,
- column_position;
4, 表索引重建, 通过并行方式重建表索引, 其中 10 可以根据 Oracle 物理机 CPU 个数更改:
alter index INDEX123456 rebuild online parallel 10;
来源: https://www.cnblogs.com/luckjinyan/p/12902885.html