The simple version of this question is: Is it possible to export all the data from an Oracle 10g XE database that has reached it's 4GB maximum size limit?
Now, here's the background: My (Windows) Oracle 10g XE database has reached its maximum allowed database size of 4GB. The solution I intended to implement to this problem was to upgrade to Oracle 11g XE which has a larger maximum size limit, and better reflects our production environment anyway. Of course, in typical Oracle fashion, they do not have an upgrade-in-place option (at least not that I could find for XE). So I decided to follow the instructions in the "Importing and Exporting Data between 10.2 XE and 11.2 XE" section of the Oracle 11g XE Installation Guide. After fighting with SQLPlus for a while, I eventually reached step 3d of the instructions which instructs the user to enter the following (it doesn't specify the command-line rather than SQLPlus, but it means the command-line):
expdp system/system_password full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
That command results in the following output:
Export: Release 10.2.0.1.0 - Production on Thursday, 29 September, 2011 10:19:11
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_06"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-12952: The request exceeds the maximum allowed database size of 4 GB
I have deleted quite a bit of data from the USERS tablespace, but am unable to resize it because of the physical locations of the data. And no matter what I do, I always get that same output. I have tried running "Compact Storage" from the admin web application with no effect.
So my question is, am I missing something? Or is Oracle really so incompetent as to leave people completely out of luck if their XE databases fill up?
You can get to the point you need to export data, sounds like you just need some help coalescing the data so you can reduce the USERS tablespace size and increase the SYSTEM tablespace size to get past your issue.
You mentioned that you removed data from the USERS tablespace but can't resize. Since you can't reduce the tablespace size smaller than the highest block, reorganiza your table data by executing the following command for each table:
ALTER TABLE <table_name> MOVE <tablespace_name>;
The tablespace name can be the same tablespace that the table currently lives in, it will still reorganize the data and coalesce the data.
This statement will give you the text for this command for all the tables that live in USERS tablespace:
select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE '||TABLESPACE_NAME||';' From dba_tables where tablespace_name='USERS';
Indexes will also have to be rebuilt (ALTER INDEX REBUILD;) as the MOVE command invalidates them because it changes physical organization of the table data (blocks) instead of relocating row by row.
After the data is coalesced you can resize the USERS tablespace to reflect the data size.
Is it a pain? Yes. Is Oracle user friendly? They would love you to think so but its really not, especially when you hit some weird corner case that keeps you from doing the types of things you want to do.