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