Lesson 13

During the course of this module, you have:

1. Create a Windows service
2. Start a new instance
3. Create a new Oracle database
4. Create data dictionary views
5. Create built-in PL/SQL procedures
6. Create the Product User Profile
7. Create initial tablespaces
8. Create some non-system rollback segments

You now have a working database, ready for you to create users, tables, and any other objects that you need. You can also read about other ways to create database.

Depending on the specific release of the Oracle database that you are using, and the operating system under which it is running, there are other ways to create a new database.
As of Oracle 23c, the "cloud-enabled" version of the Oracle database, the traditional Oracle Database Configuration Assistant (DBCA) remains the primary tool for creating and managing databases, both in on-premises and cloud environments.

• Oracle Database Configuration Assistant (DBCA)
  • The DBCA is a graphical tool that simplifies database creation, configuration, and management tasks. It guides you through a step-by-step process, whether you're setting up a database for an on-premises deployment or a cloud-enabled environment.
  • DBCA can create both pluggable and non-pluggable databases (PDBs and non-PDBs), configure storage, networking, and even performance options.
• Cloud-Enabled Features in Oracle 23c:
  • Oracle 23c, being cloud-enabled, integrates tightly with Oracle Cloud Infrastructure (OCI), and the DBCA can be used to configure databases within the cloud infrastructure as well.
  • Though DBCA is typically associated with traditional environments, Oracle 23c’s cloud integration expands its utility to hybrid and cloud-based database setups.
• Oracle Cloud-Specific Tools:
  • In Oracle’s cloud environments, there are additional cloud-native tools, such as Oracle Cloud Console and OCI Database Management Services, which assist in managing databases once they're deployed in the cloud. These tools are more cloud-focused than DBCA and are tailored for managing cloud resources directly from the Oracle Cloud dashboard.

In summary, DBCA still exists in Oracle 23c for database creation and management, regardless of whether you're working in a traditional or cloud-enabled environment.
Some releases of Oracle for UNIX allow you to use the Oracle Installer to create a new database. The interface isn't as clean and intuitive as for the Oracle Database Assistant (in my opinion), but again it is just a matter of filling in the correct information and letting the installer do the work. By now, you may think it was a waste of time for me to take you through this morass of writing the CREATE DATABASE command, executing CATALOG and CATPROC, manually creating the tablespaces, manually creating rollback segments, and so forth.
Why did I do that? Because underneath their nice interface, what the Oracle Database Assistant and the installer do is exactly what I have shown you. It's important to understand the manual method of database creation because the automated tools may not always be available, they may not always work, and if you are creating a large, complex production database, you will end up doing much of the work manually anyway.

The Product User Profile (PUP) table in Oracle is specific to the Oracle RDBMS. It is part of Oracle’s SQL*Plus tool, used primarily to restrict or grant access to specific SQL*Plus commands for different users. The table is typically created in the SYSTEM tablespace and is called PRODUCT_USER_PROFILE. Here are some key points about the Product User Profile (PUP) table:

1. Purpose:
   • The PUP table is designed to control access to certain SQL*Plus commands for users. By configuring the PUP table, you can restrict certain SQL*Plus commands, such as SELECT, INSERT, UPDATE, DELETE, or administrative commands like HOST, SPOOL, and others.
2. Location:
   • It is created in the SYSTEM schema and is specific to SQL*Plus environments.
   • The table is created using the PUPBLD.SQL script, which is provided with SQL*Plus installations.
3. Usage:
   • The restrictions defined in the PUP table apply only to SQL*Plus sessions and do not affect other Oracle database tools or interfaces.
   • For each user or role, you can define the commands they are allowed or not allowed to execute in SQL*Plus.
4. Command Example:
   • To populate the table, you would typically insert records that specify which commands or features are disabled for a particular user or role.

While the PUP table is specific to SQL*Plus and not directly related to general Oracle database functionality, it is a useful tool for administrators who need to enforce restrictions on command execution within SQL*Plus sessions.

Now, click the Quiz link below to test what you've learned. Create Database - Quiz