Q

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 first published in February 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close