9 Replies Latest reply: Oct 14, 2013 6:02 AM by user12075536123 RSS

    SYSTEM tablespace is too large

    Vouthea_OUN

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          How about checking the following?

                          System Tablespace Size Increase While Running Impdp (DOCID 1590396.1)