CHAPTER 5

DDL and Schema Objects

Chapter Overview:

• Purpose: Provides prerequisite knowledge for students, though not directly tested in
exams.
• Tools Covered: SQL*Plus and SQL Developer.
• Data Sets: HR and OE schemas provided by Oracle for exercises and examples.
• Topics: Primitive data types, heap-organized table structure, object types (indexes,
constraints, views), sequences, and synonyms.

Client Tools for Oracle Database:

• SQL*Plus:
o Purpose: Connects to the database, issues SQL commands, creates PL/SQL code,
and formats results.
o Architecture: User process in C, uses Oracle Net protocol. Can connect across
different platforms (e.g., Windows to Unix).
o Availability: On all platforms where the database is ported.
o Usage: Detailed instructions provided for Linux and Windows.
• SQL Developer:
o Functionality: Offers more features compared to SQL*Plus.
o Requirements: Needs a graphical terminal, which SQL*Plus does not.

SQL*Plus on Linux:

• Executable File Location: Typically

/u01/app/oracle/product/12.1.0/db_1/bin/sqlplus.
• Environment Variables:
o ORACLE_HOME: Points to Oracle software installation.
o PATH: Must include the Oracle Home bin directory.
o LD_LIBRARY_PATH: Should include the Oracle Home lib directory (optional).
• Commands:
o echo checks variables.
o which confirms SQL*Plus executable.
o Logon format: username/password@connect_identifier.
• Logon Details: Displays SQL*Plus version, database version, and prompt for SQL
commands.

SQL*Plus on Windows:

• Versions:
o Character Version: sqlplus.exe.
 o Graphical Version: sqlplusw.exe (no longer exists in current releases but still
usable).
• Executable File Location: Typically
D:\app\oracle\product\12.1.0\dbhome_1\BIN\sqlplus.exe.
• Logon:
o Example logon string: system/admin123@coda.
o Shortcut Modification: Use sqlplus /nolog to prevent immediate logon
prompt.
• Launching SQL*Plus: Can be run from the Start menu shortcut or command prompt.

Key Points:

• SQL*Plus: Long-standing tool, still relevant and important to learn.

• SQL Developer: More feature-rich but requires a graphical interface.
• Configuration: Correct environment setup is essential for SQL*Plus functionality.

Creating and Testing a Database Connection:

• SQL*Plus Connection Basics:

o No Storage for Connection Details: SQL*Plus does not store database
connection details; users must specify them each time.
o Authentication: Requires a username and a case-sensitive password.
• Connection Techniques:
o Using an Alias:
§ Example: sqlplus scott/tiger@orcl
§ Alias Resolution: Resolved via tnsnames.ora file or LDAP directories
like Microsoft Active Directory or Oracle Internet Directory (OID).
o Using Full Details:
§ Example: sqlplus
scott/tiger@ocp12c.oracle.com:1521/orcl.oracle.com
Details Required: Hostname, TCP port, and database service.
§
• Common Connection Issues and Errors:
o ORA-12154: TNS: could not resolve the connect identifier specified
§ Cause: The connect identifier (e.g., wrongalias) could not be resolved by
TNS.
§ Solution: Verify and correct the connect identifier.
o ORA-12541: TNS: no listener
§ Cause: The listener is not running or address resolution is faulty.
§ Solution: Ensure the listener is running; verify address resolution.
o ORA-12514: TNS: listener does not currently know of service requested in
connect descriptor
§ Cause: Listener is found, but it cannot connect to the requested service
(likely the database instance is not started).
§ Solution: Start the database instance.
o ORA-01017: invalid username/password; logon denied
§ Cause: Incorrect username or password.
 § Solution: Verify credentials; note that the error does not specify which
credential is incorrect.
• Troubleshooting Technique:
o Process: Address connection issues step by step, interpreting each error message
to identify and resolve the problem.

SQL Developer Overview:

• Purpose:
o Connects to Oracle and some non-Oracle databases.
o Issues ad hoc SQL commands and manages PL/SQL objects.
o Provides a graphical interface with wizards for common tasks.
• Platform and Requirements:
o Written in Java: Requires Java Runtime Environment (JRE).
o Platform Availability: Runs on all platforms supporting the required JRE
version.
o No Significant Platform Differences: Functionality is consistent across
platforms.
• Installation:
o Not Installed with Oracle Universal Installer: It is a standalone product.
o Download: Obtain the latest version from Oracle’s website.
o Installation Process:
§ Unzip the downloaded ZIP file.
§ JDK Requirement:
§ Windows versions may include JDK.
§ Non-Windows platforms require preinstalled JDK.
§ Check JDK Installation: Use java –version command.
§ Example output: java version "1.7.0_71"
§ If the version is incorrect, check the path with which java.
• Launching SQL Developer:
o Windows: Execute sqldeveloper.exe.
o Linux: Execute sqldeveloper.sh.
§ Ensure DISPLAY environment variable is set (e.g., 127.0.0.1:0.0 for
system console).
• User Interface:
o Layout:
§ Left Pane: Navigation for objects and connections.
§ Example: Connection labeled orcl_sys (identifier orcl, user
sys).
§ Right Pane:
§ Upper part for entering SQL statements.
§ Lower part for displaying results.
o Customization: Highly customizable; experiment with settings to find the best
configuration for your needs.
• Troubleshooting:
 o Issues with JRE and launching SQL Developer should be referred to a system
administrator.

Creating a Database Connection:

• Connection Setup:
o Access the connection window by clicking the + symbol on the Connections tab.
o Connection Name: Arbitrary.
o Username and Password:
§ Both must be supplied.
§ Option to save password (for convenience, but poses a security risk).
• Connection Types:
o Oracle Tab:
§ Role Drop-down: Option to connect as sysdba (not needed for
exercises).
§ Connection Type Radio Buttons:
§ Basic: Requires machine name, port, and SID/service.
§ TNS: Uses alias from tnsnames.ora or LDAP directory for name
resolution.
§ LDAP: Queries database service definitions from an LDAP
directory.
§ Advanced: Uses full JDBC connection string, suitable for various
databases.
§ Local/Bequeath: Connects directly to a server process if the
database and client are on the same machine.
• Choosing Connection Types:
o Basic: User needs to know connection details.
o TNS: Requires configuration by the database administrator.

Categorize the Main Database Objects:

• Object Ownership:
o Objects are owned by database users (e.g., HR).
o Owned objects form a schema.
• Query for Object Types:
o Query: SELECT object_type, COUNT(object_type) FROM dba_objects
GROUP BY object_type ORDER BY object_type;
o Example output shows various object types and their counts in a small teaching
database.
• Views for Object Information:
o DBA_OBJECTS: Contains all objects in the database.
o USER_OBJECTS: Shows objects owned by the user.
o ALL_OBJECTS: Shows objects the user has access to.
o CDB_ Views (12c): For multitenant databases (container and pluggable
databases).
• Common Object Types for SQL Programmers:
 o Tables: Store data in rows and columns.
o Views: Named queries that act like tables.
o Synonyms: Aliases for tables/views.
o Indexes: Improve data retrieval speed.
o Sequences: Generate numbers in order on demand.
• Less Common Object Types:
o More relevant to PL/SQL programmers and database administrators.

Users and Schemas

• User vs. Schema:

o User: A person who can connect to the database with a username and password.
o Schema: A container for objects owned by a user. Created along with the user,
initially empty.
• Types of Users:
o Non-Object Creators:
§ Users who do not create objects but access data and code from other
schemas.
§ Granted permissions to use other users’ objects.
o Object Owners:
§ Users who own many objects but may not log on to the database.
§ May not have CREATE SESSION privilege, effectively making their
account disabled or locked.
• Schema Object Identification:
o Objects are identified by their schema prefix (e.g., HR.REGIONS vs.
SYSTEM.REGIONS).
o Unique identifier includes the schema name and object name.
• System Users and Schemas:
o SYS User:
§ Owns the data dictionary and several PL/SQL packages.
§ Data Dictionary: Contains database definitions and should only be
updated via DDL commands.
o SYSTEM Schema:
§ Contains additional administration and monitoring objects.
o Other Users:
§ Created based on database options, like MDSYS for Spatial capabilities.

Naming Schema Objects

• Naming Rules:
o Length: 1 to 30 characters (128 for database links).
o Characters Allowed: Letters, numbers, underscore (_), dollar sign ($), hash
symbol (#).
o Case Sensitivity:
§ Names begin with a letter.
 § Lowercase letters converted to uppercase unless enclosed in double
quotes.
• Double Quotes:
o Enclosing names in double quotes allows breaking standard naming rules.
o Must always use double quotes to reference the object later.
• Case Sensitivity Example:
o Two tables named lower and "lower" are different:

CREATE TABLE lower (c1 DATE);

CREATE TABLE "lower" (col1 VARCHAR2(2));

o SQL tools like SQL*Plus and SQL Developer convert lowercase to uppercase
unless quoted.
• Best Practices:
o Avoid using lowercase names and nonstandard characters to prevent confusion.

Object Namespaces

• Namespace Concept:
o Defines a group of object types within which names must be unique by schema
and name.
o Objects in different namespaces can have the same name.
• Shared Namespace Object Types:
o Tables
o Views
o Sequences
o Private synonyms
o Stand-alone procedures
o Stand-alone stored functions
o Packages
o Materialized views
o User-defined types and operators
o Note: You cannot have a table and a view with the same name in the same
schema. They share the same namespace, allowing abstraction and security.
• Separate Namespace Object Types:
o Constraints
o Clusters
o Database triggers
o Private database links
o Dimensions
o Note: Indexes can have the same name as tables within the same schema, as these
object types have different namespaces.

Data Types for Columns

• Alphanumeric Data Types:

 o VARCHAR2: Variable-length character data. Up to 4,000 bytes (STANDARD)
or 32,767 bytes (EXTENDED). Stored in the database character set.
o NVARCHAR2: Like VARCHAR2, but stored in a national language character
set (Unicode).
o CHAR: Fixed-length character data. From 1 to 2,000 bytes. Padded with spaces if
shorter than column length.
o Note: VARCHAR is converted to VARCHAR2 in compliance with ISO and
ANSI standards.
• Binary Data Type:
o RAW: Variable-length binary data. Up to 4,000 bytes (STANDARD) or 32,767
bytes (EXTENDED). Not converted by Oracle Net.
• Numeric Data Types:
o NUMBER: Numeric data with user-specified precision (1 to 38) and scale (−84
to 127).
o FLOAT: Floating-point number with precision of 126 binary (or 38 decimal).
Alternatives include BINARY_FLOAT and BINARY_DOUBLE.
o INTEGER: Equivalent to NUMBER with scale zero.
• Date and Time Data Types:
o DATE: 7 bytes, includes century, year, month, day, hour, minute, and second.
Range: January 1, 4712 BC to December 31, 9999 AD.
o TIMESTAMP: Up to 11 bytes, includes precision for seconds (up to nine
decimal places).
o TIMESTAMP WITH TIMEZONE: Like TIMESTAMP, but includes time zone
information. Length up to 13 bytes.
o TIMESTAMP WITH LOCAL TIMEZONE: Normalized to the database time
zone on saving and to the user process’s time zone on retrieval.
o INTERVAL YEAR TO MONTH: Records periods in years and months
between two DATEs or TIMESTAMPs.
o INTERVAL DAY TO SECOND: Records periods in days and seconds between
two DATEs or TIMESTAMPs.
• Large Object Data Types:
o CLOB: Character data, size effectively unlimited (4GB minus 1 multiplied by
block size).
o NCLOB: Like CLOB, but in an alternative national language character set.
o BLOB: Binary data, not converted by Oracle Net.
o BFILE: Locator for a file on the database server’s OS, size limited to 2^64 − 1
bytes.
o LONG: Character data up to 2GB minus 1. Deprecated in favor of CLOB.
o LONG RAW: Binary data up to 2GB minus 1. Deprecated in favor of BLOB.
• ROWID Data Type:
o ROWID: Base 64 coded value pointing to the physical location of a row in a
table. Proprietary and encrypted.
• Exam Preparation Tips:
o Know data types: VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP,
INTERVAL, RAW, LONG, LONG RAW, CLOB, BLOB, BFILE, and ROWID.
o Detailed knowledge of VARCHAR2, NUMBER, and DATE is essential.
 • Column Storage:
o VARCHAR2: Adjusts storage based on actual data length. Fails if inserted data
exceeds specified maximum length.
o NUMBER: Precision and scale determine significant decimal digits. Positive
scale counts digits to the right of the decimal point; negative scale counts to the
left.
o DATE: Includes all elements (year, month, day, etc.). Default values applied if
not all elements are specified.

Creating Tables

• Table Storage Types:

o Heap Table: Simple table with rows stored in random order.
o Index Organized Tables: Rows stored in order of an index key.
o Index Clusters: Denormalize parent-child tables to store related rows together.
o Hash Clusters: Random distribution of rows, breaking any entry sequence
ordering.
o Partitioned Tables: Rows stored in separate physical structures (partitions) based
on column values.
o Note: Advanced table structures improve performance but do not affect SQL
query results, which are the same as for heap tables.
• Creating a Standard Heap Table:
o Syntax:

CREATE TABLE [schema.]table [ORGANIZATION HEAP]

(column datatype [DEFAULT expression]
[,column datatype [DEFAULT expression]…);

o Minimum Requirements: Table name and at least one column with a data type.
ORGANIZATION HEAP is the default and rarely specified.
o DEFAULT Keyword: Allows specifying default values for columns if not
provided in an INSERT statement.
o Example:

CREATE TABLE scott.emp

(empno NUMBER(4),
ename VARCHAR2(10),
hiredate DATE DEFAULT TRUNC(SYSDATE),
sal NUMBER(7,2),
comm NUMBER(7,2) DEFAULT 0.03);

o Details:
§ EMP: Created in SCOTT schema.
§ EMP_NO: Up to 4 digits, rounded to integer.
§ ENAME: Up to 10 characters.
§ HIREDATE: Defaults to current date (midnight) if not provided.
§ SAL: Numeric values up to 7 digits, with 2 decimal places; rounded.
§ COMM: Defaults to 0.03 if not provided.
 • Inserting and Selecting Data:
o Example Insertion:

INSERT INTO scott.emp (empno, ename, sal)

VALUES (1000, 'John', 1000.789);

o Example Selection:

SELECT * FROM scott.emp;

• Creating Tables from Subqueries:

o Syntax:

CREATE TABLE [schema.]table AS subquery;

o Example 1:

CREATE TABLE employees_copy AS

SELECT * FROM employees;

o Example 2:

CREATE TABLE emp_dept AS

SELECT last_name AS ename, department_name AS dname,
ROUND(SYSDATE - hire_date) AS service
FROM employees
NATURAL JOIN departments
ORDER BY dname, ename;

o Note: Primary, unique, and foreign key constraints are not copied.
• Creating an Empty Table:
o Example:

CREATE TABLE no_emps AS

SELECT * FROM employees
WHERE 1=2;

o Note: The WHERE 1=2 condition ensures no rows are inserted.

Altering Table Definitions

• Commands:
o Adding Columns:

ALTER TABLE emp

ADD (job_id NUMBER);

o Modifying Columns:
 ALTER TABLE emp
MODIFY (comm NUMBER(4,2) DEFAULT 0.05);

o Dropping Columns:

ALTER TABLE emp

DROP COLUMN comm;

o Marking Columns as Unused:

ALTER TABLE emp

SET UNUSED COLUMN job_id;

o Renaming Columns:

ALTER TABLE emp

RENAME COLUMN hiredate TO recruited;

o Marking Table as Read-Only:

ALTER TABLE emp

READ ONLY;

• Notes:
o DDL Commands: All changes are committed automatically and cannot be
reversed if an active transaction is present.
o SET UNUSED: Makes columns invisible to SQL, better alternative to dropping
columns. Use DROP UNUSED COLUMNS to remove all unused columns.

Dropping and Truncating Tables

• TRUNCATE TABLE: Removes all rows, keeps table definition.

TRUNCATE TABLE [schema.]tablename;

• DROP TABLE: Removes both table definition and data.

DROP TABLE [schema.]tablename;

• Restrictions:
o Cannot drop a table if it is referenced by a foreign key constraint or if there is an
active transaction with rows in the table.
o Recycle Bin (Oracle 12c): Dropped tables can be restored unless dropped with
the PURGE option or recycle bin is disabled.
Creating and Using Temporary Tables

• Definition:
o A temporary table is visible to all sessions in terms of its structure, but the rows
inserted are private to the session that inserted them.
• Syntax:

sql
Copy code
CREATE GLOBAL TEMPORARY TABLE temp_tab_name
(column datatype [, column datatype] )
[ON COMMIT {DELETE | PRESERVE} ROWS];

o column datatype: Specifies columns and their data types, similar to a regular
table.
o ON COMMIT {DELETE | PRESERVE} ROWS: Determines the row lifetime.
§ DELETE: Rows are removed when the transaction completes (default
behavior).
§ PRESERVE: Rows are retained until the session ends.
• Characteristics:
o Data Privacy: Each session’s rows are private and isolated from others.
o DML and SELECT Commands: Supported for temporary tables, just like
permanent tables.
o Indexes, Constraints, and Triggers: Can be defined on temporary tables.
o References: Can be used in views, synonyms, and joined with other tables.
• Performance Benefits:
o Temporary Segments: Written to a temporary segment in the user’s temporary
tablespace, which is faster than using permanent tablespaces. I/O operations are
performed directly on disk without involving the database buffer cache.
o No Redo Generation: DML operations do not generate redo because the data is
transient and persists only for the session or transaction duration. This reduces the
strain on redo generation and speeds up operations.
• Creation Tools:
o Temporary tables can be created using SQL*Plus or the Database Control Table
Creation Wizard.

Indexes Overview

• Functions of Indexes:
o Enforce primary key and unique constraints.
o Improve performance by speeding up data retrieval.
• Roles in Index Management:
o Business Analysts: Specify business rules that translate into constraints and thus
indexes.
o Database Administrators (DBAs): Monitor database performance and
recommend indexes.
o Developers: Design indexing strategies based on code and data nature.
Necessity of Indexes

• Primary Key Constraints:

o Ensure no duplicate rows by checking for existing values using indexes.
o Oracle automatically creates an index for primary key columns if not present.
• Unique Constraints:
o Require an index but allow null values.
o Enforced by indexes on columns, but indexes are on parent tables for foreign key
constraints.
• Foreign Key Constraints:
o Enforced by indexes on the parent table.
o Recommended to index foreign key columns in the child table for performance.
• Performance Impact:
o Queries with WHERE Clauses:
§ Without indexes, full table scans are needed, which is slow for large
tables.
§ Indexes allow faster access via sorted key values and pointers.
§ Full table scans may be faster for small tables or when retrieving a large
fraction of rows.
• Sorting and Joining:
o Sorting: Indexes can optimize ORDER BY, GROUP BY, UNION by returning
rows in order.
o Joining Tables: Indexes influence join strategies (e.g., nested loop, hash join, sort
merge join). Indexes are crucial for efficient joins.
• Update Performance:
o Indexes speed up SELECT, UPDATE, DELETE, and MERGE operations with
WHERE clauses but can slow down INSERT operations due to index
maintenance.

Types of Indexes

• B*Tree Indexes:
o Structure: Balanced tree with nodes pointing to other nodes, containing row
pointers.
o Efficiency: Effective for low retrieval proportions. Rebuild if tree depth exceeds
three or four levels.
o Rowid: Provides the physical location of the row.
o Use Case: Efficient for large tables with high cardinality columns.
• Bitmap Indexes:
o Structure: Stores rowids as bitmaps for each key value.
o Use Case: Effective for columns with low cardinality in data warehouses.
Handles Boolean algebra operations well.
o Advantages: Includes NULLs as distinct values and supports complex queries
with multiple conditions.
Index Type Options

• Unique or Nonunique:
o Unique: Prevents duplicate values.
o Nonunique: Default; allows duplicates.
• Reverse Key Index:
o Function: Reverses key bytes to avoid contention in multiuser systems.
• Compressed Index:
o Function: Stores repeated key values only once, reducing storage for duplicate
values.
• Composite Index:
o Function: Indexes multiple columns concatenated. The index is efficient if
queries use the leftmost columns.
• Function-Based Index:
o Function: Built on the result of functions applied to columns (e.g., UPPER(),
TO_CHAR()). Query must use the same function for index utilization.
• Ascending or Descending:
o Default: Ascending order.
o Descending: Reverses order; affects retrieval order in index full scans.

Tips

• Index Creation:
o Use B*Tree indexes for high cardinality columns.
o Use bitmap indexes for low cardinality columns in data warehouses.
o Keep index count low in transaction-heavy systems to avoid overhead.
o Consider index types based on query patterns and performance needs.

Creating and Using Indexes

• Implicit Index Creation:

o Indexes are automatically created when primary key and unique constraints are
defined, if no existing index on the relevant columns is present.
• Explicit Index Creation Syntax:
o CREATE [UNIQUE | BITMAP] INDEX [schema.]indexname ON
[schema.]tablename (column [, column...] );
o Default index type is a nonunique, noncompressed, non-reverse-key B*Tree
index.
o Unique bitmap indexes are not allowed due to cardinality issues.
• Schema and Composite Indexes:
o Indexes can be created in one schema for tables in another, though this can be
confusing.
o Composite indexes involve multiple columns (can be different data types and
non-adjacent).
• Best Practices:
 o Explicit index creation allows better control over index characteristics and
management.
• Example of Creating Indexes and Constraints:
o Tables:

CREATE TABLE dept(deptno NUMBER, dname VARCHAR2(10));

CREATE TABLE emp (empno NUMBER, surname VARCHAR2(10), forename
VARCHAR2(10), dob DATE, deptno NUMBER);

o Indexes:

CREATE UNIQUE INDEX dept_i1 ON dept(deptno);

CREATE UNIQUE INDEX emp_i1 ON emp(empno);
CREATE INDEX emp_i2 ON emp(surname, forename);
CREATE BITMAP INDEX emp_i3 ON emp(deptno);

o Constraints:

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (deptno)
REFERENCES dept(deptno);

o Index Types:
§ Unique Indexes: Prevent duplicate values.
§ Composite Indexes: Include multiple columns.
§ Bitmap Indexes: Used for columns with low cardinality relative to table
rows.
• Index Usage:
o Oracle automatically evaluates and decides on index usage when executing SQL
statements.
o Optimizer Hints: Programmers can use hints to force or avoid specific indexes.

Modifying and Dropping Indexes

• ALTER INDEX Limitations:

o The ALTER INDEX command cannot change index type, columns, or uniqueness. It
is used for adjusting physical properties.
o To change logical properties (like type or columns), the index must be dropped
and recreated.
• Example of Modifying an Index:
o Drop and recreate index to include additional columns:

DROP INDEX emp_i2;

CREATE INDEX emp_i2 ON emp(surname, forename, dob);

• Impact of Dropping Tables:

o Dropping a table also drops all associated indexes and constraints.
 o Implicit Indexes: Created with constraints will be dropped with the constraint.
o Explicit Indexes: Created independently will not be dropped if the associated
constraint is removed later.

Constraints

• Purpose:
o Enforce business rules and ensure data conforms to the entity-relationship model.
o Example: Ensuring unique identification of customers and orders, preventing
orders before customer creation, validating order dates and amounts.
• Rollback on Constraint Violation:
o Any Data Manipulation Language (DML) statement that violates a constraint will
cause an automatic rollback of the entire statement.
o Exam Tip: Only the statement with the violation is rolled back, not the entire
transaction.

Types of Constraints

• Supported Constraints:
o UNIQUE
o NOT NULL
o PRIMARY KEY
o FOREIGN KEY
o CHECK
o REF (used in object-relational interactions, not covered here)
• Naming Constraints:
o Constraints should be named according to a standard convention, but if unnamed,
Oracle generates names.

Unique Constraints

• Definition:
o Ensures all values in a column or combination of columns are unique.
o Can be a single column (key column) or multiple columns (composite key unique
constraint).
o Columns in a composite key do not need to be adjacent or of the same data type.
• Handling NULL Values:
o NULL values can be inserted into unique constraint columns; multiple NULLs are
allowed because NULL is not equal to any other value.
• Indexing:
o Unique constraints are enforced by indexes (B*Tree indexes).
o NULL values are not included in these indexes, which allows multiple NULLs
but can lead to table scans for WHERE key_column IS NULL.
Not-Null Constraints

• Definition:
o Requires that a column must have a value (mandatory columns).
o Must be defined per column; a group of columns cannot share a single not-null
constraint.
o Defaults can be used to bypass the requirement for specifying values.

Primary Key Constraints

• Definition:
o Uniquely identifies each row in a table.
o Combines the uniqueness and not-null requirements.
o Only one primary key per table, but multiple unique and not-null constraints are
allowed.
• Indexing:
o A primary key constraint automatically creates an index if one does not already
exist.
• Exam Tip: Primary and unique key constraints require an index, which is created
automatically if needed.

Foreign Key Constraints

• Definition:
o Defined on a child table and refers to the primary key or unique constraint
columns in a parent table.
o Columns do not need to have the same names but must be of the same data type.
• Handling NULL Values:
o Foreign key columns can have NULL values, potentially creating orphan rows.
• Constraint Actions:
o ON DELETE CASCADE: Automatically deletes matching rows in the child
table if a row in the parent table is deleted.
o ON DELETE SET NULL: Sets foreign key columns to NULL in the child table
if a row in the parent table is deleted.
o Restrictions: The parent table cannot be dropped or truncated if there are foreign
key relationships, even if the child table is empty.
• Self-Referencing Foreign Key:
o A table can have a foreign key that references itself. Example: An employee table
where an employee's manager is also an employee in the same table.

Check Constraints

• Definition:
o Enforces rules on column values, such as value ranges or conditions.
o Rules must evaluate to TRUE or FALSE and can use literals or other columns but
cannot use subqueries or functions like SYSDATE.
 • TIP: The not-null constraint is implemented as a preconfigured check constraint.

Defining Constraints

• Defining Constraints:
o Constraints can be defined when creating a table or added later.
o Inline Definition: Constraints are defined within the column definition. Suitable
for simple constraints but limited in flexibility.
o Table-Level Definition: Constraints are defined at the end of the table definition.
Allows more complex constraints, such as foreign keys spanning multiple
columns or check constraints involving multiple columns.
• Index Creation:
o Unique and primary key constraints require an index.
o If defined at table creation, the index is created along with the table.
• Examples of Constraint Definitions:

create table dept(

deptno number(2,0) constraint dept_deptno_pk primary key
constraint dept_deptno_ck check (deptno between 10 and 90),
dname varchar2(20) constraint dept_dname_nn not null
);

create table emp (

empno number(4,0) constraint emp_empno_pk primary key,
ename varchar2(20) constraint emp_ename_nn not null,
mgr number(4,0) constraint emp_mgr_fk references emp (empno),
dob date,
hiredate date,
deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
on delete set null,
email varchar2(30) constraint emp_email_uk unique,
constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
constraint emp_email_ck
check ((instr(email,'@') > 0) and (instr(email,'.') > 0))
);

o DEPT Table:
1. DEPTNO: Primary key (dept_deptno_pk), numeric, between 10 and 90
(dept_deptno_ck).
2. DNAME: Not null (dept_dname_nn).
o EMP Table:
1. EMPNO: Primary key (emp_empno_pk).
2. ENAME: Not null (emp_ename_nn).
3. MGR: Self-referencing foreign key (emp_mgr_fk).
4. DEPTNO: Foreign key referencing DEPTNO in the DEPT table with ON
DELETE SET NULL (emp_deptno_fk).
5. EMAIL: Unique (emp_email_uk), checked for validity (emp_email_ck).
6. HIREDATE: Checked to be at least 16 years after DOB
(emp_hiredate_ck).
 • Additional Constraints Considerations:
o Index Control: Managing index creation for unique and primary key constraints.
o Constraint Timing: Constraints can be checked at insert time or when the
transaction is committed.
o Enforcement: Constraints can be enabled or disabled.

Constraint States

• ENABLE VALIDATE:
o Ensures all existing data and new data conform to the constraint.
• DISABLE NOVALIDATE:
o Allows both conforming and non-conforming data; no validation of existing data.
• ENABLE NOVALIDATE:
o Existing non-conforming data may exist, but new data must conform.
• DISABLE VALIDATE:
o Existing data conforms to the constraint, but new data does not need to; the index
is dropped.
• Practical Example:

sql
Copy code
alter table sales_archive modify constraint sa_nn1 disable novalidate;
insert into sales_archive
select * from sales_current;
alter table sales_archive modify constraint sa_nn1 enable novalidate;
update sales_archive set channel='NOT KNOWN' where channel is null;
alter table sales_archive modify constraint sa_nn1 enable validate;

o Process:
§ Disable constraint temporarily to allow data upload.
§ Re-enable constraint with NOVALIDATE to accommodate existing data.
§ Update the table to fix data issues.
§ Enable constraint with VALIDATE to ensure all data conforms.

Constraint Checking

• Types of Constraint Checking:

o IMMEDIATE Constraints: Checked as soon as a statement is executed. This is
the default behavior.
o DEFERRED Constraints: Checked only when the transaction is committed.
Allows for constraints to be postponed.
• Deferring Constraints:
o Constraints must be created as deferrable to be deferred later. They cannot be
altered to become deferrable if they were not initially created with that option.
o Creating a Deferrable Constraint:

sql
Copy code
 alter table sales_archive add constraint sa_nn1
check (channel is not null) deferrable initially immediate;

§ Explanation:
§ DEFERRABLE: Allows the constraint check to be deferred.
§ INITIALLY IMMEDIATE: Default behavior is to check the
constraint immediately.
• Using Deferrable Constraints:
o Example Usage:

sql
Copy code
set constraint sa_nn1 deferred;
insert into sales_archive
select * from sales_current;
update sales_archive set channel='NOT KNOWN' where channel is
null;
commit;
set constraint sa_nn1 immediate;

§ Process:
§ DEFERRED: Constraint checking is postponed until the
transaction is committed.
§ IMMEDIATE: Constraint checking is resumed after the commit.
• Constraint Status Changes:
o ENABLED/DISABLED and VALIDATE/NOVALIDATE: These status
changes affect all sessions and are a data dictionary update.
o IMMEDIATE/DEFERRED: Changes are session-specific. The default state
applies to all sessions unless explicitly changed.
• Default Behavior:
o Constraints are enabled, validated, and not deferrable by default.

Exam Tip

• By default, constraints are enabled and validated, and they are not deferrable.

Views

• Definition:
o A view appears as a table to users, providing a two-dimensional structure of rows
and columns.
o Technically, a view is a named SELECT statement that executes when users
interact with it.
o Views can include any valid SELECT commands: joins, aggregations, sorts, etc.
• Namespace:
o Views share the same namespace as tables. A view name can be used wherever a
table name can be used.
• Reasons for Using Views:
 o Security: Restrict access to specific rows or columns.
o Simplify SQL: Hide complex queries or aggregations.
o Prevent Errors: Provide a clear, unambiguous representation of data.
o Data Comprehensibility: Present normalized data in an understandable format.
o Performance: Optimize queries and simplify tuning.
• Enforcing Security with Views:
o Create views to show only relevant columns or rows, e.g.:

sql
Copy code
create view hr.emp_fin as
select hire_date, job_id, salary, commission_pct, department_id
from hr.employees;

o Users can query the view without seeing sensitive information, e.g.:

sql
Copy code
select * from emp_fin where department_id=50;

• Simplifying User SQL:

o Encapsulate complex queries in views, e.g.:

sql
Copy code
create view dept_sal as
select d.department_name, sum(e.salary) dept_sal
from departments d
left outer join employees e on d.department_id=e.department_id
group by d.department_name
order by d.department_name;

o Users query the simplified view without dealing with joins or aggregations.
• Preventing Errors with Views:
o Provide views to prevent misinterpretation of data, e.g.:

sql
Copy code
create view current_staff as select * from emp where active='Y';

o Ensures users do not see logically "deleted" rows.

• Making Data Comprehensible:
o Views abstract complex, normalized data structures into a user-friendly format.
o For example, in Oracle E-Business Suite, customer data spans multiple tables but
is presented through views.
• Optimizing Performance with Views:
o Use views to force specific optimization techniques, e.g.:

sql
Copy code
 create view dept_emp as
select /*+USE_HASH (employees departments)*/ department_name,
last_name
from departments
natural join employees;

o This view forces the use of a hash join for better performance.
• Simple vs. Complex Views:
o Simple Views:
§ Draw data from a single detail table.
§ No functions or aggregations.
§ Usually allow INSERT, UPDATE, DELETE operations.
o Complex Views:
§ May join multiple tables, use functions, or perform aggregations.
§ Generally do not support direct DML operations due to complexity.
• Examples:
o Simple View: May not allow INSERT if it lacks a mandatory column, even if it's
technically simple.
o Complex View: Often cannot handle INSERT due to functions or other complex
elements, but may allow DELETE.

CREATE VIEW, ALTER VIEW, and DROP VIEW

CREATE VIEW Syntax:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW

[schema.]viewname [(alias [,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;

Key Points:

• Schema Object: Views are schema objects and can reference tables owned by different
users.
• Default Location: Views are created in the current schema by default.
• Optional Keywords:
o OR REPLACE: Drops the view if it already exists before creating a new one.
o FORCE: Creates the view even if detail tables do not exist.
o NOFORCE: Default behavior; causes an error if detail tables do not exist.
o WITH CHECK OPTION: Prevents insertion of rows or updates that affect
visibility in the view.
o WITH READ ONLY: Prevents DML operations through the view.
o CONSTRAINT constraintname: Names the constraints for error messages.
• Aliases: Optional. If not provided, columns will use table names or aliases from the
subquery.

ALTER VIEW:
 • Purpose: Compiles the view. A view must compile successfully to be usable.
• Compilation Check: Oracle checks for the existence of detail tables and columns during
view creation.
o FORCE Option: Allows creation of the view even if tables or columns are
missing, but the view remains unusable until those objects are created and
recompiled.
• Automatic Compilation: Oracle will attempt to recompile an invalid view when queried.
If successful, users may experience slower query performance.
• Manual Compilation: Recommended to ensure views compile successfully.

DROP VIEW Syntax:

DROP VIEW [schema.]viewname ;

• OR REPLACE: When used with CREATE VIEW, automatically drops an existing view
before creating a new one.

Synonyms

Definition:

• A synonym is an alternative name for a database object (e.g., tables, views).

Benefits:

• Data Independence: Allows applications to function regardless of the schema or

database where the underlying objects reside.
• Location Transparency: Users can access objects without knowing their exact location
or schema.

Example:

• If you have a public synonym:

CREATE PUBLIC SYNONYM emp FOR hr.employees@prod;

Instead of:

SELECT * FROM hr.employees@prod;

Users can simply use:

SELECT * FROM emp;

Usage:

• DML Statements: Synonyms can be used in SELECT, INSERT, UPDATE, and

DELETE statements just like the original object.
• Privileges: Users must have the appropriate privileges to access the underlying object.

Types of Synonyms:

• Private Synonyms:
o Scope: Must be in the user's schema or qualified with the schema name.
o Uniqueness: Must have unique names within their schema.
• Public Synonyms:
o Scope: Independent of any schema; accessible by any user with permission.
o Naming: Can have the same name as schema objects.

Name Resolution:

• Oracle searches for the object in the local schema first. If not found, it then searches for a
public synonym.

Syntax:

• Create Synonym:

CREATE [PUBLIC] SYNONYM synonym FOR object ;

• Drop Synonym:

DROP [PUBLIC] SYNONYM synonym ;

• Compile Synonym:

ALTER SYNONYM synonym COMPILE;

Permissions:

• Private Synonyms: Users need permission to create them.

• Public Synonyms: Usually created or dropped by the database administrator, as they
affect all users.

Behavior on Object Changes:

• If the referred object is dropped, the synonym remains but will return an error if accessed.
• The synonym must be recompiled if the object is recreated.
EXAM TIP:

• Public Synonym: The term "public" indicates that it is not tied to a specific schema and
cannot be prefixed with a schema name, but it does not imply that everyone has
permissions to use it.

Sequences

Definition:

• A sequence generates unique integer values, ensuring each value is unique and
incremented.

Use Cases:

• Primary Keys: Commonly used to generate primary key values for tables (e.g., customer
numbers, order numbers).
• Simplification: Helps in systems where complex identifiers (e.g., telephone numbers,
timestamps) are impractical for primary keys.

Characteristics:

• Serialization: Only one session can read and increment the sequence at a time, ensuring
unique values.
• Independence: Operates independently of tables, row locking, and transaction
processing, allowing for high performance in generating values.

Creating Sequences:

• Syntax:

CREATE SEQUENCE [schema.]sequencename

[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

• Options:
o INCREMENT BY: Defines the value increment (default is +1).
o START WITH: Sets the initial value (default is 1).
o MAXVALUE: Sets the maximum value for ascending sequences (default is no
maximum).
o MINVALUE: Sets the minimum value for descending sequences (default is no
minimum).
 o CYCLE: Allows the sequence to restart from the beginning after reaching
MAXVALUE or MINVALUE (default is no cycle).
o CACHE: Determines the number of values pre-generated and cached for
performance (default is 20). Larger values can improve performance.
o ORDER: Ensures that values are issued in order in a clustered database (useful in
multi-instance setups).

Using Sequences:

• NEXTVAL: Retrieves the next value and increments the sequence.

• CURRVAL: Retrieves the current value for the session (cannot be used until NEXTVAL
has been called).
• Example Usage:

CREATE SEQUENCE order_seq START WITH 10;

CREATE SEQUENCE line_seq START WITH 10;

INSERT INTO orders (order_id, order_date, customer_id)

VALUES (order_seq.NEXTVAL, SYSDATE, '1000');

INSERT INTO order_items (order_id, order_item_id, product_id)

VALUES (order_seq.CURRVAL, line_seq.NEXTVAL, 'A111');
INSERT INTO order_items (order_id, order_item_id, product_id)
VALUES (order_seq.CURRVAL, line_seq.NEXTVAL, 'B111');

COMMIT;

Commit Behavior:

• Persistence: Sequence values are permanent and visible immediately upon being issued;
they cannot be rolled back.
• Gaps: Sequences may have gaps due to system restarts or cached values being lost during
a database failure.

Handling Gaps:

• If no gaps are acceptable, alternative methods (e.g., using a table to store current values)
can be employed, but they are less efficient and can cause contention.

Modifying Sequences:

• Syntax:

ALTER SEQUENCE sequencename

[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
 [ORDER | NOORDER] ;

• Note: You cannot change the starting value with ALTER SEQUENCE. To reset the
sequence, drop and recreate it:

DROP SEQUENCE order_seq;

CREATE SEQUENCE order_seq START WITH 1;