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]
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)
