This discussion is archived
9 Replies Latest reply: Oct 14, 2013 4:02 AM by user12075536123 RSS

SYSTEM tablespace is too large

Vouthea_OUN Newbie
Currently Being Moderated

Dear all,

 

I currently found that my system tablespace is grown too large (around 70GB) after we imported using data pump.

So, what's the reason of this issue? How can I resolve it?

 

Appreciated for your help

Best Regards,

Vouthea OUN

  • 1. Re: SYSTEM tablespace is too large
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Where did you import from ?  If the objects were in the SYSTEM tablespace in the source database, the import would place them into the SYSTEM tablespace  !  You'd have to have used REMAP_TABLESPACE to import the objects to a different tablespace.

     

     

    Hemant K Chitale


  • 2. Re: SYSTEM tablespace is too large
    Vouthea_OUN Newbie
    Currently Being Moderated

    Hi Hemant,

     

    Here is my script

    impdp  UATUSR/UATUSR dumpfile=PRODDUMP.DMP DIRECTORY=DATA_PUMP_DIR FULL=Y   LOGFILE=imp.Log   REMAP_SCHEMA=UATUSR:UATUSR REMAP_TABLESPACE=FCCDATA:FCCDATA

     

    impdp  UATUSR/UATUSR dumpfile=FCUBS1201.DMP DIRECTORY=DATA_PUMP_DIR FULL=Y   LOGFILE=impDATA.Log  CONTENT= DATA_ONLY  REMAP_SCHEMA=FC120SUP:UATUSR REMAP_TABLESPACE=FC120SUP:FCCDATA

     

    We have used remap_schema and remap_tablespace as well.

     

    Regards,

    Vouthea OUN,

  • 3. Re: SYSTEM tablespace is too large
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You need to be sure that all the contents in the source were in FCCDATA and FC120SUP.   Objects that were in the SYSTEM tablespace would import into SYSTEM tablespace if UATUSR has RESOURCE privilege or QUOTA UNLIMITED on SYSTEM.

     

    Check USER_SEGMENTS to see which segments got imported into the SYSTEM Tablespace.

     

    Hemant K Chitale


  • 4. Re: SYSTEM tablespace is too large
    Vouthea_OUN Newbie
    Currently Being Moderated

    Hi Hemant,

     

    Thank for your answer. However, when I issue query

    SQL> conn UATUSR/UATUSR

    select count(*) from user_segments where tablespace_name='SYSTEM';

    COUNT(*)

    ----------

             0

     

    SQL> conn / as sysdba

    select sum(bytes)/1024/1024/1024 "Size (GB)",owner,tablespace_name

    from dba_SEGMENTS

    group by owner,tablespace_name

    order by 1 desc;

     

    Size (GB) OWNER                    TABLESPACE_NAME

    ---------- ------------------------------ ------------------------------

    60.78 SYS                      SYSTEM
    6.09 MFITRN                   FCCDATA
    4.43 UATUSRFCCDATA

     

    It seems that UATUSR's objects do not exit in SYSTEM tablespace.

     

    Regards,

    Vouthea OUN

  • 5. Re: SYSTEM tablespace is too large
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    How large was the SYSTEM usage before the import.

     

    Only metadata about objects, table/column/index statistics  and PLSQL code would be imported into SYSTEM.  So, if you have a lot of View definitions, Stored Procedures, Packages and tables with very many columns and/or partitions would SYSTEM usage be increased significantly.

     

    Hemant  K Chitale

  • 6. Re: SYSTEM tablespace is too large
    Vouthea_OUN Newbie
    Currently Being Moderated

    It was around 40GB usage.

    I have many objects within that schema.

    SQL> select count(*),object_type from dba_objects where  owner='UATUSR' group by object_type order by 1 desc;

     

      COUNT(*) OBJECT_TYPE  

    ---------- -------------------

    16379 SYNONYM      
      9576 PACKAGE      
      9499 PACKAGE BODY 
      5873 TABLE        
      5240 INDEX        
      2798 VIEW         
       506 JAVA CLASS   
       ... more ...
      

    Regards,

    Vouthea OUN

  • 7. Re: SYSTEM tablespace is too large
    user12075536123 Pro
    Currently Being Moderated

    Can you post the output of below cmd

     

    #sqlplus

    select * from (

    select owner,segment_type,segment_name,bytes from dba_segments where tablespace_name = 'SYSTEM' order by bytes desc)

    where rownum <= 20;

  • 8. Re: SYSTEM tablespace is too large
    Vouthea_OUN Newbie
    Currently Being Moderated

    Hi,

     

    Here is the result of query

    SQL>

    select * from (

    select owner,segment_type,segment_name,bytes from dba_segments where tablespace_name = 'SYSTEM' order by bytes desc)

    where rownum <= 20;

     

    Name Segment_Type Segment_Name Bytes

    SYS    TABLE    ARGUMENT$    23898095616

    SYS    INDEX    I_ARGUMENT1    19257098240

    SYS    INDEX    I_ARGUMENT2    10887364608

    SYS    TABLE    IDL_UB1$    5920260096

    SYS    TABLE    SOURCE$    2751463424

    SYS    TABLE    IDL_UB2$    721420288

    SYS    INDEX    I_SOURCE1    394264576

    SYS    TABLE    IDL_CHAR$    218103808

    SYS    CLUSTER    C_OBJ#_INTCOL#    109051904

    SYS    TABLE    IDL_SB4$    75497472

    SYS    TABLE    DEPENDENCY$    62914560

    SYS    CLUSTER    C_OBJ#    59768832

    SYS    INDEX    I_DEPENDENCY2    57671680

    SYS    CLUSTER    C_TOID_VERSION#    54525952

    SYS    INDEX    I_DEPENDENCY1    47185920

    SYS    TABLE    ACCESS$    40894464

    SYS    INDEX    I_ACCESS1    35651584

    SYS    TABLE    SETTINGS$    31457280

    SYS    INDEX    I_COL1    30408704

    SYS    INDEX    I_OBJ2    29360128

     

    Thanks,

    Vouthea OUN,

  • 9. Re: SYSTEM tablespace is too large
    user12075536123 Pro
    Currently Being Moderated

    How about checking the following?

    System Tablespace Size Increase While Running Impdp (DOCID 1590396.1)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points