Follow Below steps to drop a database in oracle. Shutdown the database [localhost]$ export ORACLE_SID=DBACLASS [localhost]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Aug 24 15:35:35 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Mount the database in exclusive restrict mode […]

There are scenarios where you database will be in hung state and we won’t be able to connect to the database using sysdba, even to shutdown the instance also.   In that case, we can connect using PRELIM option. This is the backdoor entry to the database. This prelim option connects to SGA, but it […]

In this demo, we will establish ssh connectivity between 2 servers called riyadh1 riyadh2   On riyadh1 : [root@riyadh1 ~]# su – oracle [oracle@riyadh1 ~]$ id uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(vboxsf) [oracle@riyadh1 ~]$ chmod 700 ~/.ssh [oracle@riyadh1 ~]$ /usr/bin/ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/oracle/.ssh/id_rsa): Enter passphrase […]

 If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline. In Oracle 12c SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name=’PRODUCING’; FILE_NAME TABLESPACE_NAME ONLINE_ ——————————————————– —————————— ——- /home/oracle/producing1.dbf PRODUCING ONLINE SQL> SQL> alter database move datafile ‘/home/oracle/producing1.dbf’ to ‘/home/oracle/app/oracle/oradata/cdb1/testin1.dbf’; Database altered. SQL> select file_name,tablespace_name,online_status from dba_data_files where […]

Use below query to get the size of a database. col “Database Size” format a20 col “Free space” format a20 col “Used space” format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size” , round(sum(used.bytes) / 1024 / 1024 / 1024 ) – round(free.p / 1024 / 1024 / […]

The syntax to kill a session in oracle database is : ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’ IMMEDIATE; EXAMPLE: First get the sid and serial# of the session; Here the session is executing the query SELECT * FROM DBACLASS; Use the below query to get the sid and serial# of this sql query.   COL SQL_TEXT […]

Below are the few methods of generating explain plan .   1.Generating explain plan for a sql query: We will generate the explain plan for the query ‘SELECT COUNT(*) FROM DBACLASS;’ — LOAD THE EXPLAIN PLAN TO PLAN_TABLE SQL> explain plan for 2 select count(*) from dbaclass; Explained. — DISPLAY THE EXPLAIN PLAN SQL> select […]