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 (;).
Saturday, April 5, 2008
Subscribe to:
Post Comments (Atom)
4 comments:
Hello. This post is likeable, and your blog is very interesting, congratulations :-). I will add in my blogroll =). If ossible gives a last there on my blog, it is about the Aparelho de DVD, I hope you enjoy. The address is http://aparelho-dvd.blogspot.com. A hug.
Hi
When the DBMS_METADATA is run directly (SQLPLUS), I have no problem.
But when wrapped in your procedure, I get the error (at GET_DDL) that the user is not found in the schema of the caller (ORA-31603: ), which is the same user used when calling the DBMS_METADATA directly in SQLPLUS.
follow-up on last post:
When the procedure is created in the SYS shema, there is no problems.
With SYSTEM, the problem is the same, it only works with SYS.
-----------8<-------A little joke..
create procedure SALUTACIO_FORMAL_D_ALGU_QUE_ET_CONEIX
no_grant exception;
pragma exception_init( no_grant, -31608 );
-- to skip the ORA-31608 when no person for the object requested.
cursor get_persona is
select nom
from dba_coneguts;
file_handle utl_file.file_type;
stmt clob;
begin
file_handle:=utl_file.fopen('DIR_OUTPUT','cr_persones.sql','w',32767);
for l_conegut in get_conegut loop
-- CONEGUTS
stmt := DBMS_METADATA.GET_DDL('NOM_CONEGUT', l_CONEGUT.nom);
utl_file.put(file_handle, stmt);
-- SALUTACIO
begin
stmt:= DBMS_METADATA.SALUT_I_FEINA ('I_UN_BON_FORAT_PER_POSAR_L_EINA');
exception
when you_dont'nt_know_who_I_am then stmt:='sóc el cosí de la Trini de Corçà'
end;
utl_file.put(file_handle, stmt);
end loop;
utl_file.fclose(file_handle);
end;
/
-----------8<-------A little joke..
Bye!
Post a Comment