Oracle Community Network
Please let me know how to reduce the system tablespace size
my system01.dbf size is 6gb
i want reduce from 6gb to 2gb
You probably can't. Your best chance is to move all non-sys objects out and truncate your sys.aud$ table, then query dba_extents to find the last used block, and resize the datafile down to that.
Otherwise, hire a decent consultant.
ACTUALLY I ALREADY TRUNCATED MY SYS.AUD$ FILE. (TRUNCATED 4GB DATA (NEARLY 3 CRORES ) ) BUT STILL MY SYSTEM DBF FILE SHOWS 6GB CAN RESIZE WITH RESIZE COMMAND
I already told you what to do. So I'll repeat it:
Query dba_extents to find the last used block, and resize the datafile down to that.
To make it more obvioius:
Look up the definition of the dba_extents view, and write a query against it to show the last used block in your system tablespace datafile(s). If the last used block is fairly close to the end of the datafile, you will not be able to resize the file much. In that case, you'll need to relocate the objects. You probably are not capable of doing that safely yourself, so you'll have to ask for professional help.