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! :)

How to use "wall" and "write" commands?

$ wall
-> press "ctrl+d" to send the message

$ echo "Hello World!" | wall

$ sudo wall message.txt
-> send content of message.txt file

$ write alper
-> press "crlt+c" to quit

$ write alper pts/0
-> run "who" to see terminal names like pts/0

Excel'de nesne ekleme nasıl yapılır?

[Ekle] -> [Nesne] -> [Dosyadan Oluştur] -> [Gözat] -> [Tamam]

What are hard link and soft link in Linux?

Hard link; it may used for backup (replica) purpose. (seems to RAID 1)
e.g.
$ ln original_file.txt hd_file.txt
$ ln original_file2.txt /home/alper/hd_file2.txt

Soft link; it may used as shortcut like in Windows OS.
e.g.
$ ln -s original_file soft_file.txt
$ ln -s original_file /home/alper/soft_file2.txt

to list the links;
$ ls -l 
(l; list)
or 
$ ls -i
(i; inode number)
then find the files by same inode number
$ find . -inum <inode_number>

e.g.
$ find . -inum 10000

tcpdump examples

$ tcpdump -D
-> Display all available interfaces for tcpdump

$ tcpdump -i 1 | more
-> Listen (interface 1) with more command

$ tcpdump -i any
-> Listen all actived interfaces

$ tcpdump -i eth1
-> Listen eth1 interface

$ tcpdump -i eth0 port 22
-> Listen eth0 interface on port 22

$ tcpdump -i eth2 port 23 -w example.pcap
-> Listen eth2 interface on port 23 and save as desired file name