在配置emca时, emca 资料库创建不成功，根据错误信息查看log
发现oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
于是怀疑是SYSAUX 空间不足。
1) 查看SYSAUX
sys@O10G>select
File_name,tablespace_name,bytes,autoextensible from dba_data_files where
tablespace_name='SYSAUX';
FILE_NAME TABLESPACE_NAME BYTES autoextensible
--------------------------------------------------
------------------------------
/export/home/oracle/oradata/data/sysaux01.dbf
SYSAUX 340787200 NO
2) 查看sysaux的表空间使用
SELECT
UPPER(F.TABLESPACE_NAME) "Tablespace_name",
D.TOT_GROOTTE_MB
"TBS_Size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES
"Used_Size(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100, 2), '990.99') "Used_Rate(%)",
F.TOTAL_BYTES
"Free_Size(M)",
F.MAX_BYTES "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 4 DESC;
Tablespace_name TBS_Size(M) Used_Size(M) Used_Ra
Free_Size(M) MAX_Bytes(M)
------------------------------ -----------
------------ ------- ------------
------------
SYSAUX 325
319.94 98.44 5.06
1.44
UNDOTBS 200 112.31
56.16 87.69 86.94
SYSTEM
500 237 47.40 263
262.94
USERS 20 2.31 11.55
17.69 17.69
TEST 20 .06
0.30 19.94 19.94
JAMES
100 .12 0.12 99.88
99.88
TTS 200 .19 0.10
199.81 199.81
PERFSTAT 500
.06 0.01 499.94 499.94
3) 查看SYSAUX数据文件的使用率
select a.file_name,a.bytes/1024/1024 TOTAL,b.sb/1024/1024
FREE,100*b.sb/a.bytes "FREE%"
from dba_data_files a,(select
file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where
a.file_id=b.file_id order by a.file_name;
FILE_NAME TOTAL FREE
FREE%
-------------------------------------------------- ----------
----------
----------
+TESTDB_DATA1/o10g/datafile/test.256.671976763 20
19.9375 99.6875
/export/home/oracle/oradata/data/perfstat.dbf
500 499.9375
99.9875
/export/home/oracle/oradata/data/sysaux01.dbf
325 5.0625
1.55769231
/export/home/oracle/oradata/data/system01.dbf
500 263
52.6
/export/home/oracle/oradata/data/undotbs01.dbf 200
87.6875 43.84375
/export/home/oracle/oradata/data/users01.dbf
20 17.6875
88.4375
/export/home/oracle/oradata/james.dbf 100
99.875 99.875
/export/home/oracle/oradata/tts_data.dbf
200 199.8125 99.90625
4) 发现SYSAUX表空间的空间不足(default 500M)，并且autoextensible属性没有开启，所以无法autoextend。
5) 解决方法：
1.给SYSAUX添加数据文件
alter tablespace
sysaux add datafile '/export/home/oracle/oradata/data/sysaux02.dbf' size
50M;
alter tablespace sysaux drop datafile
'/export/home/oracle/oradata/data/sysaux02.dbf';
2.Resize现有的数据文件
ALTER DATABASE DATAFILE
'/export/home/oracle/oradata/data/sysaux01.dbf' RESIZE 500M;
3.让现有的数据文件能够自动增长
alter database datafile
'/export/home/oracle/oradata/data/sysaux01.dbf' autoextend on;
alter database datafile '/db/pdhptlt10g/oradata/sysaux_f01.dbf' autoextend on next 50m maxsize 1000m;
5.如果是用户的TBS，也给以给user重新制定一个新的TBS
alter user username default
tablespace otherTBS;
6.收缩TBS。
alter tablespace SYSAUX coalesce;
然后重新配置emca OK
