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;
| Clause | Purpose |
|---|---|
SMALLFILE | Explicit file model; additional files are possible within applicable limits |
DATAFILE SIZE 64M | Initial physical file allocation of 64 MiB |
AUTOEXTEND ON NEXT 16M | Enables autoextension with a minimum 16 MiB increment |
MAXSIZE 256M | Maximum automatic size of this file; future growth needs available storage |
EXTENT MANAGEMENT LOCAL | Local bitmaps manage extent allocation |
SEGMENT SPACE MANAGEMENT AUTO | Bitmaps 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_NAME | SIZE_MIB | AUTOEXTENSIBLE | MAX_MIB |
|---|---|---|---|
…/datafile/o1_mf_app_data_….dbf | 64 | YES | 256 |
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';
| BIGFILE | EXTENT_MGMT | SPACE_MGMT | STATUS |
|---|---|---|---|
NO | LOCAL | AUTO | ONLINE |
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.
No comments:
Post a Comment