How to locate currently used listener.ora file?

e.g.
Find oracle client which is using by server:
$ which sqlplus
-- /ora11g/orahome/bin/sqlplus

Check listener.ora:
$ cat /ora11g/orahome/network/admin/listener.ora
-- e.g. port -> 1521

Ref: https://ozsoyler.blogspot.com/2016/11/how-to-configure-listener-port-when-it.html

How to generate sql explain plan?

e.g.
prepare example table:
SQL>
create table ep_employees as select * from employees

observe costs values:
SQL>
explain plan for select * from ep_employees where job_id = 'SA_MAN';
-- Explained.

SQL> 
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-------------------------
Plan hash value: 745119821

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     5 |   675 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EP_EMPLOYEES |     5 |   675 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB_ID"='SA_MAN')

Note
-----

   - dynamic sampling used for this statement (level=2)

Fix full access issue on ep_employees table:

SQL>
create index ep_emp_job_ix on ep_employees (job_id);

SQL> 
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------
Plan hash value: 3994861717

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     5 |   675 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EP_EMPLOYEES  |     5 |   675 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EP_EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JOB_ID"='SA_MAN')

Note
-----

   - dynamic sampling used for this statement (level=2)

Results:
Costs are reduced so sql query performance got increased.

How to find table by a column name in Oracle?

Check table info
SQL> desc employees
or
SQL>
select * from all_tab_columns where lower(table_name) = 'employees'

Find column name 
e.g. 
Current user scope:
SQL>
select * from all_tab_columns where lower(column_name) = 'employee_id'

DBA user scope:
SQL>
select * from dba_tab_columns where lower(column_name) = 'employee_id'

ORA-06553: PLS-907 Solution

Error
ORA-06553: PLS-907: cannot load library unit aaa (object) 
(referenced by bbb)

Solution
Compile related object (bbb)
SQL> ALTER FUNCTION HR.BBB COMPILE;

ORA-00059 solution

Error
ORA-00059: maximum number of DB_FILES exceeded

Solution
Check limit that total number of db files
SQL> show parameter db_files;
-- e.g. 250

Edit parameter file:
# add this on bottom
*.db_files=500

Shutdown and startup database:
SQL> shu immediate;
SQL> startup;

Extra:
If database is using spfile:
SQL> show parameter spfile;
-- output should be not empty

Delete spfile under $ORACLE_HOME/dbs directory such as 
"spfileALPERDB.ora"

At last, shutdown and startup database again.

How to restore database object such as package body via flashback?

Note that below SQL gets output if it is still in UNDO tablespace.
SQL> 
SELECT text  FROM all_source
       AS OF TIMESTAMP TO_TIMESTAMP ('21-04-2017 14:30:00', 'DD-MM-YYYY HH24:MI:SS')
 WHERE name = 'PRG_BODY_ALPER' 
AND TYPE = 'PACKAGE BODY';

for DBA: dba_source table

Related: https://ozsoyler.blogspot.com/2016/12/how-to-rollback-committed-data-from.html

How to see TOP 10 SQL that consuming system resources highly?

e.g.
select * from
(
select * from v$sql
order by DISK_READS desc
)
where rownum < 11

options for order by:
RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME

for old querries:
select * from dba_hist_sqltext where sql_id = '8j2hv3c6wskdy'

for SQL plan:
select * from V$SQL_PLAN where sql_id = '8j2hv3c6wskdy'

find the object location of SQL
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'

How to find and investigate a process in top command output?

Total connection by machine:
SQL> select machine, count(*) from v$session group by machine order by 2;

Total number of database user who connected:
SQL> select count(1) "NO. Of DB Users Connected", to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time from v$session where username is NOT NULL;

Define a process(pid) from top:
17831 ora11g .. ora_m000_ALPERDB  

Get detailed info from database:
SQL> select * from v$process where spid = 17831;
-> M000 means MMON Slave Process
-> e.g. pid -> 16, sid -> 555

SQL>
SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE p.pid = 16
AND s.paddr = p.addr;

SQL> select * from gv$session where sid = 555;

Get sql from below code related to example process if it has SQL:
SQL>
SELECT 
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s    
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  --AND s.inst_id= 1
  AND s.sid = 555 
  AND sq.inst_id= s.inst_id

How to get value from xml node?

e.g. xml file:
<A>
<B value="Alper XML">
<C value="Alper XML 2">
</C>
</B>
</A>

catch the value:
@echo off
for /F tokens^=1^,2^delims^=^"^= %%a in (example.xml) do (
if "%%a" equ "<B value" (
rem %%a first column, %%b second column
echo example value: %%b
)
)
)
pause

output:
example value: Alper XML

How to manage logon mechanism with trigger ?

CREATE OR REPLACE TRIGGER SYS.DENY_LOGIN

   AFTER LOGON

   ON DATABASE

BEGIN

   IF     TO_CHAR (SYSDATE, 'hh24') BETWEEN 08 AND 22

      AND SYS_CONTEXT ('USERENV', 'SESSION_USER') = 'HR'

   THEN

      raise_application_error (

         -20001,

         'You cannot login between 08 AM and 06 PM. Please contact with Alper!');

   END IF;

END deny_login;

/

Error message:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You cannot login between 08 AM and 06 PM. Please contact with Alper!

ORA-06512: at line 5

LISTAGG examples

SQL>

select LISTAGG (last_name, ', ')  WITHIN GROUP (order by last_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || last_name, ', ') WITHIN GROUP (order by first_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary, ', ') WITHIN GROUP (order by salary desc)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary || ' ' || NVL2(commission_pct, commission_pct, '0'), ', ') WITHIN GROUP (order by salary desc)

from employees

How to define "idle_time" ?

Check parameter of resource_limit
SQL> show parameter resource;

If it is 'False'
SQL> alter system set resource_limit = true;

Add it into init.ora file permanently
*.resource_limit=true;

Modify default profile
SQL> alter profile default limit idle_time 1440;
-- 1440 -> 24 hour

Check default profile setting
SQL> select profile, resource_name, limit from dba_profiles 
where lower(profile) = 'default' and resource_name = 'IDLE_TIME';

Monitor idle sessions
SQL> select * from v$session where status = 'SNIPED' order by logon_time desc;

How to kill tty/pts terminal?

Check first your connection to prevent killing yourself
$ tty

Kill other connection
$ skill -9 pts/1
or
# skill -KILL pts/1

Info
Teletypewriter - TTY
pseudo terminal slave - PTS

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%';