alter session set PARALLEL_DEGREE_POLICY=AUTO

alter session enable parallel dml

alter session enable parallel ddl

ALTER system set "_optimizer_compute_index_stats" = FALSE

alter system set SKIP_UNUSABLE_INDEXES=TRUE

create table orderbyclauses( 
owner varchar2(30 char), 
table_name varchar2(30 char), 
orderbyclause varchar2(512 char) 
)

Insert into ORDERBYCLAUSES (OWNER,TABLE_NAME,ORDERBYCLAUSE) values ('SCHEMA1','F_S','order by GESCHAEFTSFELD,SCHICHTSTATUS,FK_RESSOURCE_ID,SCHICHTART,PAZ_MIN')

commit

declare 
type tslist is varray(5) of string(30); 
schemalist tslist := tslist('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); 
begin 
for idx in schemalist.first..schemalist.last loop 
 
for XCR in (select 'alter table ' || owner || '.' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ' cascade keep index' sqlstmt 
from DBA_CONSTRAINTS 
WHERE OWNER=schemalist(idx) and CONSTRAINT_TYPE in ('R','P','U')) 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
for XCR in (select 'alter index ' || owner || '.' || index_name || ' unusable' sqlstmt 
from dba_indexes 
WHERE OWNER=schemalist(idx)) 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
for XCR in (select  
'insert /*+ append */ into ' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' select * from X_' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' subpartition (' || X.SUBPARTITION_NAME || ') ' 
|| Y.orderbyclause xferstmt, 
'alter table X_' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' truncate subpartition ' || X.SUBPARTITION_NAME || ' drop storage' truncstmt 
from dba_tab_subpartitions X 
left outer join orderbyclauses Y on Y.owner=X.TABLE_OWNER and Y.table_name=X.TABLE_NAME 
where X.table_owner=schemalist(idx)) 
loop 
DBMS_RESOURCE_MANAGER.BEGIN_SQL_BLOCK; 
dbms_output.put_line(XCR.xferstmt); 
execute immediate XCR.xferstmt; 
dbms_output.put_line(XCR.truncstmt); 
execute immediate XCR.truncstmt; 
commit; 
DBMS_RESOURCE_MANAGER.END_SQL_BLOCK; 
end loop; 
 
for XCR in (select  
'insert /*+ append */ into ' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' select * from X_' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' partition (' || X.PARTITION_NAME || ') ' 
|| Y.orderbyclause xferstmt, 
'alter table X_' || X.TABLE_OWNER || '.' || X.TABLE_NAME || ' truncate partition ' || X.PARTITION_NAME || ' drop storage' truncstmt 
from dba_tab_partitions X 
left outer join orderbyclauses Y on Y.owner=X.TABLE_OWNER and Y.table_name=X.TABLE_NAME 
where X.table_owner=schemalist(idx) and X.SUBPARTITION_COUNT=0) 
loop 
DBMS_RESOURCE_MANAGER.BEGIN_SQL_BLOCK; 
dbms_output.put_line(XCR.xferstmt); 
execute immediate XCR.xferstmt; 
dbms_output.put_line(XCR.truncstmt); 
execute immediate XCR.truncstmt; 
commit; 
DBMS_RESOURCE_MANAGER.END_SQL_BLOCK; 
end loop; 
 
for XCR in (select 
'insert /*+ append */ into ' || X.OWNER || '.' || X.TABLE_NAME || ' select * from X_' || X.OWNER || '.' || X.TABLE_NAME 
|| ' ' || Y.orderbyclause xferstmt, 
'truncate table X_' || X.OWNER || '.' || X.TABLE_NAME truncstmt 
from dba_tables X 
left outer join orderbyclauses Y on Y.owner=X.OWNER and Y.table_name=X.TABLE_NAME 
where X.OWNER=schemalist(idx) and X.PARTITIONED='NO') 
loop 
DBMS_RESOURCE_MANAGER.BEGIN_SQL_BLOCK; 
dbms_output.put_line(XCR.xferstmt); 
execute immediate XCR.xferstmt; 
dbms_output.put_line(XCR.truncstmt); 
execute immediate XCR.truncstmt; 
commit; 
DBMS_RESOURCE_MANAGER.END_SQL_BLOCK; 
end loop; 
 
end loop; 
end; 

declare 
type tslist is varray(5) of string(30); 
schemalist tslist := tslist('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); 
begin 
for idx in schemalist.first..schemalist.last loop 
 
for XCR in (select 'alter index ' || INDEX_OWNER || '.' || INDEX_NAME || ' rebuild subpartition ' || SUBPARTITION_NAME 
sqlstmt 
from DBA_IND_SUBPARTITIONS 
WHERE INDEX_OWNER=schemalist(idx)) 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
for XCR in (select 'alter index ' || INDEX_OWNER || '.' || INDEX_NAME || ' rebuild partition ' || PARTITION_NAME 
sqlstmt 
from DBA_IND_PARTITIONS 
WHERE INDEX_OWNER=schemalist(idx) and SUBPARTITION_COUNT=0) 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
for XCR in (select 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild' 
sqlstmt 
from DBA_INDEXES 
WHERE OWNER=schemalist(idx) and PARTITIONED='N') 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
for XCR in (select 'alter table ' || owner || '.' || TABLE_NAME || ' enable validate constraint ' || CONSTRAINT_NAME sqlstmt 
from DBA_CONSTRAINTS 
WHERE OWNER=schemalist(idx) and CONSTRAINT_TYPE in ('R','P','U')) 
loop 
dbms_output.put_line(XCR.sqlstmt); 
execute immediate XCR.sqlstmt; 
end loop; 
 
end loop; 
end; 

drop table orderbyclauses

ALTER system set "_optimizer_compute_index_stats" = TRUE

exec DBMS_STATS.GATHER_SCHEMA_STATS(SCHEMA1,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>16,granularity=>'ALL',cascade='TRUE') 

exec DBMS_STATS.GATHER_SCHEMA_STATS(SCHEMA2,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>16,granularity=>'ALL',cascade='TRUE') 

exec DBMS_STATS.GATHER_SCHEMA_STATS(SCHEMA3,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>16,granularity=>'ALL',cascade='TRUE') 

exec DBMS_STATS.GATHER_SCHEMA_STATS(SCHEMA4,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>16,granularity=>'ALL',cascade='TRUE') 

exec DBMS_STATS.GATHER_SCHEMA_STATS(SCHEMA5,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>16,granularity=>'ALL',cascade='TRUE') 

spool off 

exit