Export full database using data pump expdp

Create a directory.

• cd /home/oracle
• mkdir -p datapump/fullexport

Connect database using SYS user

• sqlplus / as sysdba

Create a directory object

• create a directory orcl_full as '/home/oracle/datapump/fullexport';

Grant read, write privileges on directory object and a rule 'datapump_exp_full_database' to hr user

• grant read,write on directory orcl_full to hr;
• grant datapump_exp_full_database to hr;

Date-Manipulation Functions

MONTHS_BETWEEN: gives number of months between two dates
ADD_MONTHS: adds calendar months to date
NEXT_DAY: gives next day of the date specified
LAST_DAY: gives last day of the month
ROUND: round date
TRUNC: truncate date

Drop database using RMAN

For this, we, first need to connect to the target database and mount (NOT OPEN), enable restricted mode. Following are the rman commands:

RMAN> startup force mount;
RMAN> sql 'alter system enable restricted session';
RMAN> drop database including backups noprompt;

This will remove all datafiles, online redo logs, archived redo logs and control files, backup sets, image copies without any prompts.

Change an Oracle database to noarchivelog mode

Here, we will alter our database to no archive log mode.

For this,

• shutdown our database if it is running
• take our database to mount mode
• alter database to noarchivelog
• open database
• verify

Resolving lock conflict with SQL in Oracle (Killing locked session)

In this topic, we will demonstrate how row is locked when one user is updating and at the same time other user fire update sql for the same row.

For this, we will

• create two application users user1 and user2
• create role hr_employee to create session and select, update on hr.employees table.
• grant hr_employee to user1 and user2
• user1 will fire update sql for a particular row and user1 will not commit. (i.e., row will be locked)
• At the same time, user2 will fire update sql for the same row. (user2 will not be prompted any update success message)
• dba will identify the locking conflict and resolve the locking conflict by killing user1 session

Oracle Restricted Session

Sometimes we need to work on a database without any other users being logged in. In this scenario, it is possible to restrict the database session. When the database is altered to restricted mode, only users with restricted session privileges can get access to the database. Technically it is in open mode.

If database is running, alter system command is used to enable and disable restricted session as follows. We can see, select logins from v$instance will verify this:

Shutting Down an Oracle Database Instance

There are four modes of shutting down an Oracle Instance

1. NORMAL
2. TRANSACTIONAL
3. IMMEDIATE
4. ABORT