Setting up Oracle Database on Oracle VM VirtualBox is a great way to practice database management in a controlled environment. In this guide, we’ll walk through how to create a new user, assign roles, and log in with the new credentials inside your Oracle Database.
Step 1: Checking for Version Number
Step 2: Create a New User
Start by connecting to your Oracle Database as SYSDBA. Once connected, use the following SQL command to create a new user:
Explanation:
-
C##free→ the username (the prefixC##is required for common users in a multitenant database). -
IDENTIFIED BY free→ sets the initial password. -
DEFAULT TABLESPACE users→ assigns the user to theuserstablespace. -
QUOTA UNLIMITED ON users→ allows unlimited space in that tablespace. -
PASSWORD EXPIRE→ forces the user to change the password upon first login.
Step 3: Create and Grant a Role
1. Next, create a role for the new user and assign the required privileges.
2. Then grant permissions to perform essential database operations:
3. Finally, assign this role to your new user:
4. If everything is correct, you’ll see: Grant succeeded.
Step 4: Allow the User to Connect
While still logged in as SYSDBA, grant the CONNECT privilege to the new user:
This step ensures the new user has permission to log in to the database.
Step 5: Log In as the New User
Now, log out of your SYSDBA session and log in using the newly created user:
-
Username:
C##free -
Password:
free
Since the password was set to expire, you will be prompted to create a new password upon login. Enter a secure password of your choice.
Once the password is updated, reconnect using your new credentials.
Step 6: Verification
To verify your user setup, try creating a simple table:
CREATE TABLE sample_table (
id NUMBER PRIMARY KEY,
name VARCHAR(50)
);
If this executes successfully, your user setup and configuration are complete.
Summary
In this setup, you:
-
Verified the Oracle Database version.
-
Created a new common user and role.
-
Granted essential privileges and connection access.
-
Logged in and set a new password successfully.
You’re now ready to explore Oracle SQL under your new user account in VirtualBox!
I'm excited and honored to announce that I've been recognized as an Oracle ACE Associate - a significant milestone in my experience with Oracle technology and community efforts. Receiving the Oracle ACE Associate title represents my commitment to learning, creating, and helping others along the way. Being accepted into this network is a huge honor and a great encouragement to keep sharing and growing with others.
The ACE Associate to me represents:
- A commitment to lifelong learning in Oracle technologies.
- A responsibility to amplify knowledge, not keep it to myself.
- A reminder that community and collaboration are at the heart of meaningful tech development.
Being a member of the Oracle ACE community links me to a global network of experts, innovators, and dedicated professionals who inspire me every day. This is only the beginning, and I am grateful to everyone who has supported me.
The Oracle ACE Associate Award serves as a reminder that sharing your knowledge can have a significant impact. Every contribution you make, whether it's writing a blog, organizing a webinar, or answering questions in a forum, helps build a stronger, smarter IT community.
Here’s to growth, giving back, and embracing every opportunity to learn.
In the ever-changing world of technology, it's tempting to become fixated on what's new – the most recent frameworks, cloud integrations, and AI models. However, I recently took a step back and reviewed the fundamental principles that every developer and tech enthusiast should understand. So, I completed the Oracle Database Explorer Badge.
Despite my experience working with databases on a variety of projects, I thought it was crucial to brush up on my fundamental understanding. As time passes, we often use shortcuts or tips from tutorials to get past issues, but the real impact comes from knowing why something works.
Oracle Enterprise Manager is a complete IT management solution made to assist companies in effectively managing their cloud-based or traditional IT environments. It simplifies setup, support, and maintenance by providing a comprehensive solution for controlling everything from hardware to apps.
Oracle Enterprise Manager's main characteristics include:
- Cloud Lifecycle Management: Enterprise Manager makes it simple and quick to set up, monitor, and support both cloud and conventional Oracle IT environments, including hardware and apps.
- Optimizing IT Investment: To provide the finest solutions for intelligent management of the Oracle stack and designed systems, the tool makes use of Oracle's built-in management capabilities. Additionally, it helps organizations maximize their IT expenditures by integrating Oracle's knowledge base with each client environment in real time.
- All-inclusive Management: Enterprise Manager gives companies the ability to control everything, including disks and apps, resulting in a simplified and effective method of managing IT in both traditional and cloud systems.
- Enhancing Service Levels: Enterprise Manager assists companies in keeping both cloud and traditional applications operating at peak efficiency. In order to raise overall service standards, it emphasizes business-driven application management.
- Increasing Efficiency: Oracle Enterprise Manager's real-time connection with Oracle's knowledge base makes IT operations much more efficient, resulting in quicker problem-solving and improved overall service.
This article examines how domains are used in Oracle Database 23ai, providing examples and insights that encourage consistency and reusability and make database administration easier. By the end of this article, learners will have a firm grasp of how domains standardize data definitions, reduce redundancy, and enhance system robustness.
What Are Domains?
Domains are reusable database objects containing common definitions, constraints, and characteristics. They guarantee consistency and lower errors by enabling developers to set data rules centrally and reuse them across tables and applications. Consider templates with guidelines for particular kinds of information, such as order statuses, email addresses, and phone numbers.
Consider, for example, defining a "Phone Number" field across several tables. Without domains, each table's type, length, and validation criteria would need to be individually specified. Domains allow you to define a concept once and use it anywhere. Time and effort are saved because any future modifications to the domain automatically affect all related columns.
Key Benefits
Single Point of Definition (SPOD): To guarantee consistency throughout your database, centralize data constraints.
Reusability: To cut down on effort duplication, apply the same domain to several columns or tables.
Reduced Errors: Reduce the possibility of contradictory or inconsistent restrictions between tables.
Improved Maintenance: Since modifications are made in a single location, updating restrictions is made easier and SQL standards compliance is guaranteed.
Setup: Getting Started
To start using domains, you must have the necessary privileges. Here’s a guide to set up your test environment:
Connect to Oracle as SYSDBA: This ensures you have administrative access.
conn sys/SysPassword1@//localhost:1521/orcl as sysdba |
- Create a New User: If a user doesn’t already exist, create one for testing purposes.
drop user if exists testuser cascade; create user testuser identified by password quota unlimited on users; grant connect, resource, create domain to testuser; |
- Switch to the New User: Log in as the test user to begin experimenting with domains.
conn testuser/password@//localhost:1521/orcl |
This setup ensures you have a clean environment for testing domains.
Types of Domains
Different domain types are supported by Oracle Database 23ai, and each is appropriate for a particular use scenario. Let's examine them in greater detail:
1. Multi-Column Domain
A multi-column domain works best when a group of columns have interdependent rules. This makes data validation easier and guarantees consistency.
Example:
drop domain if exists address_dom; create domain address_dom as ( street varchar2(100), city varchar2(50), postcode varchar2(10) ) constraint address_chk check ( street is not null and city is not null and postcode is not null ); |
In this example, all address fields—street, city, and postcode—are mandatory. Using this domain ensures consistent validation for any table storing address data.
2. Single Column Domain
Rules for a single column are defined using this kind of domain. For instance, standardizing phone numbers may be necessary.
Example:
drop domain if exists phone_dom; create domain phone_dom as varchar2(15) constraint phone_chk check (regexp_like(phone_dom, '^\+?[0-9]{10,15}$')) annotations (Description 'Domain for phone numbers'); |
This domain guarantees that phone numbers have ten to fifteen digits and can optionally begin with a plus sign (+). This domain will automatically enforce these restrictions for each table column that utilizes it.
3. Flexible Domain
Different rules can be applied depending on the situation in flexible domains, which adjust based on a discriminant column.
Example:
drop domain if exists region_dom; create flexible domain region_dom (city, state, country) choose domain using (region varchar2(20)) from case when region = 'US' then us_region_dom(city, state, country) when region = 'EU' then eu_region_dom(city, state, country) else default_region_dom(city, state, country) end; |
This domain is very adaptable for applications with a variety of data requirements since it applies multiple sets of rules based on the value of the region column.
4. Enumeration Domain
To restrict data to a predetermined range of values, use enumeration domains.
Example:
drop domain if exists status_dom; create domain status_dom as varchar2(10) constraint status_chk check (status_dom in ('Active', 'Inactive', 'Pending')); |
For fields like order status, where only particular values are allowed, this domain is ideal.
Creating Domains
Domains are defined using the CREATE DOMAIN statement. Here’s a beginner-friendly breakdown of its components:
Name: Specify a unique, descriptive name for the domain, such as phone_dom or email_dom.
Data Type: Define the type, length, and precision, such as varchar2(50) or number(10,2).
Constraints: Add rules to validate data, like NOT NULL or CHECK.
Annotations: Use annotations to document the purpose of the domain, aiding future maintainers.
Dropping Domains
If a domain is no longer needed, it can be removed:
drop domain if exists phone_dom; |
This ensures unused domains don’t clutter your database.
Evolving a Domain
Domains must adjust as data requirements vary over time. Tools for evolving domains while preserving their integrity are offered by Oracle Database.
Modifying Domains
You can modify a domain using the ALTER DOMAIN command:
alter domain phone_dom annotations (UpdatedDescription 'Updated phone number domain rules'); |
By doing this, you can change constraints, annotations, or other attributes without having to recreate the domain.
Strict Domains
For applications that demand rigorous data validation, strict domains enforce more comprehensive constraints:
drop domain if exists email_dom; create domain email_dom as varchar2(100) constraint email_chk check (regexp_like(email_dom, '^\S+@\S+\.\S+$') and length(email_dom) <= 100) annotations (Description 'Strict domain for email addresses'); |
Strict domains are ideal for sensitive or critical fields where data quality is paramount.
PL/SQL Support
Advanced business logic implementation is made possible by the smooth integration of domains with PL/SQL. For example, you can design triggers or processes that depend on domain constraints:
create or replace procedure validate_status(p_status in status_dom) is begin if p_status not in ('Active', 'Inactive', 'Pending') then raise_application_error(-20002, 'Invalid status value.'); end if; end; |
This guarantees that domain rules and business logic are in line.
Best Practices
Use Descriptive Names: Choose meaningful names like status_dom to indicate purpose.
Plan Ahead: Anticipate future changes to reduce disturbance.
Thorough Testing: Domains should always be tested in a non-production environment before deployment.
Document Everything: Use annotations to describe each domain's purpose and restrictions.
Conclusion
In conclusion, Oracle Database 23ai's domains provide a strong tool for standardizing and simplifying database development. By centralizing data definitions and restrictions, they improve consistency, decrease maintenance overhead, and adhere to SQL-based system best practices. Whether you're a new or veteran developer, using domains can greatly improve the quality and stability of your database applications.
