db_ultra_safe parameter

Purpose
The purpose of this parameter is to see instant file corruption on disk then DBA takes action against the issue.

Usage
Current setting:
SQL> show parameter db_ultra_safe;
-- default is "OFF"

Change setting (options -> OFF | DATA_ONLY | DATA_AND_INDEX):
add this into init.ora file;
*.db_ultra_safe='DATA_ONLY'
-- DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT parameter are set by automatically according to db_ultra_safe parameter setting.

Then restart the instance to perform.

Result
SQL> show parameter db_ultra_safe;
-- output; data_only

Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams064.htm#REFRN10295

Oracle Enterprise Manager Version History

• Oracle Enterprise Manager Cloud Control 13c Release 2 (13.2.0.0)
• Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0)
• Oracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5)

Download link: http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html

Last Updated: 21/05/2017

e.g. row lock (tx) and table lock (tm)

e.g.
row lock (tx)
In -A- database:
SQL>
select * from employees where employee_id = 100 FOR UPDATE;

to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 10000
where employee_id = 100;

error -> ORA-02049: timeout: distributed transaction waiting for lock

e.g.
table lock (tm)
In -A- database:
SQL>
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;

to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 2500
where employee_id = 105;

--or
In -B- database:
LOCK TABLE employees@alper_database IN SHARE MODE;

to see the error;
In -A- database:
SQL>
update employees
set salary = 2500
where employee_id = 105;

same error for both methods -> ORA-02049: timeout: distributed transaction waiting for lock

ref: https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

ORA-2049 examples

Error
ORA-2049 timeout: distributed transaction waiting for lock

SQL> sho parameter distr
-- Default: 60 sec

e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row

IN -B- Database
select * from employees@a_dblink where employee_id = 100

update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049 

To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5

Then restart the instance to perform.

To wait just one second in -B- Database:
select* from  employees@a_dblink
where employee_id = 100 
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked

Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;

commit;

Not to wait:
select* from  employees@a_dblink
where employee_id = 100 
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked

Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;

commit;

e.g. OLD and NEW Pseudorecords

e.g.
SQL> 
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);

SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
   BEFORE UPDATE
   ON HR.EMPLOYEES
   REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
   FOR EACH ROW
DECLARE
BEGIN
   IF :OLD_ROW.MANAGER_ID IS NOT NULL
   THEN
      INSERT INTO employees_salary_log
           VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
      NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager

How to know $ORACLE_HOME path via sqlplus in Windows ?

CMD> sqlplus  / as sysdba

SQL> 
set serveroutput on;

SQL>
DECLARE
 Result_Val VARCHAR2(100);
BEGIN
  dbms_system.get_env('ORACLE_HOME', Result_Val);
  dbms_output.put_line(Result_Val);
END;
/

How to use recylebin ?

Check recyclebin is active whether or not
session scope:
SQL>
ALTER SESSION SET recyclebin = ON;

active instance scope:
SQL>
ALTER SYSTEM SET recyclebin = ON;

permanent setting in init.ora file:
*.recyclebin=on

usage of recylebin
SQL>
create table ex_emp as select * from employees;

SQL>
drop table ex_emp;

If you use purge parameter, you cannot take back the table from recyclebin!
-- SQL> drop table ex_emp purge;

check table versions if hr logged in 
select * from USER_RECYCLEBIN order by droptime desc;
-- or
select * from RECYCLEBIN order by droptime desc;;

check table versions if sys logged in
select * from DBA_RECYCLEBIN order by droptime desc;;

Note object_name from result then:
SQL> 
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";

To restore;
SQL>
create table new_ex_emp as 
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
-- or
SQL>
FLASHBACK TABLE ex_emp 
TO BEFORE DROP 
RENAME TO new_ex_emp;

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