Home » RDBMS Server » Server Administration » Unable to move objects from SYSTEM tablespace (Oracle 11.2.0.4.0 linux 6.4)
Unable to move objects from SYSTEM tablespace [message #621040] Thu, 07 August 2014 22:20 Go to next message
kesavansundaram
Messages: 166
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

Re: Unable to move objects from SYSTEM tablespace [message #621041 is a reply to message #621040] Thu, 07 August 2014 22:34 Go to previous message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+shrink+tablespace
Previous Topic: Grid installation failed due to Display variable
Next Topic: How to find the alert log path when database is down.
Goto Forum:
  


Current Time: Fri Oct 24 11:21:44 CDT 2014

Total time taken to generate the page: 0.20109 seconds