Oracle Cheats Sheet

Managing Server

Switch to oracle user

#root

#su oracle

Start DB

oracle$sqlplus /nolog

SQL>conn sys/password as SYSDBA

#to start database

SQL>startup

#to shutdown database immediately

SQL>shutdown immediate

SQL>exit

Start Listner

#oracle$LSNRCTL

>start - to start

>status - to check current status

>stop

Start Enterprise Manager

Url: http://iPAdress:5500/em

oracle$$ORACLE_HOME/bin/./emctl start dbconsole

Start iSqlplus

Url: http://iPAdress:5560/isqlplus

oracle$$ORACLE_HOME/bin/./isqlplusctl start

http://localhost:5560/isqlplus use "system" as user name and password given during installation

How to connect to a remote Oracle server using Oracle Client:

C:\Documents and Settings\User.Domain>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 25 13:13:36 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: userName/password@iPAdress/

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>SELECT * FROM TABS;

SQL>SELECT * FROM CAT;

*Show users

SQL>show users;
SQL>show dba_users;

Table space and User Creation

Create user with default tablespace

Create user MyUserDB identified by MyUserDB123;

grant dba, connect, resource to MyUserDB;

Create user with defined tablespace

DROP USER MyUserDB CASCADE;

CREATE SMALLFILE TABLESPACE "TS_MyUserDB" DATAFILE 'TS_MyUserDB'

SIZE 1000M AUTOEXTEND ON NEXT 32M

MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "MyUserDB" PROFILE "DEFAULT" IDENTIFIED BY "MyUserDB123" DEFAULT TABLESPACE "TS_MyUserDB" ACCOUNT UNLOCK;

GRANT dba, resource, connect TO MyUserDB;

Export DB Backup/Dump
====================

How to Export dmp using emp utility:

***Export from Client Machine using Oracle Client

>exp userName/password@iPAdress/serviceName

eg:

>exp MyUserDB/MyUserDB123@192.168.0.1/orcl

then press enter to select all default options, give a path to download dmp C:\MyUserDB.dmp, and finally enter user name and then enter to start

Export from Linux:

1. At first login as Oracle user in Linux Server, or switch to oracle user by:

#su oracle

2. Enter the following command for changing directory:

$cd $ORACLE_HOME/bin

3. Enter the following command for backup the database:

$exp userName/passward file=/home/oracle/*.dmp log=/home/oracle/.log

[N.B: /home/oracle use for this document. You can use any valid path for this. If no path is supplied then the backup file will be found in the current directory, i.e.: $ORACLE_HOME/bin]

Update:(for Oracle 10g or Higher edition)

exp utility is ancient now, use expdp (export data pump) instead.

>expdp userName/password@[iPAdress/serviceName]

>expdp userName/password@[TNS_Name]

this will put your exported dump file in the following directory of the server:

..\app\oracle\admin\XE\dpdump

Create a batch file with below content to make schedule to backup database periodically with datetime in dmp and log file name

#===========================================================

@echo off For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b) #For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b) For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b) #echo %mydate%_%mytime% #user=sys, password=admin, tnsname=xe, schemas=schema name to backup. expdp 'sys/admin@xe as sysdba' schemas=test dumpfile=test_%mydate%-%mytime%.dump logfile=test_%mydate%-%mytime%.log
#===========================================================

Import DB Backup/Dump
====================

How to Import dmp using imp utility (for Oracle 10g or Lower edition)

***import from Client Machine using Oracle Client

>imp user/password@IPAddress/Servicename

eg: >imp MyUserDB/MyUserDB123@192.168.0.1/orcl

Import in Linux: (for Oracle 10g or Lower edition)

1. At first login as Oracle user in Linux Server.

#su oracle

2. Enter the following command for changing directory:

$cd $ORACLE_HOME/bin

4. Drop user, if already exists, after login as System/Sys in sqlplus:

>drop user cascade;

If drop user fails because of any connected user session then identify the session by:

>SELECT username FROM v$session WHERE username is not null;

>SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s;

and Kill by:

5. Enter the following command for creating user(if user is not available in Oracle):

>create user (userName) identified by (password);

>grant connect, resource, [dba] to (userName) [with admin option];

[grant select_catalog_role to UserName;]

[grant create any view to UserName;]

>commit;

6. Enter the following command for restoring database:

$imp userName/password file=/oracle/home/*.dmp full=y

[N.B: /home/oracle use for this document. You can use any valid path for this.]

Update: (for Oracle 10g or Higher edition)

imp utility is ancient now, use impdp (import data pump) instead.

put your exported dump file in the following directory of the server:

..\app\oracle\admin\XE\dpdump

say the dump file name is test.dmp

>impdp userName/password@[iPAdress/serviceName] dumpfile=test.dmp

>impdp userName/password@[TNS_Name] dumpfile=test.dmp

Adminstration:

ALTER USER IDENTIFIED BY ACCOUNT UNLOCK;

GRANT dba TO ;

To Check number of Connections/Session:

SELECT * FROM V$SESSION WHERE (PROGRAM IS NOT NULL)  and (username ='MyUserDB')

SELECT count(*) FROM V$SESSION WHERE (PROGRAM IS NOT NULL)  and (username ='MyUserDB' and Status='INACTIVE')

To Kill Sessions

ALTER SYSTEM KILL SESSION 'sid,serial#';  

-- Generate scripts for bulk session killing:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';' from gv$session where username='ELIBRARY';

list all available schemas

select USERNAME from SYS.ALL_USERS

PL-SQL

--#Insert into one table from another table within same user

INSERT into t_role (select pass, userid from t_user)

--#Insert into one table from another table between diff user but within same database

INSERT into Table1 (SELECT * FROM User2.Table1 MINUS SELECT * FROM Table1)