Tuesday, November 6, 2012

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)
TEMP31,6402
32,768
UNDOTBS14,75872
32,768
SYSAUX44716
32,768
USERS4,0056,506
11,264
SYSTEM4366
600
SOMEOTHER_DAT36
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:

and this on stackoverflow:

and this in the OracleFAQs

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.

1 comment:

  1. Follow up: I had to disable some constraints to delete some data, when I went to re-enable them I got an error message saying there was not enough temporary tablespace available:
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_SMALLER

    Enabling the constraints included rebuilding an index which in this case requires some space, and according to the above message, the temporary tablespace was out of space.

    This discussion:
    http://www.dbasupport.com/forums/showthread.php?46592-Enabling-constraint-runs-out-of-space..
    seemed to indicate it would be possible to run the index building on a different tablespace, but the command suggested therein produced the same error message.

    So, following the suggestion here:
    https://forums.oracle.com/forums/thread.jspa?threadID=2196758
    I increased the size of the temporary tablespace, and then could enable the contraints.

    ReplyDelete