I have two rollback datafiles that have grown to 1.1 Gb. Is this nornal. Can someone tell me how to get round this. This has consumed to much had disk space. Please advise
Printable View
I have two rollback datafiles that have grown to 1.1 Gb. Is this nornal. Can someone tell me how to get round this. This has consumed to much had disk space. Please advise
what is the contents of v$rollstat?
Can you post the results please.
If your database has used 1.1G of rollback there must be a transaction (or concurrent transactions) which have expanded the datafiles to that size. But we need to look at the contents of v$rollstat to see what is happening.
Hi roobaron
Here are the stats from the V$rollstat. Hope you will be able to read them. Look forward to hearing from you
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
0 8 407552 14828 0 6864 0
407552 0 0 0 0 0
ONLINE 1 21
2 6 31455232 4725996 0 11498 1
31455232 0 1 0 0 524083
ONLINE 1 1941
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
3 6 31455232 2644168 0 12819 0
31455232 0 1 0 0 524288
ONLINE 2 332
4 64 335542272 253610546 1 137065 1
335542272 0 50 0 0 215191021
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
ONLINE 1 1866
5 58 304084992 7629226 0 158383 0
304084992 0 1 0 0 524288
ONLINE 31 2436
6 13 68155392 6682590 1 11950 0
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
68155392 0 1 0 0 524288
ONLINE 8 1506
7 28 146798592 1814952 0 9926 0
146798592 0 0 0 0 0
ONLINE 4 1963
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
8 16 83884032 3681078 0 11418 0
83884032 0 1 0 0 524288
ONLINE 9 188
9 52 272627712 9763342 1 15793 0
272627712 0 2 0 0 996147
ONLINE 4 1041
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
10 27 141555712 4808956 0 13052 0
141555712 0 2 0 0 996147
ONLINE 11 110
11 15 78641152 10887726 0 15073 0
78641152 0 3 0 0 1892679
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
ONLINE 4 1
12 13 68155392 2146826 1 11546 0
68155392 0 0 0 0 0
ONLINE 4 2030
13 33 173012992 34348824 1 27287 0
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
173012992 0 7 0 0 7101971
ONLINE 1 2022
14 20 21055488 4162060 1 10429 1
20971520 21055488 0 4 0 0 637384
ONLINE 12 505
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
15 116 121638912 19903264 1 21486 0
121638912 0 20 0 0 10883554
ONLINE 52 291
16 4 20969472 26884980 1 22267 0
20969472 0 5 1 0 4049149
ONLINE 2 2520
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ----------
STATUS CUREXT CURBLK
--------------- ---------- ----------
17 9 47183872 28114884 0 23627 0
47183872 0 5 0 0 3902552
ONLINE 4 1947
probably you've set the autoextend on for the datafiles. Best way would be to shutdown,- startup mount - drop and recreate new rollback segs.