Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Finding objects not owned by system

How can I tell if there are objects in the SYSTEM tablespace that are not owned by SYSTEM? 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 last published in February 2004

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.