Specify a password for the local user to use to go online to the database. Note that you simply can create an external or global user, which isn’t covered during this tutorial.

DEFAULT TABLESPACE

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

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

QUOTA

Specify the utmost of space within the tablespace that the user can use. you’ll have multiple QUOTA clauses, each for a tablespace.

Use UNLIMITED if you don’t want to limit the dimensions within the tablespace that user can use.

PROFILE profile

A user profile limits the database resources or password that the user cannot exceed. you’ll 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 would like to force the user to vary the password for the primary time the user logs in to the database.

ACCOUNT {LOCK | UNLOCK}

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

To execute the CREATE USER statement, you want to have the CREATE USER system privilege. Once you create the new user, the privilege domain of the user are going to be empty. Therefore, if you would like to the user to be ready to login to the database, you ought to 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 make a replacement local user example

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

CREATE USER john IDENTIFIED BY abcd1234;

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

User JOHN created.

To find an inventory of users with the OPEN status, you query the knowledge from the dba_users:

SELECT

username,

default_tablespace,

profile,

authentication_type

FROM

dba_users

WHERE

account_status = ‘OPEN’;

oracle 6

As you’ll see from the output, user john features a default tablespace as USERS, profile as DEFAULT, and log in to the database employing a PASSWORD.

Let’s use the toilet account to log within the database.

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

Enter user-name: john@pdborcl

Enter password:

Oracle issued the subsequent error:

ERROR: ORA-01045:

user JOHN lacks CREATE SESSION privilege; logon denied

To enable the user john to log in, you would like to grant the CREATE SESSION system privilege to the user john by using the subsequent statement:

GRANT CREATE SESSION TO john;

Now, the user john should be ready to log within the database.

Enter user-name: john@pdborcl

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

2) Using Oracle CREATE USER statement to make a replacement local user with password expired example

First, use the CREATE USER statement to make a replacement user jane:

CREATE USER jane IDENTIFIED BY abcd1234

PASSWORD EXPIRE;

Second, verify if the user has been created successfully:

SELECT

username,

default_tablespace,

profile,

authentication_type

FROM

dba_users

WHERE

account_status = ‘OPEN’;

oracle 7

Third, grant the CREATE SESSION privilege to the user jane in order that you’ll use this user to log within the Oracle database.

GRANT CREATE SESSION TO jane;

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

Oracle requested for changing the password for jane, you want to provide the new password and ensure it before you’ll log in:

Changing password for jane

New password:

Retype new password:

Password changed

Connected.

SQL>

So, this brings us to the end of blog. This Tecklearn ‘Introduction to Oracle CREATE USER statement’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

367