Thursday, 27 October 2016

How to configure instant client for Toad?

›
-> unrar .zip file  e.g. C:\oracle_client\12.1.0\client_1 -> add this to "Environment Variables-> Path " That'...

How see latest sql histories from audit view?

›
Check audit paramater SQL> show parameter audit; -- basic options; 'DB','DB,EXTENDED', 'OS', 'NONE' ...
Wednesday, 26 October 2016

How to define snapshot and synonym?

›
Define source table at DB1 machine CREATE TABLE t_alper_source AS    SELECT * FROM employees; ALTER TABLE t_alper_source ADD CONSTRAIN...
Tuesday, 25 October 2016

What are the snapshot (Materialized Views) refresh methods?

›
begin DBMS_SNAPSHOT.REFRESH( 'v_alper_ss','f');  end; / f -> fast refresh ? -> force refresh c -> comple...
Monday, 24 October 2016

How to define/check/drop jobs?

›
Define a new job (will run every hour) DECLARE    X   NUMBER; BEGIN    BEGIN       SYS.DBMS_JOB.SUBMIT (          job         => X...
Friday, 21 October 2016

How to compile invalid objects with custom procedure?

›
CREATE OR REPLACE PROCEDURE P_COMPILE_INVALID_OBJECTS    AUTHID CURRENT_USER IS    str_exec    VARCHAR2 (200);    total_inv   NUMBER; ...
Wednesday, 19 October 2016

How to drop invalid objects from database?

›
Specify invalid objects SQL>   SELECT *     FROM dba_objects    WHERE status = 'INVALID' ORDER BY owner; Drop invalid obj...
Monday, 17 October 2016

How to kill remote sessions or specific sessions?

›
Observe remote sessions $ ps -ef | grep LOCAL=NO | grep ALPERDB| awk '{print $2}' $ ps -ef | grep LOCAL=NO | grep ALPERDB| wc -l ...

How to use rowid for deleting data?

›
If you desire delete data from joined tables such as log table, you may use rowid to prevent delete unwanted rows. e.g. delete from tabl...
Tuesday, 11 October 2016

How to recompile invalid objects and compile all database objects?

›
Generate and Run SQL for recompiling invalid objects serial option: SQL>  EXEC UTL_RECOMP.recomp_serial() ; SQL>  select distin...
Monday, 3 October 2016

How to define "Auto Replace" in Toad?

›
e.g. View -> Toad Options -> Editor -> Behavior -> Auto replace -> Add -> enter " *s* " as value and " ...

Useful tool shortcuts

›
TOAD Shift + Ctrl + f -> Format code Shift + b -> Comment out code Ctrl + Shift + b  -> Uncomment code Ctrl + z -> Undo Ct...
Tuesday, 27 September 2016

How to import plugin dll for Notepad++?

›
Dispath the dll file to installation folder of Notepad++ under " plugins " directory. e.g. copy ComparePlugin.dll to C:\Progr...
Wednesday, 21 September 2016

How to change NLS paramater?

›
Check current nls parameter as sys user SQL> select * from v$nls_parameters; e.g. modifying date format parameter Add below row in...

How to use existed tns connection for new one?

›
* The key point is 'Using' keyword that checks tnsnames.ora connection node then you can reach remote database. SQL>  CREATE D...
Sunday, 18 September 2016

Oracle escape character examples

›
First, we need an example data for study. SQL> insert into departments values ('280',' Ad_New ',200,1700); e.g. SQL...
Thursday, 15 September 2016

How to gain root access without entering root password on Linux?

›
Add the line into the end of /etc/sudoers file as root vi /etc/sudoers alper ALL=(ALL) NOPASSWD: ALL Test your connection ssh alper @...
Tuesday, 13 September 2016

How to define new db link?

›
Add a new connection node into tnsnames.ora file. vi $ORACLE_HOME/network/admin/tnsnames.ora e.g. ALPER_DB =   (DESCRIPTION =     (...
Monday, 22 August 2016

How to run a command remotely?

›
e.g. psExec \\10.10.10.10 cmd /c calc.exe psExec \\10.10.10.10 cmd.exe /c msg * 'merhaba' Note: You can take  psExec  tool fr...
Wednesday, 17 August 2016

How to run a command as different Windows user?

›
e.g. Press " Windows button " -> Type " cmd " -> shift key + right mouse click -> click " Run as differe...