Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    46

    Unanswered: Non-System Data Segments in System Tablespaces

    Hello all dear,

    Am using 10.2.0.1.0 oracle database on window server 2003 Release 2
    Any time I lauch my Oracle Enterprise Manager this error keeps appearing
    "Non-System Data Segments in System Tablespaces"

    I don't know what this could result to and

    I would be very grateful if anyone can help me resolve this error

    That is, How do I relocate these Non-system objects from the SYSTEM tablespace to a non-system tablespace eg. USERS tablespace

    Thanks

    Kwame

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    ALTER TABLE <the_table> MOVE TABLESPACE USERS;

    should do this.

    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2192749

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Find out what is there

    Use these simple queries to first find out what does not belong:
    Code:
    --
    -- Find users that have SYSTEM as default tablespace:
    --
    SELECT username
      FROM dba_users
     WHERE default_tablespace = 'SYSTEM' AND username NOT IN ('SYS', 'SYSTEM');
    --
    -- Find users that have objects in the SYSTEM tablespace:
    --
    SELECT   owner, 'T' typ, table_name
        FROM dba_tables
       WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS', 'SYSTEM')
    UNION
    SELECT   owner, 'I', index_name
        FROM dba_indexes
       WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS', 'SYSTEM')
    ORDER BY 1, 2, 3;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Mar 2009
    Posts
    46
    Dear DBAs
    Thanks very much for your prompt responses. I have been able to move the non-system tables from the SYSTEM tablespace to USERS tablespace.

    However, when I tried to move non-SYSTEM indexes with the same approach:

    ALTER INDEX < index_name> move tablespace <tspace_name>;

    the following error results:
    ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

    I would therefore like to know whether indexes can also be moved with the same command OR
    Is there any way to move the indexes too??

    Much thanks in Advance

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Rebuild

    For Indexes you need to use:
    Code:
    ALTER INDEX {thIndex} REBUILD TABLESPACE...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Mar 2009
    Posts
    46
    Hello LKBrwn,

    It has worked and am most grateful for your assistance

    Am equally gratefull to all who helped in one way or the other

    Thanks to all

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Make sure that the people who have SYSTEM as there default tablespace (except the system accounts) are changed to USERS or the same thing will happen again.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2009
    Posts
    1
    what (negative) impact/s will the moving of non-system objects from the SYSTEM tablespace to USERS tablespace have?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Except using space in the other tablespace, NONE. as a matter of fact, your system will run better is there is no fragmentation in the system tablespace.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •