1.查看所有表空间信息（包括总大小、已使用、空闲、使用百分比）：
select a.tablespace_name,total "Total(M)",free "Free(M)",total-free "Used(M)",round(((total-free)/total)*100,2) "Used(%)" from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
查询结果：
TABLESPACE_NAME Total(M) Free(M) Used(M) Used(%)
---------- ---------- ---------- ----------
MY_TABLESPACES 30720 30718.8125 1.1875 0
SYSAUX 880 70.0625 809.9375 92.04
UNDOTBS1 8720 8695.25 24.75 .28
TABLESPACE_NAME Total(M) Free(M) Used(M) Used(%)
---------- ---------- ---------- ----------
USER_DATA2 50 49 1 2
USERS 187.5 183.375 4.125 2.2
SYSTEM 10960 15.25 10944.75 99.86
TABLESPACE_NAME Total(M) Free(M) Used(M) Used(%)
---------- ---------- ---------- ----------
EXAMPLE 100 21.25 78.75 78.75
USER_DATA 15600 5081.5625 10518.4375 67.43
2.查看物理表空间的使用信息：
SELECT A.TABLESPACE_NAME,B.TOTAL/1024/1024||'M',(B.TOTAL-A.USE)/1024/1024||'M' FREE FROM
(
select TABLESPACE_NAME,sum(bytes) as USE from dba_segments
where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
GROUP BY TABLESPACE_NAME
) A ,
(
WITH TABLESPACE_TOTAL AS
(
SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='YES' group by tablespace_name
UNION ALL
SELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='NO' group by tablespace_name
)
SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
查询结果：
TABLESPACE_NAME B.TOTAL/1024/1024||'M' FREE
--------------------------------------------------------------------------------
MY_TABLESPACES 32767.984375M 32767.796875M
EXAMPLE 32767.984375M 32690.234375M
TABLESPACE_NAME B.TOTAL/1024/1024||'M' FREE
--------------------------------------------------------------------------------
USER_DATA 30720M 20201.3125M
这个命令看不到系统表空间SYSTEM的信息，SYSTEM可以直接查看文件.dbf大小：
ls -lh $ORACLE_HOME
-rw-r----- 1 oracle dba 11G May 3 12:25 system01.dbf
如果你的系统表空间SYSTEM占用比较多的空间，可能有以下几方面的原因：
(1)没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间
(2)开启了审计，请检查此表的大小AUD$
你可以运行以下查询来检查一下系统表空间哪些表比较大：
select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='SYSTEM' group by segment_name) where sx>100 order by sx desc;
查看该表纪录数
select count(*) total from AUD$;
3.查看表空间对应的文件名
select file_name from dba_data_files where tablespace_name='SYSTEM';
/opt/oracle/oradata/orcl/system01.dbf
解决方法大概有下面几种:
1.重新调整SYSTEM表空间的物理数据文件的大小：
alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' autoextend on;
alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' resize 10240M;
注意：单文件最大支持32G。如果表空间超过32G，需要新增数据文件。
2.为SYSTEM表空间另外新增一个数据文件：
alter tablespace system add datafile '/opt/oracle/oradata/orcl/system02.dbf' size 10240M;
3.truncate掉AUD$表、并关闭审计功能：
truncate table AUD$;
alter system set audit_trail=none scope=spfile;
showdown immediate;
startup;
注意：使用这种方法，效果立竿见影。如果不关闭此功能，需要定期清理此表。
4.将AUD$默认表空间由system移出。
创建迁移AUD$的普通表空间：
create tablespace records
datafile '/opt/oracle/oradata/orcl/auds01.dbf' size 10G autoextend on;
迁移到目标表空间：
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDS');
END;
/
查询所属表空间
select OWNER, TABLE_NAME, TABLESPACE_NAME from DBA_TABLES where TABLE_NAME = 'AUD$' and OWNER = 'SYS';
----------------
Oracle审计日志清理
数据库安装后数据库审计开关默认设置为DB（audit_trail=DB，如果为其他值则表示被认为设置过）：
SQL> show parameter audit_trail;
Name TYPE VALUE
-----------------------------------
audit_trail string DB
audit_trail=DB时Oracle会把每次审计跟踪记录在数据库的的AUD$的表中。
默认情况下，AUD$这张表所在的表空间，是Oracle最重要的SYSTEM表空间：
SQL> select owner,table_name,tablespace_name from dba_tables a where a.table_name ='AUD$';
OWNER TABLE_NAME TABLESPACE_NAME
--------------------------------------------------------------
SYS AUD$ SYSTEM
通常情况下，SYSTEM表空间都会设置为自动扩展（AUT=YES）：
SQL> select a.tablespace_name,a.bytes,a.autoextensible from dba_data_files a where a.tablespace_name='SYSTEM';
TABLESPACE_NAME BYTES AUT
--------------------------------------------------------
SYSTEM 1.1492E+10 YES
因此，时间长了以后，SYSTEM表空间最终会把磁盘撑爆。
管理审计日志
进入审计日志目录：
cd $ORACLE_BASE/实例名/$ORACLE_SID/adump
例如：ls $ORACLE_BASE/admin/orcl/adump
仅保留30天的文件，其他的都删除
find ./ -type f -name "*.aud" -mtime +31 |xargs rm -f
如果不保留、也可以删除所有审计文件
find ./ -type f -name "*.aud" |xargs rm -f
find ./ -type f -name "*.aud" –delete
设置审计参数关闭：
SQL> alter system set audit_trail=none scope=spfile;
SQL> shutdown immediate;
SQL> startup;
注意：虽然关闭了审计，但Oracle还有一个叫强制审计的功能。如果操作者用SYSDBA/SYSOPER权限登录数据库，进行startup、shutdown等操作，Oracle依然会给你在$ORACLE_BASE/admin/$ORACLE_SID/adump目录中记.aud的文件。
因此：开了DB功能，会同时将审计日志记在AUD$表中和操作系统aud文件中。时间长了以后，除了存SYSTEM表空间吧磁盘撑爆的风险外，还存在审计文件太多导致系统inodes耗尽的风险。
如果设置为NONE，仍然会而且毫无其他办法的将记录在操作系统aud文件中。
如果系统inodes被耗尽，数据库会报错：
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
查看审计日志记录的表名和审计文件的系统目录：
SQL> show parameter audit;
默认的数据库的表为：sys.aud$，操作系统目录为：$ORACLE_BASE/admin/实例名/adump/
最后：
1.如果为DB，请定期清空aud$表：
SQL> truncate table sys.aud$;
2.如果不用DB功能，可以设置关闭：
SQL> alter system set audit_trail=none scope=spfile;
SQL> shutdown immediate;
SQL> startup;
3.如果为NONE，请定期清空aud文件或者配置crontab定时任务定时清空aud文件（清空时不要直接删除adump目录，会导致sqlplus不了数据库）。
手动清除aud文件：
--进入审计日志目录
cd $ORACLE_BASE/admin/$ORACLE_SID/adump
--删除1个月前的审计文件
find ./ -type f -name "*.aud" -mtime +31 |xargs rm -rf
--一次清空所有审计文件
find ./ -type f -name "*.aud" |xargs rm -rf
find ./ -type f -name "*.aud" -mtime +7 -delete
配置定时任务自动定时清理1周以前的aud文件：
#su - oracle
$crontab -e
0 23 1 * * find $ORACLE_BASE/admin/$ORACLE_SID/adump/ -type f -name "*.aud" -mtime +7 -delete
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2019-9-18 9:47:08 |
赞(
操作
