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, plsql_code_type parameter is using to change compilation mode.

Usage
Check current parameter:
SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

Apply the setting:
init.ora file:
*.plsql_optimize_level=3
*.plsql_code_type='NATIVE'

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql;

SQL> shutdown;

SQL> startup;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Check lastly:
SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      NATIVE
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     3
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

To see current status of objects;
SQL> 
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;

Deep notes
INTERPRETED -> PL/SQL library units compile as bytecode format
NATIVE -> PL/SQL library units compile as native (machine) code format

Ref: https://becomeadba.com/?pages-list

How to find database object via SQL statements?

SQL>
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'

How to solve SQL Developer redrawing problem on Windows?

Problem

Solution

Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK

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 user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well. 

e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;

DECLARE
   v_value   VARCHAR2 (30);
BEGIN
   :v_bind_value := 100;

   SELECT last_name
     INTO v_value
     FROM employees
    WHERE employee_id = :v_bind_value;

   DBMS_OUTPUT.put_line (v_value);
END;
/

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 changes (DML, DDL, etc) are written into redo log files by Oracle. In that time, user can copy database files to desired folders because database file are frozen by system.
For example, when DML execute, all data block changes are written to redo log files as a exact block not as a delta log. Therefore, 
Oracle runs slowly according to normal mode and staying backup mode for a long time is also important risk for recovery operations. 
e.g. -> if you run "shutdown abort",  database gets crash in startup (ORA-10873, ORA-01110). To solve it, run "recover database".

Enable backup mode:
SQL> alter database begin backup;

If you get ORA-01123 error;
Enable archivelog mode;
https://ozsoyler.blogspot.com.tr/2014/10/how-to-checkopen-archieve-log-in-sqlplus.html

To check current db file status:
SQL> select * from v$backup;
-- status column output should be "ACTIVE"

Now we are able to copy/paste operation on disk:
find datafile paths;
SQL> select * from v$datafile;

Finish file transfer operations then disable backup mode:
SQL> alter database end backup;

How to calculate maximum database file size?

Info: 

• Maximum number of DB blocks are 4194304 blocks (2²²) per database file
• Maximum DB block size is 32 KB per DB block

SQL> show parameter db_block_size;
-- Generally it is 8192 byte (8 KB)

e.g. if block size is 8 KB;
maximum database file size = 4194304 x 8 KB = 33554432 KB = 32768 MB = 32 GB

e.g. if block size is 32 KB (this can be maximum 32 KB)
maximum database file size = 4194304 x 32 KB = 134217728 KB = 131072 MB = 128 GB

Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915
https://docs.oracle.com/cd/B28359_01/server.111/b32009/appg_db_lmts.htm#UNXAR408

statistics_level parameter

Purpose
The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses for defining the detail of AWR reports.

Usage
Current setting:
SQL> show parameter statistics_level;
-- default is "TYPICAL"

(options -> BASIC | TYPICAL | ALL)
Change setting permanently:
add this into init.ora file;
*.statistics_level='ALL'
-- BASIC is not enough to get AWR Report
-- TYPICAL is recommended for AWR Report
-- ALL gathers operating system and plan execution statistics records in extra for AWR Report

Then restart the instance to perform.

Change setting for current instance - temporary:
SQL> alter system set statistics_level = ALL;

Result
SQL> show parameter statistics_level;
-- output; ALL

To check detail information, we may look at v$statistics_level view.
SQL> select * from v$statistics_level;

Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214

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