-- Create a user with lots of persmissions

undef user_name

DROP USER &&user_name CASCADE;

CREATE USER &&user_name 
PROFILE "DEFAULT" 
IDENTIFIED BY &user_password 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP";

-- Grants the CREATE SESSION privilege (can't connect without it)
GRANT CONNECT TO &&user_name;

-- Create, modify, and delete certain types of schema objects.
GRANT RESOURCE TO &&user_name;

/* RESOURCE is useful for developers as it grants the following privileges:

  CREATE CLUSTER
  CREATE INDEXTYPE
  CREATE OPERATOR
  CREATE PROCEDURE
  CREATE SEQUENCE
  CREATE TABLE
  CREATE TRIGGER
  CREATE TYPE
  
  UNLIMITED TABLESPACE

Notes
-----

- UNLIMITED TABLESPACE does not appear in the RESOURCE role.
  It is allocated directly on the user.
  
- Without a quota on the tablespace (and without UNLIMITED TABLESPACE)
  this error ORA-01950: no privileges on tablespace 'USERS'
           
- If UNLIMITED TABLESPACE is granted, the user can write to ANY tablespace,
  including SYSTEM.

*/

-- Autotrace
GRANT SELECT_CATALOG_ROLE TO &&user_name;
GRANT EXECUTE_CATALOG_ROLE TO &&user_name;

-- SQL tuning advisor
GRANT ADVISOR TO &&user_name;

-- PL/SQL debugging
GRANT DEBUG ANY PROCEDURE TO &&user_name;
GRANT DEBUG CONNECT SESSION TO &&user_name;

-- Some other schema objects
GRANT CREATE VIEW TO &&user_name;
--GRANT CREATE MATERIALIZED VIEW TO &&user_name;
--GRANT CREATE ANY DIRECTORY TO &&user_name;
--GRANT DROP ANY DIRECTORY TO &&user_name;

-- Enable a user to perform most administrative tasks.
--GRANT DBA TO &&user_name;

-- Terminate a session


-- Run this sql
SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'MY_USER';

/* to get output like this

SID    SERIAL#    STATUS
-----  ---------  --------
7      15         ACTIVE 
12     63         INACTIVE

*/

-- Use this to kill a session
ALTER SYSTEM KILL SESSION '7,15';

-- Set up Fine Grained Auditing (FGA)


-- Create a copy of SCOTT.EMP play with
CREATE TABLE emp AS SELECT * FROM scott.emp;

-- Set the client id so we can locate our data.
exec dbms_session.set_identifier('FGA_TEST');

-- Register the table for FGA (requires EXECUTE_CATALOG_ROLE permission).
BEGIN
   dbms_fga.add_policy (
      object_schema   => sys_context('USERENV', 'CURRENT_SCHEMA'),
      object_name     => 'EMP',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE',
      audit_condition => 'DEPTNO=30',
      policy_name     => 'MY_FGA_POLICY'
  );
END;
/

-- Run some SQL using bind variables.
DECLARE
  l_ename   varchar2(50);
  l_emp_no  number := 7654;
  l_comm    number := 600;

BEGIN
  SELECT ename
  INTO l_ename
  FROM emp 
  WHERE empno = l_emp_no;
  
  UPDATE emp
  SET comm = l_comm
  WHERE empno = l_emp_no;
  
  DELETE FROM emp
  WHERE empno = l_emp_no;
  COMMIT;
END;
/

-- Look at the results
SELECT client_id, object_name as name, statement_type as type, sql_text, sql_bind  
FROM dba_fga_audit_trail
ORDER by timestamp;

/*
CLIENT_ID  OBJECT  TYPE     SQL_TEXT                                     SQL_BIND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------- ------- -------- -------------------------------------------- --------------------
FGA_TEST   EMP     SELECT   SELECT ENAME FROM EMP WHERE EMPNO = :B1      #1(4):7654
FGA_TEST   EMP     UPDATE   UPDATE EMP SET COMM = :B2 WHERE EMPNO = :B1  #1(3):600 #2(4):7654
FGA_TEST   EMP     DELETE   DELETE FROM EMP WHERE EMPNO = :B1            #1(4):7654
*/


-- To remove the policy
begin
   dbms_fga.drop_policy (
      object_schema => sys_context('USERENV', 'CURRENT_SCHEMA'),
      object_name   => 'EMP',
      policy_nam e  => 'MY_FGA_POLICY'
  );
end;
/

-- To tidy audit log, delete rows from sys.fga_log$ (log in as DBA user)
DELETE FROM sys.fga_log$ WHERE policyname='MY_FGA_POLICY';
COMMIT;

-- Buffer cache usage by object

SELECT
   o.object_name    object_name,
   o.object_type    object_type,
   count(1)         num_blocks
FROM
   dba_objects  o,
   v$bh         bh
WHERE o.object_id  = bh.objd
AND o.owner NOT IN ('SYS','SYSTEM')
GROUP BY o.object_name, o.object_type
ORDER BY count(1) DESC;

-- Session count by username and machine (with text graph)

SELECT 
  username,
  machine,
  count(*) sessions,
  rpad('*', count(*), '*') session_graph 
FROM v$session
GROUP BY username, machine
ORDER BY username, machine NULLS LAST;

-- The number of extents and the size in megabytes of the segments in a tablespace 

SELECT
  segment_name,
  owner,    
  segment_type,
  extents,
  bytes / 1024 mbytes
FROM sys.dba_segments
WHERE tablespace_name = :tablespace
ORDER BY mbytes desc

-- The number of megabytes remaining in each tablespace before the datafiles reach their maximum.

SELECT tablespace_name,
  TRUNC(SUM(maxbytes - bytes) /(1024 *1024)) mb_remaining
FROM dba_data_files
GROUP BY tablespace_name;