Unable to move objects from SYSTEM tablespace [message #621040] |
Thu, 07 August 2014 22:20 |
kesavansundaram
Messages: 183 Registered: October 2007 Location: MUMBAI
|
Senior Member |
|
|
Hi Team,
I am working on Amazon RDS instance ( ver. 11.2.0.4.0.v1 ).Initially aud$ is located under SYSTEM tablespace, and further I have moved aud$ table from SYSTEM to custom tablespace and implemented purge using DBMS_AUDIT_MGMT pkg. Audit data was grown upto 78.5 GB before we move to custom tablespace and implement purge. In Amazon RDS, by default all tablespaces are BIG file type with autoextned enabled including SYSTEM tablespace. Eventhough 78.5 gb is allocated size, only 310 MB used and we need to reclaim those unused space. We don't have access to SYS user. We have access to only RDS master user which is cut down version of SYS.
Could you please guide me how to claim this 78.5 gb from SYSTEM ?
--Sizes are in MB.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
--------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM 78500 309.8 78190.2 99.6053503 3968 36 -Current objects under SYSTEM
SQL> set lines 1000 pages 1000
SQL> select owner,segment_name,segment_type ,bytes/(1024*1024) size_m from dba_segments
2 where tablespace_name = 'SYSTEM'
3 and bytes/(1024*1024) > 1
4 order by size_m desc;
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------------------------------------------------------------------------- ------------------ ------
SYS IDL_UB1$ TABLE 34
SYS SOURCE$ TABLE 21
SYS IDL_UB2$ TABLE 17
SYS C_OBJ#_INTCOL# CLUSTER 15
SYS SYS_LOB0000001051C00003$$ LOBSEGMENT 13
SYS C_TOID_VERSION# CLUSTER 11
SYS C_OBJ# CLUSTER 7
SYS ARGUMENT$ TABLE 7
SYS IDL_CHAR$ TABLE 6
SYS VIEW$ TABLE 5
SYS I_ARGUMENT1 INDEX 5
SYS I_SOURCE1 INDEX 4
SYS I_ARGUMENT2 INDEX 4
SYS I_COL1 INDEX 4
SYS I_H_OBJ#_COL# INDEX 4
SYS I_COL2 INDEX 3
SYS IDL_SB4$ TABLE 3
SYS KOTAD$ TABLE 2
SYS SETTINGS$ TABLE 2
SYS ACCESS$ TABLE 2
SYS DEPENDENCY$ TABLE 2
SYS I_COL3 INDEX 2
SYS I_DEPENDENCY2 INDEX 2
SYS I_OBJ2 INDEX 2
SYS HIST_HEAD$ TABLE 2
SYS I_OBJ5 INDEX 2
SYS I_DEPENDENCY1 INDEX 2
SYS OBJ$ TABLE 2
28 rows selected.
SQL> could you please guide me to claim this 78.5 gb from SYSTEM ?
Thanks & Regards
[Updated on: Thu, 07 August 2014 22:26] Report message to a moderator
|
|
|
|