[ADVICE] Free Windows Disk Defragmenter Alternative

I strongly recommend to use Smart Defrag tool instead of classical Windows Defragmenter tool.

How to check NTFS file system in CMD?

Only you need to run this command.

e.g. chkntfs C:

C:\Windows\system32>chkntfs C:
The type of the file system is NTFS.
C: is not dirty.

How to defrag disk partiton in CMD?

Only you need to run this command as Administrator.

e.g. defrag C:

How to change font color in CMD?

Only you need to run this command in cmd.

e.g. color A

0 = Black
1 = Blue
2 = Green
3 = Aqua
4 = Red
5 = Purple
6 = Yellow
7 = White
8 = Gray
9 = Light Blue
A = Light Green
B = Light Aqua
C = Light Red
D = Light Purple
E = Light Yellow
F = Bright White

How to define a tablespace in Windows?

Please execute the commands as "sys";

To define the tablespace:
SQL> CREATE TABLESPACE ALPER_DATA 
DATAFILE 'C:\tablespaces\data_01.dbf' SIZE 128M

To alter the tablespace:
SQL> ALTER TABLESPACE ALPER_DATA ADD DATAFILE 'C:\tablespaces\data_new_02.dbf' size 128m;

To drop the datafile from the tablespace:
SQL> ALTER TABLESPACE ALPER_DATA DROP DATAFILE 'C:\tablespaces\data_new_02.dbf';

To drop the tablespace:
SQL> DROP TABLESPACE ALPER_DATA 
INCLUDING CONTENTS AND DATAFILES;

To check tablespace's database files:
SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

Used Oracle Version and OS: Oracle XE and Win7

How to prepare a video with steps via windows tool?

Please you run this command first in cmd.

CMD> psr

Then you can record the steps by clicking start record.

After finish your video, you are able to check .mht file by extracting zip file.

How to know hostname from IP in Command Prompt?

Press Windows button + R and press cmd then click "OK".
Then, write down "below command" and press enter

nbtstat -a <IP>
e.g. nbtstat -a 10.10.10.10

How to delete linux user?

# sudo userdel -r alper

-r -> Remove home directory and mail spool

How to run sql query in shell script?

Please add these codes into your .sh file then you can run .sh file

e.g. 
ssh -q -T oracle@192.168.2.2 << EOF
sqlplus / as sysdba << EOFSQL
drop user ALPER_DB_USER cascade;
exit;
EOFSQL
EOF

How to know file format description in CMD?

Please you run this command in CMD.

e.g. assoc .rar
.rar=WinRAR

e.g. assoc .txt
.txt=txtfile

What are testing types and methods?

There are two test methods which are Black Box Testing and White Box Testing

Basically, there are two test types which are Functional and Non-functional.

For example, White & Black Box (Mostly), Unit, Interface & Usability, System, Regression and User Acceptance testing are Functional testing types.

Likewise, Load & Performance, Stress, Installation and Compatibility & Migration testing are Non-functional testing types.

White & Black Box Testing

White Box Testing (we know codes and are able to debug testing in codes)

Black Box Testing (we do not know codes and act as a customer)

Black Box Testing Types & Methods

• Equivalence partitioning (keyword is clustering)
• Boundary Value Analysis (keyword is looking boundary conditions)
• Cause Effect Graphing (keyword is causes -> intermediate nodes -> effects and reduced decision table)

What is the goal of testing?

The goal of testing is to reach minimum bug number and using time effectively as person/day.

What are Verification and Validation in software world?

Verification is that the process of evaluating work-products (not the actual final product) of a development phase to determine whether they meet the specified requirements for that phase.

Activity of Verification: Reviews, Walkthroughs and Inspections

Question: Are we building the product right?

Validation is that the process of evaluating software during or at the end of the development process to determine whether it satisfies specified business requirements.

Activity of Validation: Testing

Question: Are we building the right product?

Ref: http://softwaretestingfundamentals.com/verification-vs-validation/

How to know SuSE release version?

Only run this command in terminal;

$ cat /etc/SuSE-release
or
$ cat -n /etc/SuSE-release
(-n) to see rows numbers as well

How to define and drop temporary tablespace?

Please execute the commands as "sys";

SQL> CREATE TEMPORARY TABLESPACE ALPER_TS_TEMP TEMPFILE 
 'C:\tablespaces\data_01.dbf' 
 SIZE 8000M 
 AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

To alter the tablespace:
SQL> ALTER TABLESPACE ALPER_TS_TEMP ADD TEMPFILE 'C:\tablespaces\data_temp_new_02.dbf' size 128m;

To drop the tablespace:
SQL> DROP TABLESPACE ALPER_TS_TEMP INCLUDING CONTENTS AND DATAFILES;

Used Oracle Version and OS: Oracle XE and Win7

Ref: http://onlineappsdba.com/index.php/2008/02/08/tablespace-and-datafiles-in-oracle-database-back-to-basics/

How to run .sql file in sqlplus?

Press Windows button + R and press cmd then click "OK".

Write down "below command" and press enter

Full path approach
echo exit | sqlplus hr/hr @C:\sql\example.sql

Oracle path approach
echo exit | sqlplus hr/hr @?/example.sql
? -> $ORACLE_HOME

Example sql: select * from employees;
Used Oracle Version and OS: Oracle XE and Win7

How to change a specified string inside all files by recursively?

Only run this command in terminal;

$ find . -type f -exec sed -i 's/'old'/'new'/g' {} \;

You can also use escape character to use '/'.

$ find . -type f -exec sed -i 's/'\\/old'/'\\/new'/g' {} \;
(\\ -> escape characters)

How to connect into RMAN tool in Windows?

Press Windows button + R and press cmd then click "OK".
Write down "rman" and press enter

Lastly run that command to connect environment.

RMAN> connect target /

connected to target database: XE (DBID=2732638794)

How to login Windows machine with RDP (mstsc) even though firewall is active?

Please follow the instructions to use mstsc tool for Windows machines.

Press Windows key -> Right-clicking on Computer and select Properties -> Click Remote settings -> Enable Remote connections options -> You can connect with mstsc now!

How to clear screen in sqlplus?

SQL> clear screen;

How to set DB ID in RMAN?

RMAN> set dbid=111111;
-- 111111 -> desired dbid

How to see database open-mode information?

Please execute the commands as "sys";

SQL> SELECT open_mode FROM v$database;

How to see database tablespaces?

Please execute the commands as "sys";

SQL> SELECT * FROM v$tablespace;

How to see database instance information?

Please execute the commands as "sys";

SQL> SELECT * FROM v$instance;

To see db startup time:
SQL>
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM v$instance;

How to see version of database?

Please execute the commands as "sys";

SQL> select version from v$instance;

How to see resource limits?

Please execute the commands as "sys";

SQL> SELECT * FROM v$resource_limit;

How to see all tablespace quotas?

Please execute the commands as "sys";

SQL> SELECT * FROM DBA_TS_QUOTAS;

How to see all profiles and assigned limits?

Please execute the commands as "sys";

SQL> SELECT * FROM DBA_PROFILES ORDER BY PROFILE;

How to check database link?

SQL> SELECT 1 FROM dual@mydblink

How to configure EM (Enterprose Manager) if has any problem?

On linux terminal;
set ORACLE_SID=<YOURSID>
emca -deconfig dbcontrol db -repos drop;
then;
emca -config dbcontrol db -repos create;
or
emca -config dbcontrol db -repos recreate;

How to delete archive log files until specific time in RMAN?

RMAN> delete archivelog until time ‘SYSDATE-10’;
i: it will delete them until 10 days ago

Also it has a no prompt option;
RMAN> delete noprompt archivelog until time ‘SYSDATE-10′;

How to check/open archive log in sqlplus?

Check in archive log open:
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
or
SQL> archive log list;

Open achieve log mode:
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Enterprise Manager and OEM Agent commands in Oracle

Please login to database server with ssh as oracle user and run that commands.

Go to /bin path
su - grid12
cd agent/agent_inst/bin

How to start/stop Enterprise Manager
./emctl start dbconsole;
./emctl stop dbconsole;

for 12c grid EM
/home/grid12/oms/bin/emctl start oms
/home/grid12/oms/bin/emctl stop oms

How to start/stop Oem Agent
Be sure run root.sh as root user and run below commands as oracle user:
./emctl stop agent
./emctl start agent

How to check Oem Agent
./emctl status agent

How to restore Oem Agent
Run as grid12 user:
./emctl clearstate agent; and then
./emctl upload agent;

How to alter a profile in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba

SQL> ALTER PROFILE engineer LIMIT 
SESSIONS_PER_USER 5
IDLE_TIME 60
CONNECT_TIME 1200;

SQL> exit

-- SESSIONS_PER_USER; Specify the number of concurrent sessions to which you want to limit the user.
-- IDLE_TIME; Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
-- CONNECT_TIME; Specify the total elapsed time limit for a session, expressed in minutes.

How to define new profile or change old profile in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba

SQL> CREATE PROFILE engineer LIMIT 
SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;

SQL> exit

-- SESSIONS_PER_USER; Specify the number of concurrent sessions to which you want to limit the user.
-- IDLE_TIME; Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
-- CONNECT_TIME; Specify the total elapsed time limit for a session, expressed in minutes.

other parameters:
CPU_PER_SESSION; Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL; Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
LOGICAL_READS_PER_SESSION; Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL; Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA; Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). Refer to size_clause for information on that clause.
COMPOSITE_LIMIT; Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

Change current profile
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60;
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 6000;
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 1;

Add this parameter into init.ora file permanently or apply this for already running instance temporary:
Option 1: 
*.resource_limit=TRUE 
Option 2:
alter system set resource_limit = true;

How to know system date in Oracle?

Please run that SQL in your editor's SQL window.

select sysdate from dual;

How to connect linux machine with XStart -interface?

If you have XManager tool, you can easily connect to linux machine like Windows machines.

Firstly, change runlevel in /etc/inittab as follow;
Id:3:initdefault;
Then, reboot the machine.

After that please follow the steps:

Open XStart-> Click New and input a session name 
-> Input IP into Host, select SSH protocol and username/password 
-> Then select execution command like "/usr/bin/gnome-session --display $DISPLAY"
-> Click Run button -> Lastly, you should see the linux window

or

Open XShell-> Connect and login to machine with ssh 
-> Input IP into Host, select SSH protocol and username/password 
-> Then run this command "/usr/bin/gnome-session --display $DISPLAY"
-> Lastly, you should see the linux window

How to check ports in Linux machines?

Please run the command as linux system user.

For example, you want to check .80 port
# netstat -anp | grep :80
or
# netstat -tulpn | grep :80

How to see last updated (DDL) table object in Oracle?

Please run below sql script as desired database user;

SQL> select * from all_objects order by last_ddl_time desc;

How to use Java binary for all linux system users?

To execute java binary (bin) for all users, please run these commands below;

sudo ln -s -v /opt/jdk1.7/bin/java /usr/bin/java
sudo ln -s -v /opt/jdk/bin/javac /usr/bin/javac
              <target_name>      <sembolic-file_name>

<target_name> ; java installation directory
<sembolic-file_name> ; binary directory of all users

How to see PGA size per oracle session?

Please execute following SQL lines as "sys";

select
    1048576+a.value+b.value   pga_size
from
   v$parameter a,
   v$parameter b
where
   a.name = 'sort_area_size'
and
   b.name = 'hash_area_size';

How to set SHMMAX value and what is that?

Setting the kernel.shmmax parameter does not reserve or allocate any shared memory. SHMMAX is a safeguard parameter that sets the upper limit for a process can allocate how much shared memory when requested.

Please execute following commands as “root”;
sysctl -w kernel.shmmax=2147483648
(2147483648 -> 2 GB)

or
modify /etc/sysctl.conf file

sysctl -p
(to apply it)

How to see process that used memory or CPU at most?

Please run the command as linux system user.

# top
at manual -> shift + f and then n (mem) or k (cpu)

to highlight the column: press b

to sort by time: press shift + t

to see all CPUs: press 1

to get the different graphical output: press t

Note: Type and press enter bold ones to run

What should you do if electricty down or reboot exists in lvm system?

For this case,
You should mount the logic volume (lv) to related path. 

For example, you are using ftp server;
Run this command after system is up:

mount /dev/my_vg_ora/vol01 /srv/ftp/myhome/​
(mount - logic volume path - mounted path)

How to see FRA (Flash Recovery Area) configuration?

Please execute the commands as "sys";

Check archive log configuration
SQL> archive log list;

Check archive log destination
SQL> show parameter db_recovery_file_dest;

Check archive log limit and left space
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

Check archive log files 
RMAN> list archivelog all;

How to add datafile as logic volume (LV) on the tablespace in Linux?

On linux terminal;
chown -R oracle:dba /dev/my_vg_ora/example01
chown -R oracle:dba /dev/mapper/my_vg_ora-example01
chmod 777 /dev/my_vg_ora
chmod 660 /dev/my_vg_ora/*

$ sqlplus
Enter user-name: sys as sysdba
@sys
to add database file please execute under query as system database role

SQL> ALTER TABLESPACE MYTABLESPACE ADD DATAFILE '/dev/my_vg_ora/example01' size 8000m reuse

To execute the command, make sure the instance is open and the tablespace is online and also check database datafiles;

SELECT * FROM dba_data_files;

How to kill session when system could not drop database user?

Please execute following commands as "sys user":

select inst_id, sid, serial# from gv$session where username = 'TEST_ALPER';
(inst_id -> instance id, sid -> service id, serial# -> serial number)

then use;

alter system kill session '44,61808,@1';
(44 -> inst_id, 61808 -> service id, @1 -> serial number)

finally drop user;

drop user TEST_ALPER cascade;

How to select top 10 records in Oracle 11g and 12c?

In 11g:

As you now that TOP keyword does not exist in 11g, but you can use rownum keyword to query top records.

For example, you want to see top 10 salary records based on employees:

Run that SQL in your editor's SQL window.

Wrong one;
SELECT *
FROM (
 SELECT employee_id, first_name, last_name, salary
 FROM employees
 )
WHERE rownum < 11
ORDER BY salary DESC;

* ORDER BY runs at last so that it should be in sub query.

Correct one;
SELECT *
FROM (
 SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC
 )
WHERE rownum < 11;

In 12c:
With 12c, you may use special syntax to see top records that is "FETCH" syntax.

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

Also, you may disregard first 5 records inside 10 records.

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS 
FETCH NEXT 5 ROWS ONLY;

That's all. Bye :)

Windows makineye, Fedora 20 kurulum adımları

1. Fedora 20 DVD(.iso) dosyasını sitesinden indir.
2. Fedora 20 DVD(.iso) hazırlamak için Infra Recorder programını indir.
3. Programı kurup, açtıktan sonra; Write Image seçeneğinden DVD burn edilir.
4. Hard diski formatlamak için, Run'dan mmc yazıp, çalıştırılır.
5. Add Snap-In -> Disk Management -> OK ile disk formatlanır.
6. BIOS ayarlarından, bilgisayarın DVD'den boot olacak şekilde ayarla.
7. Kurulum sırasında takılma(donma) hatasını almamak için Floopy drive -> disable yapılır.

Alternatif kurulum

1. Universal USB installer programını kullanarak .iso dosyasından Linux kurulumu yapılabilir.
2. Program gerekli dosyaları USB belleğe attıktan sonra, \boot\bootinst.bat çalıştırılır ve bilgisayar USB'den boot edilip, işletim sistemi kurulur.
3. Hızlı bir linux deneyimi için SLAX dağıtımını tavsiye ederim.

Kurulum sırasında çıkan sorunlar ve çözümleri

Gigabyte anakartlarında USB Boot olmama problemi ve çözümü;

• Öncellikle, BIOS ayarındaki Legacy USB storage detect seçeneğinin enable olduğuna emin olun. (BIOS Setup Menu -DEL tuşuyla -> Integrated Peripherals -> Legacy USB storage detect -> enable)
• Daha sonra; USB sürücünüzü HP USB Disk Storage Format Tool ile formatlayın. (Linux için FAT32 seçilmeli)
• Alternatif kurulum bittikten sonra; PC startup zamanında F12 ile Boot Menu'ye gidin. Açılan menuden + Hard Disk seçeneğine tıklayın.
• Son olarak; açılan listeden USB Storage yani USB cihazınızı seçin. (Örn. USB-HDD0)

Linux işletim sistemi düzgün uninstall edilmediğinde boot sırasında ortaya çıkan grub rescue sorunu ve bu sorunun kalıcı çözümü;

• Windows live CD veya Windows kurulum DVD ile Command Prompt penceresinden bootrec /fixmbr komutu çalıştırılır. Daha sonra bilgisayar restart edilir.

Daha fazla ayrıntı için tıklayınız.

Geçici çözüm olarak BIOS'dan boot sırası değiştirilir.

• USB flash bellek format sorunu ortaya çıkarsa şu komutlarla flash belleğe format atılır.
  - CMD > diskpart > list disk > select disk 1 (genelde bu olur) > clean > create partition primary > select partition 1 > active > assign > formatla

Daha fazla ayrıntı için tıklayınız.

How can I do watermark my pictures?

You can easily watermark your photographs or pictures with this link. It is a free and online tool.

Bye. :)

What is the SID (Service Identifier) in Oracle?

Oracle uses service (oracle service) for connecting instance database by looking to sid. You can find which service names (sid) are using in database services with this command easily.

Connection format: IP:LSTN_PORT/SID
e.g.
C:\Users\Alper> lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 11-EKI-2014 12:01:25

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date                11-EKI-2014 11:49:12
Uptime                    0 days 0 hr. 12 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\Alper-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Alper-PC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Alper-PC)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

How to define a new Linux user?

# useradd -d /home/alper -g gpalper -s /bin/csh -m alper -u 1008
# passwd alper

To check it:
$ egrep -i "^alper" /etc/passwd
$ egrep -i "^gpalper " /etc/group
or
$ grep alper /etc/passwd
$ grep gpalper /etc/group

How to see that a package and a program exists in SUSE Linux ?

for package;
$ rpm –ql test_package 
or 
$ rpm –qa test_package

for program;
$ whereis test_program

How to resolve "Enterprise Manager Agent status is Unavailable" issue in Linux?

Please run below commands as oracle system user.

• emctl clearstate agent 
• emctl upload agent

[SOLUTION] ORA-28003 problem

Please execute the command as "sys";

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

How to see related user process in Linux?

In command window;

$ ps –u alper

(“alper”, linux system user)

[SOLUTION] ORA-00845 problem

By default /dev/shm is created to use half size of your server physical memory

If you see that MEMORY_TARGET not supported on this system​ while starting Oracle Database Instance in sqlplus window, please follow the instructions as below;

For this case, you should increase memory that using by Oracle as root user (e.g. size=10240m means 10 GB RAM)
-> umount tmpfs
-> mount -t tmpfs shmfs -o size=10240m /dev/shm

If you encounter any error like that "the system is using already", (e.g. process id of /dev/shm is "5265")
-> fuser -m /dev/shm
or
-> lsof /dev/shm
-> kill 5265

Ref: http://blog.yannickjaquier.com/linux/linux-memory-usage.html

How to change oracle max connection limits for "maximum connection exceed problem"?

$ sqlplus
Enter user-name: sys as sysdba @sys
to see parameter process for altering system sessions (processes (integer) = sessions)

SQL> show parameter session 
SQL> ALTER system SET sessions= 300 scope=spfile;
SQL> show parameter process
SQL> ALTER system SET processes= 300 scope=spfile;

Finally, restart the database to activate all changes

How to drop a user from database in Oracle?

Please login to database server with ssh as oracle user and run that commands.

• sqlplus / as sysdba
• SQL> DROP USER testuser CASCADE; --If testuser's schema contains objects such as tables, then you must use the CASCADE clause to drop testuser.
• SQL> exit

How to see the size of users on specific tablespace in Oracle?

Please execute following command as "sys";

SELECT tablespace_name, owner, segment_type "Object Type",
       COUNT(owner) "Number of Objects",
       ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB"
FROM   sys.dba_segments
WHERE  tablespace_name IN ('ALPERTABLESPACE')
GROUP BY tablespace_name, owner, segment_type
ORDER BY tablespace_name, owner, segment_type;

How to login sqlplus on linux command window in Linux?

$ sqlplus scott/tiger@orcl

@orc1 -> tnsnames.ora – connection node name
scott -> db username
tiger -> db username password 

$ sqlplus / as sysdba@orcl

Login as sysdba with this.

How to see tablespace usage for Oracle Database?

Please execute following commands as "sys";

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
 from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as  free_space from dba_free_space
 group by tablespace_name) a,
 (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
 group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name

How to add a alias for using by all users in Linux?

Please edit /etc/bash.bashrc, for example;

• alias c='cd' 
• alias a='dir'
• alias l='ls -l'
• alias p='cd -'
• alias '..'='cd ..'

How to trace Linux CPU usage on system?

mpstat -P ALL 20

•  Shows that CPU usage information related to each core and all cpu in every 20 seconds.

sar 10

•  Shows that CPU usage information related to all cpu in every 10 seconds.

Examples:

oracle@alperdb:~> mpstat -P ALL 1
Linux 2.6.32.12-0.7-default (alperdb)  02/21/2014  _x86_64_

09:12:46 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
09:12:47 AM  all    0.53    0.00    0.00    0.53    0.00    0.00    0.00    0.00   98.94
09:12:47 AM    0    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
09:12:47 AM    1    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
09:12:47 AM    2    1.11    0.00    0.00    0.00    0.00    0.00    0.00    0.00   98.89
09:12:47 AM    3    1.14    0.00    0.00    2.27    0.00    0.00    0.00    0.00   96.59

oracle@alperdb:~> sar 10
Linux 2.6.32.12-0.7-default (alperdb)  02/21/2014  _x86_64_

09:16:13 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:16:23 AM     all      0.20      0.00      0.05      0.18      0.00     99.57
09:16:33 AM     all      0.90      0.00      0.32      0.22      0.00     98.56
09:16:43 AM     all      1.89      0.00      0.47      0.32      0.00     97.32

Linux - command line keyboard shortcuts

Here are some basic shortcuts while you are in command window;

CTRL + a -> go to begin of the line
CTRL + e -> go to end of the line
CTRL + d -> log-out
CTRL + w -> remove one word
CTRL + u -> remove all line
CTRL + r -> search for a word in history
CTRL + ins -> paste the clipboard

"cd -" -> go to last directory

How to kill a session with custom procedure?

-If you are authorized to sys user.

Firstly run this procedure to compile and save.
---
CREATE OR REPLACE PROCEDURE P_KILL_SESSION(DATABASE_USER_NAME IN VARCHAR2, STR_RESULT OUT VARCHAR2)
   IS
str_exec VARCHAR2(1000);
   BEGIN
     FOR cur_session IN (SELECT sid, serial#
                         FROM v$session
                        WHERE username = upper(DATABASE_USER_NAME)
                          AND status != 'KILLED') LOOP                          
      str_exec := 'alter system kill session ''' || cur_session.sid || '' || ',' || cur_session.serial# ||'''';      
      BEGIN
          execute immediate str_exec;
          STR_RESULT := 'Sessions are terminated successfully';
      EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
      END;     
   END LOOP;
END;
---

To use it;
In command window-editor,

var STR_RESULT VARCHAR2
EXEC P_KILL_SESSION('test_user',:STR_RESULT)
--test_user session could be killed

How to overcome special character problem in Windows?

You can type special character with using ASCII Table.
eg. If you want to type backslash (\), press ALT + 92

You can reach all ASCII table via this link. (Refer to DEC)

Note: Please be sure that NUM_LOCK is on.

[SOLUTION] ORA-06550 and PLS-009005 problems

Please login to database server with ssh as oracle user and run that commands.

• sqlplus / as sysdba
• SQL> grant execute on DBMS_JOB to ALPERDB_USER;
• SQL> exit

How to define distribution list in Microsoft Office Outlook?

To do that please follows the instructions;

• Contacts -> New -> Distribution List -> Enter the name of the list and Select Members
• Save and close

To use it;

• Enter the list name on To/CC/BCC and click Check Names then send your mail.

How to revert all sql lines into one line in Ultra Edit and Notepad++ ?

For example, you have a long SQL file and you must run it in command line or in such tool or program. For this, you can use Ultra Edit or Notepad++ tool for convert it.

Ultra Edit:
Open file via Ultra Edit -> Press CTRL + R -> Input ^p and space character in related columns -> Click Start button -> Click Replace All button

Notepad++:
Open file via Notepad++ -> Select desired lines -> Press CTRL + J

That's it.

How to define network path for Windows Operating System?

To do that please follows the instructions;

• Open My Computer -> Right mouse click -> Select “Add a new network location” -> Click as twice “Next” -> Enter your network address and click Next -> Enter your new network shortcut name -> Click finish

Notes;
This feature is able to use for “FTP” and “remote connection”

Like; ftp://IP or \\Your_IP