OraFAQ Forum: Enterprise Manager » mgmt_tablespace is full: options for freeing up space

Home » RDBMS Server » Enterprise Manager » mgmt_tablespace is full: options for freeing up space (OEM 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

mgmt_tablespace is full: options for freeing up space [message #626152]

Tue, 21 October 2014 11:14

zaff
Messages: 50
Registered: July 2008

Member

Hello All,
The mgmt_tablespace is showing as full. What options do i have for freeing up space i.e. Is there a command i can ran to purge raw data etc?:

SQL> select TABLESPACE_NAME,BYTES/1024/1024 SIZE_MB, MAXBYTES/1024/1024 MAX_MB FROM dba_data_files;

TABLESPACE_NAME SIZE_MB MAX_MB
------------------------------ ---------- ----------
SYSTEM 2048 0
SYSAUX 2048 0
UNDOTBS 4096 4096
USERS 32 0
SYSAUD 128 256
MGMT_TABLESPACE 32767.9844 32767.9844
MGMT_AD4J_TS 4050 32767.9844
MGMT_ECM_DEPOT_TS 1500 32767.9844
MGMT_TABLESPACE 3072 0

Thanks,
Zaff

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626154 is a reply to message #626152]

Tue, 21 October 2014 12:37

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to first clearly know what's inside the tablespace.

[Updated on: Tue, 21 October 2014 12:37]

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626173 is a reply to message #626152]

Wed, 22 October 2014 01:35

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Your query does not show that the tablespace is full. It shows only that the one datafile of which it consists is at its maximum size. You need to query dba_free_space to determine if the tablespace is full.

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626183 is a reply to message #626173]

Wed, 22 October 2014 08:48

zaff
Messages: 50
Registered: July 2008

Member

hi John,
the mgmt_tablespace isn't in dba_free_space:

SQL> select distinct(TABLESPACE_NAME) from dba_free_space;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUD
MGMT_AD4J_TS
USERS
MGMT_ECM_DEPOT_TS
UNDOTBS
SYSAUX

7 rows selected.

Regards,
Zabair

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626185 is a reply to message #626183]

Wed, 22 October 2014 08:51

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 21 October 2014 19:37

You have to first clearly know what's inside the tablespace.

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626191 is a reply to message #626185]

Wed, 22 October 2014 10:00

zaff
Messages: 50
Registered: July 2008

Member

Hi Michel - Can you point me to a doc? All I know is that the tablespace is used by the Management Repository. The tables seem to be small in size:

SQL> select * from (
2 select TABLE_NAME,(num_rows * avg_row_len)/1024/1024 SIZEMB from dba_tables where TABLESPACE_NAME='MGMT_TABLESPACE'
3 ORDER BY SIZEMB DESC
4 ) where rownum <= 50;

TABLE_NAME SIZEMB
------------------------------ ----------
MGMT_MESSAGES 63.4023342
MGMT_POLICY_ASSOC_CFG 31.7668095
EM_DISC_RAW_RESULTS 28.0714941
MGMT_POLICY_ASSOC 17.0667305
EM_VIOLATIONS 16.6717529
MGMT_LONG_TEXT 11.2504444
MGMT_POLICY_ASSOC_CFG_PARAMS 10.1151123
MGMT_POLICIES 6.87490845
EM_METRIC_COLUMNS 5.53070068
EM_CCS_PARSED_DATA 5.09974384
MGMT_LL_INV_COMPONENT 4.6494894
EM_METRIC_COLUMN_VER 3.26186371
EMCT_SPEC_RATE_LIB 2.91637421

Thanks,
Zabair

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626194 is a reply to message #626191]

Wed, 22 October 2014 10:18

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select owner, sum(bytes)/1024/1024 MB 
from dba_segments where TABLESPACE_NAME='MGMT_TABLESPACE' 
group by owner order by 1;
select segment_type, sum(bytes)/1024/1024 MB 
from dba_segments where TABLESPACE_NAME='MGMT_TABLESPACE' 
group by segment_type order by 1;

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626882 is a reply to message #626194]

Tue, 04 November 2014 06:12

zaff
Messages: 50
Registered: July 2008

Member

Thanks, Michel.
It seems the majority of space is taken up by partitions:

OWNER                                  MB 
------------------------------ ---------- 
SYSMAN                          35729.375 
SYSMAN_APM                            .75 
SYSMAN_MDS                         19.875 
SYSMAN_OPSS                         49.25 

SEGMENT_TYPE               MB 
------------------ ---------- 
INDEX                 847.875 
INDEX PARTITION      623.1875 
LOB PARTITION            2992 
LOBINDEX              17.0625 
LOBSEGMENT            289.625 
NESTED TABLE            1.375 
TABLE                 589.125 
TABLE PARTITION         30439

Each partition, on average, is 8MB in size. So, the question is; what's the best way of clearing up this tablespace so i can reclaim some of space?

Regards,
Zabair

[Updated on: Tue, 04 November 2014 07:50] by Moderator

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626884 is a reply to message #626882]

Tue, 04 November 2014 06:16

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 22 October 2014 17:18

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626887 is a reply to message #626884]

Tue, 04 November 2014 06:48

zaff
Messages: 50
Registered: July 2008

Member

OWNER MB 
------------------------------ ---------- 
SYSMAN 35729.375 
SYSMAN_APM .75 
SYSMAN_MDS 19.875 
SYSMAN_OPSS 49.25

SEGMENT_TYPE MB 
------------------ ---------- 
INDEX 847.875 
INDEX PARTITION 623.1875 
LOB PARTITION 2992 
LOBINDEX 17.0625 
LOBSEGMENT 289.625 
NESTED TABLE 1.375 
TABLE 589.125 
TABLE PARTITION 30439

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626888 is a reply to message #626887]

Tue, 04 November 2014 07:09

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you think this is REALLY formatted?

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626889 is a reply to message #626888]

Tue, 04 November 2014 07:38

zaff
Messages: 50
Registered: July 2008

Member

It's not exactly poetry in motion, but I hope this is pretty enough for your high standards?:

OWNER                          MB 
------------------------------ ---------- 
SYSMAN                         35729.375 
SYSMAN_APM                     .75 
SYSMAN_MDS                     19.875 
SYSMAN_OPSS                    49.25

SEGMENT_TYPE       MB 
------------------ ----------
INDEX              847.875 
INDEX PARTITION    623.1875 
LOB PARTITION      2992 
LOBINDEX           17.0625 
LOBSEGMENT         289.625 
NESTED TABLE       1.375 
TABLE              589.125 
TABLE PARTITION    30439

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626890 is a reply to message #626889]

Tue, 04 November 2014 07:54

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL*Plus format the result for you.
See how is your first post this day now I modified it.

Quote:

Each partition, on average, is 8MB in size. So, the question is; what's the best way of clearing up this tablespace so i can reclaim some of space?

You have to first know which tables own these partitions to know what to purge.

[Updated on: Tue, 04 November 2014 07:54]

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626921 is a reply to message #626890]

Wed, 05 November 2014 05:14

zaff
Messages: 50
Registered: July 2008

Member

Here's a list of the top storage consumers. What would be the best method of clearing these tables? Truncate?:

SQL> select SEGMENT_NAME, sum(BYTES)/1024/1024 SIZEMB,count(*) TOTAL_PARTITIONS 
  2  FROM DBA_SEGMENTS 
  3  WHERE SEGMENT_TYPE='TABLE PARTITION' 
  4  AND OWNER='SYSMAN' 
  5  GROUP BY SEGMENT_NAME 
  6  ORDER BY SIZEMB DESC;

SEGMENT_NAME                                 SIZEMB TOTAL_PARTITIONS 
---------------------------------------- ---------- ---------------- 
MGMT_AUDIT_LOGS                                1408              176 
EM_LM_PROJECT_HISTORY                          1176              147 
EM_CS_SCORE_HIST                               1160              145 
EM_CCC_HISTORY_JOBEXEC                         1160              145 
EM_CCC_WATCHDOG_ALERTS                         1160              145 
EM_CCC_OBSERVATION                             1160              145 
EM_CCC_OBSGROUP                                1144              143 
EM_CCC_OBS_GROUP_MAP                           1104              138 
EM_CCC_HISTORY_OBS_STATUS                      1072              134 
EM_CCC_HA_OBS                                  1064              133 
EM_CCC_HA_OBSGROUP                             1048              131 
EM_CCC_AUTHOBS_CR_MAP                          1048              131 
EM_CCC_CMPUBACTION                             1040              130 
EM_CCC_FILEOBS_DIFF                            1040              130 
EM_HOST_PROCESSES                              1040              130 
EM_CCC_CMPUBACTION_DETAIL                      1040              130 
EM_METRIC_VALUES_HOURLY                         472               36

Report message to a moderator

Re: mgmt_tablespace is full: options for freeing up space [message #626922 is a reply to message #626921]

Wed, 05 November 2014 05:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't use OEM so I can't help you more but you can find answers on how to purge OEM tables on the web:
https://www.google.com/?gws_rd=ssl#q=oracle+enterprise+manager+purge

Report message to a moderator

Previous Topic:	Performance Manager on EM not showing full statement
Next Topic:	Unable to login as SYSMAN OEM 12.1.0.4. Error Authenticaion Failed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 04:14:18 CST 2024