Tuesday, 25 July 2017

SQL PLUS system parameters usage (set/show)

›
To show parameter in detail SQL> SHOW DEFINE; To escape from substitution variable (&): SQL> SET DEFINE OFF; To enable DBMS_...

e.g. changing the DBID and Database Name

›
e.g. Backup database RMAN> backup database Mount database SQL> shu immediate; SQL> startup mount; Change DBID $ nid TARGET...
Monday, 17 July 2017

SYS_CONTEXT function parameters and examples

›
Active parameters ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIE...
Friday, 14 July 2017

Quiesce restricted mode vs Restricted mode

›
Differences; * Quiesce restricted option is less raugh than restricted option and they are quite similar. * In quiesce restricted option, ...
Tuesday, 11 July 2017

log_buffer, log_checkpoint_timeout and log_checkpoint_interval parameters

›
Purpose We are able to tune CKPT process with these paramaters and they should be configured together. Usage e.g. Check current parame...
Wednesday, 28 June 2017

plsql_optimize_level and plsql_code_type parameters

›
Purpose We are able to use  plsql_optimize_level parameter for getting better execution performance on plsql codes. On the other way,  pls...
Tuesday, 20 June 2017

How to find database object via SQL statements?

›
SQL> select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
Saturday, 10 June 2017

How to solve SQL Developer redrawing problem on Windows?

›
Problem Solution Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK
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...