Overview of role privileges

The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain:

Select  Role || ' contains role:' "role privilege type", Granted_Role "privilege"   From Role_Role_Privs Where Role='ONLINE_USERS'
Union
Select Role || ' contains system priv:', Privilege      From Role_Sys_Privs  Where Role='ONLINE_USERS'
Union
Select Role || ' contains object priv:',  Privilege || ' ON ' || owner || '.' || table_name From Role_Tab_Privs Where Role='ONLINE_USERS'
order by 1;

Output:

role privilege type	privilege
ONLINE_USERS contains object priv:	UPDATE ON SCOTT.EMP
ONLINE_USERS contains role:	RESOURCE
ONLINE_USERS contains system priv:	UPDATE ANY TABLE

How does Oracle decide which roles should be enabled in a session?

Oracle uses the concept of default roles to decide whether or not a role should be enabled when a user establishes a session.

CREATE USER ERIC
IDENTIFIED BY pass1w0rd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

ALTER USER ERIC QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO ERIC; 

Oracle states in its documentation

"When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles."

So we should have two default roles enabled for our user ERIC, confirmed below:

SQL> connect eric/pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE

Let's create a new role:

CREATE ROLE online_users NOT IDENTIFIED;

Role created.

Because the user ERIC was created with ALL roles enabled by default, all subsequent sessions established by ERIC will now have access to the role online_users:

GRANT online_users to ERIC; 

Grant succeeded.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
RESOURCE
ONLINE_USERS

If you want to prevent this behavior, you can alter the user with a limited default setting:

ALTER USER ERIC DEFAULT ROLE CONNECT;

User altered.

connect eric/Pass1w0rd

Connected.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT

User ERIC must now explicitly enable the required roles from within his session:

set role connect, online_users;

Role set.

SELECT * FROM SESSION_ROLES;

ROLE
-------
CONNECT
ONLINE_USERS

These features can be incorporated into a security-oriented application setup, where specific people should only be granted specific privileges through database roles.

A workaround for ORA-02287: sequence number not allowed here when using GROUP BY in a subquery

When you use GROUP BY in the subquery of an INSERT statement, Oracle will throw

ORA-02287: sequence number not allowed here 

if you try to reference a sequence's NEXTVAL pseudocolumn directly in the insert statement, like this:

INSERT INTO mytable
        (
        entry_id,   
        order_date, 
        sum(quantity)
        )
        MYSEQ.NEXTVAL, <-- INCORRECT
        SELECT  
            TRUNC(SYSDATE),
            SUM(quantity)
        FROM    orders
        WHERE   
        GROUP BY order_date
        ORDER BY 2 ASC, 3 DESC;

Solution is to create a trigger that will fire on INSERT statements, before a new row is inserted. Note that I am overriding the default correlation name NEW with NewID, by using the REFERENCING clause of the "CREATE TRIGGER" command:

CREATE OR REPLACE TRIGGER MYTRG
BEFORE INSERT ON mytable REFERENCING NEW as NewID
    FOR EACH ROW
        BEGIN
            SELECT myseq.NEXTVAL
            INTO :NewID.ENTRY_ID
            FROM DUAL;
        END;

Change your insert statement slightly:

INSERT INTO mytable
        (
        order_date, 
        sum(quantity)
        )
        SELECT  
            TRUNC(SYSDATE),
            SUM(quantity)
        FROM    orders
        WHERE   
        GROUP BY order_date
        ORDER BY 2 ASC, 3 DESC;

How to extract the base tables used by a view

To extract the tables being used in a view, you can query the USER_DEPENDENCIES view, like this:

SELECT NAME, TYPE, REFERENCED_NAME,REFERENCED_TYPE
FROM USER_DEPENDENCIES
Where Type = 'VIEW' 
And Name In ( 'V_FEMALE_ARTIST','VW_ALBUM');

Output:

NAME                           TYPE                 REFERENCED_NAME                REFERENCED_TYPE
----------------------------------------------------------------------------------------------------   
VW_ALBUM                       VIEW                 ALBUM                          TABLE              
VW_ALBUM                       VIEW                 SONGS                          TABLE              
V_FEMALE_ARTIST                VIEW                 ARTIST                         TABLE       

The column REFERENCED_NAME holds the information you are looking for.

I can see that my view VW_ALBUM is based on the tables ALBUM and SONG.
The last view uses no JOIN in its definition, and is based on one single table only.

Note what happens if you create a view based on the data dictionary, for example:

create or replace view mytables (tab_name, created, tabsize) AS
SELECT t.table_name,to_char(o.created, 'dd.mm.yyyy'),sum(s.bytes)
from user_tables t join user_objects o
on t.table_name = o.object_name join user_segments s on s.segment_name = t.table_name
where o.object_type = 'TABLE'
group by t.table_name, o.created
order by 2, 3 desc;

View created.

When you query the USER_DEPENDENCIES again, and consider the newly created view, note that the source of the view are not tables, but synonyms (and since I am using the view USER_SEGMENTS, I am consequently also depending on the DBMS_SPACE_ADMIN package):

SELECT NAME, TYPE, REFERENCED_NAME,REFERENCED_TYPE
FROM USER_DEPENDENCIES
Where Type = 'VIEW' 
And Name In ( 'V_FEMALE_ARTIST','VW_ALBUM','MYTABLES')
ORDER BY NAME;

NAME                           TYPE                 REFERENCED_NAME                REFERENCED_TYPE
----------------------------------------------------------------------------------------------------   
MYTABLES                       VIEW                 USER_OBJECTS                   SYNONYM            
MYTABLES                       VIEW                 USER_TABLES                    SYNONYM            
MYTABLES                       VIEW                 DBMS_SPACE_ADMIN               PACKAGE            
MYTABLES                       VIEW                 USER_SEGMENTS                  SYNONYM            
VW_ALBUM                       VIEW                 ALBUM                          TABLE              
VW_ALBUM                       VIEW                 SONGS                          TABLE              
V_FEMALE_ARTIST                VIEW                 ARTIST                         TABLE              

How to list all users and their number of owned objects

Join dba_users with dba_objects using a correlated subquery, and you'll get all the schemas, both those which own objects and those who don't:

set lines 200
col username format a20
col created format a30
col "Num_obj" format 999999
SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username)"Num_obj"
FROM dba_users u
-- to leave out all oracle maintained schemas, comment out the following
-- WHERE  u.oracle_maintained = 'N'
ORDER BY 3 asc;

Example output:

USERNAME             CREATED                        Num_obj
-------------------- ------------------------------ -------
ANONYMOUS            14.06.2016                           0
ADDM_USER            14.06.2016                           0
GSMUSER              14.06.2016                           0
SYSDG                14.06.2016                           0
DIP                  14.06.2016                           0
SYSBACKUP            14.06.2016                           0
SCOTT                14.06.2016                           0
USER1                20.06.2016                           0
USER2                20.06.2016                           0
SYSKM                14.06.2016                           0
XS$NULL              14.06.2016                           0
GSMCATUSER           14.06.2016                           0
APPQOSSYS            14.06.2016                           5
ORACLE_OCM           14.06.2016                           6
OUTLN                14.06.2016                          10
AUDSYS               14.06.2016                          12
OJVMSYS              14.06.2016                          16
USER3                20.06.2016                          34
DBSNMP               14.06.2016                          55
USER4                20.06.2016                          95
WMSYS                14.06.2016                         389
CTXSYS               14.06.2016                         409
SYSTEM               14.06.2016                         641
XDB                  14.06.2016                         961
SYS                  14.06.2016                       42173

If you have a large number of accounts that own no objects at all, you may want to exclude them. Do so by checking for the existence of the particular account in dba_objects.
If there are any rows at all in there, the account owns at least 1 object. Make sure to select a literal, not a value from the database. This is good practice; performance is not affected:

SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username )"Num_obj"
FROM dba_users u
WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)
ORDER BY 3 asc;

The RANK function for DBAs

The analytic function RANK is perhaps not the most commonly used by DBAs.
For what it's worth, here is a short demonstration:

1. Find the number of rows for all the tables in a schema:

select table_name, num_rows 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0 
ORDER BY NUM_ROWS;

The above simple query results in:

TABLE_NAME                      NUM_ROWS
------------------------------ ---------
REDO_DB                                1
REPCAT$_TEMPLATE_TYPES                 2
REPCAT$_AUDIT_ATTRIBUTE                2
REPCAT$_TEMPLATE_STATUS                3
AQ$_INTERNET_AGENT_PRIVS               4
AQ$_INTERNET_AGENTS                    6
AQ$_QUEUE_TABLES                      15
REPCAT$_RESOLUTION_METHOD             19
AQ$_QUEUES                            28
REPCAT$_OBJECT_TYPES                  28
MVIEW$_ADV_PARAMETERS                 40
LOGSTDBY$SKIP_SUPPORT                351
HELP                                 938

13 rows selected.

To check where in the sort order a table with, say, 10 rows, would be, use the RANK function:

select RANK(10) WITHIN GROUP(order by num_rows)"Rank" 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0;

      Rank
----------
         7

A table with 10 rows would be on 7th place, in the sort order shown above (ascending by default), squeezed in between the tables AQ$_INTERNET_AGENTS and AQ$_QUEUE_TABLES.

How to compare the number of objects per schema in two databases

After a migration of a database to a new server, or after any kind of replication, you would like to get some verification that your objects were created.

A potentially very useful query could help you with this task.

You need a database link from the target database back to the source database

create public database link proddb01_old 
connect to system
identified by mysecretpassword
using 'myproddb01';

Verify that the link works as intended:

SQL> select d.name,i.host_name from v$database d, v$instance i;

NAME      HOST_NAME
--------- -------------------------------
PRODDB01 myserver01.mydomain.com

Save the following in a script, and execute it from the target database via sqlplus:

set lines 200
col count_new format 99999
col count_old format 99999
col "object owner" format a30
col "Result" format a20
set pages 100

prompt ============================================
prompt compare users and their object count
prompt ============================================

WITH newprod_users AS(
 SELECT target.owner AS new_owner, count(*) AS count_new
 FROM   dba_objects target
 GROUP BY owner
 ORDER BY 1
),
oldprod_users AS (
 SELECT source.owner AS old_owner, count(*) AS count_old
 FROM   dba_objects@proddb01_old  source
 GROUP BY owner
 ORDER BY 1)
SELECT newprod_users.new_owner "object owner", 
       newprod_users.count_new "NEW PROD",  
       oldprod_users.count_old "OLD PROD",
       DECODE(newprod_users.count_new,
              oldprod_users.count_old,'Num rows identical','Num row differs') "Result"
FROM   newprod_users JOIN oldprod_users
ON     newprod_users.new_owner = oldprod_users.old_owner
ORDER BY "Result" DESC;

Example output:

============================================
compare number of users
============================================

object owner                     NEW PROD  OLD_PROD Result
------------------------------ ---------- ---------- --------------------

USER1                               1329       1329 Num rows identical
USER2                                  2          2 Num rows identical
USER3                               1750       1750 Num rows identical
USER4                                409        389 Num row differs
USER5                                961       1167 Num row differs
USER6                                251        256 Num row differs

The report can be easily modified to produce for example a semi-colon separated list, which can in turn be pulled into an MS Excel file if desirable.