12 Replies Latest reply: Feb 10, 2013 11:33 PM by rp0428 RSS

    ORA-00600: internal error code, arguments: [17183], [0x033134908], [], [],

    969526
      Hi Exports,


      I am getting the ORA-00600: internal error code, arguments: [17183], [0x033134908], [], [], [], [], [], []

      The temporary tablespace of the database is full and i am trying to do the following steps.




      SQL> select * from dba_temp_files;

      FILE_NAME FILE_ID TABLESPACE_NAME BYTES Blocks Etat RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
      ----------------------------------------------------------------- ---------- ------------------------- ---------- ------------ ------ ------------ --- ---------- ---------- ------------
      USER_BYTES USER_BLOCKS
      ---------- -----------
      G:\ORADATA\TEMP01.DBF 1 TEMP 3.4359E+10 4,194,176 AVAILA 1 YES 3.4360E+10 4194302 80
      3.4358E+10 4194048

      J:\ORADATA\TIVOLIORTEMPTS.DBF 5 TIVOLIORTEMPTS 104857600 12,800 AVAILA 1 NO 0 0 0
      103809024 12672

      J:\ORADATA\TEMP_ORISSA_MASTER.TMP 2 TEMP_ORISSA_MASTER 10485760 1,280 AVAILA 1 YES 52428800 6400 128
      9437184 1152

      J:\ORADATA\TEMP_ORISSA_BENCHMARK.TMP 3 TEMP_ORISSA_BENCHMARK 680525824 83,072 AVAILA 1 YES 1073741824 131072 2
      679477248 82944

      J:\ORADATA\TEMP_ORISSA_TRANSACTION 4 TEMP_ORISSA_TRANSACTION 104857600 12,800 AVAILA 1 YES 1073741824 131072 2
      103809024 12672


      5 rows selected.

      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP 3.4359E+10 0
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024

      5 rows selected.

      SQL> CREATE TEMPORARY TABLESPACE MYTEMPFILE
      2 TEMPFILE 'G:\ORADATA\TEMP001.dbf'
      3 SIZE 1000M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

      Tablespace created.

      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMPFILE;

      Database altered.

      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP 3.4359E+10 0
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024
      MYTEMPFILE 1048576 1047527424

      6 rows selected.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES;
      ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES
      *
      ERROR at line 1:
      ORA-25152: TEMPFILE cannot be dropped at this time


      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024
      MYTEMPFILE 11534336 1037041664

      5 rows selected.

      SQL> select STATUS,ENABLED,STATUS,BYTES/1024/1024 from v$tempfile;

      Etat ENABLED Etat BYTES/1024/1024
      ------ ---------- ------ ---------------
      OFFLIN READ WRITE OFFLIN 0
      ONLINE READ WRITE ONLINE 100
      ONLINE READ WRITE ONLINE 10
      ONLINE READ WRITE ONLINE 649
      ONLINE READ WRITE ONLINE 100
      ONLINE READ WRITE ONLINE 1000

      6 rows selected.

      SQL> desc v$tempfile;
      Name Null? Type
      ----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
      FILE# NUMBER
      CREATION_CHANGE# NUMBER
      CREATION_TIME DATE
      TS# NUMBER
      RFILE# NUMBER
      STATUS VARCHAR2(7)
      ENABLED VARCHAR2(10)
      BYTES NUMBER
      BLOCKS NUMBER
      CREATE_BYTES NUMBER
      BLOCK_SIZE NUMBER
      NAME VARCHAR2(513)

      SQL> select file#,STATUS,ENABLED,STATUS,BYTES/1024/1024 from v$tempfile;

      FILE# Etat ENABLED Etat BYTES/1024/1024
      ---------- ------ ---------- ------ ---------------
      1 OFFLIN READ WRITE OFFLIN 0
      5 ONLINE READ WRITE ONLINE 100
      2 ONLINE READ WRITE ONLINE 10
      3 ONLINE READ WRITE ONLINE 649
      4 ONLINE READ WRITE ONLINE 100
      6 ONLINE READ WRITE ONLINE 1000

      6 rows selected.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES;
      ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES
      *
      ERROR at line 1:
      ORA-25152: TEMPFILE cannot be dropped at this time


      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' offline;

      Database altered.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES;
      ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES
      *
      ERROR at line 1:
      ORA-25152: TEMPFILE cannot be dropped at this time


      SQL> select * from dba_temp_files;

      FILE_NAME FILE_ID TABLESPACE_NAME BYTES Blocks Etat RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
      ----------------------------------------------------------------- ---------- ------------------------- ---------- ------------ ------ ------------ --- ---------- ---------- ------------
      USER_BYTES USER_BLOCKS
      ---------- -----------
      G:\ORADATA\TEMP01.DBF 1 TEMP AVAILA


      J:\ORADATA\TIVOLIORTEMPTS.DBF 5 TIVOLIORTEMPTS 104857600 12,800 AVAILA 1 NO 0 0 0
      103809024 12672

      J:\ORADATA\TEMP_ORISSA_MASTER.TMP 2 TEMP_ORISSA_MASTER 10485760 1,280 AVAILA 1 YES 52428800 6400 128
      9437184 1152

      J:\ORADATA\TEMP_ORISSA_BENCHMARK.TMP 3 TEMP_ORISSA_BENCHMARK 680525824 83,072 AVAILA 1 YES 1073741824 131072 2
      679477248 82944

      J:\ORADATA\TEMP_ORISSA_TRANSACTION 4 TEMP_ORISSA_TRANSACTION 104857600 12,800 AVAILA 1 YES 1073741824 131072 2
      103809024 12672

      G:\ORADATA\TEMP001.DBF 6 MYTEMPFILE 1048576000 128,000 AVAILA 1 YES 2097152000 256000 1280
      1047527424 127872


      6 rows selected.

      SQL> select file#,STATUS,ENABLED,STATUS,BYTES/1024/1024 from v$tempfile;

      FILE# Etat ENABLED Etat BYTES/1024/1024
      ---------- ------ ---------- ------ ---------------
      1 OFFLIN READ WRITE OFFLIN 0
      5 ONLINE READ WRITE ONLINE 100
      2 ONLINE READ WRITE ONLINE 10
      3 ONLINE READ WRITE ONLINE 649
      4 ONLINE READ WRITE ONLINE 100
      6 ONLINE READ WRITE ONLINE 1000

      6 rows selected.

      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

      Database altered.

      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024
      MYTEMPFILE 28311552 1020264448

      5 rows selected.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' online;

      Database altered.

      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP 3.4359E+10 0
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024
      MYTEMPFILE 28311552 1020264448

      6 rows selected.

      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMPFILE;

      Database altered.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES;
      ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES
      *
      ERROR at line 1:
      ORA-25152: TEMPFILE cannot be dropped at this time


      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' offline;

      Database altered.

      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES;
      ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' DROP INCLUDING DATAFILES
      *
      ERROR at line 1:
      ORA-25152: TEMPFILE cannot be dropped at this time


      SQL> ALTER DATABASE TEMPFILE 'G:\ORADATA\TEMP01.DBF' online;

      Database altered.


      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

      Database altered.

      SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

      TABLESPACE_NAME BYTES_USED BYTES_FREE
      ------------------------- ---------- ----------
      TEMP 3.4359E+10 0
      TEMP_ORISSA_MASTER 4194304 6291456
      TEMP_ORISSA_BENCHMARK 680525824 0
      TEMP_ORISSA_TRANSACTION 37748736 67108864
      TIVOLIORTEMPTS 1048576 103809024
      MYTEMPFILE 32505856 1016070144

      6 rows selected.

      SQL> exit


      I am not able to drop the Temp file as it is used by some users.

      When i checked my Alert Log then i am getting the ora-600 error....


      When i check metalink for this argument i found that its a bug in 91 &10gR2

      Please suggest some solution of this problem.

      Is it a serious issue of database.