Project 1: Build an Application Database from Scratch

Oracle DBA Certification 20 min min read Updated: Nov 16, 2026 Intermediate
Project 1: Build an Application Database from Scratch
Intermediate Topic 1 of 4

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.

RequirementOur answer
Own isolated databaseA new PDB named lib_pdb
Separate data and index spaceTablespaces lib_data and lib_idx
Application owner accountlib_owner with a strong profile
Reporting team can read onlyRole lib_reader
Audit trailUnified audit policies
Healthy optimizerStatistics 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

CheckQuery or command
PDB opens at restartSELECT name, open_mode, restricted FROM v$pdbs; and SAVE STATE
Tablespace free spaceSELECT * FROM dba_tablespace_usage_metrics;
Users and profilesSELECT username, profile, account_status FROM dba_users WHERE username LIKE 'LIB%';
Audit policies onSELECT policy_name FROM audit_unified_enabled_policies;
Backup existsRMAN 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.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators