Build a Safe Course Environment with Users, Tablespaces, Sample Schemas, and Baseline Backups

Learning outcomes

The ServiceHub Oracle lab now connects reliably. The final foundation task is to make it safe to reuse. A single SYSTEM session that owns tables, runs the application, and performs destructive resets would teach the wrong operational habits. Instead, this lesson establishes a disposable schema boundary, separate owner/runtime identities, deterministic seed data, explicit storage assumptions, a logical baseline export, and a cleanup procedure.

1. Decide the identity boundaries before creating objects

Use two local users: SERVICEHUB_OWNER owns tables and later PL/SQL APIs; SERVICEHUB_APP represents the runtime application and receives only the object privileges it needs. This prevents the application from modifying schema structure simply because it can query and update business data. Administrative setup remains a separate privileged session.

In Oracle, a user and its schema are tightly related: a user owns a same-named schema namespace. That differs from database engines where schemas are independent namespaces assignable to principals. The owner/runtime split is therefore an identity design, not a request to create a second schema object manually.

2. Record storage assumptions before creating a dedicated tablespace

First inspect the PDB’s default/temporary tablespaces and whether Oracle Managed Files (OMF) has a configured destination. Do not paste a Linux datafile path into a Windows installation or a container and hope it exists.

SHOW CON_NAMESELECT property_name, property_valueFROM   database_propertiesWHERE  property_name IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');SELECT name, valueFROM   v$parameterWHERE  name = 'db_create_file_dest';SELECT tablespace_name, status, contentsFROM   dba_tablespacesORDER  BY tablespace_name;

The mandatory course path can safely use the existing USERS tablespace with a small explicit quota. If your lab has OMF configured and you want a dedicated learning tablespace, create it with an intentionally bounded size. If OMF is not configured, do not invent a file path; stay on USERS until Chapter 03 teaches datafiles and storage layout.

-- OPTIONAL: run only if DB_CREATE_FILE_DEST is non-null and this is disposable.CREATE TABLESPACE servicehub_data  DATAFILE SIZE 100M  AUTOEXTEND ON NEXT 50M MAXSIZE 1G;-- Verify rather than assume.SELECT tablespace_name, status, contentsFROM   dba_tablespacesWHERE  tablespace_name = 'SERVICEHUB_DATA';

3. Create owner and runtime users without hard-coded passwords

SQLcl/SQL*Plus substitution variables let the lesson avoid embedding a real credential. Enter temporary strong lab passwords when prompted. The owner receives session creation and a bounded quota; the runtime user initially receives only session creation. If you created SERVICEHUB_DATA, substitute that name for USERS deliberately and record the decision.

SHOW CON_NAME-- Stop if this is not FREEPDB1.ACCEPT owner_password CHAR PROMPT 'Temporary SERVICEHUB_OWNER password: ' HIDEACCEPT app_password   CHAR PROMPT 'Temporary SERVICEHUB_APP password: ' HIDECREATE USER servicehub_owner IDENTIFIED BY "&owner_password"  DEFAULT TABLESPACE users  TEMPORARY TABLESPACE temp  QUOTA 200M ON users;CREATE USER servicehub_app IDENTIFIED BY "&app_password"  DEFAULT TABLESPACE users  TEMPORARY TABLESPACE temp  QUOTA 0 ON users;GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE,      CREATE PROCEDURE, CREATE TRIGGER TO servicehub_owner;GRANT CREATE SESSION TO servicehub_app;UNDEFINE owner_passwordUNDEFINE app_password

The exact owner privileges will evolve with the course. Do not pre-grant broad capabilities “just in case.” For example, the application does not need DBA, CREATE ANY TABLE, or unlimited tablespace to run ordinary DML.

4. Build deterministic ServiceHub seed data

Connect as SERVICEHUB_OWNER to FREEPDB1. The seed schema is intentionally small: regions, technicians, and work orders. It uses relational keys for core integrity and one native JSON column for a device payload. Later lessons can add indexes, PL/SQL APIs, partitioning demonstrations, spatial data, and vectors without replacing the domain.

CREATE TABLE service_regions (  region_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  region_code  VARCHAR2(12) NOT NULL UNIQUE,  region_name  VARCHAR2(100) NOT NULL);CREATE TABLE technicians (  technician_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  region_id     NUMBER NOT NULL,  display_name  VARCHAR2(100) NOT NULL,  skill_level   NUMBER(1) DEFAULT 1 NOT NULL,  active_flag   CHAR(1) DEFAULT 'Y' NOT NULL,  CONSTRAINT technicians_region_fk    FOREIGN KEY (region_id) REFERENCES service_regions(region_id),  CONSTRAINT technicians_skill_ck CHECK (skill_level BETWEEN 1 AND 5),  CONSTRAINT technicians_active_ck CHECK (active_flag IN ('Y','N')));CREATE TABLE work_orders (  work_order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  region_id     NUMBER NOT NULL,  technician_id NUMBER,  status_code   VARCHAR2(20) NOT NULL,  priority_no   NUMBER(1) NOT NULL,  opened_at     TIMESTAMP WITH TIME ZONE NOT NULL,  closed_at     TIMESTAMP WITH TIME ZONE,  payload_json  JSON,  CONSTRAINT work_orders_region_fk    FOREIGN KEY (region_id) REFERENCES service_regions(region_id),  CONSTRAINT work_orders_tech_fk    FOREIGN KEY (technician_id) REFERENCES technicians(technician_id),  CONSTRAINT work_orders_status_ck    CHECK (status_code IN ('OPEN','ASSIGNED','DONE','CANCELLED')),  CONSTRAINT work_orders_priority_ck CHECK (priority_no BETWEEN 1 AND 5),  CONSTRAINT work_orders_dates_ck CHECK (closed_at IS NULL OR closed_at >= opened_at));INSERT INTO service_regions(region_code, region_name) VALUES ('NORTH','North Service Zone');INSERT INTO service_regions(region_code, region_name) VALUES ('CENTRAL','Central Service Zone');INSERT INTO service_regions(region_code, region_name) VALUES ('SOUTH','South Service Zone');INSERT INTO technicians(region_id, display_name, skill_level)SELECT region_id, 'Ari Rahimi', 4 FROM service_regions WHERE region_code='NORTH';INSERT INTO technicians(region_id, display_name, skill_level)SELECT region_id, 'Nora Chen', 5 FROM service_regions WHERE region_code='CENTRAL';INSERT INTO technicians(region_id, display_name, skill_level)SELECT region_id, 'Mina Costa', 3 FROM service_regions WHERE region_code='SOUTH';INSERT INTO work_orders(region_id, technician_id, status_code, priority_no, opened_at, payload_json)SELECT r.region_id, t.technician_id, 'ASSIGNED', 2,       TIMESTAMP '2026-08-20 09:00:00 +00:00',       JSON('{"source":"mobile","asset":"pump-17","temperature_c":72.4}')FROM service_regions r JOIN technicians t ON t.region_id=r.region_idWHERE r.region_code='NORTH';INSERT INTO work_orders(region_id, status_code, priority_no, opened_at, payload_json)SELECT region_id, 'OPEN', 1,       TIMESTAMP '2026-08-21 14:30:00 +00:00',       JSON('{"source":"api","asset":"compressor-4","alarm":"pressure"}')FROM service_regions WHERE region_code='CENTRAL';COMMIT;

The timestamps and values are fixed so query examples are reproducible. Identity values may differ after repeated reset/reseed cycles, so later assertions should join by stable business codes unless the lab explicitly resets the schema.

5. Grant runtime access—and prove the boundary with a failure

As the owner, grant only the DML needed by the ServiceHub runtime. Then connect as the runtime user and verify both a successful business query and a failed schema-creation attempt.

GRANT SELECT ON service_regions TO servicehub_app;GRANT SELECT ON technicians TO servicehub_app;GRANT SELECT, INSERT, UPDATE ON work_orders TO servicehub_app;

-- Connect as SERVICEHUB_APP to //localhost:1521/FREEPDB1SELECT r.region_code,       COUNT(*) AS work_order_countFROM   servicehub_owner.work_orders wJOIN   servicehub_owner.service_regions r ON r.region_id = w.region_idGROUP  BY r.region_codeORDER  BY r.region_code;-- Deliberately forbidden: the runtime identity should not own schema objects.CREATE TABLE should_not_work(id NUMBER);

The first query should succeed. The second should fail with insufficient-privilege behavior (commonly ORA-01031) because SERVICEHUB_APP has no CREATE TABLE privilege and no tablespace quota. That failure is a positive security test. Do not “fix” it with GRANT DBA; preserve the boundary.

6. Record a baseline manifest before backup/export

A reproducible lab needs more than SQL files. Record the database and client versions, current PDB/service, object counts, and storage assumptions. Statistics such as NUM_ROWS can be stale or null until statistics are gathered, so use direct counts for seed verification.

SELECT banner_full FROM v$version;SELECT value AS compatible FROM v$parameter WHERE name='compatible';SELECT sys_context('USERENV','CON_NAME') AS con_name,       sys_context('USERENV','SERVICE_NAME') AS service_nameFROM dual;SELECT username, default_tablespace, temporary_tablespaceFROM   dba_usersWHERE  username IN ('SERVICEHUB_OWNER','SERVICEHUB_APP')ORDER  BY username;SELECT 'SERVICE_REGIONS' AS object_name, COUNT(*) AS row_count FROM servicehub_owner.service_regionsUNION ALLSELECT 'TECHNICIANS', COUNT(*) FROM servicehub_owner.techniciansUNION ALLSELECT 'WORK_ORDERS', COUNT(*) FROM servicehub_owner.work_orders;

For the seed above the expected counts are 3 regions, 3 technicians, and 2 work orders. If they differ, fix the seed/reset process before taking the baseline export.

7. Baseline logical export with Data Pump

Oracle Data Pump creates a logical export that is excellent for a small disposable schema baseline. It is not a physical RMAN backup and does not establish a point-in-time recovery or disaster-recovery strategy. Chapter 15 will build those mechanisms. For this foundation lab, export SERVICEHUB_OWNER to a directory object that the server can access.

SELECT directory_name, directory_pathFROM   dba_directoriesWHERE  directory_name = 'DATA_PUMP_DIR';

# Omit the password so expdp prompts rather than exposing it in shell history.expdp system@//localhost:1521/FREEPDB1 \  schemas=SERVICEHUB_OWNER \  directory=DATA_PUMP_DIR \  dumpfile=servicehub_ch01_baseline.dmp \  logfile=servicehub_ch01_baseline.log \  reuse_dumpfiles=NO

Read the log and require a successful completion status. Store the dump and log outside an ephemeral container layer if you use containers. To make this a real recovery confidence check, later import the dump into a separate disposable schema/PDB and validate row counts and application behavior; an export command returning successfully is not the same as a restore drill.

8. Reset and cleanup are part of the lab design

The chapter’s reset operation is intentionally simple: drop the two local users with owned objects, then rerun the setup script. If you created the optional dedicated tablespace, drop it only after confirming no unrelated segments use it. Never make cleanup “smart” enough to guess a production target.

SHOW CON_NAME-- STOP unless this reports FREEPDB1 and you intend to destroy the lab schema.DROP USER servicehub_app CASCADE;DROP USER servicehub_owner CASCADE;-- OPTIONAL and only if you created the dedicated course tablespace:-- DROP TABLESPACE servicehub_data INCLUDING CONTENTS AND DATAFILES;

Before running reset, verify that the baseline export exists if you need to preserve the state. After reset/reseed, rerun the manifest counts and negative privilege test. A reset script that cannot prove its target container is not production-safe.

9. Production judgment

Production identity design should go beyond this small lab: use managed secrets or external identity patterns where appropriate, enforce password/account lifecycle policy, avoid shared administrator credentials, grant directly where stored-code semantics require it, and test privileges from the actual application driver. Dedicated tablespaces, quotas, encryption, backup retention, and key management belong to a documented storage/security design rather than a copied setup script.

Likewise, a Data Pump export is a migration/logical-recovery artifact, not a replacement for physical backup, archived redo, RMAN recovery, Data Guard, or tested RPO/RTO. The lab’s purpose is to establish a known-good starting point and a reversible learning environment.

10. Chapter checkpoint and bridge to Chapter 02

Chapter 01 began with boundaries and ends with an operationally useful baseline. You can identify the instance/database/CDB/PDB/service layers, distinguish product branding/RU/COMPATIBLE/licensing, choose a deployment model consciously, verify listener connectivity, and create a least-privilege disposable schema with deterministic data and a logical export. Chapter 02 now opens the running instance: SGA, PGA, server/background processes, session models, initialization parameters, startup/shutdown states, and ADR diagnostics.

Authoritative references

• Oracle AI Database Security Guide — users, privileges, roles, and least privilege
• Oracle AI Database SQL Language Reference — CREATE USER/TABLE and SQL syntax
• Oracle AI Database Administrator’s Guide — tablespaces, quotas, users, and administration
• Oracle Database Utilities — Data Pump Export and Import
• Oracle Database Sample Schemas — Oracle-maintained sample schema source for optional later enrichment
• Oracle AI Database Backup and Recovery User’s Guide — physical backup/recovery concepts that Data Pump does not replace