Dear Tom,
My question may be very basic, however I failed to find answer in the Net.
I run Oracle DB 11g XE 11.2.0.2.0 and Oracle APEX 5.1.2.00.09 on Linux virtual machine for more than 2 years and all was great. Some 2 weeks ago the server reported near zero disk space. I was sure that another OS user put some files there. My provider kindly transferred my Oracle to another machine with 20 GB free space and all ran perfect. 2 days ago diskspace was eaten again from 20 GB to 10 MB. That time we looked attentively and saw that Oracle TEMP was over 30 GB and UNDO was over 20 GB! It is much-much higher than typical volumes of my projects.
I started searching v$ views for consumers being sure that it was my programming mistake, however the suspect was this code ran several times by ANONYMOUS user:
begin dbms_session.reset_package; end;
I did not write this code myself, but it might be ran by APEX? I tried shrinking TEMP, but this did not help. I started the standard process of creating new TEMP space, switching default, dropping, etc.
So, I have a 2-part question:
1. Can RESET_PACKAGE cause dramatic growth of TEMP and UNDO? What's that??
2. Drop tablespace temp process started from Oracle SQL Developer is running already for some 6 hours and only several minutes ago Linux df showed 33 GB available - is this normal?
Sorry, I am not experiensed in DBA issues. I will be happy to provide more information, but please point where to search. Thank you.
Regards,
Yury
P.S. next day:
It seems I have come to some idea. One of my projects was parsing regular texts into Oracle tables using PL/SQL package procedure with regexp's there. The data source changed against assumed model and the procedure might come into endless loop, it was automatic and nobody saw that. That, I guess, pumped tablespaces and killed disk space. Then, maybe, some internal process of the DB or APEX called RESET_PACKAGE that I saw in v$ views. After fixing the procedure and re-creating TEMP it runs as wanted. Is this scenario realistic? Thank you.
Yury
1. Can RESET_PACKAGE cause dramatic growth of TEMP and UNDO? What's that??
No. It simply releases memory
2. Drop tablespace temp process started from Oracle SQL Developer is running already for some 6 hours and only several minutes ago Linux df showed 33 GB available - is this normal?Dropping a temporary tablespace cannot occur if someone is using it, so we'll wait....and wait....and wait....
So what you want to do in this instance is:
- create a new temp tablespace
- assign people to it
- make it the default for the database
- and then drop the old one when no-one is using it anymore (query v$sort_usage)
To see who is using up all of your undo, you can query v$transaction. Examples of that here
https://asktom.oracle.com/pls/apex/asktom.search?tag=active-transaction-201112