Scribd
Upload
14 views1 page

FINDING

Uploaded by

aribrabbani2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
14 views1 page

FINDING

Uploaded by

aribrabbani2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

--########Part 1##########

set heading off;


set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off
trimspool on
column ddl format a1000

begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform,
'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY',
true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper('&1');

--########Part 2###########

set heading off;


spool /m01/oracle/arib/CREATE_USER.sql

select dbms_metadata.get_ddl('USER', u.username) AS ddl


from dba_users u
where u.username = :v_username
/
select distinct 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ';' from
dba_sys_privs where grantee = :v_username;
select distinct 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || '
TO ' || GRANTEE || ';' from dba_tab_privs where grantee = :v_username;
select distinct ' grant ' ||GRANTED_ROLE|| ' TO ' || GRANTEE ||' ;' from
dba_role_privs where grantee= :v_username;
spool o|

grant select on owner_name.packgname to username;

You might also like