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,
         what        => 'HR.P_COMPILE_INVALID_OBJECTS;',
         next_date   => TO_DATE ('24.10.2016 10:00:00',
                                 'dd/mm/yyyy hh24:mi:ss'),
         interval    => '(SYSDATE)+1/24',
         no_parse    => FALSE);
      SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

   COMMIT;
END;

Check job parameter
SQL> select * from v$parameter where name like '%job%';

if value is "0":
SQL> alter system set job_queue_processes=20;

Add this setting into init.ora file:
*.job_queue_processes=20

Info: If job_queue_processes parameter value is bigger than "1", jobs runs automatically such as refresh materialized view job. On the other hand, user can run this job manually as well while job_queue_processes="0".

Check current job status 
SQL> select * from user_jobs;
or
SQL> select * from dba_jobs;

Check running jobs 
SQL> select * from dba_jobs_running;
-- take a note that sid output (e.g. 2283)

Get detail info about running job
SQL> SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE s.sid = 2283
AND s.paddr = p.addr;
-- s.program; CQJ0 (Job queue controller), J000, J001 ... J999 (Job Queue) 
-- These are called as background processes

Drop the job with sys or system user (as sysdba)
-- Before do this, make sure related job is broken, no enq. tx row lock session on SYS.JOB$ table that killed.

SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.dba_dml_locks a, sys.v_$session B
 WHERE     B.SID = a.session_id
       AND owner NOT IN ('WMSYS',
                         'CTXSYS',
                         'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND name = 'JOB$';

-- Run that 
SQL>
begin
sys.dbms_job.broken(1111,true); -- 1111 -> the job id
commit;
end;

SQL>

SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.v_$session B
 WHERE b.sid = 2283;

SQL>
begin
sys.dbms_job.remove(1111); -- 1111 -> the job id
commit;
end;

Dynamic SQL for dropping jobs with sys or system user (as sysdba)
-- Before do this, make sure related job is broken and session is killed.
-- Run that 
SQL>
SELECT 'begin sys.dbms_ijob.broken(' || job || ', true); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;

SQL>

SELECT DISTINCT

          'ALTER SYSTEM KILL SESSION '''

       || b.sid

       || ','

       || b.serial#

       || ''' IMMEDIATE;'

          AS KILL_THEM_ALL

  FROM sys.v_$session B, dba_jobs_running A

 WHERE a.sid = b.sid;

SQL>

SELECT 'begin sys.dbms_ijob.remove(' || job || '); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;
-- ijob uses for all jobs in dbms_scheduler (new version of dbms_jobs)

Info: Please click this for P_COMPILE_INVALID_OBJECTS 

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;
   err_code    VARCHAR2 (200);
   err_msg     VARCHAR2 (200);
BEGIN
   /*Author: Alper Özsöyler */

   FOR cur_object IN (  SELECT owner, object_name, object_type
                          FROM dba_objects
                         WHERE status = 'INVALID'
                      ORDER BY owner)
   LOOP
      str_exec := 'ALTER ';

      IF cur_object.object_type = 'PACKAGE BODY'
      THEN
         str_exec := str_exec || 'PACKAGE ';
      ELSE
         str_exec := str_exec || cur_object.object_type || ' ';
      END IF;

      str_exec :=
            str_exec
         || cur_object.owner
         || '.'
         || cur_object.object_name
         || ' COMPILE ';


      IF cur_object.object_type = 'PACKAGE BODY'
      THEN
         str_exec := str_exec || 'BODY ';
      END IF;

      BEGIN
         EXECUTE IMMEDIATE str_exec;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  cur_object.owner
               || '.'
               || cur_object.object_name
               || ' ('
               || cur_object.object_type
               || ') '
               || '-> still invalid!');

            err_code := SQLCODE;
            err_msg := SUBSTR (SQLERRM, 1, 200);

            INSERT INTO t_cmp_invalid_objects_log
                 VALUES (s_cpl_inv_objects_error_log.NEXTVAL,
                         cur_object.object_name,
                         cur_object.owner,
                         cur_object.object_type,
                         err_code || ' -ERROR- ' || err_msg);

            COMMIT;
      END;
   END LOOP;

   str_exec := 'select count(*) FROM dba_objects
                       WHERE status = ''INVALID''';

   EXECUTE IMMEDIATE str_exec INTO total_inv;

   DBMS_OUTPUT.put_line ('Total invalid number: ' || total_inv);
END;
/
DROP TABLE t_cmp_invalid_objects_log
/
CREATE TABLE t_cmp_invalid_objects_log
(
   log_id          INT PRIMARY KEY,
   object_name     VARCHAR2 (100),
   object_schema   VARCHAR2 (100),
   object_type     VARCHAR2 (100),
   error           VARCHAR2 (1000)
)
/
DROP SEQUENCE s_cpl_inv_objects_error_log
/
CREATE SEQUENCE s_cpl_inv_objects_error_log MINVALUE 0
                                            START WITH 0
                                            INCREMENT BY 1
                                            CACHE 5;

Usage:
exec P_COMPILE_INVALID_OBJECTS();

Check error logs:
SQL> show error;
or
SQL> SELECT *
  FROM ALL_ERRORS
 WHERE OWNER = USER;

How to drop invalid objects from database?

Specify invalid objects
SQL>
  SELECT *
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;

Drop invalid objects
SQL> 
  SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;

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

Kill all remote sessions
$ sudo kill -9 ` ps -ef|grep LOCAL=NO|grep ALPERDB|awk '{print $2}'`

Kill spesific sessions
SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
  FROM sys.dba_ddl_locks a, sys.v_$session B
 WHERE     B.SID = a.session_id
       AND owner NOT IN ('SYS',
                         'WMSYS',
                         'CTXSYS',
                         'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND name like '%EMPLOYEES%';

-- EMPLOYEES -> Table (Object) Name

Ref: https://ozsoyler.blogspot.com.tr/2017/01/how-to-kill-inactive-session-for-non.html

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 tableA where rowid in (select a.rowid from tableA a, tableB b where a.columnA = b.columnB);

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 distinct 'EXEC UTL_RECOMP.recomp_serial(' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

parallel option (4 parallel threads):
SQL> 
EXEC UTL_RECOMP.recomp_parallel(4);
SQL> 
select distinct 'EXEC UTL_RECOMP.recomp_parallel( 4, ' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

or recompile with manual approach

SQL> 
select 'ALTER ' || (CASE WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE object_type END) || ' ' || owner || '.' || object_name || ' COMPILE' || (CASE WHEN object_type = 'PACKAGE BODY' THEN ' BODY;' ELSE ';' END) AS SQL from dba_objects where status = 'INVALID' and object_type <> 'SYNONYM' order by owner;

Generate and Run SQL for compile all objects

SQL> 
select distinct 'EXEC DBMS_UTILITY.compile_schema(schema =>' || '''' || owner || ''')' AS SQL from dba_objects where status = 'INVALID';

Check invalid objects again
SQL> 
select * from dba_objects where status = 'INVALID' order by owner;

Count invalid objects group by object type
SQL>
select object_type,count(*) from dba_objects where status = 'INVALID' group by object_type;

How to define "Auto Replace" in Toad?

e.g.
View -> Toad Options -> Editor -> Behavior -> Auto replace -> Add -> enter "*s*" as value and "select * from " as replace with columns -> OK -> OK

Useful tool shortcuts

TOAD
Shift + Ctrl + f -> Format code
Shift + b -> Comment out code
Ctrl + Shift + b -> Uncomment code
Ctrl + z -> Undo
Ctrl + Shift + z -> Redo
Ctrl + g -> Go to line
Ctrl + . (point) -> Show object list
Ctrl + t -> Show object list
Shift + F4 -> Show action list
Ctrl + DEL -> Delete by word
Ctrl + e -> SQL Explain Plan
Ctrl + u -> Make uppercase keywords 
Ctrl + l -> Make lowercase keywords

Visual Studio
Ctrl + k + d -> Format code
Ctrl + k, Ctrl + c -> Comment code
Ctrl + k, Ctrl + u -> Uncomment code

Oracle SQL Developer
Ctrl + F7 -> Format code

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:\Program Files (x86)\Notepad++\plugins
then reopen notepad++

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 into init.ora parameter file - apply forever
*.NLS_DATE_FORMAT='DD-MM-YYYY'

Then, restart database.
SQL> shutdown immediate;
SQL> startup;

Run below command - apply just for session
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

Referance: http://docs.oracle.com/cd/A84870_01/doc/server.816/a76966/ch2.htm

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 DATABASE LINK "ALPER_DATABASE_NEW_LINK"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK2"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK3"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

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> select * from departments where department_name like 'A%';
DEPARTMENT_ID  DEPARTMENT_NAME  MANAGER_ID  LOCATION_ID
10             Administration   200         1700
110            Accounting       205         1700
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad_%';
10             Administration   200         1700
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad/_%' escape '/';
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad__%' escape '_';
280            Ad_New           200         1700

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@10.10.10.10
$ sudo su -
(executes all /etc/profile, .profile and .bashrc files)
# ifconfig
$ sudo su
(executes only .bashrc file)
# ifconfig

References:

• http://askubuntu.com/questions/168461/how-do-i-sudo-without-having-to-enter-my-password
• http://askubuntu.com/questions/376199/sudo-su-vs-sudo-i-vs-sudo-bin-bash-when-does-it-matter-which-is-used

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 =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))

Check remote host via tnsping

$ tnsping ALPER_DB

Define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

If you desire all users use this link you should add "public" into create statement

For All users (public):

SQL> CREATE PUBLIC DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

Alternative option: define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))';

Observe the new link and check it

For specific users:

SQL> select * from user_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

For All users (public):

SQL> select * from all_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

Extra
Connect without db link:
$ sqlplus "hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))"
or
CMD> sqlplus hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))

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 from microsoft site

How to run a command as different Windows user?

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

How to mount devices at startup by /etc/fstab and /etc/rc.d/rc.local for RedHat distro?

-> define uuid for connected devices
ls -l /dev/disk/by-uuid/

-> add related info into /etc/fstab
e.g.
UUID=1111 /alper/sdb1 ext3 defaults,nobootwait 0 0

-> if this cannot mount the path, you may deal with network issues. To fix it, you should add following command into /etc/rc.d/rc.local config file.
mount -a

extra info: -> If you use 1 1 instead of 0 0 for priority option, you may encounter "maintenance mode" problem. To fix it, run this command
mount -o rw, remount /

What are the differences between SAN, NAS and DAS?

SAN(Storage Area Network);
SAN is used for applications to access BLOCK storage over an optical FC network using the SCSI protocol. (e.g. FibreChannel, iSCSI, FoE)

NAS(Network Attached System);
NAS is used for access to FILE storage over TCP/IP on an Ethernet network using either the CIFS (for Windows) or NFS (for Unix) protocol. (e.g. NFS, CIFS, AFS)

DAS(Direct Attached Storage);
DAS is a block device from a disk which is physically [directly] attached to the host machine. (e.g. IDE, SCSI, SATA)

Source: 
http://searchstorage.techtarget.com/tip/SAN-vs-NAS-Which-do-you-need
http://serverfault.com/questions/81723/what-is-the-difference-between-san-nas-and-das

How to play classical Atari 2600 games on PC?

Everyone misses Atari games of childhood but we are able to play them on our new PCs now.

At first, we need to install Atari emulator on PC which will be Stella. To download it, please click below link. http://stella.sourceforge.net/downloads.php

Subsequently, we should download ROMs of our desired games. To download them, please click one of these links. 

• http://www.atariage.com/system_items.html?SystemID=2600&ItemTypeID=ROM
• http://www.atarimania.com/rom_collection_archive_atari_2600_roms.html

Finally, we are ready to play. 

• Run Stella
• Select your favorite ROM (.bin)
• Enjoy! :)

How to use sqlcl?

Recently, Oracle announced that we have a new SQL Plus command line anymore! Its name is sqlcl that seems to very useful and powerful tool. So, let's take a look at it at below.

First, we need to download it from Oracle site. After that we may extract the file under C:\ directory then add setup path to "Path" environment variable to reach it from CMD. (e.g. C:\sqlcl-4.2.0.16.131.1023-no-jre\sqlcl)

Now we are able to connect our database by sqlcl.

Here are the commands:
sql hr@//localhost:1521/xe
show all
host cls
select last_name, job_id, salary from employees order by salary desc fetch first 10 rows only; 
set sqlformat ansiconsole
set sqlformat csv
history
history 100

Happy coding! :)