Thursday, 8 June 2017

e.g. bind variable usage

›
bind variable; When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if u...
Wednesday, 7 June 2017

A brief look to Oracle backup mode

›
Backup mode;  Oracle runs slightly different that  database files are able to only use for read (not for write) in SGA and all database ch...
Monday, 5 June 2017

How to calculate maximum database file size?

›
Info:  Maximum number of DB blocks are 4194304 blocks ( 2 22 )  per database file Maximum DB block size is 32 KB per DB block SQL>...
Sunday, 4 June 2017

statistics_level parameter

›
Purpose The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses f...
Saturday, 3 June 2017

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