Project 1: Build an Application Database from Scratch
The Scenario
A team is building a new Library application. You are the DBA. They ask for a database where the application stores books and loans, a read-only account for the reporting team and a security setup that passes an audit. You will deliver it step by step.
| Requirement | Our answer |
|---|---|
| Own isolated database | A new PDB named lib_pdb |
| Separate data and index space | Tablespaces lib_data and lib_idx |
| Application owner account | lib_owner with a strong profile |
| Reporting team can read only | Role lib_reader |
| Audit trail | Unified audit policies |
| Healthy optimizer | Statistics job |
Step 1: Create the PDB
sql
-- as SYSDBA in CDB$ROOT
CREATE PLUGGABLE DATABASE lib_pdb
ADMIN USER pdbadmin IDENTIFIED BY "PdbAdminPwd#1"
FILE_NAME_CONVERT = ('/opt/oracle/oradata/FREE/pdbseed/',
'/opt/oracle/oradata/FREE/lib_pdb/');
ALTER PLUGGABLE DATABASE lib_pdb OPEN;
ALTER PLUGGABLE DATABASE lib_pdb SAVE STATE;
ALTER SESSION SET CONTAINER = lib_pdb;
Step 2: Tablespaces
sql
CREATE TABLESPACE lib_data
DATAFILE '/opt/oracle/oradata/FREE/lib_pdb/lib_data01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
CREATE TABLESPACE lib_idx
DATAFILE '/opt/oracle/oradata/FREE/lib_pdb/lib_idx01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 3G;
Step 3: Profile, Owner User and Roles
sql
CREATE PROFILE lib_app_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_LIFE_TIME 180
SESSIONS_PER_USER UNLIMITED;
CREATE USER lib_owner IDENTIFIED BY "LibOwner#2026"
DEFAULT TABLESPACE lib_data TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON lib_data QUOTA UNLIMITED ON lib_idx
PROFILE lib_app_profile;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO lib_owner;
CREATE ROLE lib_reader;
Step 4: Objects
sql
CONNECT lib_owner/"LibOwner#2026"@localhost:1521/lib_pdb
CREATE TABLE books (
book_id NUMBER GENERATED ALWAYS AS IDENTITY,
title VARCHAR2(150) NOT NULL,
author VARCHAR2(100) NOT NULL,
isbn VARCHAR2(20),
CONSTRAINT pk_books PRIMARY KEY (book_id) USING INDEX TABLESPACE lib_idx,
CONSTRAINT uq_books_isbn UNIQUE (isbn) USING INDEX TABLESPACE lib_idx
) TABLESPACE lib_data;
CREATE TABLE members (
member_id NUMBER GENERATED ALWAYS AS IDENTITY,
full_name VARCHAR2(100) NOT NULL,
email VARCHAR2(120) NOT NULL,
CONSTRAINT pk_members PRIMARY KEY (member_id) USING INDEX TABLESPACE lib_idx,
CONSTRAINT uq_members_email UNIQUE (email) USING INDEX TABLESPACE lib_idx
) TABLESPACE lib_data;
CREATE TABLE loans (
loan_id NUMBER GENERATED ALWAYS AS IDENTITY,
book_id NUMBER NOT NULL,
member_id NUMBER NOT NULL,
loan_date DATE DEFAULT SYSDATE NOT NULL,
return_date DATE,
CONSTRAINT pk_loans PRIMARY KEY (loan_id) USING INDEX TABLESPACE lib_idx,
CONSTRAINT fk_loans_book FOREIGN KEY (book_id) REFERENCES books(book_id),
CONSTRAINT fk_loans_member FOREIGN KEY (member_id) REFERENCES members(member_id)
) TABLESPACE lib_data;
CREATE INDEX idx_loans_book ON loans (book_id) TABLESPACE lib_idx;
CREATE INDEX idx_loans_member ON loans (member_id) TABLESPACE lib_idx;
Notice: We indexed both foreign key columns by hand, as taught in the indexes lesson.
Step 5: Read-Only Access for Reporting
sql
-- as lib_owner
GRANT SELECT ON books TO lib_reader;
GRANT SELECT ON members TO lib_reader;
GRANT SELECT ON loans TO lib_reader;
-- as DBA in lib_pdb
CREATE USER report_user IDENTIFIED BY "Report#2026" PROFILE lib_app_profile
DEFAULT TABLESPACE lib_data QUOTA 0 ON lib_data;
GRANT CREATE SESSION TO report_user;
GRANT lib_reader TO report_user;
Step 6: Auditing
sql
CREATE AUDIT POLICY lib_ddl_policy
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE;
AUDIT POLICY lib_ddl_policy;
AUDIT POLICY ORA_LOGON_FAILURES;
Step 7: Statistics Job
sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'LIB_GATHER_STATS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''LIB_OWNER''); END;',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=3',
enabled => TRUE);
END;
/
Step 8: Final Checklist
| Check | Query or command |
|---|---|
| PDB opens at restart | SELECT name, open_mode, restricted FROM v$pdbs; and SAVE STATE |
| Tablespace free space | SELECT * FROM dba_tablespace_usage_metrics; |
| Users and profiles | SELECT username, profile, account_status FROM dba_users WHERE username LIKE 'LIB%'; |
| Audit policies on | SELECT policy_name FROM audit_unified_enabled_policies; |
| Backup exists | RMAN LIST BACKUP SUMMARY; (next project) |
Conclusion
You built an isolated database with separate data and index space, a secure owner account, read-only reporting access, auditing and maintenance. This is the pattern for every new application you will set up.
Quick Summary: PDB, tablespaces, profile, owner, roles, objects, audit, stats job, checklist.

