Oracleadm

SQLPLUS shortcuts:

/ -will execute the last run statement in the sqlplus buffer
1 -will display the last run statement in the sqlplus buffer
change /old/new -Changes the first occurrence of the text on the current line in the sqlplus buffer.


 ---------------------------------------------------------------------------------------------------
Oracle EZCONNECT:

sqlplus user/pwd@//192.168.1.20:1521/ORCL


With this we can connect to a database without TNSNAMES configuration.
---------------------------------------------------------------------------------------------------
Oracle Analyze a table:

alter table table_name estimate statistics;

Analyze a schema:

login as sys user

begin
exec dbms_stats.gather_schema_stats("SCOTT");
end;
---------------------------------------------------------------------------------------------------
HP UX commands:

machinfo - to find the machine information
swapinfo - to find the swap partition size
bdf - to check the partition information (in kbytes)
---------------------------------------------------------------------------------------------------
Configuring SAMBA server in RHEL5.5.

-give full permission on the sharable directory( chmod 777 /pack/sample
-samba package should be installed or do yum install samba if you have yum repository
-$vi /etc/samba/smb.conf
-add the following lines in the file mentioned to share the mentioned directory
- [Share]

   path = /home/share

   writable = yes

   guest ok = yes

   guest only = yes

   create mode = 0777

   directory mode = 0777

   share modes = yes

use testparm command to check and verify the samba configuration file.
---------------------------------------------------------------------------------------------------
ORATAB file is created during oracle installation. This file resides in /etc directory or in /var/opt/oracle directory.

This file is used to list the databases installed and the oracle versions. Oratab file contains ORACLE_SID, ORACLE_HOME, and automatic startup , shutdown of database with OS startup and shutdown. The environment variables in OS level are taken from this file.
For example:

college:/oracle/app/product/11.2.0/db_home1:Y

The Y in the above line mentions to autostart the db and listener while OS startup. This oratab file works for only  one instance and you cannot add multiple instances of database into this file.

-------------------------------------------------------------------------------------------------------------

Oracle DB link


Creating dblink from oracle 10g database to a user in oracle 11g database :

create database link link_name connect to "username" identified by "password" using 'connection_string';

The username and password should be given within double quotes.

There are other conditions too for a dblink, that is

  Both the database servers should be in same domain

  The user should have 'CREATE DATABASE LINK' privilege

-------------------------------------------------------------------------------------------------------------

Oracle - Create Tablespace


SQL> create tablespace tbs_name datafile '/vol01/oradata/sample/datafile/exp101.dbf' size 100M autoextend on next 2M maxsize unlimited extent management local segment space management auto;
 
Force Drop a tablespace:
------------------------------
SQL> alter tablespace tbs_name offline drop;

Ensure no active connections writing on that particular table.

Change DB name:
-----------------------

nid is the utility to change the dbname.

The database should be in the mounted exclusive state,

nid TARGET=sys/password@tnsname
Compile Oracle Schema using sqlplus
---------------------------------------

execute dbms_utility.compile_schema('SCOTT');

as a 'SYS' user.

Managing Rouge sessions in Oracle:
------------------------------------------------

1. Identify the session to be killed

SELECT a.inst_id,

       a.sid,

       a.serial#,

       b.spid,

       a.username,

       a.program

FROM   gv$session a

       JOIN gv$process b ON b.addr = a.paddr AND b.inst_id = a.inst_id

WHERE  a.type not like  'BACKGROUND';

2. Alter system kill session 'sid,serial#';

This command will kill the session. Run as sys user. If still the session persists you can use "immediate" clause with the above command. like

Alter system kill session 'sid,serial#' immediate;

If its a RAC instance then

Alter system kill session 'sid,serial#,@inst_id' immediate;\

3. The other way is Disconnect Session

This option does not kill the session but it will disconnect the session and make the dedicated server process(dedicated server mode)  in os level to shut for that particular session.

Alter  system disconnect session 'sid,serial#' post_transaction;
the post_transaction option will wait for the current transaction to complete.

Alter system disconnect session 'sid,serial#'  immediate;
The immediate option will stop the current transaction too. (recommended)

4. Killing in OS level

If you want to kill a OS level oracle process(server process) responsible for the session then issue

orakill ORACLE_SID spid;

from command prompt for windows.

kill -9 spid

for unix flavours.(the spid for the session can be acquired from the v$process, refer the query in point 1)