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

How to change system date in Linux?

Please run that command after you logged-in as root (su) user.

# date -s "9 OCT 2014 18:00:00"

to check it:
# date
e.g. Thu Oct  9 18:00:05 EEST 2014

How to know username information in Windows OS?

Please follow below steps:

• Press Windows + R
• Type and press enter; cmd
• Type and press enter; echo %username%

How to shutdown Oracle Database Server in Windows OS?

Please follow below steps:

• Press Windows + R
• Type and press enter; sqlplus / as sysdba
• Type and press enter; SQL> shutdown immediate
• Type and press enter; SQL> exit

You can use other options instead of "immediate" keyword.

List of options:

normal

• No new connections are allowed after the statement is issued. 
• Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

immediate

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued. 
• Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.) 
• Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

abort

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued. 
• Current client SQL statements being processed by Oracle Database are immediately terminated. 
• Uncommitted transactions are not rolled back. 
• Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

transactional

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued. 
• After all transactions have completed, any client still connected to the instance is disconnected.  
• At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted

Refered to: http://docs.oracle.com/cd/B28359_01/server.111/b28310/start003.htm#ADMIN11160

Bye.

How to start Oracle Database Server in Windows OS?

Please follow below steps:

• Press Windows + R
• Type and press enter; sqlplus / as sysdba
• Type and press enter; SQL> startup
• Type and press enter; SQL> exit

That's all. Bye.

How to remove a job in Oracle?

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

begin
 sys.dbms_job.remove(100);
commit;
end;

100; job id
If you do not know desired job id, you can follow this link to learn.

Note: You have to login to database as SYS user for run SQL.

How to know DBMS Jobs information in Oracle?

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

select * from user_jobs;

Windows key tricks

Here some Windows key tricks:

• Windows key + M -> Minimizes all active windows 
• Windows key + Shift + M -> Restore operation
• Windows key + D -> Go to Desktop
• Windows key + Tab -> Select another active window
• Windows key + 1 -> Select first application in task bar
• Shift + Windows key + 1 -> Open first application as new
• Windows key + T -> Select another application in task bar
• Windows key + Left Arrow -> Fit window to the left side
• Shift + Windows key + Left Arrow/Right Arrow -> Move window to other monitor
• Windows key + Right Arrow -> Fit window to the right side
• Windows key + Down Arrow -> Minimize active window
• Windows key + Up Arrow -> Maximize active window
• Windows key + Plus key -> Zoom in via Magnifier
• Windows key + Minus key -> Zoom out via Magnifier
• Windows key + ESC -> Escape from zoom
• Windows key + P -> Show me monitor output options
• Windows key + Pause/Break key -> Open System Properties

[SOLUTION] - TOra "ORA-12154" Error

If you get ORA-12154 while logging to database user with TNS in TOra, this means system does not know tnsnames.ora file path.

To solve this, you can add TNS_ADMIN variable to among user variables.
e.g. C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
e.g. C:\orant\NET80\ADMIN

Bye.

[SOLUTION] - TOra "No available connection provider " Error

In this entry, I will show you that how to solve "No available connection provider " error in TOra SQL Editor.

If you get like below error, probably you have a problem with Oracle Client.

Fistly, you should check whether there is a Oracle Client in system. If yes, you have to add its path into path system variable.

If you don't have any Oracle Client, you should install it from Oracle website.

Bye. :)