Getting all users and roles that can access objects in a schema

To list all users and roles who can access objects in a schema, we can use following query. This will be useful when there is a need of migrating a schema from one database to another.

with
users_roles as(
        (select grantee, granted_role from dba_role_privs) union
        (select username, username from dba_users) union
        (select role, role from dba_roles)
),
direct_grantees as (
        select distinct grantee 
        from dba_tab_privs 
        where owner in ('SCHEMA_USER')
),
all_grantees as (
        select distinct grantee
        from users_roles
        start with 
                granted_role in (select * from direct_grantees)
        connect by nocycle prior grantee = granted_role
)
select grantee 
from all_grantees 
-- Filter as needed
-- For example, get only users not roles
where grantee in (select username from dba_users);

Automated spool file name generation

There was requirement recently, where spool file of SQLPlus has to be generated with an automatic name that consists of database name with timestamp (for example, db001_2014-12-26_04-54-13.log). Here is the script that generates spool file name dynamically.

set termout off
set feedback off
undefine spoolfile
column spoolfile new_value spoolfile noprint
select sys_context('userenv', 'db_name') || '_' || to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as spoolfile from dual;
set termout on
set feedback on

set echo on
spool &spoolfile

select sysdate from dual;

spool off