Oracle DBA Lesson 44A — Choose Where Application Data Belongs

A tablespace's purpose, an account's default placement, and its allocation allowance answer different operational questions. Check all three when reviewing an application account or explaining why a new table cannot acquire storage.

Oracle DBA Lesson 44A — Choose Where Application Data Belongs

Identify the storage roles

Role	What it supports	What to inspect
SYSTEM	The data dictionary	Keep database-managed metadata separate from ordinary application storage.
SYSAUX	Oracle components and their supporting objects	Component occupancy varies with configuration and use.
Application permanent tablespace	Persistent application tables and indexes	Read its actual name, status, and account assignments.
Temporary tablespace	Temporary work such as disk-based sorts	Read the account's temporary assignment.
Undo tablespace	Rollback and read consistency	Establish whether the CDB uses local or shared undo.

Assign ordinary application objects to a suitable permanent application tablespace. USERS and TEMP are conventional example names. Read the actual configuration. See logical storage structures for these roles and the recommendation to keep application storage separate.

In local undo mode, each container has its own undo tablespace in this single-instance baseline. Shared undo uses the CDB's undo instead. A separately authorized root administrator can check the mode without changing it:

SHOW CON_NAME

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';

Run that query in CDB$ROOT. TRUE indicates local undo. See CDB administration for this lookup and both modes. The application account's permanent default and quota concern its own segments. Oracle manages undo and temporary-work allocation through their respective mechanisms.

Confirm the container and read the inventory

Use the instructor-designated Oracle 19c lab and delegated dictionary access:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS database_name,
       SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

SELECT tablespace_name, contents, status
FROM dba_tablespaces
ORDER BY tablespace_name;

SHOW is a SQL*Plus command. The queries read existing configuration. Confirm LABPDB and the expected account before interpreting the rows. CONTENTS identifies permanent, temporary, and undo storage. STATUS reports states such as ONLINE, OFFLINE, and READ ONLY. The inventory establishes which names actually exist. See DBA_TABLESPACES.

Optional root comparison, using the separately authorized common administrator:

SELECT con_id, tablespace_name, contents, status
FROM cdb_tablespaces
ORDER BY con_id, tablespace_name;

Retain CON_ID with each tablespace name. Root reporting requires delegated view access and suitable CONTAINER_DATA visibility. Available rows also depend on PDBs being open and unrestricted. A root report's coverage belongs with its conclusions. See CDB views.

Read container defaults and user assignments

In LABPDB:

SELECT property_name, property_value
FROM database_properties
WHERE property_name IN ('DEFAULT_PERMANENT_TABLESPACE',
                        'DEFAULT_TEMP_TABLESPACE')
ORDER BY property_name;

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'COURSE_OWNER';

The property report identifies the container's permanent and temporary defaults. The user report identifies COURSE_OWNER's current assignments. See DATABASE_PROPERTIES for the name/value catalog, and DBA_USERS for both assignment columns. Temporary assignments can also name a tablespace group.

Assume this example account has:

Account	Default permanent	Temporary assignment
COURSE_OWNER	USERS	TEMP

At account creation, omitting a permanent assignment uses the container's default. A user's explicit permanent assignment selects its ordinary default destination. Inspect that assignment when assessing the account. See CREATE USER and PDB administration.

Follow ordinary table placement

These two statements illustrate alternative placement choices for a new ordinary heap table. They are explanation examples, outside the read-only practice:

-- Explicit destination:
CREATE TABLE new_orders (id NUMBER) TABLESPACE users;

-- Alternative: the owner's default destination:
CREATE TABLE new_orders (id NUMBER);

The first selects USERS explicitly. The second uses the schema owner's current default permanent tablespace. Under the stated assignment that is also USERS. The statements are alternatives for the same new name. Creation requires the appropriate object privilege and allocation allowance, and the name must be unused. No table is created by the practice below.

The account's temporary assignment supports temporary work during its operations. It is separate from the ordinary table's permanent destination. See CREATE TABLE for the TABLESPACE clause and owner-default behavior.

Interpret quota and creation privileges

SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = 'COURSE_OWNER'
ORDER BY tablespace_name;

An assumed quota row is:

Tablespace	BYTES	MAX_BYTES	Interpretation
USERS	2097152	52428800	2 MiB charged against a 50 MiB ceiling

The remaining quota headroom is 48 MiB: (52428800 - 2097152) / 1024 / 1024. BYTES is storage charged to this owner. MAX_BYTES is its quota ceiling. A value of -1 means unlimited quota on that tablespace. See DBA_TS_QUOTAS.

Quota is an allowance for all of the owner's allocated objects in that tablespace. Actual growth also requires enough available allocation space and usable storage. Apply the space investigation from the preceding lesson when physical capacity is the limiting factor.

CREATE TABLE permits ordinary table creation in the account's own schema. A finite quota supplies its bounded allocation allowance. UNLIMITED TABLESPACE overrides explicit quotas, so a missing quota row needs a privilege check before an allocation conclusion. That broad privilege is excluded from the course owner setup. It is granted to users directly rather than to roles. See creating user accounts for quota and this override.

An authorized administrator can inspect the relevant direct grants:

SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'COURSE_OWNER'
  AND privilege IN ('CREATE TABLE', 'UNLIMITED TABLESPACE')
ORDER BY privilege;

This report reads direct grants recorded for the named grantee. CREATE TABLE can also be available through an enabled role. Use the owner connection to inspect the actual session's available system privileges:

SELECT privilege
FROM session_privs
WHERE privilege IN ('CREATE TABLE', 'UNLIMITED TABLESPACE')
ORDER BY privilege;

SESSION_PRIVS concerns the connected user, so running it as COURSE_DBA describes that administrative session. It must be run as COURSE_OWNER for the owner conclusion. See DBA_SYS_PRIVS and SESSION_PRIVS.

An empty table may use deferred segment creation. Interpret a successful object definition separately from its eventual first storage allocation. The allowance and capacity matter when allocation occurs. This read-only audit assesses the conditions and performs no allocation test.

Distinguish defaults from existing placement

Suppose an administrator later changes COURSE_OWNER's default from USERS to APP_DATA. Existing EMPLOYEES remains in USERS. A future ordinary table with an omitted TABLESPACE uses APP_DATA and needs an allowance there. Existing segments continue to need allocation allowance in their current tablespaces as they grow.

Read actual allocated placement:

SELECT segment_name, segment_type, tablespace_name, bytes
FROM dba_segments
WHERE owner = 'COURSE_OWNER'
ORDER BY tablespace_name, segment_type, segment_name;

DBA_SEGMENTS reports allocated storage structures. A deferred empty table may have no allocated segment yet. Complex tables can have several associated segments. See DBA_SEGMENTS when interpreting these rows.

See ALTER USER for the default reassignment. Changing that policy affects later default placement. Moving existing data requires a separately planned operation. This lesson makes no default, quota, or object changes.

Independent read-only practice

1. Confirm the designated database, LABPDB, and the administrative identity. Record the Oracle release update (RU) and the actual container.
2. Inventory storage names and types. Read both container defaults and COURSE_OWNER's assignments.
3. Record its quota rows and direct relevant grants. In the owner connection, confirm its available CREATE TABLE privilege. Explain whether a new ordinary segment can allocate in the chosen destination under those conditions, including storage availability. Record missing permissions or evidence instead of claiming an allocation succeeded.
4. Inventory existing segments. Explain where an existing table is allocated and how that conclusion differs from a new default choice.
5. If the instructor has delegated access, inspect SYSAUX occupancy:

SELECT occupant_name, occupant_desc, schema_name, space_usage_kbytes
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC NULLS LAST;

The first non-null maximum identifies the largest reported occupant. Record ties and unavailable values. SPACE_USAGE_KBYTES is its reported usage in KB. Components maintain this information. This inventory calls no move procedure and generates no diagnostic report. See V$SYSAUX_OCCUPANTS for the columns. Separately licensed diagnostics features require their applicable entitlement. Component existence is an inventory observation. See licensing information.

Pass when you can explain the storage purpose, the new default destination, the allocation conditions, and the existing placement from actual evidence. No changes or cleanup are required. Default changes and controlled storage operations belong to their designated later exercise.

Quiz

1. Where should ordinary application tables be assigned?

Always SYSTEM. Always SYSAUX. A suitable permanent application tablespace identified from the configuration. The account's temporary tablespace.

2. An ordinary CREATE TABLE statement omits TABLESPACE. Which assignment chooses its destination?

The schema owner's default permanent tablespace. The largest tempfile. The current undo tablespace. The first SYSAUX occupant.

3. The quota view reports 2 MiB charged and a 50 MiB ceiling. What does 48 MiB represent?

Free filesystem space reserved for the owner. The remaining quota headroom in that tablespace. The table's row payload. The temporary tablespace's capacity.

4. No quota row appears for an account. What further fact can change the allocation conclusion?

Its default temporary name is TEMP. SYSTEM exists. A root query displays CON_ID. The account has UNLIMITED TABLESPACE.

5. Will changing a user's default tablespace move their current tables there?

Yes, all segments move immediately. Yes, at the next login. No; later default placement changes, while existing segments keep their current placement. Yes, but only the indexes move.

These notes use Oracle Database 19c, a single-instance multitenant lab, local COURSE_OWNER, and the instructor's read-only administrative identity. The instructor provisions the PDB and service, a finite quota, and the required dictionary access: DBA_TABLESPACES, DATABASE_PROPERTIES, DBA_USERS, DBA_TS_QUOTAS, DBA_SYS_PRIVS, and DBA_SEGMENTS. Optional SYSAUX and root reporting need their own delegated access. The account name COURSE_DBA conveys no automatic role.

The names, quota row, and changed-default timeline are authored examples verified against documentation. No database connection, query execution, table creation, or default or quota change was performed while preparing this lesson. Compare them with actual lab evidence. Do not treat them as captured output. The read-only practice requires no cleanup. Preserve actual credentials and host details outside teaching materials.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro