Oracle DBA Lesson 56B — Create and Verify a Tablespace

A tablespace connects a logical storage name to the data files that hold its segments. Creating it in the intended container establishes that container's storage. Checking the catalog afterward tells you where Oracle placed its file and whether the resulting settings match the agreed design.

Oracle DBA Lesson 56B — Create and Verify a Tablespace

This lesson implements the smallfile plan from Part A: COURSE_TS53 in LABPDB, one Oracle managed data file starting at 64M, with automatic growth in 16M increments up to 256M.

Container ownership comes first

A permanent tablespace belongs to exactly one container. Creating a tablespace while connected to CDB$ROOT creates root storage. Ordinary application tables in LABPDB use storage belonging to LABPDB. Identical tablespace names in two containers identify separate storage. See Managing tablespaces in a CDB.

Connect through the approved LABPDB service as the designated administrator. In SQL*Plus, inspect the connection before issuing any storage DDL:

SHOW USER
SHOW CON_NAME

SELECT db_unique_name FROM v$database;

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV', 'CON_ID') AS container_id
FROM dual;

SELECT name, open_mode FROM v$pdbs;

SHOW is a SQL*Plus command. Confirm the recorded database identity, LABPDB, and its READ WRITE open mode. Check the approved service and connected account as well. A correct container name alone is insufficient identity evidence when multiple lab databases use that name.

Conditions for this lab

The instructor must provision and approve these conditions before the example:

• Oracle Database 19c, a single-instance Linux lab CDB, SQL*Plus and the intended writable LABPDB. Record the installed release update. These examples use ordinary 19c syntax, without IF EXISTS or IF NOT EXISTS.
• COURSE_DBA is the designated local administrator with CREATE TABLESPACE and DROP TABLESPACE in LABPDB, plus the required dictionary and fixed-view access. The optional quota exercise requires approved ALTER USER in that same container. A common administrator must have the required privileges effective in LABPDB and connect there. Root privileges alone do not select the application's storage. The exercise requires no broad DBA grant and no SYSDBA connection. See CREATE TABLESPACE, DROP TABLESPACE and ALTER USER.
• The effective DB_CREATE_FILE_DEST in the exact creating session is an approved destination. For this filesystem lab, the directory already exists and the Oracle operating-system identity has the necessary traversal and write permissions. The instructor verifies free capacity, filesystem limits, other consumers and any container storage limit. A nonempty parameter value supplies a destination. Actual storage headroom must also support this plan. See DB_CREATE_FILE_DEST.
• COURSE_TS53 is absent, unused and reserved for this exercise. There are no pre-existing quota rows for this name for any owner, including rows marked DROPPED. Stop if a previous exercise left any such state.
• The lab's effective encryption policy produces an unencrypted scratch tablespace. Omitting an encryption clause follows ENCRYPT_NEW_TABLESPACES. It does not itself disable encryption. The instructor confirms that policy and any applicable keystore requirements before creation. Encrypted or otherwise differently configured labs require an instructor-adjusted procedure. No encryption, compression, In-Memory or logging-policy changes are part of this exercise.
• The instructor has tested the lab's recovery procedure and the scratch reset sequence. Existing application data, production destinations and shared work are outside the exercise. Reserve the scratch name and prevent other writers from using it through creation, practice and cleanup.

Use the lab's existing delegated catalog access. A denied or incomplete query is a reason to resolve the access and visibility with the instructor before proceeding.

Capture the starting state

As COURSE_DBA in LABPDB, save the identity checks above and these results:

SELECT tablespace_name, contents, status
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_TS53';

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

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

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

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace = 'COURSE_TS53'
   OR temporary_tablespace = 'COURSE_TS53'
   OR local_temp_tablespace = 'COURSE_TS53';

SELECT name, value
FROM v$parameter
WHERE name IN ('db_create_file_dest', 'db_block_size',
               'encrypt_new_tablespaces');

The first two queries must return no rows for this reserved new name. Record the container defaults, owner's defaults and quota absence before adding anything. DBA_USERS must identify the pre-provisioned local, non-Oracle-maintained practice owner. The instructor confirms the effective destination in the actual creating session, including any session override, and verifies the directory independently under the database's operating-system identity. The exercise does not set DB_CREATE_FILE_DEST or create a new directory.

Create the planned storage

After the preflight passes, the approved creation example in LABPDB is:

CREATE SMALLFILE TABLESPACE course_ts53
  DATAFILE SIZE 64M
  AUTOEXTEND ON NEXT 16M MAXSIZE 256M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

SMALLFILE explicitly selects the file model. The DATAFILE clause supplies the size while omitting a filename, so Oracle generates an Oracle managed file under the effective destination. The growth clauses implement the already chosen bounds. LOCAL selects local extent management and AUTO selects automatic segment space management. The new permanent tablespace is online and read-write by default. See managed-file creation rules for how an unnamed file is placed, and file specification for SIZE, AUTOEXTEND, NEXT, and MAXSIZE.

Use a separate lab session with no unrelated uncommitted work. Storage DDL commits implicitly. ROLLBACK cannot undo a successful tablespace creation. See implicit commit rules.

Verify the actual file and attributes

Query all files for the newly created tablespace and save the exact result:

SELECT d.file_id, d.file_name, d.tablespace_name,
       d.bytes, d.autoextensible, d.maxbytes, d.increment_by,
       t.block_size,
       d.increment_by * t.block_size AS next_bytes,
       d.online_status
FROM dba_data_files d
JOIN dba_tablespaces t
  ON t.tablespace_name = d.tablespace_name
WHERE d.tablespace_name = 'COURSE_TS53'
ORDER BY d.file_id;

SELECT tablespace_name, bigfile, block_size, contents, status,
       extent_management, allocation_type, segment_space_management,
       encrypted
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_TS53';

These are the expected comparisons immediately after creation, before other allocations or configuration changes:

Field	What to check and why
File rows	Exactly one row for this example's single new data file. Record every row if the observed state differs, then investigate.
FILE_NAME	Actual generated file path belongs to the approved lab destination and recorded LABPDB identity. Preserve the complete name; do not guess an OMF filename.
BYTES	Initial 64M: 67,108,864 bytes, or 64 MiB. File metadata consumes some of this total; user allocation space is reported separately by USER_BYTES.
AUTOEXTENSIBLE	YES, matching the explicit AUTOEXTEND ON.
MAXBYTES	268,435,456 bytes, or 256 MiB. This is the file's configured growth ceiling; it is not reserved physical capacity.
INCREMENT_BY	A count of Oracle blocks. Multiply it by the observed tablespace BLOCK_SIZE. The product should be 16,777,216 bytes, or 16 MiB.
BIGFILE	NO, matching CREATE SMALLFILE.
BLOCK_SIZE	The actual byte value. Because the example omits BLOCKSIZE, use the database's standard block size, verified from this result.
CONTENTS / STATUS	PERMANENT / ONLINE for this new scratch tablespace.
EXTENT_MANAGEMENT	LOCAL.
ALLOCATION_TYPE	SYSTEM, reflecting default autoallocation for the locally managed permanent tablespace.
SEGMENT_SPACE_MANAGEMENT	AUTO.
ENCRYPTED	NO under the explicitly approved unencrypted lab baseline. Resolve a differing policy before practice.

For example, if the observed block size is 8,192 bytes, the expected INCREMENT_BY is 2,048 blocks: 2048 × 8192 = 16,777,216. An 8 KiB block size is an illustrative assumption, not a hidden requirement. See DBA_DATA_FILES and DBA_TABLESPACES.

Stop and investigate any mismatch. Save a file manifest linked to the database identity, LABPDB name/container ID, tablespace, file ID, full filename and observation time. The catalog verifies configuration. The instructor's storage check verifies the corresponding filesystem location and available capacity.

Defaults, privileges and quotas

Creating a tablespace leaves the container's and users' existing defaults in place. A table with an explicit TABLESPACE course_ts53 clause selects the new storage. If that clause is omitted, a conventional heap table uses its schema owner's default tablespace. See CREATE TABLE.

The table owner also needs CREATE TABLE and allocation rights in the target tablespace. This exercise uses a small quota. An effective UNLIMITED TABLESPACE privilege overrides quota limits and makes the proposed bounded exercise unsuitable. The instructor must choose an appropriately restricted practice account rather than altering an unrelated account's privileges. See Managing tablespaces for quota guidance.

Changing a container default is a separate policy action. For example, ALTER DATABASE DEFAULT TABLESPACE ... in the root changes the root's default. It does not make root storage available to ordinary LABPDB tables. Changing a user default with ALTER USER ... DEFAULT TABLESPACE ... is also a separate action. This exercise preserves both defaults.

Bounded placement practice

Perform this only after instructor approval for the named scratch objects, account and quota. As COURSE_DBA, confirm that the sole planned practice name is unused and capture the owner's current state:

SELECT owner, object_name, object_type
FROM dba_objects
WHERE owner = 'COURSE_OWNER'
  AND object_name = 'COURSE_TS53_PROBE';

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

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

The object lookup and quota lookup must be empty. The account must already have the approved CREATE SESSION and CREATE TABLE privileges. In the owner's intended practice session, inspect its effective privilege state:

SHOW USER
SHOW CON_NAME

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

Record this state, including enabled roles if privileges are role-derived. Require COURSE_OWNER in LABPDB, effective CREATE TABLE, and absence of UNLIMITED TABLESPACE. Do not add or revoke system privileges in this lesson.

As the approved quota administrator in LABPDB, add only this allowance:

ALTER USER course_owner QUOTA 8M ON course_ts53;

Save the resulting DBA_TS_QUOTAS row. Its expected MAX_BYTES is 8,388,608. The quota limits this owner's allocation in this scratch tablespace. See DBA_TS_QUOTAS for that meaning and the -1 unlimited value.

As COURSE_OWNER in LABPDB, create one ordinary heap table:

CREATE TABLE course_ts53_probe
  (id NUMBER, note VARCHAR2(30))
  SEGMENT CREATION IMMEDIATE
  TABLESPACE course_ts53;

INSERT INTO course_ts53_probe VALUES (1, 'placement check');
COMMIT;

SELECT table_name, tablespace_name, segment_created
FROM user_tables
WHERE table_name = 'COURSE_TS53_PROBE';

SEGMENT CREATION IMMEDIATE materializes the table's segment at creation, which makes the subsequent allocation check useful even before inserting the single row. This simple table introduces no indexes, primary keys, partitions or LOB columns.

As COURSE_DBA in the same LABPDB, verify the table's allocation and map all of its extents to the recorded file:

SELECT owner, segment_name, segment_type, tablespace_name, bytes
FROM dba_segments
WHERE owner = 'COURSE_OWNER'
  AND segment_name = 'COURSE_TS53_PROBE';

SELECT e.owner, e.segment_name, e.extent_id, e.file_id,
       e.tablespace_name, e.bytes AS extent_bytes, d.file_name
FROM dba_extents e
JOIN dba_data_files d
  ON d.file_id = e.file_id
 AND d.tablespace_name = e.tablespace_name
WHERE e.owner = 'COURSE_OWNER'
  AND e.segment_name = 'COURSE_TS53_PROBE'
ORDER BY e.extent_id;

The expected segment is the owner's TABLE in COURSE_TS53. Each returned extent must map to an actual file in the saved manifest. The views distinguish the allocated segment from its physical extent locations. A segment header alone does not inventory all its extents. Check while the tablespace and its file are online and catalog visibility is valid. See DBA_SEGMENTS and DBA_EXTENTS.

Practice passes when the container, table owner, explicit tablespace, materialized segment and all extent/file matches agree with the captured creation record. Confirm the original user and container defaults are intact.

Exact cleanup and reset

Cleanup is a separate approved step. It destroys the scratch objects and file. It requires the instructor's tested reset/recovery procedure and the saved identity, file manifest, quota baseline and results. End the practice activity and prevent new allocations first.

As COURSE_OWNER, recheck SHOW USER and SHOW CON_NAME, then remove only the exact table created by this exercise:

DROP TABLE course_ts53_probe PURGE;

The table and its row are discarded. PURGE bypasses the recycle bin. As COURSE_DBA in the confirmed LABPDB, remove only the newly added quota:

ALTER USER course_owner QUOTA 0 ON course_ts53;

This restores the original allocation permission for the captured absent quota baseline. A zero-limit dictionary row can remain. Verify the zero limit and no charged allocations before proceeding. If the captured state had a finite quota, an unlimited quota or a historical dropped quota row, this exercise should already have stopped. Do not overwrite that state with zero.

Inspect all ownership before the drop

First check allocated segments and all files, without an owner filter:

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

SELECT file_id, file_name, bytes, online_status
FROM dba_data_files
WHERE tablespace_name = 'COURSE_TS53'
ORDER BY file_id;

SELECT file_id, file_name
FROM dba_temp_files
WHERE tablespace_name = 'COURSE_TS53';

SELECT username, default_tablespace, temporary_tablespace,
       local_temp_tablespace
FROM dba_users
WHERE default_tablespace = 'COURSE_TS53'
   OR temporary_tablespace = 'COURSE_TS53'
   OR local_temp_tablespace = 'COURSE_TS53';

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

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

No allocated segments, default assignments or tempfiles should remain. The data files must exactly match the creation manifest. Any extra or changed file requires investigation. The sole lesson-added quota row must have a zero limit, if it remains. No other owner's quota is permitted by this reset.

The instructor must also inspect logical storage assignments, including objects whose segments were deferred, partition/subpartition defaults and recycle-bin contents. This compact checklist queries the whole local catalog:

SELECT 'TABLE' AS kind, owner, table_name AS object_name
FROM dba_tables WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'INDEX', owner, index_name
FROM dba_indexes WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'LOB', owner, table_name || '.' || column_name
FROM dba_lobs WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'TABLE PARTITION', table_owner, table_name || '.' || partition_name
FROM dba_tab_partitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'TABLE SUBPARTITION', table_owner, table_name || '.' || subpartition_name
FROM dba_tab_subpartitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'INDEX PARTITION', index_owner, index_name || '.' || partition_name
FROM dba_ind_partitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'INDEX SUBPARTITION', index_owner, index_name || '.' || subpartition_name
FROM dba_ind_subpartitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'LOB PARTITION', table_owner, table_name || '.' || partition_name
FROM dba_lob_partitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'LOB SUBPARTITION', table_owner, table_name || '.' || subpartition_name
FROM dba_lob_subpartitions WHERE tablespace_name = 'COURSE_TS53'
UNION ALL
SELECT 'RECYCLE BIN', owner, object_name
FROM dba_recyclebin WHERE ts_name = 'COURSE_TS53';

Also review any partitioned table/index/LOB future partition default that names this tablespace (DBA_PART_TABLES.DEF_TABLESPACE_NAME, DBA_PART_INDEXES.DEF_TABLESPACE_NAME, and DBA_PART_LOBS.DEF_TABLESPACE_NAME). These should all be absent in this isolated exercise. Confirm no services, jobs or application writers depend on the scratch storage. Stop if any unexpected ownership, assignment, active operation or dependency appears. INCLUDING CONTENTS must not become a shortcut for deleting it.

Remove the recorded scratch storage

Only after that empty, nondefault, reserved-target check passes, the approved administrator in the confirmed LABPDB may issue:

DROP TABLESPACE course_ts53
  DROP QUOTA
  INCLUDING CONTENTS AND DATAFILES;

INCLUDING CONTENTS AND DATAFILES removes the named tablespace and instructs Oracle to delete its associated files. OMF files are normally removed by the supported tablespace lifecycle anyway. DROP QUOTA explicitly removes quota rows for this tablespace. It is safe here only because the captured baseline had no quota rows for any owner, and only this lesson's new row was added. The default KEEP QUOTA would retain quota rows. A plain drop cannot be assumed to restore catalog absence. See DROP TABLESPACE syntax and DROP TABLESPACE.

Requery DBA_TABLESPACES, DBA_DATA_FILES, DBA_SEGMENTS, the logical assignment views and DBA_TS_QUOTAS for the scratch name. Confirm the created owner table is absent and compare user/container defaults and privileges with the saved baseline. Through the approved storage tools, check each exact recorded file path and the alert-log record of deletion. The tablespace drop can succeed even when a filesystem error prevents file deletion, so investigate any residual file with the instructor. Do not use broad directory deletion or guess a path. See Dropping tablespaces.

The reset passes when the scratch object, tablespace, added quota rows and recorded files are gone, existing defaults and privileges match the baseline, and unaffected lab tablespaces and client access still work. A successful DDL message alone is not the reset record.

A dropped tablespace has no recycle-bin undo and this DDL cannot be rolled back. For this empty scratch exercise, the tested reset restores the absent baseline. A later repeat starts from fresh preflight and creates a new OMF filename. Recreating the tablespace does not recover deleted data. If valued data was unexpectedly affected, stop further changes and use the instructor's rehearsed backup/recovery procedure, available backups/redo and keys when applicable. Backup pieces and archived redo remain governed by their separate retention plan. Dropping this scratch tablespace is not a backup cleanup step.

Recap

Create the reserved smallfile tablespace COURSE_TS53 in LABPDB: one Oracle managed data file at 64M, automatic growth of 16M, and a ceiling of 256M. Confirm the creating container, then compare the generated filename and catalog attributes with the approved plan. For the bounded practice, add an 8M quota for COURSE_OWNER, create COURSE_TS53_PROBE with explicit placement and immediate segment creation, and map every extent to the recorded file. Cleanup removes only that table, sets the added quota back to zero, and, after the ownership checks are empty, drops the tablespace with DROP QUOTA and INCLUDING CONTENTS AND DATAFILES. The drop cannot be rolled back, and a later recreate does not recover deleted data.

Quiz

1. Will a tablespace created in CDB$ROOT store ordinary LABPDB tables?

Yes, all permanent tablespaces are shared across the CDB. No, permanent tablespaces belong to their container. Yes, if its name is the same as one in LABPDB. Only when automatic growth is enabled.

2. What allows this example to omit the data filename?

SMALLFILE always chooses an operating-system directory. The effective, approved DB_CREATE_FILE_DEST enables OMF placement. The user's default tablespace supplies a new file path. Oracle creates an arbitrary missing parent directory automatically.

3. How do you verify the 16M automatic-growth increment?

Interpret INCREMENT_BY as bytes directly. Read DBA_SEGMENTS.NEXT_EXTENT as the file-growth increment. Multiply DBA_DATA_FILES.INCREMENT_BY by the observed tablespace block size. Divide MAXBYTES by the current row count.

4. After storage creation, what does the practice owner need to create the table there?

Only the tablespace name. A changed container default and a SYSDBA connection. Effective CREATE TABLE and the approved bounded quota, with explicit placement. A manually guessed OMF filename in the table statement.

5. Why does this reset explicitly use DROP QUOTA after restoring the added quota to zero?

A tablespace drop always removes all quota rows by default. It clears this lesson's added row to restore the captured all-owner absence. It rolls back the table's committed row. It deletes all backup pieces for the tablespace.

The statements in this lesson are unexecuted teaching examples. Expected values are comparison criteria, not captured database output. Run the practice in the designated Oracle Database 19c lab, confirm the exact release update, and record the real results from your instructor's designated lab.

AIOps Pro

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