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;
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.