Index

Lab Manual | Database Administration Essentials | 2023-24

Enrollment No: Name:

No. Practical Title Page No Due Date Submission Evaluation* Teacher’s

Date Signature with
Practical Viva Date

1. Basic startup and sutdown

commands.

2. Managing tablespace and

datafiles.

3. Managing user, privileges

and roles.

*Journal completion and timely submission are the pre-requisites for any practical evaluation.
 Babu Madhav Institute of Information Technology

Course Code: IT5017 | Course Title: Database Administration Essentials

Course Credits: 04 Total Hours: 48 [Lectures: 04, Tutorial: 00, Practical: 02]
Prerequisites: Database Management Systems, Relational Database Management Systems
Course Objectives: To make the student acquaint with the internal operations and architecture of
database server, concurrency management, server configuration, network
architecture, database security and auditing, database performance, backup, and
recovery.
Programme  PO1: Knowledge: Apply the fundamental knowledge of information technology
Outcomes: along with analytical, problem-solving and designing. Also to provide
communication skill for life-long learning in chosen field.
 PO2: Problem Analysis and Solution: Identify, Analyse and provide the solution
for emerging real world problems with the help of theoretical and practical
understanding of tools and technologies.
 PO3: Core Competence: To cultivate professionalism, ethics, and aesthetic to
become competent leader to serve the community.
 PO4: Preparation: To Prepare the student for higher studies, research and
development and social upliftment. Also to provides skills which help students
to work and recognized themselves as an individual and as a team player.
Course Outcomes:  CO1: Understand database architecture and analyse the process of server,
instances and storage structure
 CO2: Manage database storage and concurrency
 CO3: Know the concept of network architecture
 CO4: Manage users and logs including the security aspects as well as the
parameters related to the log
 CO5: Manage database performance and database tuning
 CO6: Acquaint with different types of backup and recovery techniques.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 2

 Babu Madhav Institute of Information Technology

Practical List - Course Outcomes Matrix

P# Practical Title CO1 CO2 CO3 CO4 CO5 CO6

1. Basic startup and shutdown command. √ √
2. Managing tablespace and datafiles. √ √
3. Managing user, privileges and roles. √

Tools and Technology Oracle 12c

Computing Processor: Intel® Core™ i5 Processors
Environment: Memory: 16 GB / 12 GB
Learning Resources:
Instructions for Lab Continuous assessment of the student lab work and journal in the prescribed
Teachers: format must be followed.
Instructions for Lab 1. Do not disturb machine Hardware / Software Setup.
Students: 2. All the students should sit according to their roll numbers starting from their
left to right.
3. Do not change the terminal on which you are working without lab teacher’s
permission.
4. Refer the learning resources related to the programs to be implemented in the
lab.
5. Given Journal / paper work must be finished before you enter in the lab.
6. Strictly observe the instructions given by the teacher/Lab Instructor.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 3

 Babu Madhav Institute of Information Technology

Practical List

Lab Manual | Database Administration Essentials| 2023-24

Practical No: 1 Basic startup and shutdown commands.

Objective(s): To study the Basic startup and shutdown commands.

Pre-requisite: Familiarity with SQL.

Keywords/ STARTUP, SHUTDOWN, MOUNT, OPEN, CLOSE

Concepts:

Solution: Must contain code and output.

Nature of Handwritten
submission:

Learning Recourses:

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 4

 Babu Madhav Institute of Information Technology

Start Oracle Database

Summary: in this tutorial, you will learn how to use the Oracle STARTUP command to start an Oracle
Database instance.
To start up a database instance, you use the STARTUP command:
STARTUP
Code language: SQL (Structured Query Language) (sql)
When the Oracle Database starts an instance, it goes through the following stages: NOMOUNT, MOUNT,
and OPEN.

The STARTUP command allows you to control the stage of the database instance.
1) NOMOUNT stage
In the NOMOUNT stage, Oracle carries the following steps:
 First, search for a server parameter file in the default location. You can override the default
behavior by using the SPFILE or PFILE parameters in the STARTUP command.
 Next, read the parameter file to get the values of the initialization parameters.
 Then, allocate the system global area (SGA) based on the initialization parameter settings.
 After that, start the Oracle background processes such as SMON, PMON, and LGWR.
 Finally, open the alert log and trace files and record all explicit parameters to the alert log in the
valid parameter syntax.
At the NOMOUNT stage, Oracle does not associate the database with the instance.
2) MOUNT stage
In the MOUNT stage, Oracle associates a database with an instance. In other words, the instance mounts
the database.
The instance carries the following steps to mount a database:
 First, get the name of the database control files specified in the CONTROL_FILE initialization
parameter.
 Second, open the control files.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 5

 Babu Madhav Institute of Information Technology

 Third, find the name of data files and the online redo log files.
When a database is mounted, the database is only available to database administrators, not all users.
3) OPEN stage
In the OPEN stage, Oracle performs the following actions:
 First, open the online data files in tablespaces other than the undo tablespaces.
 Then, select an undo tablespace. The instance uses default undo tablespace if an undo tablespace is
specified in the UNDO_TABLESPACE initialization parameter. Otherwise, it will select the first
available undo tablespace.
 Finally, open the online redo log files.
When Oracle opens a mounted database, the database is available for normal operations.
The following picture illustrates the Oracle database startup process:

Oracle STARTUP command

The basic syntax of the STARTUP command is as follows:
STARTUP;
Code language: SQL (Structured Query Language) (sql)
It is equivalent to start the database instance in the OPEN stage:
STARTUP OPEN;
Code language: SQL (Structured Query Language) (sql)
If you want to start the database instance in a specific stage, you can use the following syntax:
STARTUP stage;
Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 6

 Babu Madhav Institute of Information Technology

For example, to start up a database instance in the NOMOUNT stage, you use the following command:
STARTUP NOMOUNT;
Code language: SQL (Structured Query Language) (sql)
To bring the database to the next stage, you use the ALTER DATABASE statement. For example, this
statement brings the database from the NOMOUNT to the MOUNT stage:
ALTER DATABASE MOUNT;
Code language: SQL (Structured Query Language) (sql)
Oracle STARTUP command example
First, launch the SQL*Plus program and log in to the Oracle Database as the SYS user.
Second, issue the SHUTDOWN IMMEDIATE command to shut down the database:
shutdown immediate;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
Database closed.
Database dismounted.
ORACLE instance shut down.
Code language: SQL (Structured Query Language) (sql)
Third, start the database instance at the OPEN stage:
startup
Code language: SQL (Structured Query Language) (sql)
Here is the output:

Fourth, shut down the instance again:

shutdown immediate;

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 7

 Babu Madhav Institute of Information Technology

Code language: SQL (Structured Query Language) (sql)

Fifth, start the database instance at the MOUNT state:
startup mount;
Code language: SQL (Structured Query Language) (sql)
The output is:
ORACLE instance started.

Total System Global Area 2550136832 bytes

Fixed Size 3835304 bytes
Variable Size 738200152 bytes
Database Buffers 1795162112 bytes
Redo Buffers 12939264 bytes
Code language: SQL (Structured Query Language) (sql)
Sixth, check the current status of the database instance by querying the v$instance view:
SELECT
instance_name,
status
FROM
v$instance;
Code language: SQL (Structured Query Language) (sql)
Output:
INSTANCE_NAME STATUS
---------------- ------------
orcl MOUNTED
Code language: SQL (Structured Query Language) (sql)
Seventh, bring the database to the OPEN stage by using the ALTER DATABASE command:
ALTER DATABASE OPEN;
Code language: SQL (Structured Query Language) (sql)
Output:
Database altered.
Code language: SQL (Structured Query Language) (sql)
Finally, check the status of the database by executing the following statement:
SELECT
instance_name,

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 8

 Babu Madhav Institute of Information Technology

status
FROM
v$instance;
Code language: SQL (Structured Query Language) (sql)
Now, the database is open and available for normal operations.
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN

Oracle SHUTDOWN
Summary: in this tutorial, you will learn how to use the Oracle SHUTDOWN statement to shut down the
Oracle Database.

Use the SHUTDOWN IMMEDIATE command to shut down the Oracle Database gracefully:
SHUTDOWN IMMEDIATE
Code language: SQL (Structured Query Language) (sql)
Introduction to the Oracle SHUTDOWN statement
To shut down a currently running Oracle Database instance, you use the SHUTDOWN command as follows:
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Code language: SQL (Structured Query Language) (sql)
Let’s examine each option of the SHUTDOWN command.
SHUTDOWN NORMAL
The SHUTDOWN NORMAL option waits for the current users to disconnect from the database before
shutting down the database. The database instance will not accept any further database
connection. The SHUTDOWN NORMAL does not require an instance recovery on the next database startup.
The NORMAL is the default option if you don’t explicitly specify any option.
Therefore SHUTDOWN and SHUTDOWN NORMAL commands have the same effect.
The SHUTDOWN or SHUTDOWN NORMAL is not really practical because you practically cannot wait for all
users to come back to their desks and disconnect from the database.
SHUTDOWN TRANSACTIONAL
The SHUTDOWN TRANSACTIONAL waits for all uncommitted transactions to complete before shutting down
the database instance. This saves the work for all users without requesting them to log off.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 9

 Babu Madhav Institute of Information Technology

The database instance also does not accept any new transaction after a SHUTDOWN TRANSACTIONAL .
When completing all transactions, the database instance disconnects all the currently connected users from
the database and shuts down.
The SHUTDOWN TRANSACTIONAL does not require any instance recovery procedure on the next database
startup.
The optional LOCAL mode waits for only local transactions to complete, not all the transactions. Then it
shuts down local instance. This option is useful in some cases e.g., a scheduled outage maintenance.
SHUTDOWN ABORT
The SHUTDOWN ABORT is not recommended and only used on some occasions. The SHUTDOWN
ABORT has a similar effect as you unplug the power of the server. The database will be in an inconsistent
state. Therefore, you should never use the SHUTDOWN ABORT command before backing up the database. If
you try to do so, you may not be able to recover the backup.
It is recommended to use the SHUTDOWN ABORT only when you want to shut down the database
instantaneously. For example, if you know a power shutdown is going to happen in a minute or you
experience some problems when starting up a database instance.
The SHUTDOWN ABORT proceeds with the fastest possible shutdown of the database. However, it requires
instance recovery on the next database startup.
SHUTDOWN IMMEDIATE
The SHUTDOWN IMMEDIATE is the most common and practical way to shut down the Oracle database.
The SHUTDOWN IMMEDIATE does not wait for the current users to disconnect from the database or current
transactions to complete.
During the SHUTDOWN IMMEDIATE, all the connected sessions are disconnected immediately, all
uncommitted transactions are rolled back, and the database completely shuts down.
After issuing the SHUTDOWN IMMEDIATE statement, the database will not accept any new connection. The
statement will also close and dismount the database.
Unlike the SHUTDOWN ABORT option, the SHUTDOWN IMMEDIATE option does not require an instance
recovery on the next database startup.
The following table illustrates the differences between the shutdown modes:
Shutdown Modes A I T N

Allow new connection No No No No

Wait until all current sessions end No No No Yes

Wait until all current transactions end No No Yes Yes

Force a checkpoint and close files No Yes Yes Yes

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 10

 Babu Madhav Institute of Information Technology

Shutdown Modes:
 A = ABORT
 I = IMMEDIATE
 T = TRANSACTIONAL
 N = NORMAL
Notes
To issue the SHUTDOWN statement, you must connect to the database as SYSDBA, SYSOPER, SYSBACKUP,
or SYSDG. If the current database is a pluggable database, the SHUTDOWN statement will close the
pluggable database only. The consolidated instance will continue to run. On the other hand, if the current
database is a CDB, the SHUTDOWN statement will close the CDB instance.
Oracle SHUTDOWN statement example
First, launch SQL*Plus:
> sqlplus
Code language: SQL (Structured Query Language) (sql)
Second, log in to the Oracle database using the SYS user:
Enter user-name: sys as sysdba
Enter password: <sys_password>
Code language: SQL (Structured Query Language) (sql)
Third, check the current status of the Oracle instance:
SQL> select instance_name, status from v$instance;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
Code language: SQL (Structured Query Language) (sql)
Fourth, issue the SHUTDOWN IMMEDIATE command:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the Oracle SHUTDOWN statement to shut down the Oracle
Database.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 11

 Babu Madhav Institute of Information Technology

PROBLEM STATEMENTS

SN Problem Statements

1. When the Oracle Database starts an instance, it goes through the 3 stages. Describe the operations
performed in all three stages giving diagram.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 12

 Babu Madhav Institute of Information Technology

2. What is the default stage of STARTUP command?

3. Start an Oracle instance with default stage.

4. Start an Oracle instance with NOMOUNT stage.

5. Start an Oracle instance with MOUNT stage.

6. Start an Oracle instance with OPEN stage.

7. Give instruction for normal shutdown to oracle instance.

8. Give instruction for immediate shutdown to oracle instance.

9. What is the difference between normal and immediate shutdown?

10. An instance is started with NOMOUNT option. How do you bring the database into mount stage?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 13

 Babu Madhav Institute of Information Technology

11. An instance is started with MOUNT option. How do you bring the database into open stage?

12. How do you close a database?

13. How do you dismount a database?

14. List the name and the current status of the database instance.

Reference questions for Viva:

Multiple Choice Questions:

1. Which command is used to start the Oracle database instance?

a) STARTUP
b) SHUTDOWN
c) ALTER SYSTEM
d) CREATE DATABASE

2. Which command is used to shut down the Oracle database instance?

a) STARTUP
b) SHUTDOWN
c) ALTER SYSTEM
d) CREATE DATABASE

3. Which command is used to start the Oracle listener?

a) STARTUP LISTENER
b) SHUTDOWN LISTENER
c) ALTER SYSTEM SET LISTENER
d) CREATE LISTENER

4. Which command is used to shut down the Oracle listener?

a) STARTUP LISTENER
b) SHUTDOWN LISTENER
c) ALTER SYSTEM SET LISTENER

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 14

 Babu Madhav Institute of Information Technology

d) CREATE LISTENER

5. Which command is used to start the Oracle Enterprise Manager Database Control?
a) EMCTL START DBCONTROL
b) EMCTL STOP DBCONTROL
c) EMCTL START AGENT
d) EMCTL STOP AGENT

Answers:
1. STARTUP
2. SHUTDOWN
3. STARTUP LISTENER
4. SHUTDOWN LISTENER
5. EMCTL START DBCONTROL

Short Question Answer

1. What command is used to start an Oracle Database instance?

The STARTUP command is used to start an Oracle Database instance.

2. What are the stages of the Oracle Database startup process?

The stages of the Oracle Database startup process are NOMOUNT, MOUNT, and OPEN.

3. What happens during the NOMOUNT stage of the startup process?

During the NOMOUNT stage, Oracle searches for a server parameter file, reads the parameter
file to get initialization parameter values, allocates the system global area (SGA), starts
background processes, and opens the alert log and trace files.

4. How does Oracle associate a database with an instance during the MOUNT stage?
During the MOUNT stage, Oracle associates a database with an instance by getting the name of
the control files, opening the control files, and finding the data files and online redo log files.

5. What actions are performed during the OPEN stage of the startup process?
During the OPEN stage, Oracle opens the online data files, selects an undo tablespace, and opens
the online redo log files.

6. How can you start the database instance in a specific stage?

You can start the database instance in a specific stage by using the STARTUP command followed
by the desired stage keyword (e.g., STARTUP NOMOUNT or STARTUP MOUNT).

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 15

 Babu Madhav Institute of Information Technology

7. What command is used to shut down the Oracle Database gracefully?

The SHUTDOWN IMMEDIATE command is used to shut down the Oracle Database gracefully.

8. What is the default option for the SHUTDOWN command?

The default option for the SHUTDOWN command is NORMAL.

9. What does the SHUTDOWN ABORT option do?

The SHUTDOWN ABORT option shuts down the database instantaneously, similar to unplugging
the power of the server. It requires instance recovery on the next database startup.

10. How does the SHUTDOWN IMMEDIATE option differ from the SHUTDOWN ABORT option?
The SHUTDOWN IMMEDIATE option shuts down the database immediately, disconnects all
sessions, rolls back uncommitted transactions, and does not require instance recovery on the
next startup.

11. What privileges are required to issue the SHUTDOWN statement?

To issue the SHUTDOWN statement, you need to connect to the database as SYSDBA, SYSOPER,
SYSBACKUP, or SYSDG.

12. What does the SHUTDOWN statement do when the current database is a pluggable database?
If the current database is a pluggable database, the SHUTDOWN statement will close the
pluggable database only. The consolidated instance will continue to run.

13. How can you check the current status of the Oracle instance?
You can check the current status of the Oracle instance by querying the v$instance view.

14. What is the purpose of the STARTUP command in Oracle?

The STARTUP command is used to start an Oracle Database instance and control the stage of the
instance during the startup process.

15. What is the purpose of the SHUTDOWN command in Oracle?

The SHUTDOWN command is used to shut down the Oracle Database and gracefully terminate
the database instance.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 16

 Babu Madhav Institute of Information Technology

Practical No: 2 Managing tablespace and datafiles.

Objective(s): To study the logical and physical database.

Pre-requisite: Familiarity with SQL.

Keywords/ TABLESPACE, SEGMENT, EXTENT, BLOCK, DATAFILE

Concepts:

Solution: Must contain code and output.

Nature of Handwritten
submission:

Learning Recourses:

Oracle Tablespace
Summary: in this tutorial, you will learn about the Oracle tablespace and how Oracle uses tablespaces to
logically store the data in the database.
What is an Oracle Tablespace
Oracle divides a database into one or more logical storage units called tablespaces.
Each tablespace consists of one or more files called datafiles. A datafile physically stores the data objects of
the database such as tables and indexes on disk.
In other words, Oracle logically stores data in the tablespaces and physically stores data in datafiles
associated with the corresponding tablespaces.
The following picture illustrates the relationship between a database, tablespaces, and datafiles:

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 17

 Babu Madhav Institute of Information Technology

By using tablespaces, you can perform the following operations:

 Control the storage size allocated for the database data.
 Grant specific space quotas to the database users.
 Control the availability of data by taking tablespaces online or offline (more on this later).
 Improve the performance of the database by allocating data storage across devices.
 Perform partial database backup or recovery.
Default tablespaces in Oracle
Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.
 The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables.
And you should not store any object in these tablespaces.
 The USERS tablespace is helpful for ad-hoc users.
 The UNDOTBS1 holds the undo data.
 The TEMP is the temporary tablespace which is used for storing intermediate results of sorting,
hashing, and large object processing operations.
Online and Offline Tablespaces
A tablespace can be online or offline. If a tablespace is offline, you cannot access data stored in it. On the
other hand, if a tablespace is online, its data is available for reading and writing.

Note that the SYSTEM tablespace must always be online because it contains the data dictionary that must be
available to Oracle.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 18

 Babu Madhav Institute of Information Technology

Normally, a tablespace is online so that its data is available to users. However, you can take a tablespace
offline to make data inaccessible to users when you update and maintain the applications.
In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. Any
attempt to access data in offline tablespace will result in an error.
Read-Only Tablespaces
The read-only tablespaces allow Oracle to avoid performing backup and recovery of large, static parts of a
database. Because Oracle doesn’t update the files of a read-only tablespace, you can store the files on the
read-only media.
Oracle allows you to remove objects such as tables and indexes from a read-only tablespace. However, it
does not allow you to create or alter objects in a read-only tablespace.
When you create a new tablespace, it is in the read-write mode. To change a tablespace to read-only
tablespace, you use the ALTER TABLESPACE command with the READ ONLY option.

Oracle CREATE TABLESPACE

Summary: in this tutorial, you will learn how to use the Oracle CREATE TABLESPACE statement to create a
new tablespace in a database.
Introduction to the CREATE TABLESPACE statement
The CREATE TABLESPACE statement allows you to create a new tablespace. The following illustrates how to
create a new tablespace named tbs1 with size 1MB:
CREATE TABLESPACE tbs1
DATAFILE 'tbs1_data.dbf'
SIZE 1m;
Code language: SQL (Structured Query Language) (sql)
In this statement:
 First, specify the name of the tablespace after the CREATE TABLESPACE keywords. In this example,
the tablespace name is tbs1.
 Second, specify the path to the data file of the tablespace in the DATAFILE clause. In this case, it
is tbs1.dbf. Note that you can use the datafile full path.
 Third, specify the size of the tablespace in the SIZE clause. In this example, 1m stands for 1MB, which
is quite small.
Once the tablespace is created, you can find its information by querying data from the dba_data_files view:
SELECT
tablespace_name,

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 19

 Babu Madhav Institute of Information Technology

file_name,
bytes / 1024/ 1024 MB
FROM
dba_data_files;
Code language: SQL (Structured Query Language) (sql)
Here are all the tablespaces in the current database:

The CREATE TABLESPACE is quite complex with many options, you can find more information from the
Oracle CREATE TABLESPACE page.
Tablespaces and CREATE TABLE statement
When you create a new table, Oracle automatically places the table in the default tablespace of
the user which you use to create the table. However, you can explicitly specify the tablespace to which the
table belongs as shown in the following query:
CREATE TABLE table_name(
...
)
TABLESPACE tablespace_name;
Code language: SQL (Structured Query Language) (sql)
Note that you must have privilege on the tablespace that you specify in the CREATE TABLE statement.
Consider the following example.
First, create a new table called t1 whose tablespace is tbs1:
CREATE TABLE t1(
id INT GENERATED ALWAYS AS IDENTITY,
c1 VARCHAR2(32)
) TABLESPACE tbs1;
Code language: SQL (Structured Query Language) (sql)
Second, insert 10,000 rows into the t1 table:
BEGIN
FOR counter IN 1..10000 loop
INSERT INTO t1(c1)
VALUES(sys_guid());

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 20

 Babu Madhav Institute of Information Technology

END loop;
END;
/
Code language: SQL (Structured Query Language) (sql)
Third, check the free space of the tbs1 tablespace by querying from the dba_free_space view:
SELECT
tablespace_name,
bytes / 1024 / 1024 MB
FROM
dba_free_space
WHERE
tablespace_name = 'TBS1';
Code language: SQL (Structured Query Language) (sql)

Fourth, insert 10,000 rows into the t1 table, Oracle will issue an error due to insufficient storage in the
tablespace:
BEGIN
FOR counter IN 1..10000 loop
INSERT INTO t1(c1)
VALUES(sys_guid());
END loop;
END;
/
Code language: SQL (Structured Query Language) (sql)
Here is the error message:
ORA-01653: unable to extend table OT.T1 by 8 in tablespace TBS1
Code language: SQL (Structured Query Language) (sql)
To fix this, you can resize the tablespace using the following ALTER DATABASE statement:
ALTER DATABASE
DATAFILE 'tbs1.dbf'
RESIZE 10m;
Code language: SQL (Structured Query Language) (sql)
If you insert 10,00 rows into the t1 table again, it should work.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 21

 Babu Madhav Institute of Information Technology

The second way to avoid this issue, when creating a new tablespace, you can use the AUTOEXTEND ON clause
as follows:
CREATE TABLESPACE tbs1
DATAFILE 'tbs1.dbf'
SIZE 1m
AUTOEXTEND 20m;
Code language: SQL (Structured Query Language) (sql)

Oracle DROP TABLESPACE

Summary: in this tutorial, you will learn how to remove a tablespace from the database by using the
Oracle DROP TABLESPACE statement.
Introduction to Oracle DROP TABLESPACE statement
The DROP TABLESPACE allows you to remove a tablespace from the database. Here is the basic syntax of
the DROP TABLESPACE statement:
DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS [AND | KEEP] DATAFILES]
[CASCADE CONSTRAINTS];
Code language: SQL (Structured Query Language) (sql)
In this syntax:
 First, specify the name of the tablespace that you want to drop after the DROP
TABLESPACE keywords.
 Second, use the INCLUDE CONTENTS to delete all contents of the tablespace. If the tablespace has
any objects, you must use this option to remove the tablespace. Any attempt to remove a tablespace
that has objects without specifying the INCLUDING CONTENTS option will result in an error.
 Third, use AND DATAFILES option to instruct Oracle to delete the datafiles of the tablespace and KEEP
DATAFILES option to leave the datafiles untouched.
 Fourth, if the tablespace that has objects such as tables whose primary keys are referenced
by referential integrity constraints from tables outside the tablespace, you must use the CASCADE
CONSTRAINTS option to drop these constraints. If you omit the CASCACDE CONSTRAINTS clause in
such situations, Oracle returns an error and does not remove the tablespace.
You can use the DROP TABLESPACE to remove a tablespace regardless of whether it is online and offline.
However, it’s good practice to take the tablespace offline before removing it to ensure that no sessions are
currently accessing any objects in the tablespace.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 22

 Babu Madhav Institute of Information Technology

Note that you cannot drop the SYSTEM tablespace and only can drop the SYSAUX tablespace when you
started the database in the MIGRATE mode.

You need to have the DROP TABLESPACE system privilege to execute the DROP TABLESPACE statement. To
drop the SYSAUX tablespace, you need to have the SYSDBA system privilege.
Oracle DROP TABLESPACE statement examples
Let’s take some examples of using the DROP TABLESPACE statement.
1) Using Oracle DROP TABLESPACE to remove an empty tablespace example
First, create a new tablespace named tbs1:
CREATE TABLESPACE tbs1
DATAFILE 'tbs1_data.dbf'
SIZE 10m;
Code language: SQL (Structured Query Language) (sql)
Second, use the DROP TABLESPACE to remove the tbs1 tablespace:
DROP TABLESPACE tbs1;
Code language: SQL (Structured Query Language) (sql)
2) Using Oracle DROP TABLESPACE to remove a non-empty tablespace example
First, create a new tablespace named tbs2:
CREATE TABLESPACE tbs2
DATAFILE 'tbs2_data.dbf'
SIZE 5m;
Code language: SQL (Structured Query Language) (sql)
Second, create a new table t2 in the tablespace tbs2:
CREATE TABLE t2 (
c1 INT
) TABLESPACE tbs2;
Code language: SQL (Structured Query Language) (sql)
Third, use the DROP TABLESPACE statement to drop the tbs2 tablespace:
DROP TABLESPACE tbs2;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
ORA-01549: tablespace not empty, use `INCLUDING CONTENTS` option
Code language: SQL (Structured Query Language) (sql)
To drop the tbs2 tablespace, we need to use the INCLUDING CONTENTS option:
DROP TABLESPACE tbs2

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 23

 Babu Madhav Institute of Information Technology

INCLUDING CONTENTS;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following message indicating that the tablespace has been dropped:
Tablespace dropped.
Code language: SQL (Structured Query Language) (sql)
3) Using Oracle DROP TABLESPACE to remove a tablespace whose tables are referenced by referential
constraints
First, create two tablespaces named tbs3 and tbs4:
CREATE TABLESPACE tbs3
DATAFILE 'tbs3_data.dbf'
SIZE 5m;

CREATE TABLESPACE tbs4

DATAFILE 'tbs4_data.dbf'
SIZE 5m;
Code language: SQL (Structured Query Language) (sql)
Next, create a new table in the tbs3 tablespace:
CREATE TABLE t3(
c1 INT PRIMARY KEY
) TABLESPACE tbs3;
Code language: SQL (Structured Query Language) (sql)
Then, create a new table in the tbs4 tablespace:
CREATE TABLE t4(
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
FOREIGN KEY(c2) REFERENCES t3(c1)
) TABLESPACE tbs4;
Code language: SQL (Structured Query Language) (sql)
After that, drop the tablespace tbs3:
DROP TABLESPACE tbs3
INCLUDING CONTENTS;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
ORA-02449: unique/primary keys in table referenced by foreign keys

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 24

 Babu Madhav Institute of Information Technology

Code language: SQL (Structured Query Language) (sql)

Finally, use the DROP TABLESPACE that includes the CASCADE CONSTRAINTS option to drop the tablespace:
DROP TABLESPACE tbs3
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
Code language: SQL (Structured Query Language) (sql)
It worked as expected.

Oracle Extend Tablespace

Summary: in this tutorial, you will learn how to extend the size of a tablespace in the Oracle Database.
When tablespaces of the database are full, you will not able to add or remove data on these tablespaces
anymore.
There are a few ways you can extend a tablespace.
Extending a tablespace by adding a new datafile
The first way to extend a tablespace is to add a new datafile by using the ALTER TABLESPACE statement:
ALTER TABLESPACE tablespace_name
ADD DATAFILE 'path_to_datafile'
SIZE size;
Code language: SQL (Structured Query Language) (sql)
If you use the AUTOEXTEND ON clause, Oracle will automatically extend the size of the datafile when needed:
ALTER TABLESPACE tablespace_name
ADD DATAFILE 'path_to_datafile'
SIZE size
AUTOEXTEND ON;
Code language: SQL (Structured Query Language) (sql)
Let’s see the following example.
First, create a new tablespace called tbs10 with the size 1MB:
CREATE TABLESPACE tbs10
DATAFILE 'tbs10.dbf' SIZE 1m;
Code language: SQL (Structured Query Language) (sql)
Next, create a new table t1 whose tablespace is tbs10:
CREATE TABLE t1(id INT PRIMARY KEY)
TABLESPACE tbs10;
Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 25

 Babu Madhav Institute of Information Technology

Then, insert 1,000,000 rows into the t1 table:

BEGIN
FOR counter IN 1..1000000 loop
INSERT INTO t1(id)
VALUES(counter);
END loop;
END;
/
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
ORA-01653: unable to extend table OT.T1 by 8 in tablespace TBS10
Code language: SQL (Structured Query Language) (sql)
So the tablespace tbs10 does not have enough space for the 1 million rows.
After that, use the ATLER TABLESPACE statement to add one more datafile whose size is 10MB with
the AUTOEXTEND ON option:
ALTER TABLESPACE tbs10
ADD DATAFILE 'tbs10_2.dbf'
SIZE 10m
AUTOEXTEND ON;
Code language: SQL (Structured Query Language) (sql)
Finally, insert 1 million rows into the t1 table. It should work now. This query returns the number of rows
from the t1 table:
SELECT count(*) FROM t1;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
COUNT(*)
----------
1000000
Code language: SQL (Structured Query Language) (sql)
Extending a tablespace by resizing the datafile
Another way to extend a tablespace is to resize the data file by using the the ALTER DATABASE RESIZE
DATAFILE statement:
ALTER DATABASE
DATAFILE 'path_to_datafile'

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 26

 Babu Madhav Institute of Information Technology

RESIZE size;
Code language: SQL (Structured Query Language) (sql)
Consider the following example.
First, create a new tablespace called tbs11:
CREATE TABLESPACE tbs11
DATAFILE 'tbs11.dbf'
SIZE 1m;
Code language: SQL (Structured Query Language) (sql)
Next, create a new table called t2 that uses tbs11 as the tablespace:
CREATE TABLE t2(
c INT PRIMARY KEY
) TABLESPACE tbs11;
Code language: SQL (Structured Query Language) (sql)
Then, query the size of the tablespace tbs11:
SELECT
tablespace_name,
bytes / 1024 / 1024 MB
FROM
dba_free_space
WHERE
tablespace_name = 'TBS11';
Code language: SQL (Structured Query Language) (sql)
The following illustrates the output:
TABLESPACE_NAME MB
--------------- ----------
TBS11 .9375
Code language: SQL (Structured Query Language) (sql)
After that, use the ALTER DATABASE to extend the size of the datafile of the tablespace to 15MB:
ALTER DATABASE
DATAFILE 'tbs11.dbf'
RESIZE 15m;
Code language: SQL (Structured Query Language) (sql)
Finally, query the size of the tbs11 tablespace:
SELECT

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 27

 Babu Madhav Institute of Information Technology

tablespace_name,
bytes / 1024 / 1024 MB
FROM
dba_free_space
WHERE
tablespace_name = 'TBS11';
Code language: SQL (Structured Query Language) (sql)
Here is the output:
TABLESPACE_NAME MB
---------------- ----------
TBS11 14.9375
Code language: SQL (Structured Query Language) (sql)
As you can see, the size of the tablespace tbs11 has been extended to 15MB.
Note that Oracle does not allow you to add a datafile to a bigfile tablespace, therefore, you only can
use ALTER DATABASE DATAFILE RESIZE command.

Oracle Temporary Tablespace

Summary: in this tutorial, you will learn about the Oracle temporary tablespaces and how to manipulate the
temporary tablespaces effectively.
Introduction to Oracle temporary tablespaces
A temporary tablespace, as its name implied, stores the temporary data that only exists during the database
session.
Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations which do not fit in
memory. On top of this, Oracle stores temporary tables, temporary indexes, temporary B-trees, and
temporary LOBs in temporary tablespaces.
By defeault, Oracle creates a single temporary tablespace named TEMP for each new Oracle Database
installation. This TEMP tablespace can be shared by multiple users.
Besides the TEMP default temporary tablespace, you can create addition temporary tablespaces and assign
them to a user using the CREATE USER or ALTER USER statement.
Oracle default temporary tablespace
When you create a user without specifying a temporary tablespace, Oracle assigns the default temporary
tablespace TEMP to user. If you want to change the default temporary tablespace, you can use the following
command:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 28

 Babu Madhav Institute of Information Technology

Code language: SQL (Structured Query Language) (sql)

To find the current default temporary tablespace, you execute the following statement:
SELECT
property_name,
property_value
FROM
database_properties
WHERE
property_name='DEFAULT_TEMP_TABLESPACE';
Code language: SQL (Structured Query Language) (sql)
Here is the output:

Viewing space allocation in a temporary tablespace

This statement returns the space allocated and free space in a temporary tablespace:
SELECT * FROM dba_temp_free_space;
Code language: SQL (Structured Query Language) (sql)
Creating a temporary tablespace
To create a new temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement:
CREATE TEMPORARY TABLESPACE tablespace_name
TEMPFILE 'path_to_file'
SIZE size;
Code language: SQL (Structured Query Language) (sql)
Oracle default tablespace examples
First, create a new temporary tablespace named temp2 with the size of 100MB:
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'temp2.dbf'
SIZE 100m;
Code language: SQL (Structured Query Language) (sql)
Next, find all temporary tablespaces in the current Oracle Database:
SELECT
tablespace_name,
file_name,
bytes/1024/1024 MB,

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 29

 Babu Madhav Institute of Information Technology

status
FROM
dba_temp_files;
Code language: SQL (Structured Query Language) (sql)

Then, check which tablespace is the default temporary tablespace:

SELECT
property_name,
property_value
FROM
database_properties
WHERE
property_name='DEFAULT_TEMP_TABLESPACE';
Code language: SQL (Structured Query Language) (sql)

After that, change the default temporary tablespace name to temp2:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;Code language: SQL (Structured Query
Language) (sql)
Finally, drop the temp2 tablespace:
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
SQL Error: ORA-12906: cannot drop default temporary tablespace
You cannot drop the default temporary tablespace. To delete the temp2 tablespace as the default temporary
tablespace, you must first change the default tablespace back to the TEMP tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Code language: SQL (Structured Query Language) (sql)
And then drop the temp2 temporary tablespace:
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 30

 Babu Madhav Institute of Information Technology

PROBLEM STATEMENTS

SN Managing tablespace and datafiles.

1. List the default tablespaces created in Oracle.

2. When creating a tablespace, what three main choices a DBA has to make?

3. What are the three types of tablespace?

4. The syntax for creating a tablespace is given below. Explain the keywords given in bold letter.

CREATE

[ SMALLFILE | BIGFILE ]

TABLESPACE tablespace_name

DATAFILE 'filename'

[ SIZE integer [ K | M | G | T | P | E ] ]

AUTOEXTEND { OFF | ON [ NEXT integer [ K | M | G | T | P | E ] ]

[ MAXSIZE { UNLIMITED | } [ K | M | G | T | P | E ] } ]

MINIMUM EXTENT integer [ K | M | G | T | P | E ]

| LOGGING | NOLOGGING | FORCE LOGGING

SPACE MANAGEMENT { AUTO | MANUAL }

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 31

 Babu Madhav Institute of Information Technology

5. Create a new tablespace named tbs1 with data file D:\DBA\tbs1_data.dbf of size 1MB.

6. Create a new table table1 (Id integer) that belongs to tablespace tbs1.

7. How do you bring the tablespce tbs1 in offline mode?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 32

 Babu Madhav Institute of Information Technology

8. How do you bring the tablespce tbs1 in online mode?

9. How do you bring the tablespce tbs1 in read only mode?

10. How do you bring the tablespce tbs1 in read write mode?

11. Change the size of the datafile 'tbs1_data.dbf' from 1 M to 10 M.

12. Remove the tablespace tbs1 in such a way that all contents also get deleted along with data files.

13. Remove the tablespace tbs1 in such a way that all contents also get deleted but not the data files.

14. What happen when you attempt to remove a tablespace that has objects without specifying the
INCLUDING CONTENTS option?

15. Is it possible to remove a tablespace if it is online? Justify your answer.

16. Can you drop the SYSTEM tablespace?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 33

 Babu Madhav Institute of Information Technology

17. Create a tablespace tbs1. Create a table table1 that belongs to tablespace tbs1. Now remove
tablespace tbs1 by giving following command: DROP TABLESPACE tbs1;

What output you get?

18. Add a new data file D:\DBA\tbs1_data2.dbf of size 10MB and allow the data file to extend
automatically when it is required.

19. What happens when you create a user without specifying a temporary tablespace?

20. Create a new temporary tablespace TempTbs1.

21. Change the default temporary tablespace to TempTbs1.

22. How do you find the current default temporary tablespace?

Reference questions for Viva:

Multiple Choice Questions

1. Which command is used to create a new tablespace in Oracle?

a) CREATE TABLESPACE b) ALTER TABLESPACE c) DROP TABLESPACE d) RENAME TABLESPACE

2. Which command is used to alter the size of a tablespace in Oracle?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 34

 Babu Madhav Institute of Information Technology

a) CREATE TABLESPACE b) ALTER TABLESPACE c) DROP TABLESPACE d) RENAME TABLESPACE

3. Which command is used to drop a tablespace in Oracle?

a) CREATE TABLESPACE b) ALTER TABLESPACE c) DROP TABLESPACE d) RENAME TABLESPACE

4. Which command is used to rename a tablespace in Oracle?

a) CREATE TABLESPACE b) ALTER TABLESPACE c) DROP TABLESPACE d) RENAME TABLESPACE

5. Which command is used to add a data file to a tablespace in Oracle?

a) ADD DATAFILE b) ALTER TABLESPACE c) DROP DATAFILE d) RENAME DATAFILE

6. Which command is used to resize a data file in a tablespace in Oracle?

a) RESIZE DATAFILE b) ALTER TABLESPACE c) DROP DATAFILE d) RENAME DATAFILE

7. Which command is used to drop a data file from a tablespace in Oracle?

a) ADD DATAFILE b) ALTER TABLESPACE c) DROP DATAFILE d) RENAME DATAFILE

8. Which command is used to rename a data file in a tablespace in Oracle?

a) ADD DATAFILE b) ALTER TABLESPACE c) DROP DATAFILE d) RENAME DATAFILE

9. Which command is used to move a table from one tablespace to another in Oracle?

a) MOVE TABLE b) ALTER TABLESPACE c) MOVE DATAFILE d) RENAME TABLE

10. Which command is used to change the default tablespace for a user in Oracle?

a) ALTER USER b) ALTER TABLESPACE c) ALTER SESSION d) ALTER PROFILE

Answers:
1. a) CREATE TABLESPACE
2. b) ALTER TABLESPACE
3. c) DROP TABLESPACE
4. d) RENAME TABLESPACE
5. a) ADD DATAFILE
6. a) RESIZE DATAFILE
7. c) DROP DATAFILE
8. d) RENAME DATAFILE
9. a) MOVE TABLE
10. a) ALTER USER

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 35

 Babu Madhav Institute of Information Technology

Short Question Answers

1. What is an Oracle tablespace used for?

An Oracle tablespace is used to logically store data objects such as tables and indexes in the database.

2. How does Oracle physically store data in a tablespace?

Oracle physically stores data in a tablespace by using datafiles, which are the physical files on disk that
contain the data objects.

3. Can you control the storage size allocated for the database data using tablespaces?
Yes, tablespaces allow you to control the storage size allocated for the database data. You can specify the
size of the tablespace when creating it.

4. What are the default tablespaces in Oracle?

The default tablespaces in Oracle are SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

5. How can you extend the size of a tablespace in Oracle?

You can extend the size of a tablespace in Oracle by adding a new datafile or by resizing the existing
datafile.

6. What is a temporary tablespace used for in Oracle?

A temporary tablespace in Oracle is used to store temporary data during a database session, such as
sorting operations and temporary tables.

7. What is the purpose of a temporary tablespace in Oracle?

A temporary tablespace in Oracle is used to store temporary data that exists only during a database
session. It is primarily used for sorting operations and storing temporary tables, indexes, B-trees, and
LOBs.

8. How can you view the space allocation in a temporary tablespace?

You can view the space allocation in a temporary tablespace by querying the DBA_TEMP_FREE_SPACE
view.

9. How can you extend a tablespace in Oracle?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 36

 Babu Madhav Institute of Information Technology

There are multiple ways to extend a tablespace in Oracle. You can add a new datafile to the tablespace
using the ALTER TABLESPACE statement or resize the existing datafile using the ALTER DATABASE
statement.

10. Can you drop a default temporary tablespace in Oracle?

No, you cannot drop a default temporary tablespace in Oracle. You need to change the default temporary
tablespace to a different one before dropping it.

11. What happens if a tablespace is offline in Oracle?

When a tablespace is offline in Oracle, the data within that tablespace becomes inaccessible to users. It is
useful for performing maintenance operations or resolving issues related to the tablespace.

12. How can you remove a tablespace from the database in Oracle?
To remove a tablespace from the database in Oracle, you can use the DROP TABLESPACE statement.
However, you need to ensure that the tablespace is empty or specify the INCLUDING CONTENTS option to
remove its contents.

13. What is the purpose of the SYSTEM tablespace in Oracle?

The SYSTEM tablespace in Oracle stores the core data dictionary tables and other system-related objects.
It is a crucial component of the database and should not be used to store user data.

14. How can you check the default temporary tablespace in Oracle?
You can check the default temporary tablespace in Oracle by querying the DATABASE_PROPERTIES view
and filtering for the DEFAULT_TEMP_TABLESPACE property.

15. Can you create multiple temporary tablespaces in Oracle?

Yes, you can create multiple temporary tablespaces in Oracle using the CREATE TEMPORARY TABLESPACE
statement. This allows you to allocate different temporary storage areas for specific users or applications.

16. What is the purpose of the SYSAUX tablespace in Oracle?

The SYSAUX tablespace in Oracle is a system auxiliary tablespace used to store additional system-related
data and metadata that are not critical for basic database functionality. It helps reduce the load on the
SYSTEM tablespace and provides better manageability.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 37

 Babu Madhav Institute of Information Technology

Practical No: 3 Managing user, privileges and roles.

Objective(s): To study the user management in Oracle database.

Pre-requisite: Familiarity with SQL.

Keywords/ CREATE USER, DROP USER, ALTER USER, CREATE ROLE, GRANT, PRIVILEGES

Concepts:

Solution: Must contain code and output.

Nature of Handwritten
submission:

Learning Recourses:

Oracle CREATE USER

Summary: in this tutorial, you will learn how to use the Oracle CREATE USER statement to create a new user
in the Oracle database.

Introduction to Oracle CREATE USER statement

The CREATE USER statement allows you to create a new database user which you can use to log in to the
Oracle database.

The basic syntax of the CREATE USER statement is as follows:

CREATE USER username

IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace]
[QUOTA {size | UNLIMITED} ON tablespace]
[PROFILE profile]
[PASSWORD EXPIRE]
[ACCOUNT {LOCK | UNLOCK}];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

CREATE USER username

Specify the name of the user to be created.

IDENTIFIED BY password

Specify a password for the local user to use to log on to the database. Note that you can create an external or
global user, which is not covered in this tutorial.

DEFAULT TABLESPACE

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 38

 Babu Madhav Institute of Information Technology

Specify the tablespace of the objects such as tables and views that the user will create.

If you skip this clause, the user’s objects will be stored in the database default tablespace if available,
typically it is USERS tablespace; or the SYSTEM tablespace in case there is no database default tablespace.

QUOTA

Specify the maximum of space in the tablespace that the user can use. You can have multiple QUOTA clauses,
each for a tablespace.

Use UNLIMITED if you don’t want to restrict the size in the tablespace that user can use.

PROFILE profile

A user profile limits the database resources or password that the user cannot exceed. You can assign a profile
to a newly created user. If you skip this clause, Oracle will assign the DEFAULT profile to the user.

PASSWORD EXPIRE

Use the PASSWORD EXPIRE if you want to force the user to change the password for the first time the user
logs in to the database.

ACCOUNT {LOCK | UNLOCK}

Use ACCOUNT LOCK if you want to lock user and disable access. On the other hand, specify ACCOUNT
UNLOCK to unlock user and enable access.

To execute the CREATE USER statement, you must have the CREATE USER system privilege. Once you create
the new user, the privilege domain of the user will be empty. Therefore, if you want to the user to be able to
login to the database, you should grant the CREATE SESSION system privilege to the user.

Oracle CREATE USER examples

Let’s practice with the CREATE USER statement.

1) Using Oracle CREATE USER statement to create a new local user example

This example uses the CREATE USER statement to create a new local user named john with the
password abcd1234:

CREATE USER john IDENTIFIED BY abcd1234;

Code language: SQL (Structured Query Language) (sql)

Oracle issues the following output indicating that user john has been created successfully.

User JOHN created.

Code language: SQL (Structured Query Language) (sql)

To find a list of users with the OPEN status, you query the information from the dba_users:

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 39

 Babu Madhav Institute of Information Technology

SELECT
username,
default_tablespace,
profile,
authentication_type
FROM
dba_users
WHERE
account_status = 'OPEN';
Code language: SQL (Structured Query Language) (sql)

As you can see from the output, user john has a default tablespace as USERS, profile as DEFAULT, and log in
to the database using a PASSWORD.

Let’s use the john account to log in the database.

Launch the SQL*Plus program and enter the following information:

Enter user-name: john@pdborcl

Enter password:<john_password>
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ERROR: ORA-01045:
user JOHN lacks CREATE SESSION privilege; logon denied

To enable the user john to log in, you need to grant the CREATE SESSION system privilege to the user john by
using the following statement:

GRANT CREATE SESSION TO john;

Code language: SQL (Structured Query Language) (sql)

Now, the user john should be able to log in the database.

Enter user-name: john@pdborcl

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Code language: SQL (Structured Query Language) (sql)

2) Using Oracle CREATE USER statement to create a new local user with password expired example

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 40

 Babu Madhav Institute of Information Technology

First, use the CREATE USER statement to create a new user jane:

CREATE USER jane IDENTIFIED BY abcd1234

PASSWORD EXPIRE;
Code language: SQL (Structured Query Language) (sql)

Second, verify if the user has been created successfully:

SELECT
username,
default_tablespace,
profile,
authentication_type
FROM
dba_users
WHERE
account_status = 'OPEN';
Code language: SQL (Structured Query Language) (sql)

Third, grant the CREATE SESSION privilege to the user jane so that you can use this user to log in the Oracle
database.

GRANT CREATE SESSION TO jane;

Code language: SQL (Structured Query Language) (sql)

Finally, use the user jane to log in to the database via the SQL*plus program:

SQL> connect jane@orclpdb/abcd1234

ERROR:
ORA-28001: the password has expired
Code language: SQL (Structured Query Language) (sql)

Oracle requested for changing the password for jane, you must provide the new password and confirm it
before you can log in:

Changing password for jane

New password:<new_password>
Retype new password:<new_password>
Password changed
Connected.
SQL>

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 41

 Babu Madhav Institute of Information Technology

Oracle DROP USER

Summary: in this tutorial, you will learn how to use the Oracle DROP USER to delete a user from the database.

Introduction to Oracle DROP USER statement

The DROP USER statement allows you to delete a user from the Oracle Database. If the user has schema
objects, the DROP USER statement also can remove all the user’s schema objects along with the user.

The following illustrates the basic syntax of the DROP USER statement:

DROP USER username [CASCADE];

Code language: SQL (Structured Query Language) (sql)

In this syntax, you need to specify the user that you want to drop after the DROP USER keywords.

If the user whose schemas contain objects such as views and tables, you need to delete all schema objects of
the user first and then drop the user.

Deleting all schema objects of the users first before removing the user is quite tedious. Therefore, Oracle
provides you with the CASCADE option.

If you specify the CASCADE option, Oracle will remove all schema objects of the user before deleting the user.

If the schema objects of the dropped user are referenced by objects in other schemas, Oracle will invalidate
these objects after deleting the user.

If a table of the dropped user is referenced by materialized views in other schemas, Oracle will not drop these
materialized views. However, the materialized views can no longer be refreshed because the base table
doesn’t exist anymore.

Note that Oracle does not drop roles created by the user even after it deletes the user.

Notice that if you attempt to delete the user SYS or SYSTEM, your database will corrupt.

Oracle DROP USER statement examples

Let’s take some examples of removing a user from the database.

1) Using Oracle DROP USER to remove a user that has no schema object

First, log in to the Oracle database using the user ot using SQL*Plus:

Enter user-name: ot@pdborcl

Enter password: <user_password>
Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 42

 Babu Madhav Institute of Information Technology

Second, create a new user called foo:

CREATE USER foo IDENTIFIED BY abcd1234;

Code language: SQL (Structured Query Language) (sql)

Third, drop the user foo using the DROP USER statement:

DROP USER foo;

Code language: SQL (Structured Query Language) (sql)

You should see the following message:

User dropped.
Code language: SQL (Structured Query Language) (sql)

Because the user foo has no schema objects, you could delete it without specifying the CASCADE option.

2) Using Oracle DROP USER to delete a user that has schema objects

First, create a new user called bar and grant the CREATE SESSION and CREATE TABLE system privileges to the user:

CREATE USER bar

IDENTIFIED BY abcd1234
QUOTA 5m ON users;

GRANT
CREATE SESSION,
CREATE TABLE
TO bar;
Code language: SQL (Structured Query Language) (sql)

Second, use the user bar to log in to the Oracle database:

Enter user-name: bar@pdborcl

Enter password: <bar_password>
Code language: SQL (Structured Query Language) (sql)

Third, create a new table named t1 in the bar user’s schema:

CREATE TABLE t1(

id NUMBER PRIMARY KEY,
v VARCHAR2(100) NOT NULL
);

INSERT INTO t1(id,v)

VALUES(1,'A');
Code language: SQL (Structured Query Language) (sql)

Fourth, go back to the session of the user ot and drop user bar:

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 43

 Babu Madhav Institute of Information Technology

DROP USER bar;

Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-01940: cannot drop a user that is currently connected

Code language: SQL (Structured Query Language) (sql)

Fifth, end the user bar’s session first using the exit command:

exit
Code language: SQL (Structured Query Language) (sql)

And issue the DROP USER statement again in the user ot session:

DROP USER bar;

Code language: SQL (Structured Query Language) (sql)

Oracle issued the following message:

ORA-01922: CASCADE must be specified to drop 'BAR'

Code language: SQL (Structured Query Language) (sql)

You could not delete the user bar without specifying CASCADE because the user bar has the table t1 as a schema
object.

Seventh, use the DROP USER statement with CASCADE option to delete the user bar:

DROP USER bar CASCADE;

Code language: SQL (Structured Query Language) (sql)

Oracle could delete the user bar and also the table t1.

Oracle ALTER USER

Summary: in this tutorial, you will learn how to use the Oracle ALTER USER statement to modify the
authentication or database resource of a database user.

The ALTER USER statement allows you to change the authentication or database resource characteristics of a
database user.

Generally speaking, to execute the ALTER USER statement, your account needs to have the ALTER USER system
privilege. However, you can change your own password using the ALTER USER statement without having
the ALTER USER system privilege.

Let’s create a user named dolphin and grant the CREATE SESSION system privilege to dolphin:

CREATE USER dolphin IDENTIFIED BY abcd1234;

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 44

 Babu Madhav Institute of Information Technology

GRANT CREATE SESSION TO dolphin;

Code language: SQL (Structured Query Language) (sql)

1) Using Oracle ALTER USER statement to change the password for a user
The following example uses the ALTER USER statement to change the password for the user dolphin:

ALTER USER dolphin IDENTIFIED BY xyz123;

Code language: SQL (Structured Query Language) (sql)

Log in to the Oracle Database using the dolphin user:

Enter user-name: dolphin@pdborcl

Enter password: <dolphin password>
Code language: SQL (Structured Query Language) (sql)

The user dolphin should be able to authenticate to the Oracle Database using the new password xyz123

2) Using Oracle ALTER USER statement to lock/unlock a user

This example uses the ALTER USER statement to lock the user dolphin:

ALTER USER dolphin ACCOUNT LOCK;

Code language: SQL (Structured Query Language) (sql)

If you use the user dolphin to log in to the Oracle Database, you should see a message indicating that the user
is locked:

Enter user-name: dolphin@pdborcl

Enter password: <dolphin password>
ERROR:
ORA-28000: the account is locked
Code language: SQL (Structured Query Language) (sql)

To unlock the user dolphin, you use the following statement:

ALTER USER dolphin ACCOUNT UNLOCK;

Code language: SQL (Structured Query Language) (sql)

Now, the user dolphin should be able to log in to the Oracle Database.

3) Using Oracle ALTER USER statement to set user’s password expired

To set the password of the user dolphin expired, you use the following statement:

ALTER USER dolphin PASSWORD EXPIRE;

Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 45

 Babu Madhav Institute of Information Technology

When you use the user dolphin to log in to the database, Oracle issues a message indicating that the password
has expired and requests for the password change as follows:

Enter user-name: dolphin@orclpdb

Enter password: <dolphin password>
ERROR:
ORA-28001: the password has expired

Changing password for dolphin

New password: <new password>
Retype new password: <new password>
Password changed
Code language: SQL (Structured Query Language) (sql)

4) Using Oracle ALTER USER statement to set the default profile for a user
This statement returns the profile of the user dolphin:

SELECT
username,
profile
FROM
dba_users
WHERE
username ='DOLPHIN';
Code language: SQL (Structured Query Language) (sql)

When you create a new user without specifying a profile, Oracle will assign the DEFAULT profile to the user.

Let’s create a new user profile called ocean:

CREATE PROFILE ocean LIMIT

SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 60;
Code language: SQL (Structured Query Language) (sql)

and assign it to the user dolphin:

ALTER USER dolphin

PROFILE ocean;
Code language: SQL (Structured Query Language) (sql)

Now, the default profile of the user dolphin is ocean.

5) Using Oracle ALTER USER statement to set default roles for a user

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 46

 Babu Madhav Institute of Information Technology

Currently, the user dolphin has no assigned roles as shown in the output of the following query when
executing from the dolphin’s session:

SELECT * FROM session_roles;

Code language: SQL (Structured Query Language) (sql)

First, create a new role called rescue from the user OT‘s session:

CREATE ROLES rescue;

GRANT CREATE TABLE, CREATE VIEW TO rescue;

Code language: SQL (Structured Query Language) (sql)

Second, grant this role to dolphin:

GRANT rescue TO dolphin;

Code language: SQL (Structured Query Language) (sql)

Third, use the user dolphin to log in to the Oracle Database. The default role of the user dolphin is rescue now.

SELECT * FROM session_roles;

Code language: SQL (Structured Query Language) (sql)

Here is the output:

ROLE
---------
RESCUE
Code language: SQL (Structured Query Language) (sql)

Fourth, create another role called super and grant all privileges to this role:

CREATE ROLE super;

GRANT ALL PRIVILEGES TO super;

Code language: SQL (Structured Query Language) (sql)

Fifth, grant the role super to the user dolphin:

GRANT super TO dolphin;

Code language: SQL (Structured Query Language) (sql)

Sixth, set the default role of the user dolphin to super:

ALTER USER dolphin DEFAULT ROLE super;

Code language: SQL (Structured Query Language) (sql)

Seventh, disconnect the current session of the user dolphin and log in to the Oracle Database again. The
default role of the user dolphin should be super as shown in the output of the following query:

SELECT * FROM session_roles;

Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 47

 Babu Madhav Institute of Information Technology

The following shows the output:

ROLE
---------
SUPER

Oracle CREATE ROLE

Summary: in this tutorial, you will learn how to use the Oracle CREATE ROLE statement to create roles in the
Oracle Database.

Introduction to Oracle CREATE ROLE statement

A role is a group of privileges. Instead of granting individual privileges to users, you can group related
privileges into a role and grant this role to users. Roles help manage privileges more efficiently.

To create a new role, you use the CREATE ROLE statement. The basic syntax of the CREATE ROLE statement is as
follows:

CREATE ROLE role_name

[IDENTIFIED BY password]
[NOT IDENTIFIED]
Code language: SQL (Structured Query Language) (sql)

In this syntax:

 First, specify the name of the role that you want to create.
 Second, use IDENTIFIED BY password option to create a local role and indicate that the user, who was
granted the role, must provide the password to the database when enabling the role.
 Third, use NOT IDENTIFIED to indicate that the role is authorized by the database and the user, who was
granted this role, don’t need a password to enable the role.

After a role is created, it is empty. To grant privileges to a role, you use the GRANT statement:

GRANT {system_privileges | object_privileges} TO role_name;

Code language: SQL (Structured Query Language) (sql)

In addition, you can use the GRANT statement to grant privileges of a role to another role:

GRANT role_name TO another_role_name;

Code language: SQL (Structured Query Language) (sql)

Oracle CREATE ROLE statement examples

Let’s take some examples of using the CREATE ROLE statement.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 48

 Babu Madhav Institute of Information Technology

1) Using Oracle CREATE ROLE without a password example

First, create a new role named mdm (master data management) in the sample database:

CREATE ROLES mdm;

Code language: SQL (Structured Query Language) (sql)

Second, grant object privileges

on customers, contacts, products, product_categories, warehouses, locations, employees tables to the mdm role:

GRANT SELECT, INSERT, UPDATE, DELETE

ON customers
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON contacts
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON products
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON product_categories
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON warehouses
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON locations
TO mdm;

GRANT SELECT, INSERT, UPDATE, DELETE

ON employees
TO mdm;
Code language: SQL (Structured Query Language) (sql)

Third, create a new user named alice and grant the CREATE SESSION privilege to alice:

CREATE USER alice IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO alice;

Code language: SQL (Structured Query Language) (sql)

Fourth, log in to the database as alice:

Enter user-name: alice@pdborcl

Enter password:
Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 49

 Babu Madhav Institute of Information Technology

and attempt to query data from the ot.employees table:

SELECT * FROM ot.employees;

Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-00942: table or view does not exist

Code language: SQL (Structured Query Language) (sql)

Go back to the first session and grant alice the mdm role:

GRANT mdm TO alice;

Code language: SQL (Structured Query Language) (sql)

Go to the alice’s session and enable role using the SET ROLE statement:

SET ROLE mdm;

Code language: SQL (Structured Query Language) (sql)

To query all roles of the current user, you use the following query:

SELECT * FROM session_roles;

Code language: SQL (Structured Query Language) (sql)

Here is the role of alice:

ROLE
------
MDM
Code language: SQL (Structured Query Language) (sql)

Now, alice can manipulate data in the master data tables such as customers and employees.

2) Using Oracle CREATE ROLE to create a role with IDENTIFIED BY password example

First, create a new role named order_entry with the password xyz123:

CREATE ROLE order_entry IDENTIFIED BY xyz123;

Code language: SQL (Structured Query Language) (sql)

Next, grant object privileges on the orders and order_items tables to the order_entry role:

GRANT SELECT, INSERT, UPDATE, DELETE

ON orders
TO order_entry;

GRANT SELECT, INSERT, UPDATE, DELETE

ON order_items
TO order_entry;
Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 50

 Babu Madhav Institute of Information Technology

Then, grant the order_entry role to the user alice:

GRANT order_entry TO alice;

Code language: SQL (Structured Query Language) (sql)

After that, log in as alice and enable the order_entry role by using the SET ROLE statement:

SET ROLE
order_entry IDENTIFIED BY xyz123,
mdm;
Code language: SQL (Structured Query Language) (sql)

Finally, use the following statement to get the current roles of alice:

SELECT * FROM session_roles;

Code language: SQL (Structured Query Language) (sql)

Here are the current roles of alice:

ROLE
-------------
MDM
ORDER_ENTRY

Granting all privileges to an existing user

To grant all privileges to an existing user, you just need to use the GRANT ALL PRIVILEGES statement. For
example, the following statement grants all privileges to the user alice:

GRANT ALL PRIVILEGES to alice;

Code language: SQL (Structured Query Language) (sql)

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 51

 Babu Madhav Institute of Information Technology

PROBLEM STATEMENTS

SN Problem Statements

Managing user, privileges and roles.

1. Create a user user1 with password user1_pass. This password must expires one’s the user logged in
for first time. The default tablespace for this user should be user1_tbs. This user’s account should be
unlocked immediately after user creation.

2. Create a user user2 with password user2_pass. This password should not expires. The default
tablespace for this user should be user1_tbs. This user’s account should be locked immediately after
user creation.

3. List the user name and their default tablespaces.

4. Try to connect the newly created user user1 with database. What output you get? Justify it.

5. How do you grant CREATE SESSION system privilege to the user user1?

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 52

 Babu Madhav Institute of Information Technology

6. What is privilege? What are the 2 types of privileges? Give some examples of each.

7. Grant following privileges to user1.

8. Grant following privileges to user1 with admin option.

SYSDBA, SYSOPER

9. What is the significance of with admin option clause in grant statement?

10. Grant following privileges to user1 with admin option.

INSERT, UPDATE, DELETE, INDEX, EXECUTE

11. How do you allows user1 to select data from any table in any schema in the database?

12. How to you allow user2 to insert and update records on table1 which is created by user1.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 53

 Babu Madhav Institute of Information Technology

13. Explain revoke statement with syntax. What is the significance of with admin option in revoke
statement?

14. How do you revoke the insert and update privileges from user2?

15. Revoke following privileges from user1.

INSERT, UPDATE, DELETE, INDEX, EXECUTE

16. How do you get back the privileges from user1 so that he can not select data from any table in any
schema in the database?

17. How do you revoke the privileges form user2 to insert and update records on table1 which is created
by user1.

18. For what purpose ALTER USER system privilege is used?

19. Do a user requires to have ALTER USER system privilege to change it’s own password?

20. Change the password of user1 to ‘USER123’.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 54

 Babu Madhav Institute of Information Technology

21. Modify the user1 so that it’s account get locked.

22. Modify the user1 so that it’s account get unlocked.

23. How do you make the user1’s password expire?

24. Create a role STUDENT_ROLE.

25. Grant following privileges to student_role: CREATE TABLE, CREATE VIEW

26. How do you grant student_role to user1.

27. Create a role SUPER_USER_ROLE.

28. How do you grant all privileges to SUPER_USER_ROLE.

29. Grant SUPER_USER_ROLE to user2.

30. Remove user user2.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 55

 Babu Madhav Institute of Information Technology

31. Why CASCADE option is used along with DROP statement?

32. Create a user user3 with password user3_pass. The password should not expires. The default
tablespace for this user should be user3_tbs. Also assign a temporary tablespace temp_tbs to this
user. This user’s account should be locked immediately after user creation. Allow unlimited space on
user3_tbs to this user.

33. Change the default tablespace for user3 to user1_tbs.

34. Change the temporary tablespace for user3 to temp1_tbs.

35. Modify user3 so that 100 MB space on user1_tbs is assign to user3.

36. Modify user3 so that unlimited space on user1_tbs is assign to user3.

37. Modify user3 so that its account get unlocked.

38. Modify user3 so that its password get expired when user logged in next time.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 56

 Babu Madhav Institute of Information Technology

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 57

 Babu Madhav Institute of Information Technology

Reference questions for Viva:

Multiple Choice Questions

1. Which command is used to create a new user in Oracle Database?

A) CREATE TABLESPACE
B) CREATE SCHEMA
C) CREATE USER
D) ADD USER

2. What is the purpose of the ALTER USER command in Oracle Database?

A) To modify table structures.
B) To grant privileges to a user.
C) To change the name of a user.
D) To modify user account settings.

3. Which statement is true about the DROP USER command in Oracle Database?
A) It removes the user's privileges from all objects in the database.
B) It deletes the user and all objects owned by the user from the database.
C) It deletes only the user account but retains all the objects owned by the user.
D) It is not allowed in Oracle Database.
4. To grant specific privileges on a table to another user, which command is used in Oracle Database?
A) GRANT OBJECT
B) GRANT ACCESS
C) GRANT PERMISSIONS
D) GRANT

5. What is the purpose of the WITH ADMIN OPTION clause in the GRANT command?
A) It grants administrative privileges to the user.
B) It allows the user to grant the same privilege to other users.
C) It grants all privileges available in the database to the user.
D) It revokes all administrative privileges from the user.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 58

 Babu Madhav Institute of Information Technology

6. Which command is used to revoke a previously granted privilege from a user in Oracle Database?
A) REVOKE
B) DENY
C) REMOVE
D) DELETE PRIVILEGE

7. When granting privileges to a user, which option allows the user to use the specified privileges only on
objects owned by the user?
A) WITH ADMIN OPTION
B) WITH GRANT OPTION
C) WITH OBJECT OPTION
D) WITH LOCAL OPTION

8. Which command is used to view the privileges granted to a specific user in Oracle Database?
A) SHOW USER PRIVILEGES
B) LIST USER PRIVILEGES
C) DESCRIBE USER PRIVILEGES
D) SELECT * FROM USER_SYS_PRIVS

9. Which command is used to change the password for a user in Oracle Database?
A) CHANGE USER PASSWORD
B) ALTER USER PASSWORD
C) UPDATE USER PASSWORD
D) PASSWORD USER

10. When using the CREATE USER command, which option specifies the default tablespace for the user?
A) DEFAULT TABLESPACE
B) TABLESPACE DEFAULT
C) USER TABLESPACE
D) USER DEFAULT TABLESPACE

11. Question 3: Which command is used to remove a user from the Oracle Database without deleting the
user's objects?
A) DROP USER

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 59

 Babu Madhav Institute of Information Technology

B) DELETE USER
C) REMOVE USER
D) DISABLE USER

12. What happens when you use the CASCADE clause with the DROP USER command in Oracle Database?
A) It drops all users associated with the current user.
B) It drops all objects owned by the user without deleting the user account.
C) It drops all objects in the database without deleting any user accounts.
D) It drops all users and objects associated with the current user.

13. Which command is used to modify the quota for a specific tablespace for a user in Oracle Database?
A) ALTER QUOTA
B) MODIFY TABLESPACE QUOTA
C) ALTER USER QUOTA
D) SET TABLESPACE QUOTA

14. Question 6: To revoke all privileges from a user in Oracle Database, which command is used?
A) REVOKE ALL
B) DELETE ALL PRIVILEGES
C) DROP ALL PRIVILEGES
D) REVOKE ALL PRIVILEGES

15. What is the purpose of the IDENTIFIED BY clause in the CREATE USER command?
A) To specify the user's password.
B) To specify the user's tablespace.
C) To specify the user's privileges.
D) To specify the user's role.

16. Question 8: Which command is used to grant a role to a user in Oracle Database?
A) GRANT ROLE
B) ADD ROLE
C) GRANT USER ROLE
D) SET ROLE

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 60

 Babu Madhav Institute of Information Technology

Answers:

1. C) CREATE USER
2. D) To modify user account settings.
3. B) It deletes the user and all objects owned by the user from the database.
4. D) GRANT
5. B) It allows the user to grant the same privilege to other users.
6. A) REVOKE
7. B) WITH GRANT OPTION
8. D) SELECT * FROM USER_SYS_PRIVS
9. B) ALTER USER PASSWORD
10. A) DEFAULT TABLESPACE
11. A) DROP USER
12. B) It drops all objects owned by the user without deleting the user account.
13. C) ALTER USER QUOTA
14. D) REVOKE ALL PRIVILEGES
15. A) To specify the user's password.
16. A) GRANT ROLE

Short Questions and Answers

1. What is the basic syntax of the CREATE USER statement?

The basic syntax is:

CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE tablespace] [QUOTA {size |
UNLIMITED} ON tablespace] [PROFILE profile] [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK}];

2. How can you set the default tablespace for a user while creating them?

By using the "DEFAULT TABLESPACE" clause in the CREATE USER statement.

3. What does the "QUOTA" clause do in the CREATE USER statement?

The "QUOTA" clause specifies the maximum space in the tablespace that the user can use.

4. How can you grant privileges to a user in Oracle Database?

By using the GRANT statement followed by the type of privilege and the user or role to which the
privilege is granted.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 61

 Babu Madhav Institute of Information Technology

5. What is the purpose of the CASCADE option in the DROP USER statement?

The CASCADE option allows you to delete a user and all their schema objects.

6. How can you change a user's password using the ALTER USER statement?

By using the ALTER USER statement followed by the "IDENTIFIED BY" clause and the new password.

7. What is the benefit of using roles in Oracle Database?

Roles help manage privileges more efficiently by grouping related privileges and granting them to
users.

8. How can you force a user to change their password on the first login?

By using the "PASSWORD EXPIRE" clause in the CREATE USER statement.

9. What system privilege is required to execute the CREATE USER statement?

The CREATE USER system privilege.

10. How can you view the privileges granted to a user in Oracle Database?

By querying the "dba_users" view or "user_sys_privs" view.

11. What is the purpose of the "WITH GRANT OPTION" clause in the GRANT command?

It allows the user to grant the same privilege to other users.

12. When using the DROP USER statement, what happens to the user's schema objects by default?

They are also dropped along with the user unless you use the CASCADE option.

13. How can you set a role's password when creating it with the CREATE ROLE statement?

By using the "IDENTIFIED BY" clause followed by the desired password.

14. What does the "NOT IDENTIFIED" option do in the CREATE ROLE statement?

It indicates that the role is authorized by the database and doesn't require a password to be enabled.

15. How can you enable a role for a user in Oracle Database?

By using the SET ROLE statement followed by the role name.

16. What is the purpose of the ALTER USER statement in Oracle Database?

The ALTER USER statement is used to modify the authentication or database resource characteristics
of a database user.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 62

 Babu Madhav Institute of Information Technology

17. How can you lock a user account in Oracle Database using the ALTER USER statement?

By using the "ACCOUNT LOCK" option in the ALTER USER statement.

18. What option can you use to unlock a locked user account in Oracle Database?

By using the "ACCOUNT UNLOCK" option in the ALTER USER statement.

19. What is the significance of the PASSWORD EXPIRE option in the ALTER USER statement?

It forces the user to change the password for the first time they log in to the database.

20. How can you change the default profile for a user in Oracle Database?

By using the "ALTER USER" statement followed by the "DEFAULT ROLE" clause and the desired profile
name.

21. What happens when you use the NOT IDENTIFIED option in the CREATE ROLE statement?

It indicates that the role is authorized by the database and doesn't require a password to be enabled.

22. How can you grant all privileges to an existing user in Oracle Database?

By using the "GRANT ALL PRIVILEGES" statement followed by the user or role name.

23. What is the purpose of using roles in Oracle Database?

Roles help manage privileges more efficiently by grouping related privileges and granting them to
users or other roles.

D r. O mp ra ka sh Ch an d rak ar | L ab Man u a l | D atab a se Ad m in i st rat i on E ss en tia l s | 2 0 23 - 24 | P a g e | 63