Sunday, 21 May 2017

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) Orac...
Thursday, 18 May 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- ...
Tuesday, 16 May 2017

ORA-2049 examples

›
Error ORA-2049 timeout: distributed transaction waiting for lock SQL> sho parameter distr -- Default: 60 sec e.g. In -A- Database...
Wednesday, 10 May 2017

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);...
Monday, 8 May 2017

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(...

How to use recylebin ?

›
Check recyclebin is active whether or not session scope: SQL> ALTER SESSION SET recyclebin = ON; active instance scope: SQL> A...
Thursday, 4 May 2017

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/or...

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 ...
Wednesday, 3 May 2017

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 c...
Friday, 28 April 2017

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> AL...
Thursday, 27 April 2017

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_file...
Friday, 21 April 2017

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_TIME...
Wednesday, 12 April 2017

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, EXEC...
Tuesday, 11 April 2017

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 ...

How to get value from xml node?

›
e.g. xml file: <A> <B value="Alper XML"> <C value="Alper XML 2"> </C> </B> </A...
Tuesday, 21 March 2017

How to manage logon mechanism with trigger ?

›
CREATE OR REPLACE TRIGGER SYS .DENY_LOGIN     AFTER LOGON     ON DATABASE BEGIN    IF     TO_CHAR (SYSDATE, 'hh24') BETW...

LISTAGG examples

›
SQL> select LISTAGG (last_name, ', ')   WITHIN GROUP (order by last_name) from employees SQL> select LISTAGG (fir...
Thursday, 9 March 2017

How to define "idle_time" ?

›
Check parameter of resource_limit SQL> show parameter resource; If it is 'False' SQL> alter system set resource_limit = t...

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 T...
Friday, 24 February 2017

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 -...