Home » RDBMS Server » Server Administration » undo tablespace
undo tablespace [message #305779] Wed, 12 March 2008 00:23 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi All,

I have question regarding release space from undo tablespace.

Previouly undo_retention = 20 Hrs and undo_tablespace = 16G

Now i changed undo_retention = 8 Hrs , then i am expecting to release space from the undo tablespace.

But from last one week there is no release of space from undo tablespace.

If i tried to resize then i am getting following error.

ALTER DATABASE DATAFILE '/u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_02.dbf' RESIZE 6200m;

ORA-03297: file contains used data beyond requested RESIZE value


But for the space management there is need to release the space from undo tablespace.

One option i know is that to create new undo tablespace drop the current undo tablespace and then assign new undo tablespace.

Is any way to release space?
How can i release space?

Thanks in advance.

[Updated on: Wed, 12 March 2008 00:27]

Report message to a moderator

Re: undo tablespace [message #305781 is a reply to message #305779] Wed, 12 March 2008 00:39 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
more information


select * from v$parameter where name='undo_retention'

NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, ISMODIFIED, ISADJUSTED, ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH

724.00	undo_retention	3.00	28800	28800	FALSE	FALSE	IMMEDIATE	TRUE	SYSTEM_MOD	FALSE	FALSE	undo retention in seconds
Re: undo tablespace [message #305795 is a reply to message #305779] Wed, 12 March 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You either have:
1 - restart the instance in restricted mode and resize before opening the activity (disable the restricted mode).
2 - create a new undo tablespace and switch to it

Regards
Michel
Re: undo tablespace [message #305805 is a reply to message #305779] Wed, 12 March 2008 01:32 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Michel,

Then i think 2nd option will help me.

Are the following steps correct?

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_03.dbf' SIZE 1000M;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

System altered.

SQL> DROP TABLESPACE UNDOTBS1;

Tablespace dropped.


If yes then my doubt is,
1: The data file is not dropped from the UNDOTBS1 tablespace.
Do we need to delete it mannually?
2: And what happens if any the transaction is using undo tablespace during this operation?
3: Will i able to recover at any point if i needed?

Thanks in advance.

[Updated on: Wed, 12 March 2008 01:35]

Report message to a moderator

Re: undo tablespace [message #305810 is a reply to message #305805] Wed, 12 March 2008 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Yes but you could add "including contents and datafiles" in "drop tablespace" statement to let Oracle do it.

2. You couldn't drop the tablespace until all transactions using it were ended. You'd had to wait.

3. (I assume this is related to the previous question so) N/A

Regards
Michel

[Updated on: Wed, 12 March 2008 02:21]

Report message to a moderator

Re: undo tablespace [message #305814 is a reply to message #305810] Wed, 12 March 2008 02:10 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Michel Smile
Re: undo tablespace [message #306825 is a reply to message #305814] Mon, 17 March 2008 00:40 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,
I used INCLUDING CONTENTS AND DATAFILES in the drop tablespace clause.

The data files are deleted from the disk but the disk space is not released.

Previously there were 16G undo data files and i dropped those and created new undo tablespace with 2g. So we should get 14g Space on disk.

But i do not find the space is released from the disk
Please tell me why the space is not released.
Or is there anything need to do?

Thanks in advance.

06:26:01 

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_03.dbf' SIZE 2000M;

Tablespace created.

06:27:56 SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

System altered.

06:30:34 SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

06:31:36 SQL>


Re: undo tablespace [message #306832 is a reply to message #306825] Mon, 17 March 2008 01:14 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please find the alert log.
Mon Mar 17 06:27:22 2008
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_03.dbf' SIZE 2000M
Mon Mar 17 06:27:56 2008
Completed: CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_03.dbf' SIZE 2000M
Mon Mar 17 06:30:34 2008
Successfully onlined Undo Tablespace 5.
Undo Tablespace 1 successfully switched out.
Mon Mar 17 06:30:34 2008
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
Mon Mar 17 06:31:35 2008
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
Mon Mar 17 06:31:36 2008
Deleted file /u06/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_01.dbf
Deleted file /u04/TESTENV/oracle/oradata/ora01/UNDOTBS/undotbs_02.dbf
Completed: DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
Mon Mar 17 06:38:59 2008


[Updated on: Mon, 17 March 2008 01:19]

Report message to a moderator

Re: undo tablespace [message #306853 is a reply to message #306825] Mon, 17 March 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the OS.
Maybe Oracle keep an open handle on the file. In this case you have to restart the instance.
Maybe it is just not immediatly reported by your OS, wait a little bit. Check differences between du and df.

Regards
Michel

Re: undo tablespace [message #306868 is a reply to message #305779] Mon, 17 March 2008 02:32 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Michel,

I find that it is bug Bug 6316479 - RDBMS DOES NOT CLOSE FILE HANDLE ON DROPPED DATAFILE.
Since it applies all the way up to 10.2.0.3,

http://forums.oracle.com/forums/thread.jspa?messageID=2405278&#2405278

What you think about it?
Thanks in advacne.
Re: undo tablespace [message #306878 is a reply to message #306868] Mon, 17 March 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Quote:
What you think about it?

Nothing, it has been clsed as "not a bug". So Oracle thinks it is the expected behaviour and if you want to get back your space immediatly you have to restart the instance.

Regards
Michel
Re: undo tablespace [message #306881 is a reply to message #305779] Mon, 17 March 2008 02:47 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Michel,

Do you think that following is the only solution to handle this issue?

https://metalink.oracle.com/metalink/plsql/f?p=130:15:5061045330771111058::::p15_database_id,p15_docid,p15_show_header,p15_show_help, p15_black_frame,p15_font:Bug,6316479,1,0,1,helvetica

Thanks,
Re: undo tablespace [message #306883 is a reply to message #305779] Mon, 17 March 2008 02:49 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Michel,

Please explain.

Quote:

if you want to get back your space immediatly you have to restart the instance.



Soppose i will wait for some time then will i get the free space automatically?

Thanks.
Re: undo tablespace [message #306886 is a reply to message #306883] Mon, 17 March 2008 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The "bug" seems to say no.

Regards
Michel
Re: undo tablespace [message #306897 is a reply to message #305779] Mon, 17 March 2008 03:35 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Michel,
I do not understand what do you mean. Sad

Please tell me is any other way to free the space without shutdown/startup database.
Smile
Thanks.
Re: undo tablespace [message #306899 is a reply to message #306897] Mon, 17 March 2008 03:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none.

Regards
Michel
Previous Topic: i need to learn oracle DBA 10 g , any free books or documents?
Next Topic: archive log file get increased very high
Goto Forum:
  


Current Time: Sat Dec 10 14:43:28 CST 2016

Total time taken to generate the page: 0.08586 seconds