Migrate users with DBMS_METADATA

Do you need to migrate users with all system and object grants ?. The DBMS_METADATA package helps us on Oracle 9i or higher. This script collects all information that you need:

-- DIR_OUTPUT: directory where create the output script file cr_users.sql.

create procedure EXTRACT_DDL_FOR_USERS as

no_grant exception;

pragma exception_init( no_grant, -31608 );
-- to skip the ORA-31608 when no data for the object requested.

cursor get_username is
select username
from dba_users;

file_handle utl_file.file_type;
stmt clob;

begin
file_handle:=utl_file.fopen('DIR_OUTPUT','cr_users.sql','w',32767);

for l_user in get_username loop

-- USERS
stmt := DBMS_METADATA.GET_DDL('USER', l_user.username);
utl_file.put(file_handle, stmt);

-- SYSTEM GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no system grants';
end;
utl_file.put(file_handle,stmt);

-- OBJECT GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no object grants';
end;
utl_file.put(file_handle,stmt);

-- ROLE GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no role grants';
end;
utl_file.put(file_handle, stmt);

-- TABLESPACE QUOTAS
begin
stmt:=DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',l_user.username);
exception
when no_grant then stmt := '-- no tablespace quota';
end;
utl_file.put(file_handle, stmt);

-- DEFAULT ROLES
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username);
exception
when no_grant then stmt := '-- no default role';
end;
utl_file.put(file_handle, stmt);

end loop;
utl_file.fclose(file_handle);
end;
/



Then you can execute it with:

set long 100000
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
extract_ddl_for_users;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
end;
/


I use the SET_TRANSFORM_PARAM to add the terminator character statement (;).