How to manage archive logs?

Check status of archivelog mode:
SQL> archive log list;

Open if it is closed:
Link

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
-> REMOVE *.DB_RECOVERY_FILE_DEST from pfile if you desire different path

SQL> select * from V$ARCHIVE_DEST;

Define default log archive destination:
*.log_archive_dest='C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH\'

Define new paths: 
*.log_archive_dest_1='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\'
*.log_archive_dest_2='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_2\'

Define duplex destination if needs
*.log_archive_duplex_dest='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_DUPLEX\'

Differences between regular dest and duplex dest:
LOG_ARCHIVE_DEST calls as primary archive destination,
LOG_ARCHIVE_DUPLEX_DEST calls as secondary archive destination
The important point is that these are using only on local machine

-> On the other hand, LOG_ARCHIVE_DEST_n may use in remote and local as well.

Test the path:
SQL> alter system switch logfile;
Check archive log file in local path like "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH\"

How to use logminer?

Determine the archive log path:
SQL> archive log list;

If "Archive destination" is "DB_RECOVERY_FILE_DEST":
SQL> show parameter DB_RECOVERY_FILE_DEST;

Select the archive log file to use for logminer 
such as "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC"

e.g.
delete from employees where employee_id = 206

Run following codes as SYS user:
BEGIN
   DBMS_LOGMNR.add_logfile (
      logfilename   => 'C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC', options => DBMS_LOGMNR.new);
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
/

Run these codes for examining the logs in example table (as SYS):
SQL>
CREATE TABLE HR.ex934210760 AS SELECT * FROM v$logmnr_contents;
SQL> 
GRANT SELECT ON HR.ex934210760 TO PUBLIC;

To find DML (delete) SQL in example table:
SQL>
select * from HR.ex934210760 where table_name='EMPLOYEES'

GRANT/REVOKE examples

check own privileges:
SQL> SELECT * FROM SESSION_PRIVS;

syntax of system privileges:
GRANT [system_privileges | roles] {ANY} <object_type>
TO [user | role | PUBLIC] {WITH GRANT OPTION}

syntax of object privileges:
GRANT [ALL {object_privileges}] ON object 
TO [user | role | PUBLIC] {WITH GRANT OPTION}  

system privileges options:
ALTER, CREATE, DROP, EXECUTE, SELECT

object privileges options:
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL

object types:
CONTEXT, DATABASE LINK, DICTIONARY, INDEX, MATERIALIZED VIEW, PACKAGES, PROCEDURE, PROFILE, ROLE, SESSION, SEQUENCE, SYNONYM, USERS, VIEW, TYPE, TABLE, TABLESPACE, TRIGGER

some of system roles:
CONNECT, RESOURCE, DBA

examples of system privileges:
e.g.
SQL> grant dba to newhr

e.g.
SQL> grant create type to hrnew;

e.g. using ANY syntax
SQL> grant create any table to hrnew;
-> hrnew user is able to create table for any schema/user.

e.g. revoke ANY grant
SQL> revoke create any table from hrnew;

e.g. grant for refreshing materialized view
SQL> grant alter any materialized view to hrnew; 
-> hrnew is able to refresh all materialized views on whole database schemas

examples of object privileges:
e.g.
SQL> grant select on hr.employees to hrnew;

e.g. 
SQL> grant select, update, delete on hr.employees to hrnew;

e.g. 
SQL> grant all on hr.employees to hrnew;

e.g.
SQL> grant all on hr.employees to public;

e.g.
SQL> revoke select on hr.employees from hrnew;

e.g.
SQL> revoke select, update, delete on hr.employees from hrnew;

e.g.
SQL> revoke all on hr.employees from public;

e.g. for "references" option
SQL> conn hr/hr
Connected.
SQL> grant references on employees to hrnew;
Grant succeeded.
SQL> conn hrnew/hrnew
Connected.
SQL> alter table newemployees add foreign key (employee_id) references employees(employee_id);
Table altered.

How to kill all inactive sessions with custom procedure?

CREATE OR REPLACE PROCEDURE P_KILL_ALL_INACTIVE_SESSIONS
IS
   str_exec   VARCHAR2 (10000);
BEGIN
   FOR cur_session
      IN (SELECT DISTINCT
                    'ALTER SYSTEM KILL SESSION '''
                 || b.sid
                 || ','
                 || b.serial#
                 || ''''
                    AS KILL_THEM_ALL
            FROM sys.dba_ddl_locks a, sys.v_$session B
           WHERE     B.SID = a.session_id
                 AND owner NOT IN ('SYS',
                                   'WMSYS',
                                   'CTXSYS',
                                   'DBSNMP')
                 AND B.TYPE <> 'BACKGROUND'
                 AND B.status = 'INACTIVE')
   LOOP
      BEGIN
         str_exec := cur_session.KILL_THEM_ALL;
         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

Usage:
BEGIN
P_KILL_ALL_INACTIVE_SESSIONS;
END;
/

Ref: https://ozsoyler.blogspot.com/2014/10/how-to-kill-session-with-custom.html

How to use "EXECUTE IMMEDIATE' ?

e.g.
DECLARE
   v_name      VARCHAR (100) := &i1;
   v_surname   VARCHAR (100) := &i2;
BEGIN
   EXECUTE IMMEDIATE 'insert into EMPLOYEES_A  values (:input, :input2)'
      USING v_name, v_surname;
END;

watch examples

$ watch free -m
-> Display free command output in every two seconds 

$ watch -n 5 free -m
-> Display free command output in every five seconds  (with -n parameter)

Dynamic sql script for synonyms

Define new synonyms:
SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';

SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%ALPER2%' and table_owner like '%ALPER%'

Drop synonyms:
SQL>
select 'drop public synonym ' || synonym_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';