Ask the Expert

Finding objects not owned by system

How can I tell if there are objects in the SYSTEM tablespace that are not owned by SYSTEM?

    Requires Free Membership to View

    Login

There will always be objects in the SYSTEM tablespace that are not owned by SYSTEM. In particular, the data dictionary tables, owned by SYS will be there. Aside from that, it is good practice to move all other objects to other tablespaces. You can use the following query to find out which objects (or segments) are in the SYSTEM tablespace owned by other users: 
SELECT owner,segment_name,segment_type
FROM dba_segments
WHERE tablespace_name='SYSTEM'
  AND owner NOT IN ('SYSTEM','SYS');

Once you've identified those segments, move them. For table segments, move them with:

ALTER TABLE owner.table_name MOVE new_tablespace;

For indexes, move them with:

ALTER TABLE owner.index_name REBUILD TABLESPACE new_tablespace;

This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top