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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: