How to Reclaim Space Used by LogMiner Tables (Doc ID 456814.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 30-May-2013***


Goal

By default all LogMiner tables are created to use the SYSTEM tablespace, but the SYSTEM tablespace may not have enough space for these tables.
To determine the amount of space allocated for the Logminer metadata schema, run the statements below within an Sql*plus session:

connect /as sysdba
set linesize 256

select sum(bytes)/(1024*1024) "Total_size_in_MB"
from dba_extents where segment_name like '%LOGMN%';

select owner, segment_name, segment_type, sum(bytes) "ext_bytes"
from dba_extents where segment_name like '%LOGMN%'
group by owner, segment_name, segment_type order by ext_bytes desc;

 
You have the option to relocate the LogMiner tables to a new tablespace before mining with the LogMiner Utility.

How to reclaim the space used by LogMiner tables such as LOGMNR_OBJ$ and LOGMNR_COL$?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms