Oracle DBA

Amennyiben felhasznalokat kell migralnod, objektum/rendszer privilegiumokkal, role-ok, tablater quota-k megorzesevel itt egy szkript amivel a klonozo sql-t generalhatjuk:

 set long 100000
declare
 begin
 DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',TRUE);
 declare
   no_grant      EXCEPTION;
   PRAGMA EXCEPTION_INIT (no_grant, -31608);
   CURSOR get_username
   IS
      SELECT username
        FROM all_users;
      
   file_handle   UTL_FILE.file_type;
   stmt          CLOB;
BEGIN
   file_handle := UTL_FILE.fopen ('DP07', 'cr_users.sql', 'w', 32767);

   FOR l_user IN get_username
   LOOP

-- USERS
    stmt:=DBMS_METADATA.get_ddl ('USER', l_user.username)||chr(10);
    begin
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||'-- SYSTEM_GRANT'||chr(10);
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT', l_user.username)||chr(10);
     exception
      when no_grant then stmt := '-- no system grants'||chr(10);
    end;
    begin
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||'-- OBJECT_GRANT'||chr(10);
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT', l_user.username)||chr(10);
     exception
      when no_grant then stmt := '-- no object grants'||chr(10);
    end;
    begin
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||'-- ROLE_GRANT'||chr(10);
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username)||chr(10);
     exception
      when no_grant then stmt := '-- no role grants'||chr(10);
    end;
    begin
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||'-- TABLESPACE_QUOTA'||chr(10);
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||DBMS_METADATA.GET_GRANTED_DDL(
'TABLESPACE_QUOTA',l_user.username)||chr(10);
     exception
      when no_grant then stmt := '-- no tablespace quota'||chr(10);
    end;
   begin
     stmt:=stmt||'---------------------------------------'||chr(10);
     stmt:=stmt||'-- DEFAULT_ROLE'||chr(10);
     stmt:=stmt||'---------------------------------------'||chr(10);
    stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username)||chr(10);
    exception
     when no_grant then stmt := '-- no default role'||chr(10);
   end;

   UTL_FILE.put (file_handle, stmt);

 end loop;
 UTL_FILE.fclose (file_handle);
 END;
DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,
'SQLTERMINATOR',FALSE);
end;

/