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'