This discussion is archived
12 Replies Latest reply: Feb 10, 2013 9:33 PM by rp0428 RSS

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

969526 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points