Use below script to find active sessions in oracle database. set echo off set linesize 95 set head on set feedback on col sid head “Sid” form 9999 trunc col serial# form 99999 trunc head “Ser#” col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head “Client|Machine” col program […]

You can rename a tablespace with alter tablespace  command. See the below example.     set pagesize 200 set lines 200 col file_name for a57 SQL> select file_id,file_name,tablespace_name from dba_data_files where file_id=37; FILE_ID FILE_NAME TABLESPACE_NAME ———- ——————————————————– —————————— 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING — Rename the tablespace_name from TESTING to PRODUCING; SQL> alter tablespace TESTING rename to […]

Below is the commands to drop a tablespace. 1. Drop a tablespace without removing the physical database files.   SQL> select file_name from dba_data_files where tablespace_name=’TESTING’; FILE_NAME ——————————————————————————– /home/oracle/app/oracle/oradata/cdb1/testin1.dbf SQL> drop tablespace TESTING; Tablespace dropped. SQL> select file_name from dba_data_files where tablespace_name=’TESTING’; no rows selected SQL> SQL> !ls -ltr /home/oracle/app/oracle/oradata/cdb1/testin1.dbf -rw-rw—-. 1 oracle oracle 104865792 […]

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 […]