Enhance Skills Daily

October 04, 2026  ·   · 

Oracle DBA Lesson 56 Part 2 — Create Storage in the Correct PDB

Create an ordinary permanent application tablespace in its intended pluggable database, then verify its physical file, growth settings and space-management policy. A successful creation supplies storage. Application use also depends on the selected tablespace, the user's object-creation privilege and sufficient quota.

Oracle DBA Lesson 56 Part 2 — Create Storage in the Correct PDB

Check the container, unused name, and destination

In Oracle Database 19c, ordinary application storage belongs to the open read-write PDB that will use it. Confirm the container, the unused tablespace name, and the effective file destination:

SHOW CON_NAME

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

SHOW PARAMETER db_create_file_dest

The current container is APP_PDB. The tablespace query returns no rows while APP_DATA is absent. A tablespace belongs to the container in which it is created. An application tablespace created in CDB$ROOT does not become an ordinary PDB tablespace.

SHOW commands are SQL*Plus client commands. The SELECT statement is SQL. The value shown is the effective DB_CREATE_FILE_DEST for this PDB and session, and creation uses that destination. A filesystem directory must already exist and permit Oracle to create files. A suitable ASM destination is also supported. The parameter value alone does not establish writability or usable capacity. The storage report includes other consumers, growth, and headroom. Part 1 shows that MAXSIZE does not reserve future disk capacity.

See Using Oracle Managed Files and DB_CREATE_FILE_DEST.

Create the bounded tablespace

CREATE SMALLFILE TABLESPACE app_data
  DATAFILE SIZE 64M
  AUTOEXTEND ON NEXT 16M MAXSIZE 256M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
ClausePurpose
SMALLFILEExplicit file model; additional files are possible within applicable limits
DATAFILE SIZE 64MInitial physical file allocation of 64 MiB
AUTOEXTEND ON NEXT 16MEnables autoextension with a minimum 16 MiB increment
MAXSIZE 256MMaximum automatic size of this file; future growth needs available storage
EXTENT MANAGEMENT LOCALLocal bitmaps manage extent allocation
SEGMENT SPACE MANAGEMENT AUTOBitmaps manage available space inside segments

M denotes binary megabytes: 1 MiB = 1,048,576 bytes. With an effective Oracle Managed Files destination, the statement omits the physical filename. Oracle creates and names the file at that managed destination. The statement leaves user defaults unchanged, grants neither CREATE TABLE nor quota, and leaves existing objects where they are. APP_DATA stays nondefault.

The logical tablespace belongs to APP_PDB. Its data file is a physical file on disk. The diagram's trace connects those two identities. It depicts that relationship, and it does not show data or a file moving between containers.

CREATE TABLESPACE performs an implicit commit. ROLLBACK cannot undo it. DROP TABLESPACE is separate DDL and also commits implicitly.

See CREATE TABLESPACE and Managing Tablespaces. See COMMIT and DROP TABLESPACE.

Verify file placement and growth settings

SELECT file_id, file_name,
       bytes / 1048576 AS size_mib,
       autoextensible,
       maxbytes / 1048576 AS max_mib
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id;

The planned initial row is:

FILE_NAMESIZE_MIBAUTOEXTENSIBLEMAX_MIB
…/datafile/o1_mf_app_data_….dbf64YES256

The ellipses shorten the display only. They are not executable path syntax, and they are not a complete generated filename. Record every full FILE_NAME from the query result. An OMF path can include generated directories and identifiers. A readable directory label does not establish which container owns the file. The current PDB and its dictionary row establish the container and tablespace association. Confirm that the full path belongs to the intended physical destination, and include every file for that tablespace.

BYTES is the allocated file size. MAXBYTES is the maximum file size. Neither value measures remaining filesystem or ASM capacity, or the table payload. Continue monitoring the shared destination. To verify NEXT, join the file's INCREMENT_BY to the matching tablespace BLOCK_SIZE, as Part 1 shows. INCREMENT_BY is in Oracle blocks, not bytes.

See DBA_DATA_FILES.

Verify tablespace policy and availability

SELECT bigfile,
       extent_management AS extent_mgmt,
       segment_space_management AS space_mgmt,
       status
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';
BIGFILEEXTENT_MGMTSPACE_MGMTSTATUS
NOLOCALAUTOONLINE

NO confirms smallfile. LOCAL and AUTO match the chosen extent management and segment space management. ONLINE describes tablespace availability. Investigate unexpected or missing rows. A successful CREATE message leaves the settings, the file inventory, and the container still to be confirmed together.

See DBA_TABLESPACES.

Defaults, explicit placement, privileges, and quota

Creating APP_DATA leaves the existing defaults in place. Read the user default, the container default, and any quota on the new tablespace:

SELECT username, default_tablespace
FROM dba_users
WHERE username = 'APP_USER';

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

SELECT username, tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = 'APP_USER'
  AND tablespace_name = 'APP_DATA';

See DATABASE_PROPERTIES, DBA_USERS, and DBA_TS_QUOTAS.

When APP_USER's default tablespace is USERS, an ordinary new heap table that omits a TABLESPACE clause is created in USERS. TABLESPACE app_data selects APP_DATA. These are alternative statements. Each one places order_items in a different tablespace:

CREATE TABLE order_items (id NUMBER);

CREATE TABLE order_items (id NUMBER)
  TABLESPACE app_data;

A default chooses placement. Quota limits the user's allocation in the selected tablespace. The user needs the object-creation privilege and enough quota, unless UNLIMITED TABLESPACE applies. A quota does not reserve physical disk. DBA_TS_QUOTAS.MAX_BYTES = -1 indicates unlimited quota. An absent quota row can still coincide with UNLIMITED TABLESPACE, so inspect the applicable system privileges as well.

See CREATE TABLE, CREATE USER, Creating User Accounts, and ALTER USER.

Changing a user's default tablespace affects later object creation that uses that default. Existing segments remain in their original tablespaces. The container default has a broader scope. ALTER DATABASE DEFAULT TABLESPACE reassigns non-SYSTEM user defaults and influences later object creation. Existing objects stay where they are. Review the affected users and the release-specific behavior before a container-default change. Creating APP_DATA leaves both defaults unchanged. These are the separate statements:

ALTER USER app_user DEFAULT TABLESPACE app_data;

ALTER DATABASE DEFAULT TABLESPACE app_data;

See ALTER DATABASE and DBA_SEGMENTS.

Recap

Create the permanent tablespace in the PDB that should own it. For this application, that container is the open read-write PDB APP_PDB, the name APP_DATA is unused, and DB_CREATE_FILE_DEST is the effective managed destination. The statement creates a smallfile tablespace with a 64 MiB file, autoextension by at least 16 MiB, a 256 MiB maximum, local extent management, and automatic segment space management. Oracle generates the filename. Defaults, CREATE TABLE, and quota stay as they were, and ROLLBACK cannot undo the DDL. DBA_DATA_FILES should then show 64 MiB allocated, autoextension YES, and a 256 MiB maximum, with the full generated path taken from the query. DBA_TABLESPACES should show smallfile (NO), LOCAL, AUTO, and ONLINE. BYTES and MAXBYTES describe the file. They do not describe free destination capacity or table payload. INCREMENT_BY is in Oracle blocks. A heap table that omits TABLESPACE uses the user's default, such as USERS. TABLESPACE app_data selects the new tablespace. The user still needs the object-creation privilege and quota unless UNLIMITED TABLESPACE applies. MAX_BYTES of -1 means unlimited quota. Changing a default affects later objects that use it and leaves existing segments where they are.

Quiz

1. Where should ordinary application storage for APP_PDB be created?

2. What permits the example to omit a physical filename?

3. Which file-query values match the planned initial state?

4. APP_USER's default is USERS. Which clause explicitly selects the new storage for an ordinary table?

5. What happens to existing segments when a user's default tablespace changes?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...