Lesson 1

Database Creation and Architecture

Welcome to Database Creation and Architecture.

Course Goals

The first part of this series focuses on architecture, database creation, and the basics of interacting with an Oracle database. After you complete this course, you will be able to:

Create a new database
Find the files in a database, and identify their purpose
Identify the processes that operate on a database
Understand the significant memory structures of a database instance
Start up and shut down a database

Oracle Database Tables A table is the basic unit of storage in an Oracle database. Without any tables, a database has no value to an enterprise. Regardless of the type of table, data in a table is stored in rows and columns, similar to how data is stored in a spreadsheet. But that is where the similarity ends. The robustness of a database table due to the surrounding reliability, integrity, and scalability of the Oracle database makes a spreadsheet a poor second choice when deciding on a place to store critical information. In this section, we will review the many different types of tables in the Oracle database and how they can satisfy most every data-storage need for an organization.
Relational Tables A relational table is the most common type of table in a database. A relational table is heaporganized; in other words, the rows in the table are stored in no particular order. In the CREATE TABLE command, you can specify the clause ORGANIZATION HEAP to define a heap-organized table, but because this is the default, the clause can be omitted.
Each row of a table contains one or more columns; each column has a datatype and a length. As of Oracle version 8, a column may also contain a user-defined object type, a nested table, or a VARRAY. In addition, a table can be defined as an object table. We will review object tables and objects later in this section.

Oracle Server Architecture : Main components

The whole point of a relational database management system (RDBMS) is to store and supply data to clients who request it. Each RDBMS does this in its own way, so knowing how one works does not necessarily mean that you can figure out the rest. Oracle, being one of the more sophisticated RDBMSs out there, allows for a great deal of flexibility in its configuration and operation. Part of this is possibly due to its architectural design.
The architecture of Oracle is configured in such a way as to ensure that client requests for data retrieval and modification are satisfied efficiently while maintaining database integrity. The architecture also ensures that, should parts of the system become unavailable, mechanisms of the architecture can be used to recover from such failure and, once again, bring the database to a consistent state, ensuring database integrity. Furthermore, the architecture of Oracle needs to provide this capability to many clients at the same time so performance is a consideration when the architecture is configured.
In understanding the Oracle architecture and how it is used to process client requests, several terms need to be introduced. Figure 1-1 displays a diagram of the architectural components that make up a typical Oracle configuration. The terms shown in the diagram and briefly explained below deal with shared memory structures, processes, and datafiles that are used by Oracle. The diagram contains terms that you will read about in this module.

Oracle's architectural components include the instance, database, and other files and processes.

Standalone XQuery Virtual Machine is deprecated

To migrate from the deprecated Standalone XQuery Virtual Machine (SVM) to more modern Oracle tools, the recommended path involves leveraging Oracle XML DB, which provides integrated support for XQuery within Oracle Database. Here’s a guide on how to proceed:

Use Oracle Database's XML DB for XQuery: Oracle XML DB is a feature of Oracle Database that supports XML data storage and querying. It provides a robust XQuery implementation for querying XML data, and it’s well-maintained with Oracle’s database releases. You can migrate your existing XQuery scripts to this platform.
1. Step 1: Ensure XML DB is Installed:
  Most Oracle Database installations come with XML DB installed by default. To verify this, you can run:
```
SELECT COMP_ID, COMP_NAME, VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'XML';
```
  If it is not installed, it can be added through Oracle's Database Configuration Assistant (DBCA).
2. Step 2: Loading XML Data into Oracle DB:
  XML data can be stored as `CLOB`, `BLOB`, or as XMLType. You would typically use XMLType for native XML handling:
```
CREATE TABLE xml_table OF XMLType;
INSERT INTO xml_table VALUES (XMLType(''));
```
3. Step 3: Writing XQuery:
  In Oracle XML DB, XQuery can be run through SQL queries or PL/SQL:
```
 SELECT XMLQuery('declare namespace ns="http://example.com";
				 for $i in /ns:root/ns:item
				 return $i'
		 PASSING xml_column
		 RETURNING CONTENT)
 FROM xml_table;
 
```
  This integrates your XQuery directly within SQL statements.
Use SQL/XML Functions: If you need to bridge between XQuery and SQL, Oracle provides several SQL/XML functions, such as `XMLQuery()`, `XMLTable()`, `XMLExists()`, and `XMLCast()`. These functions allow you to execute XQuery expressions and integrate them with relational data.
Example: To select and return XML data as a table:
```
 SELECT * FROM XMLTable(
	 '/ns:root/ns:item'
	 PASSING xml_column
	 COLUMNS
		 item_id   VARCHAR2(20) PATH 'id',
		 item_name VARCHAR2(100) PATH 'name'
 )
 FROM xml_table;
 
```
Migrate and Optimize Your XQuery Scripts:
- XQuery Migration: The syntax and constructs you are familiar with in SVM should largely work in Oracle XML DB without major changes. However, you may need to tweak queries for performance and compatibility, especially when working with large datasets.
- Optimization: Consider using Oracle-specific features like indexing XML data using `XMLIndex` to speed up queries.
Testing and Debugging: After migration, thoroughly test the XQuery queries to ensure compatibility and performance within the Oracle DB environment. You can use SQL Developer or any other Oracle-supported tool for this.
Alternative Oracle Tools: If you need more than just a database-based solution, Oracle offers other technologies that support XML and XQuery, including Oracle Fusion Middleware and Oracle Service Bus.

By moving your XQuery to Oracle XML DB, you will benefit from better support, optimizations, and the continued development Oracle offers with each database version.

Oracle Cloud Infrastructure for Solutions Architects

Oracle certification

This course, taken in conjunction with the next four courses in the Oracle Database Administration Certification Series, will prepare you for the following Oracle certification exam:
This database administration exam is one of five that you must pass in order to earn certification as an Oracle database administrator.