find all users objects that is stored in SYSTEM tablespace

find all users objects that is stored in SYSTEM tablespace


To find all users objects that is stored in SYSTEM tablespace (except from SYS, SYSTEM, etc) execute:



SELECT 'alter ' || object_type || ' '|| owner || '.' || segment_name || 
DECODE(object_type, 'TABLE', ' move ', 'INDEX', ' rebuild ', NULL) 
|| 'tablespace ' || default_tablespace || ' nologging;' move_sql
FROM (SELECT S.owner, S.segment_name, o.OBJECT_TYPE, u.DEFAULT_TABLESPACE 
FROM dba_segments S, dba_users U, dba_objects O
WHERE tablespace_name = 'SYSTEM'
AND o.owner NOT IN ('SYS', 'SYSTEM', 'OUTLN') 
AND S.OWNER = o.OWNER 
AND S.OWNER = U.USERNAME
AND S.SEGMENT_NAME = o.OBJECT_NAME);