Creating Locally Managed Tablespaces in Oracle AI Database 26ai

A locally managed tablespace (LMT) tracks extent allocation through bitmaps stored in its datafiles. It is an established part of Oracle storage administration and the normal choice for modern application tablespaces. The DBA selects an allocation policy; Oracle maintains the structures that identify allocated and available space.

In this lesson, you will create two application tablespaces in a pluggable database (PDB). One lets Oracle select extent sizes with AUTOALLOCATE; the other uses a fixed extent size with UNIFORM. You will then verify those choices separately from file capacity and automatic file growth.

What Local Extent Management Does

A segment obtains storage in extents, each consisting of contiguous database blocks within a datafile. As a segment needs additional allocated space, Oracle can assign another extent from its tablespace. Local management records that allocation using the tablespace's own bitmap structures rather than the central bookkeeping used by legacy dictionary-managed extent allocation.

This does not remove the data dictionary. Object definitions, tablespace metadata, privileges, and administrative views still exist. Nor does every row insertion require a new extent. An insert may fit into space already allocated to the segment. Extent allocation, block free-space management, and optimizer statistics maintenance are different activities.

Local management also makes manual free-extent coalescing unnecessary. That benefit should not be interpreted as eliminating every kind of unused space. A table can still have sparsely populated blocks, and an allocation policy can still reserve more space than a small object needs. Diagnose the particular storage condition before proposing a reorganization.

Separate the Four Storage Controls

Several clauses contain words such as automatic or space, but they control different things. Understanding those differences prevents a common mistake: changing file growth settings to solve an extent-allocation or schema-quota problem.

ASSM uses its own bitmaps to describe space availability inside eligible segments. LMT bitmaps describe extent allocation. They work together in the application tablespaces created here, but they are not interchangeable features. Object storage settings such as PCTFREE are another consideration and should not be described as the tablespace's extent bitmap.

Choose AUTOALLOCATE or UNIFORM

AUTOALLOCATE for Objects of Different Sizes

With AUTOALLOCATE, Oracle selects extent sizes as segments develop. This is a practical starting point for an application containing a mixture of small lookup tables, larger transaction tables, and indexes with different growth patterns. The DBA does not need to predict one uniform extent size that suits every segment.

Do not build an application around an assumed sequence of extent sizes. Actual allocations depend on database and segment characteristics. A simple statement that every extent starts at exactly 64 KB omits relevant block-size qualifications. Observe the resulting allocations when their size matters to a capacity investigation.

UNIFORM for an Intentional Allocation Policy

UNIFORM SIZE requests a consistent extent size within the tablespace. This can be useful when the administrator has a specific reason to standardize allocation units and understands the objects placed there. The choice should follow a measured storage requirement, rather than a general belief that fixed sizes always improve performance.

For example, large uniform extents can allocate disproportionately large amounts of space to many small segments. Smaller extents may require more allocations as large segments grow. Consider the population of objects, expected growth, and administrative goals together. The 1 MB uniform extent used below is a teaching example, not a universal production recommendation.

Both options use local extent management. Choosing between them does not mean choosing between modern and legacy storage. For a normal new permanent application tablespace, local management is the modern default; the examples specify it explicitly so the intended configuration is clear.

Check the PDB and Storage Destination

Run the examples in a learning PDB that is open read-write. Use an authorized account with CREATE TABLESPACE and permission to query the administrative views shown later. Confirm that the names APP_AUTO and APP_UNIFORM are not already in use.

Connect through the intended PDB service. In SQL*Plus or SQLcl, check the current container and Oracle Managed Files destination:

SHOW CON_NAME
SHOW PARAMETER db_create_file_dest

These are client commands, not SQL statements. The destination must be approved, writable by the database, and have sufficient capacity. Oracle Managed Files (OMF) lets Oracle generate and manage filenames. It can use a suitable filesystem or ASM destination; OMF does not automatically mean ASM.

The examples omit filenames deliberately. If OMF is not configured, obtain appropriate unused datafile paths from the database administrator and include those paths in the DATAFILE clauses. Do not reuse another database's paths or add REUSE merely to make a failing statement succeed.

These statements also follow the deployment's encryption policy. If encryption is required, TDE and the necessary PDB key material must already be available. Omitting an encryption clause does not guarantee unencrypted storage. Use the previous lesson's encryption guidance when checking this dependency.

Create the AUTOALLOCATE Tablespace

Create an ordinary permanent application tablespace using local extent management and ASSM:

CREATE BIGFILE TABLESPACE app_auto
  DATAFILE SIZE 200M
  AUTOEXTEND ON NEXT 100M MAXSIZE 2G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

BIGFILE makes the file model explicit: this tablespace has one datafile. Its initial allocation is 200 MB, and automatic extensions are requested in 100 MB increments up to a 2 GB maximum. These small sizes suit a laboratory demonstration; production sizing should follow workload and capacity requirements.

LOCAL AUTOALLOCATE lets Oracle choose extent sizes. SEGMENT SPACE MANAGEMENT AUTO enables ASSM for the application segments. The two clauses make separate decisions even though both involve bitmap-based management.

Creating this tablespace does not create application tables or assign a schema quota. It establishes a storage destination. Application object creation still depends on privileges, quota, and the object DDL selecting the intended tablespace.

Create the UNIFORM Tablespace

Keep the file and segment-space settings the same so the extent-allocation difference is easy to examine:

CREATE BIGFILE TABLESPACE app_uniform
  DATAFILE SIZE 200M
  AUTOEXTEND ON NEXT 100M MAXSIZE 2G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  SEGMENT SPACE MANAGEMENT AUTO;

The UNIFORM SIZE 1M clause selects the extent allocation unit. It does not replace the datafile size or autoextension increment. Keeping those values separate is essential when reviewing storage scripts.

Do not divide file size by extent size and assume the result is the exact number of available application extents. File metadata and space-management structures consume capacity too. Similarly, setting a maximum file size does not reserve that much space on the underlying device.

Both examples are bigfile tablespaces. You cannot grow them by adding a second datafile. Their existing files must grow within the relevant limits. A smallfile design is a different choice and should be identified explicitly in any example that adds files.

Verify the Tablespace Definitions

Check what Oracle recorded rather than relying only on successful DDL completion. Run the following query from the same PDB with authorized dictionary access:

SELECT tablespace_name, contents, bigfile,
       extent_management, allocation_type,
       segment_space_management, block_size
FROM   dba_tablespaces
WHERE  tablespace_name IN ('APP_AUTO', 'APP_UNIFORM')
ORDER  BY tablespace_name;

Both definitions should identify permanent tablespaces with BIGFILE = YES, EXTENT_MANAGEMENT = LOCAL, and SEGMENT_SPACE_MANAGEMENT = AUTO. The important difference is ALLOCATION_TYPE: expect SYSTEM for APP_AUTO and UNIFORM for APP_UNIFORM.

Here, SYSTEM means system-selected extent allocation. It does not mean the application data is stored in the database's SYSTEM tablespace. BLOCK_SIZE reports bytes per database block and provides useful context when interpreting extent sizes.

These are expected properties of the supplied definitions, not output captured from an executed lab. If your results differ, confirm the PDB, object names, and actual DDL used before changing anything.

Verify Datafile Capacity Separately

The allocation method does not tell you how much file capacity exists. Query the files independently:

SELECT tablespace_name,
       ROUND(bytes / POWER(1024, 2), 1) AS allocated_mib,
       autoextensible,
       ROUND(maxbytes / POWER(1024, 2), 1) AS maximum_mib
FROM   dba_data_files
WHERE  tablespace_name IN ('APP_AUTO', 'APP_UNIFORM')
ORDER  BY tablespace_name;

The units are MiB because the calculation divides bytes by 1,048,576. ALLOCATED_MIB describes file capacity, not the amount of application data stored in the file. The file can contain both allocated segment space and space available for future allocation.

Read AUTOEXTENSIBLE alongside MAXIMUM_MIB. A maximum-size column alone is not evidence that a file can currently extend. For these examples, autoextension is enabled, but physical storage exhaustion or another applicable limit can still prevent growth before the configured ceiling.

If you later combine file capacity and free-space information, aggregate each source at the intended reporting level before joining it. Joining multiple free extents directly to file rows can repeat file sizes and produce inflated totals.

Observe Extents in an Existing Segment

To examine actual extent allocations, use an existing nonpartitioned heap table owned by the current user. Replace the illustrative name below with its dictionary name:

SELECT segment_name, segment_type, extent_id,
       bytes / POWER(1024, 2) AS extent_mib
FROM   user_extents
WHERE  segment_name = 'YOUR_EXISTING_TABLE'
ORDER  BY segment_type, extent_id;

This query inspects allocations; it does not force them. Newly created tablespaces do not automatically contain application table segments. An empty result can mean the name is wrong, the object belongs to another user, or no segment has yet been allocated. Deferred segment creation is one possible explanation for an empty table.

When comparing policies, first confirm which tablespace contains the selected table. Different extent sizes in a system-allocated segment do not indicate corruption. Nor does the number of extents alone establish that a table is inefficient. Relate allocation observations to the actual capacity or performance question being investigated.

Troubleshoot the Controlling Limit

A storage failure is easier to resolve when you identify the layer responsible. Changing several unrelated settings at once obscures the cause and can remove useful limits.

• No schema quota: An application user can fail to allocate space even when the file has free capacity. Check its quota and object privileges. Do not grant UNLIMITED TABLESPACE as a routine workaround.
• File at MAXSIZE: Autoextension stops at its configured ceiling. Review expected growth and available capacity before approving a larger limit.
• Storage destination full: A generous MAXSIZE cannot create physical capacity. Investigate the filesystem or ASM destination and competing consumers.
• Wrong container: A tablespace created in another PDB is not the intended application's storage. Confirm scope before repeating the DDL.
• Missing privilege: CREATE TABLE and CREATE TABLESPACE authorize different operations. Request the appropriate administrative action.
• Encryption dependency: If policy requires encryption, resolve key availability through the established TDE procedure rather than weakening the policy.

For an application schema, relevant object privileges and a tablespace quota are separate from the DBA's ability to create storage. Also check whether UNLIMITED TABLESPACE overrides an intended quota. File limits and user limits should express deliberate, documented decisions.

Keep Specialized Tablespaces Separate

The examples apply to ordinary permanent application storage. SYSTEM is core database storage established during provisioning; do not reproduce these scripts with SYSTEM substituted as the name. In particular, do not prescribe UNIFORM allocation or ASSM for SYSTEM.

Temporary tablespaces use tempfiles and specialized allocation rules. Undo tablespaces support transaction consistency and recovery with their own restrictions. Local extent management across these categories does not imply that all CREATE TABLESPACE clauses apply identically. Use the relevant administrative procedure for each category.

Likewise, changing an existing application's allocation design requires planning. Do not assume that a populated AUTOALLOCATE tablespace can simply be switched to UNIFORM. Assess a new destination and an appropriate object-movement procedure, including dependent objects, available capacity, and application availability.

Apply the Choice to an Application

Suppose a business application has hundreds of small reference tables and a few transaction tables that grow continuously. AUTOALLOCATE provides a straightforward starting point because the objects do not share one predictable allocation requirement. Review growth over time instead of selecting a large uniform extent merely to accommodate the largest table.

Now consider a separately managed collection of objects for which the team has deliberately standardized allocation units. UNIFORM can express that policy clearly. Before adopting it, estimate the effect on small objects as well as large ones, and verify that the expected benefit is relevant to the workload. A more regular allocation pattern is not, by itself, proof of faster SQL.

In either case, keep the decision independent of the file budget. Two tablespaces can use different extent policies while having identical initial file sizes and growth limits, as this lesson demonstrates. Conversely, two AUTOALLOCATE tablespaces can have very different capacity limits because their applications have different retention requirements. Record both decisions so future maintenance does not confuse an allocation choice with a capacity restriction.

Record and Review the Result

Document the PDB, tablespace purpose, extent policy, file limits, and authorized application owners. Include the verification results so another administrator can distinguish the intended design from a later accidental change.

Before accepting the configuration, answer three questions: Does the allocation policy fit the objects? Can the application allocate space within its quota? Can the files grow within the physical storage budget? A successful CREATE statement answers only part of that review.

Next lesson: Explore transportable tablespaces and the requirements for moving a tablespace set between databases.

Oracle Documentation

• Managing Tablespaces
• CREATE TABLESPACE
• DBA_TABLESPACES
• Storage Clause