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 -...
Tuesday, 21 February 2017

How to use logminer?

›
Determine the archive log path: SQL> archive log list; If "Archive destination" is "DB_RECOVERY_FILE_DEST": SQL...

GRANT/REVOKE examples

›
check own  privileges: SQL>   SELECT * FROM SESSION_PRIVS; syntax of  system privileges: GRANT [ system_privileges | roles] {ANY} ...
Monday, 20 February 2017

How to kill all inactive sessions with custom procedure?

›
CREATE OR REPLACE PROCEDURE P_KILL_ALL_INACTIVE_SESSIONS IS    str_exec   VARCHAR2 (10000); BEGIN    FOR cur_session       IN (SELECT ...
Tuesday, 7 February 2017

How to use "EXECUTE IMMEDIATE' ?

›
e.g. DECLARE    v_name      VARCHAR (100) := &i1;    v_surname   VARCHAR (100) := &i2; BEGIN     EXECUTE IMMEDIATE 'inse...
Saturday, 4 February 2017

watch examples

›
$ watch free -m -> Display free command output in every two seconds  $ watch -n 5 free -m -> Display free command output in every...
Friday, 3 February 2017

Dynamic sql script for synonyms

›
Define new synonyms: SQL> select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || ...
Tuesday, 31 January 2017

screen commands

›
screen -> start the tool ctrl + a -> control screen ctrl + a + c -> define new screen ctrl + a + n -> s...

How to remove datafile carefully?

›
find datafile path; SQL>  select file_name from dba_data_files; before run rm; $ cd /oradata $ lsof * $ rm -f alper_data_01.dbf
Monday, 30 January 2017

ORA-27137 Solution

›
Error ORA-27137: unable to allocate large pages to create a shared memory segment Solution Comment out this in pfile; ###*.use_large_p...
Wednesday, 25 January 2017

How to define/use Oracle domain index?

›
What is domain index?: Oracle says that domain indexes reformed beyond to the basic types of hash, bitmapped, and B-tree indexes that deve...

How to list/define/delete shared folder in Windows?

›
Check shared folder CMD> net share Share a folder CMD>   net share Alperinko$=D:\Oracle /grant:everyone,FULL /users:25 -> Al...
Monday, 23 January 2017

How to configure login options in Oracle?

›
Apply the option permanently sec_case_sensitive_logon=FALSE or sec_case_sensitive_logon=TRUE Check the parameter SQL> show parame...
Friday, 20 January 2017

How to start/stop/configure vnc service?

›
install tool: yum install tigervnc-server configure settings: vi /etc/sysconfig/vncservers e.g. VNCSERVERS="2:root 3:oracle 4:a...
Thursday, 19 January 2017

output columns of top command

›
Columns: PID     -> Process ID    USER    -> Process owner    PR      -> Priority of process  NI      -> Nice value of proc...
Wednesday, 18 January 2017

ORA-29885 solution

›
Error ORA-29885: domain index is defined on the column to be modified Solution Find problematic index; SQL> SELECT *   FROM dba_i...

How to calculate IP range?

›
e.g.  Address:     10.10.10.0/24             00001010 0 0001010 0 0001010 00000000 Netmask:     255.255.255.0              11111111 1...
Friday, 13 January 2017

An example of PL/SQL collection for nested table

›
DECLARE    TYPE employee IS TABLE OF VARCHAR (5);    employees   employee := employee ('A',  'B',  'C',  'D...

How to change table read-write mode?

›
Set read only mode: SQL> ALTER TABLE HR.EMPLOYEES READ ONLY ; Set read/write mode: SQL> ALTER TABLE HR.EMPLOYEES READ WRITE ; ...
Thursday, 12 January 2017

How to see load averages on linux?

›
$ top or  $ uptime e.g. load average: 0.35. 0.23, 0.25 First column; last one minute Second column; last five minutes Third column;...