Find users other than SYS, with objects stored in the SYSTEM tablespace
                                                 Last update (2012-06-22 23:33:37)
                                                                                                                   Date added (2008-02-29 21:04:21)

Summary
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);
Reviews
Filters
Search