Solved

Oracle XE Help tablespace bloated

Posted on 2009-04-08
7
1,348 Views
Last Modified: 2012-05-06
I am not an Oracle DBA, I have managed to install XE on my home PC to enable me to develop reporting tools using Excel, and therefore to emulate my work DB.  To do this I have recently had to truncate and re-populate a number of large(ish) tables.

The problem now seem to have is I appear to have hit a limit, not THE limit in terms of the 4GB DB size, but a limit, on what I think to be 2 of the TABLESPACES on the DB.

My question is, how to I "shrink" them?  I have tried the compact database function within the XE interface which reports back:
Space Allocated: 1,590 MB
Available:      3,530 MB
Maximum Allowable: 5,120 MB
Percent Used:      31%
Which seems to suggest I have more capacity, however when I view the DB through SQLSeveloper, it shows the following
SYSTEM       PCT_USED: 97.75 ALLOCATED:350 USED:342.13 FREE:7.88
SYSAUX      PCT_USED: 97.02 ALLOCATED: 300 USED:291.06 FREE: 8.94
UNDO      PCT_USED: 81.2 ALLOCATED: 500 USED: 406 FREE: 94
USERS      SPCT_USED: 47.06      ALLOCATED: 940 USED: 442.38 FREE: 497.63

It would seem that both the SYSTEM and SYSAUX tablespaces are the problem, however I do not have the first idea of what to do to overcome this; I have tried dropping a couple of large tables from the DB, but this has only freed up space in the USER tablespace.

Any help greatly appreciated.

0
Comment
Question by:MISServices
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 24095189
Hi,

What is the exact error message or what makes you think you hit a limit?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24095310
If you the above mentioned  feeling you can put the tablespaces in autoextend mode
and they will not cause expansion problems:

alter database datafile    '/u01/app/oracle/SYSTEM01.dbf'  autoextend on;

Find the correct names and locations of that files.

Be aware that these percents are normal for these tablespaces and
in the common case autoextend is set to ON.




0
 

Author Comment

by:MISServices
ID: 24096878
OK, so from what schwertner has said, the size of the 2 tablespaces I refer to is not the issue.  As is typical in these cases I am now attempting to append byte-sized parts of the table, and as yet they have not failed to append.  However I still feel there is an issue, that I am not fully understanding, and that is why it takes so long to append the rows, and I mean a very long time indeed.  For example the table I am trying to append from is over 1.5 million rows, if I was to append the lot into the XE equivalent table it would take over 3 hours, some times longer before Access reports back (usually with a failure error).

So is the approach I am using appropriate, e.g. I use a make table query at work to create an Access table of the Oracle table.  At home I then link the XE equivalent table and run an MS Access append query.  Is there a more efficient way to achieve the same results?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:MISServices
ID: 24097856
I have now managed to attempt an append that has resulted in an error.  The error, as reported back by MS Access is:
ODBC insert on linked table [TableName] failed.
[Oracle][ODBC][Ora]ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'(#30036)

Both the UNDO tablespace and USER tablespace or at 99-100%

The append query (100,000 rows) took 2 hours before it failed!

Thanks
0
 
LVL 48

Accepted Solution

by:
schwertner earned 500 total points
ID: 24098024
It is so, because you have to try to append the rows in small chunks.
about 1000 rows every and to COMMIT in order to empty the UNDO segment.
This is not enough. There is an parameter UNDO_RETENTION that you should
set to a very low level.

There are experts that claim that it is possible to insert without affecting the size
of the UNDO segment:
They suggested to use:
 insert /*+APPEND */ into ... select ... from [some other table or view]
But the table must be defined (or altered) to "nologging".

Also to speed up the inserting you can disable all indexes on the thable(s)
and enable them after insert.
0
 

Author Closing Comment

by:MISServices
ID: 31568463
Thanks schwertner

Based on your advice I created a VBA function that dynamically created append queries of 500 rows a time, worked a dream, and none of the issues previously mentioned arose.  I also found that by turning the LOGGING off for both the table and it's indexes reduced the append query by a further 8-10 secs, per 500 rows.  So, I would suggest this is the best solution if you are to use Access as the vehicle for appending the data.

Having said all of this I have procured a copy of DATA LOADER which seems to handle things in a tidier way.

Thanks again for your advice.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24110552
Thats a fantastic news!
Good luck!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question