1 2 Previous Next 16 Replies Latest reply: Aug 6, 2013 8:46 AM by Sathyanarayanan RSS

    can i delete a table from system table space.

    947771

      hi,

      i am using 11g xe r2 on windos.

       

      my system space is filled it shows (600M(used) 600M(maxsize) datafile detail autoextend is on)

      but other tablespaces have some space in it.

       

      1)i show one table in system schema

      SYS_EXPORT_SCHEMA_01

      can i delete it ,is there any thing else which is related to export import in system tablespacewhich i  can be deleted to free up some space.

       

       

      but i was able to add recored in my tables

      because other tablespaces have some space in it ,

      after that when i was trying to add a stored proc it gave me a message

      "Unable to extend index SYS.I_IDL_SB41 by 8 in tablespace SYSTEM."

       

      i have tried the below query it did the job of extending the datafile.

      alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 600M  MAXSIZE 700M;

      but after that when i tried to add a stored proc it gave me a message,

      that u have exceded 11g  limit.

       

      1)please suggest me some solution for above

       

      2) is there any way to clean system schema form unused objects.

       

      yours sincerely

        • 1. Re: can i delete a table from system table space.
          DK2010

          Hi,

           

          SYS_EXPORT_SCHEMA_01 Table created by export-data pump and drop after the Export job successfully completed,Seems your export has not completed in  past.

          i believe this  table does not have much data  which can impact in your case,

          Query from dba_ts_quotas/dba_segments and find out what the other user use your SYSTEM TS, other then sys objects,you can move those objects on different TS.

           

          HTH

          • 2. Re: can i delete a table from system table space.
            EdStevens

            947771 wrote:

             

            hi,

            i am using 11g xe r2 on windos.

             

            my system space is filled it shows (600M(used) 600M(maxsize) datafile detail autoextend is on)

            but other tablespaces have some space in it.

             

            1)i show one table in system schema

            SYS_EXPORT_SCHEMA_01

            can i delete it ,is there any thing else which is related to export import in system tablespacewhich i  can be deleted to free up some space.

             

             

            but i was able to add recored in my tables

            because other tablespaces have some space in it ,

            after that when i was trying to add a stored proc it gave me a message

            "Unable to extend index SYS.I_IDL_SB41 by 8 in tablespace SYSTEM."

             

            i have tried the below query it did the job of extending the datafile.

            alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 600M  MAXSIZE 700M;

            but after that when i tried to add a stored proc it gave me a message,

            that u have exceded 11g  limit.

             

            1)please suggest me some solution for above

             

            2) is there any way to clean system schema form unused objects.

             

            yours sincerely

            My suggestion is to forget about that one table and take a look at the whole usage of your SYSTEM ts.  it sounds like you have a bunch of stuff there that shouldn't ... like a bunch of user/application objects.   What do you get from

             

            select owner, segment_name

            from dba_segments

            where tablespace_name = 'SYSTEM'

            and owner != 'SYS'

            /

            No, don't post the list back here.  Just look and see if there are any owners listed who shouldn't be there ... or segments belonging to SYSTEM that shouldn't ....

            • 3. Re: can i delete a table from system table space.
              947771

              two more questions

              1) please tel me how to delete a segment from system , with out harming any thing?

              2) how to identify which segment i am not using, specially from systems?

               

              yours sincerely

              • 4. Re: can i delete a table from system table space.
                947771

                please tel me how to  transfer a segment from system to other TS , with out harming ?

                 

                yours sincerely

                • 5. Re: can i delete a table from system table space.
                  Srini Chavali-Oracle

                  SYS and SYSTEM objects are seeded objects required for the correct functioning of the database, and should not be messed with. You have yet to post any proof that conclusively shows that any of the seeded tables are the cause of the space issue

                   

                  HTH
                  Srini

                  • 6. Re: can i delete a table from system table space.
                    947771

                    1) i show this table in system schema, where as it was not in other databases system schema, thought i perform same task at both datababase (oracle installation at diffrent machine)

                    2) i have same amount of data in both oracle installation(databases), but their system schema (used space is diffrent).

                     

                    yours sincerley

                    • 7. Re: can i delete a table from system table space.
                      Srini Chavali-Oracle

                      How much space does this table occupy ? Use DBA_SEGMENTS view to determine the size. This table is created / used by expdp/impdp utilities.

                       

                      Srini

                      • 8. Re: can i delete a table from system table space.
                        947771

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

                        HEADER_BLOCK:73896

                        BYTES:327680

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

                        i am also pasing complet row .

                         

                         

                        OWNER                          SEGMENT_NAME                                                                      PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SUBTYPE TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTION MINRETENTION PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE

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

                        SYSTEM                         SYS_EXPORT_SCHEMA_01                                                                                             TABLE              MSSM            SYSTEM                                   1        73896 327680     40       5          65536     1048576           1  2147483645 2147483645                                             1               1            1 DEFAULT     DEFAULT     DEFAULT        

                         

                        yours sincerly

                        • 9. Re: can i delete a table from system table space.
                          DK2010

                          Hi,

                          this table size is only 327680==>320KB, do you think this cause your system TS ?

                          use this Query to find the top segment

                           

                          SELECT owner,segment_name, segment_type,  sum(BYTES)/1024/1024 M  FROM dba_segments where tablespace_name='SYSTEM'

                          GROUP BY SEGMENT_NAME, SEGMENT_TYPE,owner ORDER BY 4 desc;

                          Show the top 20  rows here

                          • 10. Re: can i delete a table from system table space.
                            947771

                            -----

                            SYS SOURCE$ 112 TABLE

                            SYS IDL_UB1$ 72 TABLE

                            SYS IDL_UB2$ 31 TABLE

                            SYS C_OBJ#_INTCOL# 21 CLUSTER

                            SYS I_SOURCE1 21 INDEX

                            SYS C_TOID_VERSION# 17 CLUSTER

                            SYS C_OBJ# 15 CLUSTER

                            SYS IDL_CHAR$ 11 TABLE

                            SYS ARGUMENT$ 10 TABLE

                            SYS SYS_LOB0000001051C00003$$ 9 LOBSEGMENT

                            SYSTEM SYS_LOB0000097728C00045$$ 8 LOBSEGMENT

                            SYS I_COL1 8 INDEX

                            SYS I_ARGUMENT1 7 INDEX

                            DDLUSER GGS_MARKER 7 TABLE

                            DDLUSER SYS_LOB0000063135C00007$$ 7 LOBSEGMENT

                            SYS HIST_HEAD$ 6 TABLE

                            SYS VIEW$ 6 TABLE

                            SYS I_ARGUMENT2 5 INDEX

                            SYS C_COBJ# 5 CLUSTER

                            SYS I_COL2 4 INDEX

                             

                            -----

                            please tel me how to find which one i can delete any link would be appreciatable?

                            my primay objective is to delete any think unwanted becuase i am using 11g express r2 , space limitation is quite stringent.

                             

                            yours sincerely

                            • 11. Re: can i delete a table from system table space.
                              DK2010

                              Hi,

                               

                              this  is not the result of my Query,which Query you run ?

                              show the result of this one

                              SELECT owner,segment_name, segment_type,  sum(BYTES)/1024/1024 M  FROM dba_segments where tablespace_name='SYSTEM' and owner='DDLUSER'

                              GROUP BY SEGMENT_NAME, SEGMENT_TYPE,owner ORDER BY 4 desc;

                              • 12. Re: can i delete a table from system table space.
                                947771

                                hi,

                                DDLUSER GGS_MARKER TABLE 7

                                DDLUSER SYS_LOB0000063135C00007$$ LOBSEGMENT 7

                                DDLUSER SYS_LOB0000039123C00004$$ LOBSEGMENT 4

                                DDLUSER SYS_LOB0000063135C00008$$ LOBSEGMENT 2

                                DDLUSER SYS_C0032497 INDEX 0.4375

                                DDLUSER GGS_MARKER_IND1 INDEX 0.25

                                DDLUSER SYS_LOB0000039123C00005$$ LOBSEGMENT 0.25

                                DDLUSER SYS_LOB0000063135C00009$$ LOBSEGMENT 0.125

                                DDLUSER ACTIVITY TABLE 0.125

                                DDLUSER MSSQLTTEST2 TABLE 0.0625

                                DDLUSER SYS_IL0000063135C00007$$ LOBINDEX 0.0625

                                DDLUSER MSSQLTTEST_PK INDEX 0.0625

                                DDLUSER SYS_LOB0000062984C00005$$ LOBSEGMENT 0.0625

                                DDLUSER SYS_IL0000062990C00004$$ LOBINDEX 0.0625

                                DDLUSER SYS_C0032498 INDEX 0.0625

                                DDLUSER GGS_DDL_RULES_LOG TABLE 0.0625

                                DDLUSER SYS_IL0000063135C00008$$ LOBINDEX 0.0625

                                DDLUSER SYS_IL0000062984C00004$$ LOBINDEX 0.0625

                                DDLUSER SYS_C0032523 INDEX 0.0625

                                DDLUSER SYS_C0032529 INDEX 0.0625

                                DDLUSER CHKDDLUSER TABLE 0.0625

                                DDLUSER MSSQLTTEST1_PK INDEX 0.0625

                                DDLUSER SYS_IL0000062990C00005$$ LOBINDEX 0.0625

                                DDLUSER GGS_DDL_RULES TABLE 0.0625

                                DDLUSER SYS_IL0000039123C00004$$ LOBINDEX 0.0625

                                DDLUSER SYS_IL0000039123C00005$$ LOBINDEX 0.0625

                                DDLUSER SYS_LOB0000062990C00005$$ LOBSEGMENT 0.0625

                                DDLUSER CHKDDLUSER_LOX TABLE 0.0625

                                DDLUSER SYS_IL0000063135C00009$$ LOBINDEX 0.0625

                                DDLUSER SYS_LOB0000062984C00004$$ LOBSEGMENT 0.0625

                                DDLUSER MSSQLTTEST1 TABLE 0.0625

                                DDLUSER SYS_LOB0000062990C00004$$ LOBSEGMENT 0.0625

                                DDLUSER SYS_IL0000062984C00005$$ LOBINDEX 0.0625

                                DDLUSER MSSQLTTEST TABLE 0.0625

                                DDLUSER PK_ACTIVITY INDEX 0.0625

                                 

                                 

                                This user was created to be used in OGG replication. according to documents of OGG installation for oracle.

                                I can delete this now , because we are using simple way to transfer data from one database to other using OGG , so i gess these segments of DDLUSER is not in use.

                                yes this i want to delete SYS_EXPORT_SCHEMA_01 and others like this.

                                if u tel me some safe procedure.

                                yours sincerely

                                • 13. Re: can i delete a table from system table space.
                                  DK2010

                                  Hi,

                                   

                                  If you can drop, then you can drop the user DDLUSER

                                   

                                  drop user DDLUSER cascade;

                                   

                                  It will drop all the object related to that user, and u will get free space on SYSTEM TS

                                   

                                  HTH

                                  • 14. Re: can i delete a table from system table space.
                                    947771

                                    thank u for suggestion, for DDLuser,

                                    appart from this i would like to know about this SYS_EXPORT_SCHEMA_01 table as it is related to export import , generally , export process it self drops it , due to space problem it could not delete it so please  tel me

                                    how to drop it safly .

                                    And is there any way i can delte segments from system table? if yes the how can i find thoes segments. which can be deleted?

                                     

                                    yours sincerely

                                    1 2 Previous Next