系统磁盘空间不足
使用df-h命令查看系统磁盘空间
使用du -h --max-depth=1 data/*查看哪个文件或目录占用较大的磁盘空间
删除/data/tmp两年前的tmp文件linux空间不足linux系统安装教程,使用以下命令:
# find data/tmp/ -name '*.*' -mtime +720 |xargs rm
ASM磁盘空间不足
ORA-16014:log 3 sequence# 99 not archived, no available destinations
ASM磁盘空间不足导致的重做日志不能归档linux空间不足linux操作系统教程,登录RMAN删除无用的归档日志释放空间
RMAN> delete archivelog until time 'sysdate-7'; --删除7天前的归档日志
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;
更改undo表空间
UNDO表空间过大导致磁盘空间不足的解决
查看表空间名:
SQL> show parameter undo_tablespace
NAMETYPE VALUE
------------------------ ----------- ----------------
undo_tablespace string UNDOTBS1
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST
RMAN
8 rows selected.
SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';
FILE_NAMEBYTES/1024/1024
------------------- -------------------------------------
+DATA/orcl/datafile/undotbs1.264.951533909 85
查看回滚段的使用情况
哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)
SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;
检查UNDO Segment状态:
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USNXACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
2 0 .000358582.001945496 2
7 0 .000358582.001213074 2
0 0 .000358582.000358582 0
8 0 .000480652.000968933 4
6 1 .001945496.003227234 1
4 0 .001945496.001945496 2
1 0 .001945496.001945496 0
5 0 .001945496.001945496 1
9 0 .001945496.001945496 1
10 0 .002189636.002189636 0
3 0 .003044128.003044128 0
11 rows selected.
创建新的UNDO表空间
设置自动扩展参数:
SQL> create undo tablespace undotbs2 datafile '+data' size 100m reuse autoextend on next 50m maxsize 500M;
Tablespace created.
动态更改spfile配置文件:
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
等待原UNDO表空间所有UNDO SEGMENT OFFLINE:
SQL> select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024 HWMSIZE/1024/1024
---------- ---------- --------------- ---------------- -----------------
SHRINKS
----------
4 0 OFFLINE .3046875 5.1171875
3
0 0 OFFLINE .3671875 .3671875
0
3 0 OFFLINE 1.1171875 5.1171875
4
2 0 OFFLINE 1.1171875 4.1171875
2
1 0 OFFLINE 1.1171875 4.1171875
3
5 0 OFFLINE 1.1171875 5.1171875
2
6 0 OFFLINE 1.1171875 4.1171875
3
7 0 OFFLINE 1.9921875 4.1171875
3
8 0 OFFLINE 2.1171875 4.1171875
1
9 0 OFFLINE 2.1171875 5.1171875
4
10 0 OFFLINE 2.1171875 4.1171875
1
11 rows selected.
删除原有的UNDO表空间:
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
更新pfile
SQL> create pfile from spfile;
File created.