Lesson 1

Managing Database Users

Welcome to Managing Users and Connectivity. This course is the second of five in the Oracle Database Administration Certification Series. Taken together, these five courses teach you how to create and manage an Oracle database.

Course Goals Part 1 of this series focused on architecture^[1], database creation, and the basics of interacting with an Oracle database. Part 2, the part that you are about to take, deals with managing users and their access to the database. Part 2 also teaches you about SQL*Plus and Enterprise Manager, two important tools that you will use in your day-to-day management of an Oracle database. When you have completed this course, you will be able to do the following:

Process architecture refers to the various processes that run to make an Oracle database work, and file architecture, which refers to the way in which Oracle uses files.
Configure Oracle Net Services on client PCs so they can access Oracle on the server
Effectively use SQL*Plus to run scripts, execute SQL statements, and generate reports
Start the various Enterprise Manager applications, and navigate through them to display information about your database
Create and manage database users
Manage security, both for DBAs and for normal users

Oracle Certification

This course, together with Oracle Database Administration parts 1, 3, 4 and 5, helps prepare you for the following Oracle certification exam: Oracle has retired several older certification exams, such as:
Oracle SQL Certification Exams

1Z0-071: Oracle Database SQL (commonly used for 12c and 18c)
1Z0-061: Oracle Database 12c: SQL Fundamentals (older and sunset)
1Z0-051: Oracle Database 11g: SQL Fundamentals I (legacy)
1Z0-047: Oracle Database SQL Expert (advanced-level, retired)

✅ Current Equivalent Certifications for Oracle 19c and Oracle 23c (as of 2025):
For SQL Fundamentals (General SQL Skills):

Exam: [Oracle Database SQL 1Z0-071]
- Still valid if previously passed, but no longer available for registration.
Current Equivalent (as of 2024–2025):
- Oracle Database SQL Certified Associate
- Exam Code: 1Z0-071 is still used for Oracle 19c and Oracle 21c, and implicitly covers skills used in Oracle 23c. However:

Oracle has not released a new SQL certification specifically branded for 23c.
Oracle University currently recommends 1Z0-071 for validating SQL skills, despite its 12c naming.

For Advanced SQL Expertise (Replacement for 1Z0-047):
Oracle has not released a new direct replacement for the SQL Expert (1Z0-047) exam. Advanced SQL is now integrated into:

Oracle Database Administration Certifications, especially:
- Oracle Database Administrator Certified Professional (OCP) for Oracle 19c

Summary Table:

Legacy Exam	Status	Suggested Equivalent (2025)	Covers
1Z0-071 (SQL)	Retired	Oracle Database SQL Certified Associate (no new code)	Oracle 19c/21c SQL
1Z0-061 (12c Fundamentals)	Retired	Same as above	Entry SQL
1Z0-051 (11g Fundamentals)	Retired	Same as above	Entry SQL
1Z0-047 (SQL Expert)	Retired	No direct replacement	Consider DBA OCP path

Additional Notes:

Oracle 23c Free–Developer Release has learning content, but not yet formal certification exams.
Oracle University emphasizes cloud-related tracks and autonomous database, shifting away from standalone SQL certification updates.

🧭 Recommended Study Path for Oracle SQL (Oracle 23c–Aligned)

Here is a recommended study path for SQL certification aligned with Oracle 23c skills, even though no exam is yet branded specifically for Oracle 23c, the following approach ensures you're up to date and well-positioned for both current and upcoming certifications:
🎓 Step 1: Build Strong SQL Fundamentals
✅ Objective: Master ANSI SQL as implemented in Oracle
Topics to Study:

Basic SELECT queries, WHERE clause, ORDER BY
Data types in Oracle 23c (new JSON-native type, etc.)
Functions: TO_DATE, NVL, CASE, aggregation
Joins (INNER, LEFT, RIGHT, FULL OUTER)
Subqueries and correlated subqueries
Set operators (UNION, INTERSECT, MINUS)
Data manipulation (INSERT, UPDATE, DELETE)
DDL: CREATE TABLE, constraints, sequences, synonyms

Resources:

✅ Oracle Live SQL: https://livesql.oracle.com/
✅ Oracle 23c Free Developer Edition Docs: oracle-database/23/
✅ Practice exercises: W3Schools Oracle SQL Tutorial

📘 Step 2: Study Oracle-Specific SQL Features (Focus on Oracle 23c)
✅ Objective: Incorporate new Oracle 23c SQL features into your learning
Key 23c Features to Learn:

JSON Relational Duality Views
Native JSON datatype and JSON_TABLE
IS JSON, JSON_EXISTS, JSON_QUERY, JSON_VALUE
Row-limiting with FETCH FIRST, OFFSET
INVISIBLE COLUMNS
DEFAULT ON NULL
MULTIINSERT, MERGE
PIVOT / UNPIVOT

Resources:

✅ Oracle 23c New Features Guide
✅ Video tutorials from Oracle Dev Live (YouTube or Oracle Learning)

💡 Step 3: Hands-On Practice

✅ Objective: Reinforce SQL with real-world data
Tools to Use:

Oracle 23c Free–Developer Edition (install locally or use in Docker)
Use datasets like:
- HR Schema (Employees, Departments, Jobs)
- Sample Sales Schema

Practice Ideas:

Write reports with joins, aggregates, subqueries
Build SQL scripts using CASE, analytic functions
Create views and test access control using roles

🧪 Step 4: Prepare for Oracle SQL Certification (1Z0-071 until replaced)
✅ Objective: Pass Oracle Database SQL Certified Associate exam
Recommended Materials:

📘 OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) – McGraw Hill / Oracle Press
📗 Oracle Practice Tests (Kaplan, Whizlabs, or MeasureUp)
🎥 Oracle University’s Free Learning path for SQL: learn.oracle.com

Pro Tip: Even though 1Z0-071 is tied to 12c/18c, 90%+ of its SQL syntax is valid for 19c and 23c. You can still use this as a benchmark until Oracle releases a new version (likely for 23c/24c).
📜 Step 5: Track Oracle Certification Announcements
✅ Objective: Stay informed about 23c SQL or Admin exams
Resources:

🏁 Final Outcome
If you follow this path:

✅ You’ll be fluent in SQL as it applies to Oracle 19c and 23c
✅ You’ll be ready to take 1Z0-071 or its eventual replacement
✅ You’ll be familiar with modern Oracle SQL features including JSON duality, fetch-offset, and multitenancy-aware design

In the next lesson, the database will be discussed.

[1]architecture: In the context of Oracle’s database, the term architecture refers to the overall design of the product, especially with reference to how the various individual components interact with each other. With Oracle, there is memory architecture, which refers to how Oracle uses memory.