Home » Other » General » UNDO tablespace is full (10g, 10.2.0.1 (windows xp))
UNDO tablespace is full [message #566989] Fri, 21 September 2012 23:27 Go to next message
juhi_23
Messages: 4
Registered: September 2012
Location: BANGALORE
Junior Member
Hi All,

During export / import process if my UNDO tablespace gets full and if don't want to increase the size (or no adding of file) then how can i tackle the situation. Is there any parameter?

Actually, this is an interview question, asked to me.

Thanks is advance

Regards,
Juhi
Re: UNDO tablespace is full [message #566993 is a reply to message #566989] Sat, 22 September 2012 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59983
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your opinion. Why?

Regards
Michel
Re: UNDO tablespace is full [message #566999 is a reply to message #566993] Sat, 22 September 2012 02:36 Go to previous messageGo to next message
juhi_23
Messages: 4
Registered: September 2012
Location: BANGALORE
Junior Member
Dear Michel,

I don't know why interviewer wants such answer. I googled it, but didn't found the answer. if you know the answer then let me know.

Regards,

Juhi

Re: UNDO tablespace is full [message #567001 is a reply to message #566999] Sat, 22 September 2012 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59983
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't ask why the interviewer asks for this (it is interviewer job to ask for everything even non-sense question) but what is your opinion/answer and why this opinion/answer. What do you think about the question and its answer?
Any direct answer to this question will be useless for you because there are million of such questions an interview can ask you and you will most likely never have this one again. But knowing how to answer is better and this starts by knowing how you currently answer and think.

Regards
Michel

[Edit: typos]

[Updated on: Mon, 10 December 2012 01:17]

Report message to a moderator

Re: UNDO tablespace is full [message #567003 is a reply to message #567001] Sat, 22 September 2012 03:53 Go to previous messageGo to next message
juhi_23
Messages: 4
Registered: September 2012
Location: BANGALORE
Junior Member
Dear Michel,

As per my knowledge, even UNDO tablespace is full, import / export process doesn't hamper. We don't require to look after the UNDO tablespace. During import process, space constraint is based on default tablespace rather than UNDO tablespace.

Please let me know what could be the correct answer?

regards,
Juhi
Re: UNDO tablespace is full [message #567005 is a reply to message #567003] Sat, 22 September 2012 05:01 Go to previous messageGo to next message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I wouldn't expect an export to generate UNDO, but it may use UNDO generated by other sessions to ensure read consistency. If an export is unable to see an old version of a row in UNDO because UNDO is too small, then you either need to make export faster, or export less data. You can make export faster by exporting to a local (not network) SSD, make sure you are using DataPump and not Original Export and probably tweak a few other parameters. You can export less data by exporting one table at a time or perhaps using filters to export part of a table. Realistically though, what you would really do is to wait for a quiet period when there are few transactions to perform the export.

Import will generate undo in Conventional mode (ie. not direct path). If using the Original Import, you could use the COMMIT parameter to commit as it loads - that would free up some UNDO. DataPump does not offer a COMMIT parameter, so you would have to either import less data (eg. one table at a time) or use DirectPath DataPump import instead. If using DirectPath, I wouldn't be surprised if index maintenance generated UNDO, so if UNDO filled up that way then you could drop the indexes first.

There's probably lots of other factors as well. I don't think there is a succinct answer to this one; it depends on whether you are talking about Import or Export, DataPump or Original, Direct Path or Conventional, single table or many tables. It's possible (though not likely) that this was the interviewer's intention: to see haw many contributing factors you can think of and explain.

Ross Leishman
Re: UNDO tablespace is full [message #567011 is a reply to message #567005] Sat, 22 September 2012 06:27 Go to previous messageGo to next message
juhi_23
Messages: 4
Registered: September 2012
Location: BANGALORE
Junior Member
Thanks Rleishman,

Great help.
Re: UNDO tablespace is full [message #567023 is a reply to message #567005] Sat, 22 September 2012 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59983
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
... export... probably tweak a few other parameters...


consistent=n (or no FLASHBACK parameter with data pump), for instance then very few UNDO may be required.

Regards
Michel
Re: UNDO tablespace is full [message #572275 is a reply to message #567023] Sun, 09 December 2012 21:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
G'day,

I thought the UNDO was always full and the database just overwrites the oldest entry.

David
Re: UNDO tablespace is full [message #572278 is a reply to message #572275] Sun, 09 December 2012 23:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59983
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It depends if you use it or not
2/ It depends on what you mean by "full". See dba_undo_extents.status

Regards
Michel
Re: UNDO tablespace is full [message #572284 is a reply to message #572278] Mon, 10 December 2012 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59983
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I mean is:
SQL> select sum(blocks) from dba_data_files where tablespace_name='UNDOTBS';
SUM(BLOCKS)
-----------
      47360

SQL> select sum(blocks) from dba_undo_extents;
SUM(BLOCKS)
-----------
       2600

SQL> select status, count(*) nb, sum(blocks) blocks from dba_undo_extents group by status;
STATUS            NB     BLOCKS
--------- ---------- ----------
UNEXPIRED          6        288
EXPIRED           48       2184
ACTIVE             1        128

Only 2600 out of 47360 blocks have been used during this instance.
Among these 2600, 128 are actually used, 2184 are expired and so can be reused and 288 are not currently used but still in the scope of undo_retention and so can't be reused unless there is no more space in the tablespace and the undo retention is not guaranted (see dba_tablespaces.retention).

Regards
Michel
Re: UNDO tablespace is full [message #572328 is a reply to message #572284] Mon, 10 December 2012 16:45 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
From one of our development databases which is massively quiet in the leadup to Christmas with most staff on leave.
SQL> select sum(blocks) from dba_data_files where tablespace_name='UNDOTBS';

SUM(BLOCKS)
-----------
      65536

SQL> select sum(blocks) from dba_undo_extents;

SUM(BLOCKS)
-----------
      61256

SQL> select status, count(*) nb, sum(blocks) blocks from dba_undo_extents group by status;

STATUS            NB     BLOCKS
--------- ---------- ----------
EXPIRED          314      12744
UNEXPIRED        520      48512

Interestingly, one of our production instances matches your statistics.
SQL> select sum(blocks) from dba_data_files where tablespace_name='UNDOTS';

SUM(BLOCKS)
-----------
    3403016

SQL> select sum(blocks) from dba_undo_extents;

SUM(BLOCKS)
-----------
      26328

SQL> select status, count(*) nb, sum(blocks) blocks from dba_undo_extents group by status;

STATUS            NB     BLOCKS
--------- ---------- ----------
EXPIRED          352       2816
UNEXPIRED        254      23384
ACTIVE             1        128

It looks as though I should spend some time and get this production 'undo' space a bit smaller.

David
Previous Topic: Dirty reads (splitted from "NO LOCK?")
Next Topic: connect oracle 10g with openoffice.calc
Goto Forum:
  


Current Time: Thu Dec 18 12:54:37 CST 2014

Total time taken to generate the page: 0.08744 seconds