Like windows have recyclebin, Oracle database has also provided recyclebin, which keep all the dropped objects. When we drop a table(DROP TABLE TABLE_NAME) in the database, the tables will logically be removed, but it still exists in the same tablespace but with a prefix BIN$$. And it will not release the space also. NOTE – The […]

Though GUI is a preferable method to do client installation, But sometimes DBAs might be not to be able to xmanager to enable GUI for the server. In that case, installation using response file will be useful. In this example, we will do the installation of Oracle client 12.1.0.2 version. 1. Download Oracle client software […]

Below are the steps for applying quarterly PSU patch on Oracle 11g database. 1. Download the patch from Oracle support portal.   2. Validate patch version: opatch utility is used to apply patches to the database. So before applying patch, we need to check whether the existing opatch version is supported for the patch or […]

This article explains the steps for creating a listener using NETCA GUI utility. NOTE – > utility need to execute from XMANAGER/XSHELL(From where we can call GUI),Just like DBCA or DBUA utility. 1. Set appropriate ORACLE_HOME as per requirement( EITHER GRID HOME OR ORACLE DB BINARY HOME) export ORACLE_HOME=/oracle/app/oracle/product/12.2.0/dbhome_3 export PATH=$ORACLE_HOME/bin:$PATH 2. execute NETCA   […]

Recently Oracle has released its Oracle 18c in premise version.(previously it was only for oracle cloud). This release has lot of new features, which need to to be explored.   So let’s start with Oracle binary installation and database creation. Download the Oracle software from oracle website.(DOWNLOAD LINK)   Copy the software to your server and unzip […]

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects. GLOBAL_NAMES is either set to FALSE OR TRUE. If the value of GLOBAL_NAMES is false, then any name can be given to DB_LINK. If value is TRUE, then database link name should be same as […]

Hash partitioning is one of the partitioning method, where hash algorithm is used to distribute data equally among multiple partitions. It is usually used for large tables, where we can’t use RANGE key, and column contains lot of distinct value. EXAMPLE: TABLE WITH HASH PARTITION:(WITH PREDEFINED PARTITION_NAME) create table DBACLASS ( DBANO number(4), eDBANAME varchar2(30), […]

We all know about the database block size. Remember db_block_size. But where the redo block size is defined.??? Answer is -> Redo block size is platform specific . It will the take the default value depending upon the operating system. The easy way to find redo block size is to get trace of redolog and […]

Prior to 12c, PMON process used to handle the instance registration with listner, From oracle 12c, a new background process called lreg takes care of this instance registration, Which reduces the load from pmon process. $ ps -ef | grep lreg oracle 3536 105780 0 17:21 pts/5 00:00:00 grep lreg oracle 95761 1 0 Oct26 […]

Below script can be used to find the active transactions in the oracle database. col name format a10 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti ‘Active transactions’ select s.sid,username,t.start_time, r.name, t.used_ublk “USED BLKS”, decode(t.space, ‘YES’, ‘SPACE TX’, decode(t.recursive, ‘YES’, ‘RECURSIVE TX’, decode(t.noundo, ‘YES’, ‘NO UNDO […]