| undo tablespace [message #305779] |
Wed, 12 March 2008 00:23  |
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  |
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 #305805 is a reply to message #305779] |
Wed, 12 March 2008 01:32 |
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 |
 |
Michel Cadot
Messages: 68684
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 #306825 is a reply to message #305814] |
Mon, 17 March 2008 00:40 |
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 |
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 #306881 is a reply to message #305779] |
Mon, 17 March 2008 02:47 |
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 |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
