My application was running without any problems for almost one year. But recently someone executed a big select for a report, and the Oracle´s TEMP datafile has grown from 5GB to 30GB. The server then ran out of space.
The users's tablespace/datafiles were not affected because I created the files large enough to fit all the data for a long time, in fact, we are still using about 15% of user tablespace space initially allocated. So the problem is not here I guess. No loss of data was reported or detected.
After the server ran out of space, I deleted some old backup files and did the following:
- CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/db/temp02.dbf' SIZE 50m;
- ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
- DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
- CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u02/oradata/db/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M;
- ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
- DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
One hour after that I started to receive calls from users saying that the application is very slow, they can´t log in, everything is slow, the application freezes. But the problem "solve" itself after 2-3 minutes. When I do some test, to check memory/io/cpu, everything is fine. I ask the user and he says that now everything is fine, and I did nothing.
I tried to rebuild index and recompute statistics. But the problem persists.
On the application side, I have a lot Oracle errors logged when the user calls me:
- ORA-12571: TNS: packet writer failure
- ORA-01012: not logged on
- ORA-00028: your session has been killed
- ORA-12170: TNS:Connect timeout occurred
After 2-3 minutes, no more errors and the performance is fine.
This is happening with 80 users logged during the day, and with 2 users logged at night with a very low usage of the system.
Oracle Release 18.104.22.168.0 Server Linux CentOS
Anyone have any ideas please?