はじめに
Oracle 11g XEで、SYSAUX表領域とUNDO表領域のデータファイルが逆になっているのに気がつきました。
col tablespace_name for a10 col file_name for a60 col mb for 999999 select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files order by tablespace_name;
既知のバグっぽい
OTNのディスカッションフォーラムにも上がっているけど、既知のバグみたいです。
After installing the Oracle database express edition 11g on the 32 bit windows there are some issues with the datafile naming convention.
The datafile undotbs01.dbf belongs to the SYSAUX tablepsace & sysaux01.dbf belongs to the UNDO tablespace.
I don't knw the recurperssion of the same OR whether it is Bug ???
it's a known bug, as reported here: Datafiles and here SYSAUX and UNDOTBS1 are reversed
OTN Discussion Forums : Express edition 11g datafiles Issue ...
対処方法(Windows 7の場合)
以下のサイトに載っている手順をWindows向けに書き換える。
sqlplus /nolog connect sys/oracle as sysdba shutdown immediate; exit; cd C:\oraclexe\app\oracle\oradata\XE ren SYSAUX.dbf UNDOTBS02.dbf ren UNDOTBS1.dbf SYSAUX.dbf ren UNDOTBS02.dbf UNDOTBS01.dbf sqlplus /nolog connect sys/oracle as sysdba startup mount alter database rename file 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX.dbf' to 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS01.dbf'; alter database rename file 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.dbf' to 'C:\oraclexe\app\oracle\oradata\XE\SYSAUX.dbf'; alter database open;
直った!
col tablespace_name for a10 col file_name for a60 col mb for 999999 select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files order by tablespace_name;