Dear All:
We are using Oracle 10.2.0.4, and planned to migrate on 11.2.0.2.
We want to generate ddl to create database users, grant the roles and privileges.
I am able to generate the DDL to create the users (with below PL/SQL code), but DDL to grant roles and privileges is required.
set serveroutput on SIZE 10000000
spool D:\DB_Upgrade\Pre_Upgrade_Scripts\create_users.sql
declare
user_script CLOB;
cursor cur_user IS
select username
from dba_users
where account_status = 'OPEN'
and username not in ('SYS',
'SYSTEM',
'SYSMAN',
'DBSNMP',
'SQLTXPLAIN',
'TRCANLZR',
'MGMT_VIEW');
BEGIN
dbms_output.put_line('set serveroutput on SIZE 10000000;');
dbms_output.put_line('spool create_users.log;');
for rec_user in cur_user LOOP
select dbms_metadata.get_ddl('USER', rec_user.username) || ';'
into user_script
from dual;
dbms_output.put_line(user_script);
end loop;
dbms_output.put_line('spool off;');
end;
/
Regards,
Hassan