-
unable to resize a RBS tablespace
Hi Guys,
My RBS tablespace has grow to 1.6Gb during some heavy transactions. I have check one the rollback segments. All of them
OWNER SEGMENT_NAME TABLESPACE BYTES EXTENTS
------------------------------ ---------- ----------------- SYS RBS0 RBS 4194304 8
SYS RBS1 RBS 4194304 8
SYS RBS2 RBS 4194304 8
SYS RBS3 RBS 4194304 8
SYS RBS4 RBS 4194304 8
SYS RBS5 RBS 4194304 8
SYS RBS6 RBS 4194304 8
File Name Size (M) Used (M) Free (M) HWM (M)
---------------------------------------- ----------
/opt/oracle/oradata/irmsitdb/rbs01.dbf 1600 28 1069 1588
From above, my RBS tablespace is using 28Mb only, and 1.5Gb at HWM.
I have tried to coalesce and it doesn;t change anythign on the HWM. I did issue a resize on the datafile and it does not work :
SQL> alter database datafile '/opt/oracle/oradata/irmsitdb/rbs01.dbf' resize 1500m;
alter database datafile '/opt/oracle/oradata/irmsitdb/rbs01.dbf' resize 1500m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
I have perform this on other tablespaces including TEMp and it works.
What can be done to reduce the size of my RBS tablespace's datafile??
Regards
CF
-
First shrink all the rollback segments and then coalesce the tablespace.
alter rollback segment rbsname shrink to value;
Then resize the rbs datafile.
Might be that would help.
K.Chithra
Oracle DBA
-
ALl the individual RBS are in their optimal size of 4M only. that is why from the extent usage of the RBS tablepsace, i have 1,5Gb free.
At this point, coalescing the tablespace RBS does not changes anything, and reszie of datafile fails.
Regards
CF
-
If its ur PRO box dont try this....
Recreate the datafiles with seting max size...for Datafiles....
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I'm testing it out on my DEV database. But does this mean ORACLE does not have any mean to resize the RBS tablespace like any other tablespaces ?
Regards
CF
-
Can you check the max free space available also,instead of just the total free sapce.
By doing so,we can check if it is fragmented it or not isn't it?
max free chunk just be near to the value to total free chunk.
To resize the datafile the tail part of datafile should be free.
In between if it is free,we can'd do much abt it ,I guess.
K.Chithra
Oracle DBA
-
It does, but u need to plan correctly before creating any Tablespace or so...
i guess u might have spcified UNLIMITED option for the datafile with autoextend ON....and moreover rollback segment is more prone to fragmentation....and collace will not work in such cases...u also need to be use the parmeters correctly while creation of rollback segments....
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Feng
Try this.
ALTER ROLLBACK SEGMENT <> SHRINK TO size; -IF OPTIMAL SIZE is defined , or TO MINEXTENT SIZE if optimal size is not defined.....Additionally SMON will try once every 12 hours to shrink all rollback segments that have grown
over the optimal
To shrink all rollback segments do the following
SET PAGES 0
TRIMS ON
ECHO OFF
VERIFY OFF
FEEDBACK OFF
TTITLE OFF
CLEAR COLUMN
SPOOL shrink_rbs.tmp
PROMPT SET ECHO ON
PROMPT SPOOL shrink_rbs.log
PROMPT
SELECT 'ALTER ROLLBACK SEGMENT ' || segment_name
|| ' SHRINK;'
FROM dba_rollback_segs
WHERE status = 'ONLINE'
/
PROMPT ALTER TABLESPACE rbs COALESCE
/
PROMPT SPOOL OFF
PROMPT SET ECHO OFF
SPOOL OFF
CLEAR COLUMN
SET PAGES 24
TRIMS ON
ECHO OFF
VERIFY ON
FEEDBACK ON
@shrink_rbs.tmp
PROMPT
PROMPT ******************************
PROMPT
PROMPT Output saved at shrink_rbs.log
PROMPT
Hope this help
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
Hi,
Run this:
set lines 256
col "File Name" for A40
column file_name format a40;
column highwater format 9999999999;
SELECT Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
FROM dba_data_files df,
(SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
(SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
(SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
dba_extents c,
(SELECT value db_block_size from v$parameter where name='db_block_size') d
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
ORDER BY
df.tablespace_name, df.file_name
/
to see up to what size you can resize downwards. It is the value (in Megabytes) in the last column (HWM).
Cheers,
Julian
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Hi Julian,
I think i have a similar script and the values are as below for my RBS ( as from my 1st thread)
File Name Size (M) Used (M) Free (M) HWM (M)
---------------------------------------- ---------------------------
/opt/oracle/oradata/mydb/rbs01.dbf 1600 28 1069 1588
From above, my RBS tablespace is using 28Mb only, and 1.5Gb at HWM.
I should be able to downsize a free space of 1.5gb, but i can't.
Could i do the following :
1) create another seperate RBS tablepsace and create some public rollback segments in that tablepace.
2) offline those current rollback segments in the oversize tablespace, and drop the tablespace.
3) Create back the dropped RBS with a smaller datafile size, and recreate back those drop rollback segments.
Will this work?
Regards
CF
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|