How I investigated and shrank Oracle XE

Or how I learned to stop worrying and log in as sysdba to reset the system password

Oracle XE has a limit of 11 GB on data, which I was close to using.  However, the entire Oracle XE system was taking up 52 GB on disk.  Here is how I investigated why it was so large and how I shrunk the size.

I started with google and didn't find anything obvious.  I then went to the web-based dashboard / control for oracle XE:
localhost:8080/apex

and attempted to log in as system.  I was informed that the password was expired.  Google revealed that I should log in as sysdba to reset the password, and that I could log in in as sysdba and reset the password using:

1. running sqlplus.exe as Administrator
2. for username entering 
• /as sysdba
3. alter user system identified by newpass

I then logged into the dashboard / control and checked under the "storage" tab.  This listed the tablespaces in the system.

Tablespace	Free Space (MB)	Used Space (MB)	Percent Used	Maximum (MB)
TEMP	31,640	2		32,768
UNDOTBS1	4,758	72		32,768
SYSAUX	44	716		32,768
USERS	4,005	6,506		11,264
SYSTEM	4	366		600
SOMEOTHER_DAT	36			100

Clicking on a tablespace brings up a list of the tables within the tablespace and their sizes.  I started by looking at USERS and was able to find a relatively large and unused table to clean up (~4 GB).

Note the inaccuracy of the bar graph - SYSAUX is reporting used space of 716 MB out of a max of 32,768 MB, however the bar graph for Percent Used indicates it is nearly full.  Unfortunately I initially just looked at the bar graph, and looked for / tried solutions to shrink the SYSAUX tablespace.  This mainly involved deleting old snapshots, but ultimately did not help.

Another google search / reading forum discussions led me to try the old school method of just manually searching the Oracle XE install directory for the directory / file that was the largest.  What I found was that the directory

oracleXe\app\oracle\oradata\XE

(note the base directory "oracleXe" is the install directory for Oracle XE on my system)

contained the bulk of the data - 47.9 GB.  Within it were the tablespace files corresponding to the tablespaces listed above.  The file sizes do not mach the information in the above table though:

TEMP.DBF is the largest by far.  Also note that despite removed 4GB from the USERS tablespace, the tablespace file is still > 10GB.  The size of SYSAUX does not match any of the parameters.

I then set about investigating what is in the TEMP tablespace and if it could be shrunk.  I found this discussion on OTN:

https://forums.oracle.com/forums/thread.jspa?threadID=1122210

and this on stackoverflow:

http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

and this in the OracleFAQs

http://www.orafaq.com/node/2

Short answers:  copied directly from OracleFAQ's:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

I certainly have done a lot of those on large datasets, so having a very large temp tablespace makes sense.

Based on the above forum discussion and stackoverflow question/answer, I used this query to check the temp tablespaces:
SELECT tablespace_name, file_name, bytes
  FROM dba_temp_files WHERE tablespace_name like 'TEMP%'

The result confirmed the presence of the very large (~30 GB) TEMP tablespace.

I then tried shrinking the TEMP tablespace with:
alter tablespace temp shrink space keep 128M

This took a few minutes to run, but ultimately didn't shrink the size of the tablespace file (TEMP.DBF).  I then tried creating a new, smaller TEMP tablespace, setting it as the system default, and deleting the old one:

CREATE TEMPORARY TABLESPACE temp_smaller TEMPFILE 'oracleXe\app\oracle\oradata\XE\temp01.dbf' SIZE 1G;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_smaller;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

The last statement to drop the tablespace ran for ~8 hours without completing.  At that point I cancelled the operation and attempted to delete the tablespace file TEMP.DBF directly.  That was not allowed because the file was open / in use by the Oracle service.  I shutdown the database and then was able to delete the ~30 GB TEMP.DBF file.  I restarted the database, re-ran the query to check the TEMP tablespaces - results were:

TEMP C:\NO_BACKUP\BIN\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF 20971520
TEMP_SMALLER C:\NO_BACKUP\BIN\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP01.DBF 1073741824

The TEMP tablespace is now much smaller.  I dropped it which worked quickly.  Database shrink accomplished.