Oracle - SQL Admin Queries

Grant access to Oracle table
grant select,insert,update,delete on data_profiling.YOURTABLE to USERS611;
GRANT SELECT ON EDWA.WRK_MERGED_DATA TO EDWADMIN_ROLE;

Revoke access to Oracle table
REVOKE SELECT ON EDW.WRK_JOB_TO_LINK_WORKSHEET FROM USERS611;
REVOKE SELECT ON EDW.WRK_JOB_TO_LINK_WORKSHEET FROM USERS611;
REVOKE SELECT ON EDW.TAB_GROUP_USERS FROM USERS611;

View users table privileges
SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE = 'USERS611';

View users other privileges
SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME = 'USERS611';
SELECT * FROM USER_SYS_PRIVS WHERE USERNAME = 'USERS611';

Get historic query statistics (1 month retention)
SELECT hist.session_id, HIST.SAMPLE_TIME, HIST.PROGRAM, HIST.MACHINE, HIST.SQL_OPNAME, U.ACCOUNT_STATUS, U.USERNAME, HIST.SQL_ID, hist.MODULE, hist.client_id, hist.action, SQL.SQL_TEXT
FROM
DBA_HIST_ACTIVE_SESS_HISTORY HIST
INNER JOIN DBA_HIST_SQLTEXT SQL ON HIST.SQL_ID = SQL.SQL_ID
INNER JOIN DBA_USERS U ON HIST.USER_ID = U.USER_ID
WHERE HIST.SQL_OPNAME='SELECT' AND UPPER(sql.sql_text) LIKE '%FCT_ENCOUNTER%';

Get Oracle version
SELECT * FROM v$version;

Display size of table
SELECT SEGMENT_NAME TABLE_NAME,
SUM(BYTES)/(1024*1024) TABLE_SIZE_MB
FROM USER_EXTENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME LIKE 'AHPN_%'
GROUP BY SEGMENT_NAME
ORDER BY SUM(BYTES)/(1024*1024) DESC;

Search for a table by name
SELECT * FROM all_tables WHERE table_name LIKE '%HCM_%';

Search for all user tables
SELECT table_name
FROM user_tables
WHERE rownum < 10
ORDER BY table_name;

View all tables and owners
SELECT table_name, owner FROM all_tables ORDER BY owner, table_name;

Find all tables that are using a specific column name
select table_name from all_tab_columns where column_name = 'column_name_here';


Get logged on users expiry date
SELECT * FROM USER_USERS;
SELECT * FROM dba_users;

Alter user password
ALTER USER user_name IDENTIFIED BY new_password REPLACE old_password;

View role privileges
SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME='PROD_ROLE';
SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE = 'PROD_ROLE';
SELECT * FROM USER_SYS_PRIVS WHERE USERNAME = 'PROD_ROLE';

View user sessions and application info
select s.sid, s.serial#, p.spid, s.username, s.schemaname, s.program, s.terminal, s.osuser, s.machine
  from v$session s
  join v$process p
    on s.paddr = p.addr
 where s.type != 'BACKGROUND'
 and s.status = 'ACTIVE'

View ORACLE RAC user sessions and application info
select s.inst_id, s.sid, s.serial#, p.spid, s.username, s.schemaname, s.program, s.terminal, s.osuser, s.machine
  from Gv$session s
  join Gv$process p
    on s.paddr = p.addr
   and s.inst_id = p.inst_id
 where s.type != 'BACKGROUND'
 and s.status = 'ACTIVE'

View query elapsed time
select sesion.sid,
       sesion.username,
       sesion.osuser,
       sesion.machine,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
 from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

View query elapsed time (detailed)
select sesion.sid,
       sesion.username,
       sesion.osuser,
       sesion.machine,
       sqlarea.parsing_schema_name,
       sqlarea.first_load_time,
       sqlarea.last_load_time,
       sqlarea.last_active_time,
       sqlarea.rows_processed,
       sqlarea.optimizer_mode,
       sqlarea.hash_value,
       sqlarea.address,
       sqlarea.cpu_time,
       sqlarea.elapsed_time,
       sqlareasql_text
from v$sqlarea sqlarea
left outer join  v$session sesion
on sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
--   WHERE sesion.username is not null
WHERE sql_text LIKE 'SELECT "Custom SQL Query"."AMLOS"%';

Cancel a user's session
alter system kill session 'sid,serial#';

Terminate a user's session
alter system kill session 'sid,serial#' immediate;

View queries for a specific table
SELECT SYS.V_$SQL.SQL_ID, SYS.V_$SESSION.SQL_ID, SYS.V_$SESSION.STATUS, SYS.V_$SQL.SQL_TEXT, SYS.V_$SQL.EXECUTIONS, SYS.V_$SQL.CHILD_ADDRESS, SYS.V_$SQL.ROWS_PROCESSED, SYS.V_$SQL.BUFFER_GETS, SYS.V_$SQL.MODULE, SYS.V_$SQL.LOADS, SYS.V_$SQL.FIRST_LOAD_TIME, SYS.V_$SQL.LAST_ACTIVE_TIME, SYS.V_$SESSION.SID, SYS.V_$SESSION.SADDR, SYS.V_$SESSION.SQL_CHILD_NUMBER, SYS.V_$SESSION.PREV_SQL_ADDR, SYS.V_$SESSION.LOGON_TIME, SYS.V_$SESSION.USER#, SYS.V_$SESSION.PROCESS, SYS.V_$SESSION.PROGRAM, SYS.V_$SESSION.BLOCKING_SESSION_STATUS, SYS.V_$SESSION.EVENT
FROM SYS.V_$SQL, SYS.V_$SESSION
WHERE SYS.V_$SQL.SQL_ID = SYS.V_$SESSION.SQL_ID(+)
AND SYS.V_$SQL.SQL_TEXT LIKE '%some_table_name%'

View queries by a specific user
SELECT sess.sid,
       sess.username,
       sqla.optimizer_mode,
       sqla.hash_value,
       sqla.address,
       sqla.cpu_time,
       sqla.elapsed_time,
       sqla.sql_text
  FROM v$sqlarea sqla, v$session sess
WHERE sess.sql_hash_value = sqla.hash_value
   AND sess.sql_address = sqla.address
   AND sess.username = 'TABLEAU_READER'