This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 6, 2013 6:46 AM by Sathyanarayanan RSS

can i delete a table from system table space.

947771 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    -----

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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