Oracle Cheats Sheet

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)


Comments

Popular posts from this blog

Sql Server Script Generator

Stored Procedure to prevent sql injection with optional parameters and server side Paging and Sorting

MS Sql Server Local DB