tablespace SYSTEM used by others user, not sys. Help!

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • tracy

    tablespace SYSTEM used by others user, not sys. Help!

    hi,
    i need advise and guidance from experts.
    Today, I discovered about 1464 tables created in SYSTEM tablespace and
    the owner is dev_user.

    As, i read from ORACLE 101 DBA's it said :'System tablespace is a
    tablespace user to stored objects belong to user SYS only. if, we do
    not ensure this, the SYSTEM tablespace will :
    • Tend to get very fragmented
    • Have potentially severe contention against it and affect performance

    we cannot alter SYSTEM tablespace offline, there is no way to rebuild
    this tablespace without rebuilding the whole database and this can be
    very time consuming.'

    Then, wat should i do? please advise.


    regards,
    tracykim
  • x

    #2
    Re: tablespace SYSTEM used by others user, not sys. Help!

    You should move all tables and rebuild all indexes to different tablespace.

    something like:

    alter table my_table move tablespace users;

    alter index my_index rebuild tablespace users;

    Use dba_tables and dba_indexes to find out which tables and indexes are in
    system tablespace. You could use it like this:


    select 'alter table ' || table_name || ' move tablespace users;'
    from dba_tables
    where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')

    Output of this query is a script which you should run to move all the
    tables. Do the same for indexes.


    Comment

    • Mark D Powell

      #3
      Re: tablespace SYSTEM used by others user, not sys. Help!

      "x" <x@x.hrwrote in message news:<c9mrg5$6t 5$1@ls219.htnet .hr>...
      You should move all tables and rebuild all indexes to different tablespace.
      >
      something like:
      >
      alter table my_table move tablespace users;
      >
      alter index my_index rebuild tablespace users;
      >
      Use dba_tables and dba_indexes to find out which tables and indexes are in
      system tablespace. You could use it like this:
      >
      >
      select 'alter table ' || table_name || ' move tablespace users;'
      from dba_tables
      where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')
      >
      Output of this query is a script which you should run to move all the
      tables. Do the same for indexes.
      I think the first step should be to alter the user dev_user to have a
      default tablespace other than SYSTEM. If dev_user is not a DBA I
      would then alter the user's quota/privilege depending on exactly how
      this ID is to be used to prevent it from using SYSTEM. This will stop
      any new non-SYS objects from being created in the SYSTEM tablespace.

      Moving or dropping the objects is the next step, and depending on
      object usage may take a while.

      HTH -- Mark D Powell --

      Comment

      • tracy

        #4
        Re: tablespace SYSTEM used by others user, not sys. Help!

        hi, guys,
        tanz for the help. Just to make sure i run the rite script, here is
        the sql, how i found out non-sys user created a tables in SYSTEM
        tablespace. In fact, this dev_user have been assigened a default
        tablespace which is dev_user Tablespace. I wonder, how can the table
        created by them is in system schema? Pls let me know, if u have the
        answer.

        1 select owner, TABLESPACE_NAMe , table_name
        2 from dba_tables
        3 where TABLESPACE_NAMe ='SYSTEM'
        4 AND OWNER='DEV_USER '

        SQL/
        OWNER TABLESPACE_NAME
        TABLE_NAME
        ------------------------------ ------------------------------
        --------------------------
        DEV_USER SYSTEM
        CES_DETAIL
        DEV_USER SYSTEM CES_MAIN
        DEV_USER SYSTEM
        CHARGES_LOOKUP
        DEV_USER SYSTEM
        CHECK_AGING
        DEV_USER SYSTEM
        CHECK_CREDITOR



















        Mark.Powell@eds .com (Mark D Powell) wrote in message news:<2687bb95. 0406030639.4b7b 69c7@posting.go ogle.com>...
        "x" <x@x.hrwrote in message news:<c9mrg5$6t 5$1@ls219.htnet .hr>...
        You should move all tables and rebuild all indexes to different tablespace.

        something like:

        alter table my_table move tablespace users;

        alter index my_index rebuild tablespace users;

        Use dba_tables and dba_indexes to find out which tables and indexes are in
        system tablespace. You could use it like this:


        select 'alter table ' || table_name || ' move tablespace users;'
        from dba_tables
        where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')

        Output of this query is a script which you should run to move all the
        tables. Do the same for indexes.
        >
        I think the first step should be to alter the user dev_user to have a
        default tablespace other than SYSTEM. If dev_user is not a DBA I
        would then alter the user's quota/privilege depending on exactly how
        this ID is to be used to prevent it from using SYSTEM. This will stop
        any new non-SYS objects from being created in the SYSTEM tablespace.
        >
        Moving or dropping the objects is the next step, and depending on
        object usage may take a while.
        >
        HTH -- Mark D Powell --

        Comment

        • x

          #5
          Re: tablespace SYSTEM used by others user, not sys. Help!

          >I wonder, how can the table
          created by them is in system schema? Pls let me know, if u have the
          answer.
          >
          create table my_table (x varchar2(1)) tablespace system

          Easy as that. If your dev_user has right priviledges he can create table
          where ever he wants.



          Comment

          • tracy

            #6
            Re: tablespace SYSTEM used by others user, not sys. Help!

            hi, x
            dev_user only have priviledge to create table under his own tablespace
            which is dev_user only. he only granted the priviledge 'create table'
            not 'create any table'


            "x" <x@x.hrwrote in message news:<c9p4i6$nd q$1@ls219.htnet .hr>...
            I wonder, how can the table
            created by them is in system schema? Pls let me know, if u have the
            answer.
            >
            create table my_table (x varchar2(1)) tablespace system
            >
            Easy as that. If your dev_user has right priviledges he can create table
            where ever he wants.

            Comment

            • Frank van Bortel

              #7
              Re: tablespace SYSTEM used by others user, not sys. Help!

              tracy wrote:
              hi, x
              dev_user only have priviledge to create table under his own tablespace
              which is dev_user only. he only granted the priviledge 'create table'
              not 'create any table'
              >
              >
              "x" <x@x.hrwrote in message news:<c9p4i6$nd q$1@ls219.htnet .hr>...
              >
              >>>I wonder, how can the table
              >>>created by them is in system schema? Pls let me know, if u have the
              >>>answer.
              >>>
              >>
              >>create table my_table (x varchar2(1)) tablespace system
              >>
              >>Easy as that. If your dev_user has right priviledges he can create table
              >>where ever he wants.
              Unless granted the resource role, which offers quota unlimited on
              any tablespace. Unless you specifically do
              alter user quota 0 on system;
              your dev_user can still create objects in the system tablespace
              --

              Regards,
              Frank van Bortel

              Comment

              • Mark D Powell

                #8
                Re: tablespace SYSTEM used by others user, not sys. Help!

                Frank van Bortel <fvanbortel@net scape.netwrote in message news:<c9qd39$53 p$1@news1.tilbu 1.nb.home.nl>.. .
                tracy wrote:
                hi, x
                dev_user only have priviledge to create table under his own tablespace
                which is dev_user only. he only granted the priviledge 'create table'
                not 'create any table'


                "x" <x@x.hrwrote in message news:<c9p4i6$nd q$1@ls219.htnet .hr>...
                >>I wonder, how can the table
                >>created by them is in system schema? Pls let me know, if u have the
                >>answer.
                >>
                >
                >create table my_table (x varchar2(1)) tablespace system
                >
                >Easy as that. If your dev_user has right priviledges he can create table
                >where ever he wants.
                >
                Unless granted the resource role, which offers quota unlimited on
                any tablespace. Unless you specifically do
                alter user quota 0 on system;
                your dev_user can still create objects in the system tablespace

                Frank provided the information on how to lock the user out if it has
                the resource role. Privileges should be aligned to what the ID has to
                be able to do and no more.

                Depending on the age of the objects it is also possible that they were
                created before the default tablespace of the user was changed and were
                never relocated.

                Once you review and modify the privileges as necessary you still need
                to clean up the system tablespace.

                -- Mark D Powell --

                Comment

                Working...