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.
