[20141008]临时表空间问题.txt

--我的测试环境,出现如下提示在alert*.log文件中:

WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

         ALTER TABLESPACE ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP2

$ ll -l /u01/app/oracle11g/oradata/test/temp*
-rw-r-----  1 oracle11g oinstall 262152192 2014-09-28 17:42:57 /u01/app/oracle11g/oradata/test/temp01.dbf

SYS@test> select file_name,file_id, tablespace_name from dba_temp_files;

FILE_NAME                                                       FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------------------------
/u01/app/oracle11g/oradata/test/temp01.dbf                            1 TEMP

SYS@test> select temporary_tablespace , username from dba_users where temporary_tablespace<>'TEMP';
no rows selected

--生成建立控制文件的脚本.

SYS@test> alter database backup controlfile to trace ;
Database altered.

--检索temp2也没有找到任何信息.为什么每次启动数据库,会出现如上提示呢?我以前确实建立过temp2的临时表空间.

SYS@test> select ts#,name,online$,contents$ from sys.ts$ where name like 'TEMP%';
       TS# NAME                    ONLINE$  CONTENTS$
---------- -------------------- ---------- ----------
         3 TEMP                          1          1
      1049 TEMP2                         1          1

-- 另外sys.ts$也记录了我以前删除的表空间信息.我看了其他一些已经删除表空间的信息:
SYS@test> select ts#,name,online$,contents$ from sys.ts$ where name like 'TEST20%' or name='SYSTEM';
       TS# NAME                    ONLINE$  CONTENTS$
---------- -------------------- ---------- ----------
      1050 TEST2010                      3          0
      1051 TEST2011                      3          0
      1052 TEST2012                      3          0
         0 SYSTEM                        1          0

--很明显online$=1表示在线.test20XX这些表空间已经删除.看前面的信息可以发现temp2临时表空间依旧在线.
--当然选择修改数据字典方式存在一点风险,比较保险的做法是建立它,然后删除掉.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  '/u01/app/oracle11g/oradata/test/temp02.dbf' SIZE 1M AUTOEXTEND OFF
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

*
ERROR at line 1:
ORA-01543: tablespace 'TEMP2' already exists

--提示已经存在.

SYS@test> ALTER TABLESPACE temp2 ADD TEMPFILE '/u01/app/oracle11g/oradata/test/temp02.dbf';
ALTER TABLESPACE temp2 ADD TEMPFILE '/u01/app/oracle11g/oradata/test/temp02.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle11g/oradata/test/temp02.dbf'
ORA-17610: file '/u01/app/oracle11g/oradata/test/temp02.dbf' does not exist and no size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--还是不行.alert*.log给出的方案不行.

SYS@test> drop tablespace temp2;
Tablespace dropped.

--ok成功!

SYS@test> select ts#,name,online$,contents$ from sys.ts$ where name like 'TEMP%';
       TS# NAME                    ONLINE$  CONTENTS$
---------- -------------------- ---------- ----------
         3 TEMP                          1          1
      1049 TEMP2                         3          0

--ok,现在问题应该解决.
--不知道自己做什么测试导致这个问题.

$ grep -n  -i temp2 /u01/app/oracle11g/diag/rdbms/test/test/trace/alert_test.log
157178:           Empty temporary tablespace: TEMP2
157264:CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
157268:ORA-1543 signalled during: CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
157273:CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
157277:ORA-1543 signalled during: CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
157282:ALTER TABLESPACE temp2 ADD TEMPFILE '/u01/app/oracle11g/oradata/test/temp02.dbf'
157283:ORA-1119 signalled during: ALTER TABLESPACE temp2 ADD TEMPFILE '/u01/app/oracle11g/oradata/test/temp02.dbf'...
157285:drop tablespace temp2
157286:Completed: drop tablespace temp2

--可以确定后面的启动在没有出现这个提示!