-- (C) arnd.spiering@as-informatik.net.de
-- call: select MDSYS_USER_METADATA_BACKUP() as backup from dual;
create or replace
FUNCTION MDSYS_USER_METADATA_BACKUP RETURN VARCHAR2 AS 
cursor c_meta is select * from all_objects where owner = 'MDSYS' and object_name like 'USER%' and object_type = 'VIEW';
v_error number;
v_count number;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    v_count := 0;
    v_error := 0;
   for rec in c_meta
        loop
          begin
            execute immediate 'drop table '|| replace(rec.object_name,'USER','ZZZZZ') || ' ';
          exception
            when others then
              null;
          end;  
          
          begin
            execute immediate 'create table '|| replace(rec.object_name,'USER','ZZZZZ') || ' as select * from ' || rec.object_name;
          exception
            when others then
              v_error := v_error +1;
          end;
          v_count := v_count +1;
        end loop;
  
    return 'MDSYS backup tables created successfully (ZZZZ Tables: ' || v_count || ' - - Errors: ' || v_error || ')!';
END MDSYS_USER_METADATA_BACKUP;