Script to generate Analyze Schema, Coalesce Tablespace and Compile Invalid Objects

To Generate Coalesce Tablespace:

SELECT 'ALTER TABLESPACE ' || NAME || ' COALESCE;' FROM V$TABLESPACE
WHERE NAME <> 'TEMP'
AND NAME <> 'SYS'
AND NAME <> 'SYSTEM'
AND NAME <> 'UNDOTBS1'
ORDER BY NAME

To Generate Analyze Schema:

SELECT 'execute DBMS_UTILITY.ANALYZE_SCHEMA('''|| username ||''',''COMPUTE'');' FROM dba_users
WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM'
ORDER BY USERNAME;

To Generate Invalid Objects:

SELECT ' ALTER VIEW '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='VIEW';

SELECT ' ALTER PROCEDURE '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='TRIGGER'

To Generate Index Rebuild:

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;'
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS','SYSTEM');