Sunday, 24 May 2026

Can We Create a New Tablespace in Oracle Autonomous Database?

One of the most common questions Oracle DBAs ask when working with Oracle Autonomous Database is:

“Can I manually create a new tablespace like we do in traditional Oracle databases?”

The short answer is:

No — Oracle Autonomous Database does not allow users to manually create tablespaces.

In this blog, we will understand:

  • Why CREATE TABLESPACE fails in Autonomous Database
  • What ORA-01031 means
  • How storage works internally in Autonomous Database
  • How much free space is available
  • How Oracle manages tablespaces automatically
  • Best practices for storage management in Autonomous Database

The Attempt to Create a Tablespace

Let us try creating a custom tablespace: 

SQL> create tablespace test_tbs datafile '+DATA' size 10g;

Output: 

Error starting at line : 1 in command -
create tablespace test_tbs datafile '+DATA' size 10g

Error report -
ORA-01031: insufficient privileges

Understanding ORA-01031

The ORA-01031 error means: 

ORA-01031: insufficient privileges

This indicates that the current user does not have permission to perform the requested operation.

In traditional on-premises Oracle databases, a DBA user with proper privileges can:

  • Create tablespaces
  • Add datafiles
  • Manage ASM disks
  • Resize storage
  • Create bigfile tablespaces

However, Oracle Autonomous Database is a fully managed cloud service.

Oracle intentionally restricts administrative operations to maintain:

  • Cloud automation
  • Self-healing infrastructure
  • Security isolation
  • Automatic scaling
  • Storage optimization

Why CREATE TABLESPACE is Restricted in Autonomous Database

In Autonomous Database:

  • Oracle manages storage internally
  • ASM is hidden from customers
  • Datafiles are managed automatically
  • Tablespaces are pre-created and optimized
  • Storage scaling is handled by Oracle Cloud

That is why this command fails: 

create tablespace test_tbs datafile '+DATA' size 10g;

The +DATA disk group belongs to Oracle-managed ASM infrastructure and is not directly accessible to users.

Existing Tablespaces in Autonomous Database

Let us inspect the existing tablespaces: 

SQL> select name from v$tablespace;

Output: 

NAME
---------------
SYSTEM
SYSAUX
UNDO_21871
DATA
DBFS_DATA
TEMP
SAMPLESCHEMA
UNDO_4F8D9

These are Oracle-managed tablespaces automatically provisioned by the Autonomous service.

Understanding Important Tablespaces

Tablespace Purpose
SYSTEM Core Oracle data dictionary
SYSAUX Auxiliary system components
UNDO_* Undo management
TEMP Temporary operations and sorting
DATA Main user/application data
DBFS_DATA Database File System storage
SAMPLESCHEMA Sample/demo schemas

How Much Free Space is Available?

Now let us check the available space inside the Autonomous Database. 

SELECT
    df.tablespace_name AS "Tablespace",
    total_mb AS "Total MB",
    (total_mb - free_mb) AS "Used MB",
    free_mb AS "Free MB",
    ROUND((free_mb / total_mb) * 100, 2) AS "% Free"
FROM
    (SELECT tablespace_name,
            ROUND(SUM(bytes) / 1048576) AS total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name,
            ROUND(SUM(bytes) / 1048576) AS free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name;

Tablespace Space Usage Output

Tablespace         Total MB    Used MB    Free MB    % Free
--------------- ----------- ---------- ---------- ---------
SYSTEM                 1311       1302          9      0.69
SYSAUX                 3340       2855        485     14.52
DATA                   1124        255        869     77.31
DBFS_DATA               100          8         92        92
UNDO_21871             1024        665        359     35.06
UNDO_4F8D9              100          2         98        98
SAMPLESCHEMA         204800

Analyzing the Free Space

1. DATA Tablespace

The most important user tablespace is: 

DATA

It currently has:

  • Total Size: 1124 MB
  • Used Space: 255 MB
  • Free Space: 869 MB
  • Free Percentage: 77.31%

This means your Autonomous Database still has significant free space available for application objects such as:

  • Tables
  • Indexes
  • Partitions
  • LOBs
  • Materialized views

2. SYSTEM Tablespace

SYSTEM tablespace is nearly full: 

SYSTEM  →  0.69% free

But this is completely managed by Oracle internally.

Customers should never attempt to modify SYSTEM or SYSAUX tablespaces in Autonomous Database.

3. SAMPLESCHEMA Tablespace

Interestingly: 

SAMPLESCHEMA = 204800 MB

This large allocation is internally managed and may represent cloud-managed storage allocation rather than actual physical consumption.

Where Should Users Store Data?

In Autonomous Database, Oracle expects users to store application data primarily inside: 

DATA tablespace

Example: 

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

By default, user objects are created in the DATA tablespace.

How Storage Works in Autonomous Database

Unlike traditional databases, storage in Autonomous Database is abstracted from the user.

Oracle Automatically Handles:

  • Datafile creation
  • ASM management
  • Disk redundancy
  • Autoextend operations
  • Storage balancing
  • Compression optimization
  • Encryption
  • Scaling

This is one of the biggest advantages of Autonomous Database.

Can We Increase Storage?

Yes.

Although users cannot manually create tablespaces, they can:

  • Increase database storage from OCI Console
  • Enable auto scaling
  • Scale CPU and storage independently

Oracle automatically adjusts underlying storage allocation.

Traditional Oracle vs Autonomous Database

Feature Traditional Oracle DB Autonomous Database
Create Tablespace Allowed Restricted
Manage ASM Allowed Hidden
Add Datafiles Manual Automatic
Storage Scaling Manual Automatic
Tuning Storage DBA Managed Oracle Managed
Autoextend Manual Configuration Managed by Oracle

Best Practices

  • Use the default DATA tablespace for user objects
  • Do not attempt ASM operations
  • Avoid modifying SYSTEM/SYSAUX
  • Monitor free space periodically
  • Use OCI Console for scaling storage
  • Leverage Autonomous auto scaling features

Key Takeaways

  • Oracle Autonomous Database does not allow manual tablespace creation
  • CREATE TABLESPACE fails with ORA-01031
  • Storage management is fully automated by Oracle
  • Users primarily use the DATA tablespace
  • Your DATA tablespace currently has 869 MB free space
  • Autonomous Database abstracts low-level storage administration

Conclusion

Oracle Autonomous Database fundamentally changes how DBAs manage storage.

Instead of manually creating tablespaces, datafiles, and ASM disk groups, Oracle handles everything automatically behind the scenes.

The ORA-01031 error while creating a tablespace is expected behavior and is part of the Autonomous architecture design.

The good news is that users still have sufficient storage available inside the DATA tablespace, and Oracle automatically manages scaling, optimization, and storage allocation for the database.

This allows DBAs and developers to focus more on:

  • Application development
  • SQL optimization
  • Analytics
  • Security
  • Business logic

rather than worrying about low-level storage administration.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)