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

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

Comments

Popular posts from this blog

Reboot Exadata Machine

ORA-01565: error in identifying file '?/dbs/spfile@.ora'

STEPS TO troubleshoot long running concurrent request in R12.2.x