Oracle User Privileges: How to See All Privileges for a User

Querying DBA/USER Privilege Views

A database administrator (DBA) for Oracle can simply execute a query to view the rows in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to retrieve information about user privileges related to the system, tables, and roles, respectively.

To view all system privileges granted to all users would issue the following query:

SELECT  * FROM  DBA_SYS_PRIVS;

The DBA_SYS_PRIVS view contains three columns of data:

GRANTEE is the name, role, or user that was assigned the privilege.
PRIVILEGE is the privilege that is assigned.
ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option.

To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view:

SELECT  * FROM  DBA_TAB_PRIVS;

Important columns are:

GRANTEE is the name of the user with granted access.
TABLE_NAME is the name of the object.
PRIVILEGE is the privilege assigned to the GRANTEE for the associated object.
 
Finally, querying the DBA_ROLE_PRIVS view has much of the same information but applicable to roles instead, where the GRANTED_ROLE column specifies the role in question:

SELECT  * FROM  DBA_ROLE_PRIVS;
 
TO KNOW CURRENT USER’S PRIVILEGES

To view the privileges solely for the current user.

This is done by alternatively querying USER_ versions of the above DBA_ views. Thus, instead of looking at DBA_SYS_PRIVS we’d query USER_SYS_PRIVS, like so:

SELECT  * FROM  USER_SYS_PRIVS;

Since the USER_ privilege views are effectively the same as their DBA_ counterparts, but specific to the current user only, the type of returned data and column names are all identical to those when querying DBA_ views intead.

Which session is generating the redo ?

select username, sid, value, sql_id, prev_sql_id
from ( select username,s.sid, value, sql_id, prev_sql_id 
from v$session u, v$sesstat s, v$statname n
where n.name = 'redo size'
and s.statistic# = n.statistic#
and u.sid = s.sid order by value desc )
where rownum <= 10
order by value desc
/

User privilege for kill session

1) please create in sys ( login as sysdba)
-------------------------------------------------
create or replace procedure sys.kill_session
(
p_sid NUMBER
,p_serial# NUMBER
) AS
m_sql VARCHAR2(1000);
m_loginusername VARCHAR2(30);
m_killusername VARCHAR2(30);
m_schemaname varchar2(30);
BEGIN
SELECT a.USERNAME,
a.SCHEMANAME
INTO m_killusername,
m_schemaname
FROM v$session a
WHERE a.sid = p_sid
AND a.serial# = p_serial#;
select user
into m_loginusername
from dual;
IF m_loginusername = m_killusername AND
m_schemaname NOT IN ('SYS', 'SYSTEM')
THEN
m_sql := 'ALTER SYSTEM KILL SESSION ' || '''' || p_sid || ',' ||
p_serial# || '''' || ' IMMEDIATE';
EXECUTE IMMEDIATE m_sql;
dbms_output.put_line('Session Killed ');
ELSE
dbms_output.put_line(' Login User Name ' || m_loginusername ||
' User Session to Kill ' ||m_killusername);
dbms_output.put_line('Cannot kill session of other users or Admin users');
raise_application_error(-20101,'Cannot kill session of other users or Admin users or self');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Kill Session.' ||SQLERRM(SQLCODE));
END kill_session;
------------------------------------------------------
2) grant execute on kill_seesion to suh;
3) grant select on v_$session to suh;
4) login as suh/suh
5) select a.sid, a.serial#
from v$session a
where username = 'SUH'
/
6) exec kill_session ( sid,serial#)
7) exec kill_session(18,3320);

How to find all child tables of a table in Oracle??

Sometimes it’s useful to find out all the dependent/child tables for a particular table. The SQL statement below will work for an Oracle database:

SELECT
  a.owner,a.table_name, a.constraint_name
FROM
  sys.all_constraints a,
  (SELECT owner,constraint_name from sys.all_constraints
    WHERE
     owner = 'OWNER' and
     table_name = 'TABLE_NAME' and
     constraint_type in ('P','U')
  ) b
WHERE
  a.constraint_type = 'R' and
  a.r_constraint_name = b.constraint_name and
  a.r_owner = b.owner

Where  OWNER is the owner/schema to which the table belongs andTABLE_NAME is the table to be reported on.

How to get PASSWORD changed time for an Oracle user??

SELECT name, ctime, ptime
FROM sys.user$
WHERE name = 'TEST_SUH';

How to find total number of Oracle schema objects and its size??

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

Long Running Jobs/Queries

Long Running JOBS
Select sid,serial#,username,opname,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, totalwork,sofar,time_remaining remaining, message
from v$session_longops
where time_remaining = 0 order by time_remaining desc
Long Query Progress
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
ORDER BY elapsed_seconds
Long running Queries, Total, So far and Remaining time 
SELECT SID, username, SERIAL#, opname, start_time,elapsed_seconds, TOTALWORK,SOFAR,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE, time_remaining
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1

List of tables available in Database

Execute below query as sys or system, it will give all available tables in database owner wise
SELECT owner, table_name FROM dba_tables
If you don't have sys or system privileges use below queries to find available tables in schema
SELECT owner, table_name FROM all_tables
SELECT table_name FROM user_tables

How To Find Out What Privileges a User Currently Has?

Privileges granted to users are listed in two system views: DBA_SYS_PRIVS and USER_SYS_PRIVS.
We can find out what privileges a user currently has by running below query - as normal user

We can find out what privileges a user currently has by running below query - as SYS user

Database present status

SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours", status
from  sys.v_$session where  sid=1

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'),'HH24:MI:SS') UPTIME FROM v$session WHERE SID = 1;

Monthly database growth

select to_char(creation_time, 'YYYY Month') "Month",
sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v$datafile
where creation_time > SYSDATE-365*3 /* 3 years growth details */   
group by to_char(creation_time, 'YYYY Month') ORDER BY to_char(creation_time, 'YYYY Month')                      

TEMP tablespace usage

Temporary Segments in Database
SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

Database Session that is using space in a sort segment

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,S.program,SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE    T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.program, 
TBS.block_size, T.tablespace ORDER BY sid_serial;
Sort Space Usage by Statement
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used,  T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;

Who is using UNDO tablespace??

select s.sid, 
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username order by undo_size_mb desc

To find UNDO tablespace active transactions

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
       NVL(s.username, 'None') orauser,
       s.program,
       r.name undoseg,
       t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
  FROM sys.v_$rollname    r,
       sys.v_$session     s,
       sys.v_$transaction t,
       sys.v_$parameter   x
 WHERE s.taddr = t.addr
   AND r.usn   = t.xidusn(+)
   AND x.name  = 'db_block_size'

Extract tablespace DDL

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tbs_list.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || '/'
from dba_tablespaces tb;
spool off

KILL Session

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'username'

ALTER SYSTEM KILL SESSION '14,30';

Find and KILL Orphand Jobs - DATAPUMP

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2

drop table SYSTEM.SYS_EXPORT_SCHEMA_01;
drop table SYSTEM.SYS_EXPORT_TABLE_01;

Schema Size in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

Schema Data Space and Index Space in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

TEMP Usage

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid