Home » RDBMS Server » Server Administration » Undo Tablespace Size (Oracle 9iR2 on RHEL)
| Undo Tablespace Size [message #493364] |
Mon, 07 February 2011 09:04  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello
As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?
As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery
So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?
In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table
Can we say in this case data is retained so as to support 'incomplete recovery'?
As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?
SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space
5 group by tablespace_name ) a,
6 ( select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
7 sum(bytes)/1024 Kbytes_alloc,
8 tablespace_name
9 from sys.dba_data_files
10 group by tablespace_name
11 union all
12 select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
13 sum(bytes)/1024 Kbytes_alloc,
14 tablespace_name
15 from sys.dba_temp_files
16 group by tablespace_name )b
17 where a.tablespace_name (+) = b.tablespace_name
18 and a.tablespace_name ='UNDOTBS01';
TABLESPACE_NAME TOT_MBYTES_AVAILABLE MBYTES_ALLOCATED MBYTES_FREE_FROM_ALLOCATED PCTUSED
------------------------------ -------------------- ---------------- -------------------------- ----------
UNDOTBS01 30000 30000 27166 9.44666667
SQL> create table t100 as select * from dba_objects where 1=2;
Table created.
SQL> begin
for i in 1..10
loop
insert into t100 select * from dba_objects;
end loop;
end; 2 3 4 5 6
7 /
PL/SQL procedure successfully completed.
---Slight Increase in the size of Undo Tablespace
SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available, K
bytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space
5 group by tablespace_name ) a,
6 ( select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
7 sum(bytes)/1024 Kbytes_alloc,
8 tablespace_name
9 from sys.dba_data_files
10 group by tablespace_name
11 union all
12 select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
13 sum(bytes)/1024 Kbytes_alloc,
14 tablespace_name
15 from sys.dba_temp_files
16 group by tablespace_name )b
17 where a.tablespace_name (+) = b.tablespace_name
18 and a.tablespace_name ='UNDOTBS01';
TABLESPACE_NAME TOT_MBYTES_AVAILABLE MBYTES_ALLOCATED MBYTES_FREE_FROM_ALLOCATED PCTUSED
------------------------------ -------------------- ---------------- -------------------------- ----------
UNDOTBS01 30000 30000 27164.125 9.45291667
SQL> commit;
Commit complete.
-- Undo tablespace size is not restored though segments are eligible to get wrapped
SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space
5 group by tablespace_name ) a,
6 ( select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
7 sum(bytes)/1024 Kbytes_alloc,
8 tablespace_name
9 from sys.dba_data_files
10 group by tablespace_name
11 union all
12 select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
13 sum(bytes)/1024 Kbytes_alloc,
14 tablespace_name
15 from sys.dba_temp_files
16 group by tablespace_name )b
17 where a.tablespace_name (+) = b.tablespace_name
18 and a.tablespace_name ='UNDOTBS01';
TABLESPACE_NAME TOT_MBYTES_AVAILABLE MBYTES_ALLOCATED MBYTES_FREE_FROM_ALLOCATED PCTUSED
------------------------------ -------------------- ---------------- -------------------------- ----------
UNDOTBS01 30000 30000 27164.125 9.45291667
SQL> drop table t100;
Table dropped.
---- Undo tablespace size is not yet restored even when related data is not required for any query or transaction
SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space
5 group by tablespace_name ) a,
6 ( select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
7 sum(bytes)/1024 Kbytes_alloc,
8 tablespace_name
9 from sys.dba_data_files
10 group by tablespace_name
11 union all
12 select sum(decode(maxbytes,0,bytes,maxbytes))/1024 Total_Kbytes_Available,
13 sum(bytes)/1024 Kbytes_alloc,
14 tablespace_name
15 from sys.dba_temp_files
16 group by tablespace_name )b
17 where a.tablespace_name (+) = b.tablespace_name
18 and a.tablespace_name ='UNDOTBS01';
TABLESPACE_NAME TOT_MBYTES_AVAILABLE MBYTES_ALLOCATED MBYTES_FREE_FROM_ALLOCATED PCTUSED
------------------------------ -------------------- ---------------- -------------------------- ----------
UNDOTBS01 30000 30000 27164.125 9.45291667
Regards,
OraKaran
[Updated on: Mon, 07 February 2011 10:17] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Undo Tablespace Size [message #493368 is a reply to message #493366] |
Mon, 07 February 2011 09:29  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Thanks BlackSwan
Quote:
UNDO tablespace does not get shrunk.
I understand from your answer that, It will "never" be of size less than any past time.
Also could you please answer following question?
Quote:
As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?
I am asking this over the precautions taken to reduce undo
Regards,
OraKaran
|
|
|
|
|
|
|
|
| Re: Undo Tablespace Size [message #493441 is a reply to message #493364] |
Tue, 08 February 2011 00:09 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
If transaction volumes in subsequent days are lower, over the course of time, Oracle may well expire and drop undo extents and segments. However, the tablespace and datafile sizes will not shrink.
Hemant K Chitale
|
|
|
|
| Re: Undo Tablespace Size [message #493505 is a reply to message #493441] |
Tue, 08 February 2011 08:44 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Thanks Hemant
Quote:
If transaction volumes in subsequent days are lower, over the course of time, Oracle may well expire and drop undo extents and segments. However, the tablespace and datafile sizes will not shrink
In that case can we follow the below mentioned practice to keep the undo tbs size in control:
Suppose existing undo tbs is undotbs1
create tablespace undotbs2...
shutdown immediate;
startup
alter system set undo_tablespace = undotbs2;
drop tablespace undotbs1;
May be startup and shutdown is redundant here
But can we use the above steps as a 'practice' in 9i, 10g
Regards,
OraKaran
|
|
|
|
| Re: Undo Tablespace Size [message #493518 is a reply to message #493505] |
Tue, 08 February 2011 09:29 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Creating a new Undo tablespace and switching to it is a practice adopted by some sites. For example, if you have a weekly/monthly batch job that needs a very large undo tablespace, you have one created only for that job and then switch out to your daily , smaller, undo tablespace.
Be cautious before dropping an undo tablespace. Ensure that Oracle has switched out all transactions (any transaction still in progress when you issue the ALTER SYSTEM SET UNDO_TABLESPACE='undotbs2'; would still persist in undotbs1 until they complete -- a transaction does not span undo tablespaces). Also ensure that you don't have long running queries that may need to read from the old undo tablespace -- else you run the risk of ORA-1555 errors if you drop the old undo tablespace too soon.
Hemant K Chitale
|
|
|
|
| Re: Undo Tablespace Size [message #493525 is a reply to message #493518] |
Tue, 08 February 2011 09:46 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Thanks Hemant
I assume Oracle will allow me to switch to new undo tablespace only when transactions using old undo tablespace are all complete. Right?
Is there any mechanism to know if no currently executing select statement will require the old undo tablespace and it can be dropped safely?
Thanks and Regards,
OraKaran
|
|
|
|
| Re: Undo Tablespace Size [message #493536 is a reply to message #493525] |
Tue, 08 February 2011 10:03 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Oracle does NOT prevent you from issuing the ALTER SYSTEM SET UNDO_TABLESPACE command -- the command returns almost immediately. It doesn't wait for transactions to complete. It only signifies that *new* transactions will start using the new undo tablespace.
You have to monitor the alert.log for messages and/or query V$TRANSACTION (you could join to DBA_ROLLBACK_SEGS also) to watch transactions that were still present in the old Undo Tablespace.
As for SELECTs, identify SELECTs that began before the ALTER SYSTEM SET UNDO_TABLESPACE was issued and see if any are long running -- see if they may need to read undo from the old undo tablespace. Another (lazy ?) way is to wait for UNDO_RETENTION or TUNED_UNDO_RETENTION (whichever is higher duration) before you attempt to drop the old undo tablespace.
Hemant K Chitale
|
|
|
|
| Re: Undo Tablespace Size [message #493542 is a reply to message #493364] |
Tue, 08 February 2011 10:16 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Hemant
I understood the methods you have described. It needs 100% manual efforts!
Thanks for your help on this
Regards,
OraKaran
|
|
|
|
| Re: Undo Tablespace Size [message #493588 is a reply to message #493542] |
Tue, 08 February 2011 19:25 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>It needs 100% manual efforts!
So does issuing commands like "CREATE UNDO TABLESPACE UNDOTBS2" and "ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2'"
Hemant K Chitale
|
|
|
|
| Re: Undo Tablespace Size [message #493638 is a reply to message #493364] |
Wed, 09 February 2011 03:24 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Quote:
So does issuing commands like "CREATE UNDO TABLESPACE UNDOTBS2" and "ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2'"
Thanks Hemant
Regards,
OraKaran
|
|
|
|
Goto Forum:
Current Time: Wed May 08 05:30:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
