日常运维中经常需要关注数据库的表空间占用情况，手动扩容，增加表空间文件等，记录一些常用sql。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 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;
|
1
2
3
4
5
6
| SELECT OWNER, T.SEGMENT_NAME, T.SEGMENT_TYPE, SUM(T.BYTES / 1024 / 1024) M
FROM DBA_SEGMENTS T
WHERE T.OWNER = 'HDM'
AND T.SEGMENT_TYPE = 'TABLE'
GROUP BY OWNER, T.SEGMENT_NAME, T.SEGMENT_TYPE
ORDER BY M DESC;
|
查看表空间排名
| OWNER |
SEGMENT_NAME |
SEGMENG_TYPE |
M |
| HDM |
HBI_DCM_ERROR_B |
TABLE |
973 |
| HDM |
HDM_INTERFACE_EBS_JE |
TABLE |
288 |
| HDM |
BIN$ZlgSEHdQrejgU4EMeAqCog==$0 |
TABLE |
264 |
| HDM |
HDM_DATA_JE_OFFSET |
TABLE |
89 |
| HDM |
HDM_DATA_JE_OFFSET180203 |
TABLE |
80 |
HBI_DCM_ERROR_B的大小为973M，但是实际数据只有2W多条，推测可能存在DELETE没有释放存储的问题，执行释放语句
1
2
3
4
5
6
| //释放
alter table HBI_DCM_ERROR_B move;
//执行move会导致该表的索引全部失效，如果有则需要重建索引
SELECT INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,INDEX_TYPE,STATUS FROM DBA_INDEXES WHERE TABLE_OWNER='HDM';
//重建
ALTER INDEX INDEX_NAME REBUILD;
|
BIN$ZlgSEHdQrejgU4EMeAqCog==$0这种以BIN$开头的表是因为oracle开启了flashback闪回区，drop掉的表会放到闪回区。
当误删除某些表时，可以通过命令恢复回来：
1
| flashback table table_name to before drop；
|
查询所有此类表
1
| select * from recyclebin where type='TABLE';
|
用来删除回收站中所有的表
1
2
3
4
5
| --这语句就能清除所有以BIN开头的残留文件
PURGE RECYCLEBIN;
--通过表名或者产生的BIN$名清除指定的表
PURGE TABLE "HDM_DATA_JE_OFFSET180203";
PURGE TABLE "BIN$ZlgSEHdQrejgU4EMeAqCog==$0";
|
可以在的Drop表时不产生Bin型表
1
| DROP TABLE "TableName" purge；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| --临时表空间
SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 FILE_SIZE,AUTOEXTENSIBLE FROM DBA_TEMP_FILES;
--查询表空间文件名称，路径
select * from dba_data_files
--查看表空间是否自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
--更改表空间自动增长
--临时表空间
ALTER DATABASE TEMPFILE '/ORADATA/DMS/DMS_TS_DATA.DBF' AUTOEXTEND ON NEXT 512M ;
--非临时表空间
ALTER DATABASE DATAFILE '/ORADATA/DMS/DMS_TS_DATA.DBF' AUTOEXTEND ON NEXT 512M;
--单个表空间文件不能超过32个G，新增表空间文件
alter tablespace DMS_TS_DATA add datafile '/u01/app/oracle/oradata/DMPROD/DMS_TS_DATA_03.DBF' SIZE 30720M;
|