Lock Account

 set echo on
spool /home/admin/lockAccount.log
BEGIN
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
 END LOOP;
END;
/
spool off

Who can ACCESS

Description : This script can be used to find who can access an object that is passed in. It checks recursively for users hierarchically via roles.

whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000

spool who_can_access.lis

undefine object_to_find
undefine owner_to_find
undefine output_method
undefine file_name
undefine output_dir
undefine skip_user
undefine user_to_skip

set feed off
col system_date  noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;

prompt who_can_access: Release 1.0.3.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. 
set feed on
prompt
accept object_to_find char prompt 'NAME OF OBJECT TO CHECK       [USER_OBJECTS]: ' default USER_OBJECTS
accept owner_to_find char prompt  'OWNER OF THE OBJECT TO CHECK          [USER]: ' default USER
accept output_method char prompt  'OUTPUT METHOD Screen/File                [S]: ' default S
accept file_name char prompt      'FILE NAME FOR OUTPUT              [priv.lst]: ' default priv.lst
accept output_dir char prompt     'OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: ' default /tmp
accept skip_user char prompt      'EXCLUDE CERTAIN USERS                    [N]: ' default N
accept user_to_skip char prompt   'USER TO SKIP                         [TEST%]: ' default TEST%
prompt 
declare
    --
    lg_fptr utl_file.file_type;
    lv_file_or_screen varchar2(1):='S';
    --
    procedure open_file (pv_file_name in varchar2,
            pv_dir_name in varchar2) is 
    begin
        lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A');
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (open_file) => '||sqlcode);
            dbms_output.put_line('MSG (open_file) => '||sqlerrm);

    end open_file;
    --
    procedure close_file is
    begin
        utl_file.fclose(lg_fptr);
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (close_file) => '||sqlcode);
            dbms_output.put_line('MSG (close_file) => '||sqlerrm);

    end close_file;
    --
    procedure write_op (pv_str in varchar2) is
    begin
        if lv_file_or_screen='S' then
            dbms_output.put_line(pv_str);
        else
            utl_file.put_line(lg_fptr,pv_str);
        end if;
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (write_op) => '||sqlcode);
            dbms_output.put_line('MSG (write_op) => '||sqlerrm);

    end write_op;
    --
    function user_or_role(pv_grantee in dba_users.username%type) 
    return varchar2 is
        --
        cursor c_use (cp_grantee in dba_users.username%type) is
        select  'USER' userrole 
        from    dba_users u 
        where   u.username=cp_grantee 
        union 
        select  'ROLE' userrole 
        from    dba_roles r 
        where   r.role=cp_grantee;
        --
        lv_use c_use%rowtype;
    begin
        open c_use(pv_grantee);
        fetch c_use into lv_use;
        close c_use;
        return lv_use.userrole;
    exception
        when others then
            dbms_output.put_line('ERROR (user_or_role) => '||sqlcode);
            dbms_output.put_line('MSG (user_or_role) => '||sqlerrm);
    end user_or_role;
    --
    procedure get_obj (pv_object in varchar2,pv_owner in varchar2) is
        --
        cursor c_main (cp_object in varchar2,cp_owner in varchar2) is
 select g.name grantee,
  decode(o.type#,2,'TABLE',
    4,'VIEW',
    6,'SEQUENCE',
    7,'PROCEDURE',
    8,'FUNCTION',
    9,'PACKAGE',
    13,'TYPE',
    22,'LIBRARY',
    23,'DIRECTORY',
    24,'QUEUE',
    29,'JAVA CLASS',
    30,'JAVA RESOURCE',
    32,'INDEXTYPE',
    33,'OPERATOR',
    48,'CONSUMER GROUP',
    62,'EVALUATION CONTEXT',
    'UNDEFINED') object_type,
  t.name privilege,
  decode(mod(a.option$,2),2,'YES','NO') grantable,
  '--' column_name,
  'TAB' coltype
 from sys.objauth$ a,
  sys.obj$ o,
  sys.user$ u,
  sys.user$ g,
  sys.table_privilege_map t
 where a.obj#=o.obj#
 and a.grantee#=g.user#
 and a.col# is null
 and a.privilege#=t.privilege
 and u.user#=o.owner#
 and u.name=cp_owner
 and o.name=cp_object
 union
 select g.name grantee,
  decode(o.type#,2,'TABLE',
    4,'VIEW',
    6,'SEQUENCE',
    7,'PROCEDURE',
    8,'FUNCTION',
    9,'PACKAGE',
    13,'TYPE',
    22,'LIBRARY',
    23,'DIRECTORY',
    24,'QUEUE',
    29,'JAVA CLASS',
    30,'JAVA RESOURCE',
    32,'INDEXTYPE',
    33,'OPERATOR',
    48,'CONSUMER GROUP',
    62,'EVALUATION CONTEXT',
    'UNDEFINED') object_type,
  t.name privilege,
  decode(mod(a.option$,2),2,'YES','NO') grantable,
  c.name column_name,
  'COL' coltype
 from sys.objauth$ a,
  sys.obj$ o,
  sys.user$ u,
  sys.user$ g,
  sys.col$ c,
  sys.table_privilege_map t
 where a.obj#=o.obj#
 and a.grantee#=g.user#
 and a.col#=c.col#
 and bitand(c.property,32)=0
 and a.col# is not null
 and a.privilege#=t.privilege
 and u.user#=o.owner#
 and u.name=cp_owner
 and o.name=cp_object
        order by 2,3,6;
        --
        lv_old_type dba_objects.object_type%type:='NOTSET';
        lv_old_priv dba_tab_privs.privilege%type:='NOTSET';
        --
        lv_userrole dba_users.username%type;
        lv_tabstop number;
        --
        procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is
            --
            lv_tab varchar2(50):='';
            lv_loop number;
            lv_user_or_role dba_users.username%type;
            --
            cursor c_user (cp_username in dba_role_privs.grantee%type) is
            select  r.grantee,
                    r.admin_option
            from    dba_role_privs r
            where   r.granted_role=cp_username;
            --
        begin
            pv_tabstop:=pv_tabstop+1;
            for lv_loop in 1..pv_tabstop loop
                lv_tab:=lv_tab||chr(9);
            end loop;
            
            for lv_user in c_user(pv_grantee) loop
                lv_user_or_role:=user_or_role(lv_user.grantee);
                if lv_user_or_role = 'ROLE' then
             if lv_user.grantee = 'PUBLIC' then
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option||')');
                 else
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option||')'
           ||' which is granted to =>');
                 end if;
                    get_users(lv_user.grantee,pv_tabstop);
                else
                    if upper('&&skip_user') = 'Y' and lv_user.grantee like upper('&&user_to_skip') then
                     null;
                    else
                     write_op(lv_tab||'User => '||lv_user.grantee
                   ||' (ADM = '||lv_user.admin_option||')');
      end if;
                end if;
            end loop;
            pv_tabstop:=pv_tabstop-1;
            lv_tab:='';
        exception
            when others then
                dbms_output.put_line('ERROR (get_users) => '||sqlcode);
                dbms_output.put_line('MSG (get_users) => '||sqlerrm);        
        end get_users;
        --
    begin
        write_op(chr(10));
        lv_tabstop:=1;
        for lv_main in c_main(pv_object,pv_owner) loop
            if (lv_old_type != lv_main.object_type)  then
                write_op('Object type is => '||lv_main.object_type||' ('||lv_main.coltype||') ');
            end if;
            if (lv_old_priv != lv_main.privilege) or (lv_old_type != lv_main.object_type) then
                write_op(chr(9)||'Privilege => '||lv_main.privilege||' is granted to =>');
            end if;
            lv_userrole:=user_or_role(lv_main.grantee);
            if lv_userrole='USER' then
                if upper('&&skip_user') = 'Y' and lv_main.grantee like upper('&&user_to_skip') then
                    null;
                else
             write_op(chr(9)||'User => '||lv_main.grantee
                               ||' (ADM = '||lv_main.grantable||')');
                end if;
            else
             if lv_main.grantee='PUBLIC' then
              write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.grantable||')');              
             else
              write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.grantable||')'
                ||' which is granted to =>');
              
             end if;
                get_users(lv_main.grantee,lv_tabstop);
            end if;
            lv_old_type:=lv_main.object_type;
            lv_old_priv:=lv_main.privilege;
        end loop;
    exception
        when others then
            dbms_output.put_line('ERROR (get_obj) => '||sqlcode);
            dbms_output.put_line('MSG (get_obj) => '||sqlerrm);
    end get_obj;
begin
     lv_file_or_screen:= upper('&&output_method');
     if lv_file_or_screen='F' then
         open_file('&&file_name','&&output_dir');
     end if;
     write_op('Checking object => '||upper('&&owner_to_find')||'.'||upper('&&object_to_find'));
     write_op('====================================================================');
     get_obj(upper('&&object_to_find'),upper('&&owner_to_find'));
     if lv_file_or_screen='F' then
         close_file;
     end if;
exception
     when others then
         dbms_output.put_line('ERROR (main) => '||sqlcode);
         dbms_output.put_line('MSG (main) => '||sqlerrm);

end;
/

prompt
prompt For updates please visit http://www.petefinnigan.com/tools.htm
prompt
spool off

whenever sqlerror continue

Who Has the ROLE

Description : Use this script to find which users and roles have been granted a specific role that you would like to query. The checks are done hierarchically via roles granted to roles etc.

whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000

spool who_has_role.lis

undefine role_to_find
undefine output_method
undefine file_name
undefine output_dir
undefine skip_user
undefine user_to_skip

set feed off
col system_date  noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;
set feed on

prompt who_has_priv: Release 1.0.3.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. 
prompt 
accept role_to_find char prompt  'ROLE TO CHECK                          [DBA]: ' default DBA
accept output_method char prompt 'OUTPUT METHOD Screen/File                [S]: ' default S
accept file_name char prompt     'FILE NAME FOR OUTPUT              [priv.lst]: ' default priv.lst
accept output_dir char prompt    'OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: ' default /tmp
accept skip_user char prompt     'EXCLUDE CERTAIN USERS                    [N]: ' default N
accept user_to_skip char prompt  'USER TO SKIP                         [TEST%]: ' default TEST%
prompt 
declare
    --
    lg_fptr utl_file.file_type;
    lv_file_or_screen varchar2(1):='S';
    --
    procedure open_file (pv_file_name in varchar2,
            pv_dir_name in varchar2) is 
    begin
        lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A');
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (open_file) => '||sqlcode);
            dbms_output.put_line('MSG (open_file) => '||sqlerrm);

    end open_file;
    --
    procedure close_file is
    begin
        utl_file.fclose(lg_fptr);
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (close_file) => '||sqlcode);
            dbms_output.put_line('MSG (close_file) => '||sqlerrm);

    end close_file;
    --
    procedure write_op (pv_str in varchar2) is
    begin
        if lv_file_or_screen='S' then
            dbms_output.put_line(pv_str);
        else
            utl_file.put_line(lg_fptr,pv_str);
        end if;
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (write_op) => '||sqlcode);
            dbms_output.put_line('MSG (write_op) => '||sqlerrm);

    end write_op;
    --
    function user_or_role(pv_grantee in dba_users.username%type) 
    return varchar2 is
        --
        cursor c_use (cp_grantee in dba_users.username%type) is
        select  'USER' userrole 
        from    dba_users u 
        where   u.username=cp_grantee 
        union 
        select  'ROLE' userrole 
        from    dba_roles r 
        where   r.role=cp_grantee;
        --
        lv_use c_use%rowtype;
        --
    begin
        open c_use(pv_grantee);
        fetch c_use into lv_use;
        close c_use;
        return lv_use.userrole;
    exception
        when others then
            dbms_output.put_line('ERROR (user_or_role) => '||sqlcode);
            dbms_output.put_line('MSG (user_or_role) => '||sqlerrm);
    end user_or_role;
    --
    function role_pwd(pv_role in dba_roles.role%type)
    return dba_roles.password_required%type is
     --
 cursor c_role(cp_role in dba_roles.role%type) is
 select r.password_required
 from dba_roles r
 where r.role=cp_role;
 --
 lv_role c_role%rowtype;
     --
    begin
     open c_role(pv_role);
     fetch c_role into lv_role;
     close c_role;
     return lv_role.password_required;
    exception     
        when others then
            dbms_output.put_line('ERROR (role_pwd) => '||sqlcode);
            dbms_output.put_line('MSG (role_pwd) => '||sqlerrm);
    end role_pwd;
    --
    procedure get_role (pv_role in varchar2) is
        --
        cursor c_main (cp_role in varchar2) is
 select p.grantee,
  p.admin_option
 from dba_role_privs p
 where p.granted_role=cp_role;
        --
        lv_userrole dba_users.username%type;
        lv_tabstop number;
        --
        procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is
            --
            lv_tab varchar2(50):='';
            lv_loop number;
            lv_user_or_role dba_users.username%type;
            --
            cursor c_user (cp_username in dba_role_privs.grantee%type) is
            select  d.grantee,
                    d.admin_option 
            from    dba_role_privs d
            where   d.granted_role=cp_username;
            --
        begin
            pv_tabstop:=pv_tabstop+1;
            for lv_loop in 1..pv_tabstop loop
                lv_tab:=lv_tab||chr(9);
            end loop;
            
            for lv_user in c_user(pv_grantee) loop
                lv_user_or_role:=user_or_role(lv_user.grantee);
                if lv_user_or_role = 'ROLE' then
             if lv_user.grantee = 'PUBLIC' then
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option
           ||'|PWD = '||role_pwd(lv_user.grantee)||')');
                 else
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option
          ||'|PWD = '||role_pwd(lv_user.grantee)||')'
           ||' which is granted to =>');
                 end if;
                    get_users(lv_user.grantee,pv_tabstop);
                else
                    if upper('&&skip_user') = 'Y' and lv_user.grantee like upper('&&user_to_skip') then
                     null;
                    else
                 write_op(lv_tab||'User => '||lv_user.grantee
                     ||' (ADM = '||lv_user.admin_option||')');
                    end if;
                end if;
            end loop;
            pv_tabstop:=pv_tabstop-1;
            lv_tab:='';
        exception
            when others then
                dbms_output.put_line('ERROR (get_users) => '||sqlcode);
                dbms_output.put_line('MSG (get_users) => '||sqlerrm);        
        end get_users;
        --
    begin
        lv_tabstop:=1;
        for lv_main in c_main(pv_role) loop 
  lv_userrole:=user_or_role(lv_main.grantee);
  if lv_userrole='USER' then
                 if upper('&&skip_user') = 'Y' and lv_main.grantee like upper('&&user_to_skip') then
                        null;
                     else
                     write_op(chr(9)||'User => '||lv_main.grantee
                  ||' (ADM = '||lv_main.admin_option||')');
   end if;
  else
              if lv_main.grantee='PUBLIC' then
               write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.admin_option
                ||'|PWD = '||role_pwd(lv_main.grantee)||')');
              else
               write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.admin_option
                ||'|PWD = '||role_pwd(lv_main.grantee)||')'
                ||' which is granted to =>');
              end if;
                 get_users(lv_main.grantee,lv_tabstop);
  end if;
 end loop;
    exception
        when others then
            dbms_output.put_line('ERROR (get_role) => '||sqlcode);
            dbms_output.put_line('MSG (get_role) => '||sqlerrm);
    end get_role;
begin
 lv_file_or_screen:= upper('&&output_method');
 if lv_file_or_screen='F' then
         open_file('&&file_name','&&output_dir');
 end if;
     write_op('Investigating Role => '||upper('&&role_to_find')||' (PWD = '
      ||role_pwd(upper('&&role_to_find'))||') which is granted to =>');
     write_op('====================================================================');
 get_role(upper('&&role_to_find'));
 if lv_file_or_screen='F' then
         close_file;
 end if;
exception
    when others then
        dbms_output.put_line('ERROR (main) => '||sqlcode);
        dbms_output.put_line('MSG (main) => '||sqlerrm);

end;
/
prompt For updates please visit http://www.petefinnigan.com/tools.htm
prompt
spool off

whenever sqlerror continue

Who Has the Privilege

Description : Use this script to find which users have been granted the privilege passed in. The script checks hierarchically for each user granted the privileges via a role.

whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000

spool who_has_priv.lis

undefine priv_to_find
undefine output_method
undefine file_name
undefine output_dir
undefine skip_user
undefine user_to_skip

set feed off
col system_date  noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;
set feed on

prompt who_has_priv: Release 1.0.3.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. 
prompt 
accept priv_to_find char prompt  'PRIVILEGE TO CHECK        [SELECT ANY TABLE]: ' default 'SELECT ANY TABLE'
accept output_method char prompt 'OUTPUT METHOD Screen/File                [S]: ' default S
accept file_name char prompt     'FILE NAME FOR OUTPUT              [priv.lst]: ' default priv.lst
accept output_dir char prompt    'OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: ' default /tmp
accept skip_user char prompt     'EXCLUDE CERTAIN USERS                    [N]: ' default N
accept user_to_skip char prompt  'USER TO SKIP                         [TEST%]: ' default TEST%
prompt 
declare
    --
    lg_fptr utl_file.file_type;
    lv_file_or_screen varchar2(1):='S';
    --
    procedure open_file (pv_file_name in varchar2,
            pv_dir_name in varchar2) is 
    begin
        lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A');
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (open_file) => '||sqlcode);
            dbms_output.put_line('MSG (open_file) => '||sqlerrm);

    end open_file;
    --
    procedure close_file is
    begin
        utl_file.fclose(lg_fptr);
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (close_file) => '||sqlcode);
            dbms_output.put_line('MSG (close_file) => '||sqlerrm);

    end close_file;
    --
    procedure write_op (pv_str in varchar2) is
    begin
        if lv_file_or_screen='S' then
            dbms_output.put_line(pv_str);
        else
            utl_file.put_line(lg_fptr,pv_str);
        end if;
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (write_op) => '||sqlcode);
            dbms_output.put_line('MSG (write_op) => '||sqlerrm);

    end write_op;
    --
    function user_or_role(pv_grantee in dba_users.username%type) 
    return varchar2 is
        --
        cursor c_use (cp_grantee in dba_users.username%type) is
        select  'USER' userrole 
        from    dba_users u 
        where   u.username=cp_grantee 
        union 
        select  'ROLE' userrole 
        from    dba_roles r 
        where   r.role=cp_grantee;
        --
        lv_use c_use%rowtype;
    begin
        open c_use(pv_grantee);
        fetch c_use into lv_use;
        close c_use;
        return lv_use.userrole;
    exception
        when others then
            dbms_output.put_line('ERROR (user_or_role) => '||sqlcode);
            dbms_output.put_line('MSG (user_or_role) => '||sqlerrm);
    end user_or_role;
    --
    procedure get_priv (pv_privilege in varchar2) is
        --
        cursor c_main (cp_privilege in varchar2) is
 select p.grantee,
  p.admin_option
 from dba_sys_privs p
 where p.privilege=cp_privilege;
        --
        lv_userrole dba_users.username%type;
        lv_tabstop number;
        --
        procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is
            --
            lv_tab varchar2(50):='';
            lv_loop number;
            lv_user_or_role dba_users.username%type;
            --
            cursor c_user (cp_username in dba_role_privs.grantee%type) is
            select  r.grantee,
                    r.admin_option
            from    dba_role_privs r
            where   r.granted_role=cp_username;
            --
        begin
            pv_tabstop:=pv_tabstop+1;
            for lv_loop in 1..pv_tabstop loop
                lv_tab:=lv_tab||chr(9);
            end loop;
            
            for lv_user in c_user(pv_grantee) loop
                lv_user_or_role:=user_or_role(lv_user.grantee);
                if lv_user_or_role = 'ROLE' then
             if lv_user.grantee = 'PUBLIC' then
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option||')');
                 else
          write_op(lv_tab||'Role => '||lv_user.grantee
           ||' (ADM = '||lv_user.admin_option||')'
           ||' which is granted to =>');
                 end if;
                    get_users(lv_user.grantee,pv_tabstop);
                else
                    if upper('&&skip_user') = 'Y' and lv_user.grantee like upper('&&user_to_skip') then
                     null;
                    else
                        write_op(lv_tab||'User => '||lv_user.grantee
                  ||' (ADM = '||lv_user.admin_option||')');
                    end if;
                end if;
            end loop;
            pv_tabstop:=pv_tabstop-1;
            lv_tab:='';
        exception
            when others then
                dbms_output.put_line('ERROR (get_users) => '||sqlcode);
                dbms_output.put_line('MSG (get_users) => '||sqlerrm);        
        end get_users;
        --
    begin
        lv_tabstop:=1;
        for lv_main in c_main(pv_privilege) loop 
  lv_userrole:=user_or_role(lv_main.grantee);
  if lv_userrole='USER' then
                 if upper('&&skip_user') = 'Y' and lv_main.grantee like upper('&&user_to_skip') then
                        null;
                     else
                  write_op(chr(9)||'User => '||lv_main.grantee
                   ||' (ADM = '||lv_main.admin_option||')');
                 end if;
  else
              if lv_main.grantee='PUBLIC' then
               write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.admin_option||')');
              else
               write_op(chr(9)||'Role => '||lv_main.grantee
                ||' (ADM = '||lv_main.admin_option||')'
                ||' which is granted to =>');
              end if;
                 get_users(lv_main.grantee,lv_tabstop);
  end if;
 end loop;
    exception
        when others then
            dbms_output.put_line('ERROR (get_priv) => '||sqlcode);
            dbms_output.put_line('MSG (get_priv) => '||sqlerrm);
    end get_priv;
begin
 lv_file_or_screen:= upper('&&output_method');
 if lv_file_or_screen='F' then
         open_file('&&file_name','&&output_dir');
 end if;
     write_op('Privilege => '||upper('&&priv_to_find')||' has been granted to =>');
     write_op('====================================================================');
 get_priv(upper('&&priv_to_find'));
 if lv_file_or_screen='F' then
         close_file;
 end if;
exception
    when others then
        dbms_output.put_line('ERROR (main) => '||sqlcode);
        dbms_output.put_line('MSG (main) => '||sqlerrm);

end;
/
prompt For updates please visit http://www.petefinnigan.com/tools.htm
prompt
spool off

whenever sqlerror continue

Find Privileges granted to a specific user

Description : Use this script to find which privileges have been granted to a  particular user. This scripts lists ROLES, SYSTEM privileges  and object privileges granted to a user. If a ROLE is found then it is checked recursively.
        
The output can be directed to either the screen via dbms_output or to a file via utl_file. The method is decided at run time by choosing either 'S' for screen or 'F' for File. If File is       chosen then a filename and output directory are needed. 

whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000

spool find_all_privs.lis

undefine user_to_find
undefine output_method
undefine file_name
undefine output_dir

set feed off
col system_date  noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;
set feed on

prompt find_all_privs: Release 1.0.7.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. 
prompt
accept user_to_find char prompt  'NAME OF USER TO CHECK                 [ORCL]: ' default ORCL
accept output_method char prompt 'OUTPUT METHOD Screen/File                [S]: ' default S
accept file_name char prompt     'FILE NAME FOR OUTPUT              [priv.lst]: ' default priv.lst
accept output_dir char prompt    'OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: ' default /tmp
prompt 
declare
    --
    lv_tabs number:=0;
    lg_fptr utl_file.file_type;
    lv_file_or_screen varchar2(1):='S';
    --
    procedure open_file (pv_file_name in varchar2,
            pv_dir_name in varchar2) is 
    begin
        lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A');
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (open_file) => '||sqlcode);
            dbms_output.put_line('MSG (open_file) => '||sqlerrm);

    end open_file;
    --
    procedure close_file is
    begin
        utl_file.fclose(lg_fptr);
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (close_file) => '||sqlcode);
            dbms_output.put_line('MSG (close_file) => '||sqlerrm);

    end close_file;
    --
    procedure write_op (pv_str in varchar2) is
    begin
        if lv_file_or_screen='S' then
            dbms_output.put_line(pv_str);
        else
            utl_file.put_line(lg_fptr,pv_str);
        end if;
    exception
        when utl_file.invalid_path  then
            dbms_output.put_line('invalid path');
        when utl_file.invalid_mode  then
            dbms_output.put_line('invalid mode');
        when utl_file.invalid_filehandle  then
            dbms_output.put_line('invalid filehandle');
        when utl_file.invalid_operation  then
            dbms_output.put_line('invalid operation');
        when utl_file.read_error  then
            dbms_output.put_line('read error');
        when utl_file.write_error  then
            dbms_output.put_line('write error');
        when utl_file.internal_error  then
            dbms_output.put_line('internal error');
        when others then
            dbms_output.put_line('ERROR (write_op) => '||sqlcode);
            dbms_output.put_line('MSG (write_op) => '||sqlerrm);

    end write_op;
    --
    procedure get_privs (pv_grantee in varchar2,lv_tabstop in out number) is
        --
        lv_tab varchar2(50):='';
        lv_loop number;
        --
        cursor c_main (cp_grantee in varchar2) is
        select  'ROLE' typ,
            grantee grantee,
            granted_role priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_role_privs
        where   grantee=cp_grantee
        union
        select  'SYSTEM' typ,
            grantee grantee,
            privilege priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_sys_privs
        where   grantee=cp_grantee
        union
        select  'TABLE' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            '--' colnm,
            owner owner
        from    dba_tab_privs
        where   grantee=cp_grantee
        union
        select  'COLUMN' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            column_name colnm,
            owner owner
        from    dba_col_privs
        where   grantee=cp_grantee
        order by 1;
    begin
        lv_tabstop:=lv_tabstop+1;
        for lv_loop in 1..lv_tabstop loop
            lv_tab:=lv_tab||chr(9);
        end loop;
        for lv_main in c_main(pv_grantee) loop
            if lv_main.typ='ROLE' then
                write_op(lv_tab||'ROLE => '
                ||lv_main.priv||' which contains =>'); 
                get_privs(lv_main.priv,lv_tabstop);
            elsif lv_main.typ='SYSTEM' then
                write_op(lv_tab||'SYS PRIV => '
                    ||lv_main.priv
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='TABLE' then
                write_op(lv_tab||'TABLE PRIV => '
                    ||lv_main.priv
                    ||' object => '
                    ||lv_main.owner||'.'||lv_main.tabnm
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='COLUMN' then
                write_op(lv_tab||'COL PRIV => '
                    ||lv_main.priv
                    ||' object => '||lv_main.tabnm
                    ||' column_name => '
                    ||lv_main.owner||'.'||lv_main.colnm
                    ||' grantable => '||lv_main.ad);
            end if;
        end loop;
        lv_tabstop:=lv_tabstop-1;
        lv_tab:='';
    exception
        when others then
            dbms_output.put_line('ERROR (get_privs) => '||sqlcode);
            dbms_output.put_line('MSG (get_privs) => '||sqlerrm);
    end get_privs;
begin
 lv_file_or_screen:= upper('&&output_method');
 if lv_file_or_screen='F' then
         open_file('&&file_name','&&output_dir');
 end if;
     write_op('User => '||upper('&&user_to_find')||' has been granted the following privileges');
     write_op('====================================================================');    
 get_privs(upper('&&user_to_find'),lv_tabs);
 if lv_file_or_screen='F' then
         close_file;
 end if;
exception
    when others then
        dbms_output.put_line('ERROR (main) => '||sqlcode);
        dbms_output.put_line('MSG (main) => '||sqlerrm);

end;
/
prompt For updates please visit http://www.petefinnigan.com/tools.htm
prompt
spool off

whenever sqlerror continue

Privileges Granted to a ROLE

set lines 110 pages 1000 ver off
col role for a15
col pv for a75 hea 'PRIVILEGE OR ROLE'

bre on role on type skip 1

define rolecheck = 'OLAP'

select role
, 'ROL' type
, granted_role pv
from role_role_privs
where role like '%&rolecheck%'
union
select role
, 'PRV' type
, privilege pv
from role_sys_privs
where role like '%&rolecheck%'
union
select role
, 'OBJ' type
, regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ'))||
max(decode(privilege,'EXECUTE','EXECUTE')), 'WRITE,READ,EXECUTE', 'ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')), 'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv
from role_tab_privs a
, dba_objects b
where role like '%&rolecheck%'
and a.owner = b.owner
and a.table_name = b.object_name
group by a.owner
, table_name
, object_type
, role
union
select grantee
, 'COL' type
, privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv
from dba_col_privs
where grantee = '&rolecheck'
union
select role, '---' type
, 'this is an empty role ---' pv
from dba_roles
where not role in (select distinct role from role_role_privs)
and not role in (select distinct role from role_sys_privs)
and not role in (select distinct role from role_tab_privs)
and role like '%&rolecheck%'
group by role
order by role
, type
, privilege;

-bash: /bin/rm: Argument list too long

[root@octopus trace]# rm -rf *.trc
-bash: /bin/rm: Argument list too long

$ find . -name "*.trc" -print | xargs rm
$ find . -name "*.trm" -print | xargs rm

Table Locks

To view the session which is locking the table:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine 
from v$locked_object a , v$session b, dba_objects c 
where b.sid = a.session_id 
and a.object_id = c.object_id;


select 
username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER
/


select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Tablespace Creation Script

select 'create tablespace '||tablespace_name||' datafile '''||file_name||''' size  '||sum(bytes)/1024/1024||'M;'
 from dba_data_files where tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS')
 group by tablespace_name,file_name;

Tablespace Usage

Check free/used space per tablespace


SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Sample output:
Tablespace                      Size (MB)    Free (MB)   % Free     % Used
------------------------------   ------------  ------------    ---------- ----------
UNDOTBS1                             65         17.8125      27         73
EXAMPLE                               100        22.625        23         77
USERS                                    5          1.0625       21          79
TEMP                                     20          2              10          90
SYSAUX                                 625        54.5           9           91
SYSTEM                                 700        9.0625        1           99

Tablespace Usage Info (Free Space, Used Space, Percentage)

select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1'; 

How To Get Temporary Tablespace Information

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;

or 

SELECT inst_id "instid", SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment; 


SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';


SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

TNSNAMES.ORA Template

ABCD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = xyz)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = xyz)
    )
  )

How to drop and recreate UNDO Tablespace

1.Determine the size of your undo tablespace

  SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

  SUM(BYTES)/1024/1024/1024
  ---------------------------------
         12.09375
               
2.Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

  SQL>create undo tablespace UNDOTBS2 datafile '<PATH>\<FILENAME.DBF>'  size 400M;

   Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the  newly created tablespace.
   
     Change undo_tablespace=UNDOTBS2
     Change undo_management=MANUAL

   Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step.7.  

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.    

5. Startup the database

6. Confirm the new tablespace is in use:

   SQL> show parameter undo_tablespace

      NAME                 TYPE              VALUE
   -------------        -----------       ------------
   undo_tablespace      string            UNDOTBS2
   
7.Check the status of the undo segments and determine if all the segments in the old undo tablespace  are offline. The segments in the new tablespace may also show offline.
   
  SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

   OWNER         SEGMENT_NAME         TABLESPACE_NAME     STATUS
   ---------         --------------------             -------------------------  ----------
   PUBLIC         _SYSSMU3$                       UNDOTBS1                OFFLINE
   PUBLIC          SYSSMU2$                       UNDOTBS1                 OFFLINE
   PUBLIC          SYSSMU19$                     UNDOTBS2                 OFFLINE

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo     tablespace:


    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.


9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

Importanat UNIX commands

1. How do you delete 3 days old log files?

    Usage: find /location -name "*.log" -mtime +3 -exec rm -rf{} \;

    Example : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;

2. Display latest 20 largest files/directories in current directory?

    Solution: du -ka sort -n tail -20

3. How do you display/remove Specifice Month files in Unix?

    Solution: rm `ls -l grep Jun awk '{print $9}'`

4. How do you find the files which contains a specific Word?
    
    Solution : find /home/joe \( -type f \) -exec grep -l test {} \;

YUM command usuage

#yum search packagename
#yum install packagename

Understanding oracle versioning