Can We Change the Default Tablespace for a User in Oracle Autonomous Database?

• Storage segregation
• Performance management
• Application isolation
• Quota control

However, things work differently in Oracle Autonomous Database.

A very interesting behavior appears when trying to change the default tablespace for a user.

In this article, we will explore:

• How default tablespaces work in Autonomous Database
• What happens when ALTER USER is executed
• Why Oracle silently keeps DATA tablespace
• How Autonomous Database internally manages storage
• Important limitations of Autonomous Database

---

Checking the Current Default Tablespace

Let us first check the current default tablespace of a user:

SQL> select DEFAULT_TABLESPACE
     from dba_users
     where username='TEST_USER';

Output:

DEFAULT_TABLESPACE
---------------------
DATA

This confirms that the user TEST_USER uses the:

DATA tablespace

by default.

---

Available Tablespaces in Autonomous Database

Now let us inspect the available tablespaces:

SQL> select name from v$tablespace;

Output:

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

We can clearly see another tablespace called:

SAMPLESCHEMA

So naturally, the next step is trying to assign it as the default tablespace.

---

Attempting to Change the Default Tablespace

Now let us execute:

SQL> alter user TEST_USER
     default tablespace SAMPLESCHEMA;

User TEST_USER altered.

Interesting — Oracle reports:

User TEST_USER altered.

At first glance, it appears the operation succeeded successfully.

---

Verifying the Change

Now let us verify the result:

SQL> select DEFAULT_TABLESPACE
     from dba_users
     where username='TEST_USER';

Output:

DEFAULT_TABLESPACE
---------------------
DATA

---

Surprising Result: The Tablespace Did NOT Change

Even though Oracle displayed:

User TEST_USER altered.

the default tablespace still remains:

DATA

This reveals an important behavior of Oracle Autonomous Database:

Oracle Autonomous Database internally enforces DATA as the default user tablespace.

---

Why Does This Happen?

Oracle Autonomous Database is a fully managed cloud platform.

Oracle intentionally abstracts storage administration from users and DBAs.

Internally, Oracle wants:

• Simplified storage management
• Automated scaling
• Consistent storage architecture
• Automatic optimization
• Cloud-native automation

Allowing unrestricted tablespace management would complicate:

• Auto scaling
• Performance tuning
• Storage balancing
• Compression management
• Internal automation

---

Important Observation

The most interesting part is:

• The ALTER USER command does not throw an error
• The command reports success
• But the default tablespace remains unchanged

This behavior demonstrates that Autonomous Database may internally override or ignore unsupported storage-level administrative changes.

---

Understanding the DATA Tablespace

In Autonomous Database, the:

DATA

tablespace is the primary managed tablespace used for:

• User tables
• Indexes
• LOB storage
• Application schemas
• Partitions

Oracle automatically optimizes this tablespace behind the scenes.

---

Can SAMPLESCHEMA Be Used?

Although:

SAMPLESCHEMA

appears in:

v$tablespace

it may be:

• Oracle-managed internally
• Reserved for sample/demo schemas
• Protected from user assignment
• Not intended for customer workloads

Autonomous Database exposes several internal tablespaces that are not fully user-manageable.

---

Traditional Oracle vs Autonomous Database

Feature	Traditional Oracle DB	Autonomous Database
Create Tablespaces	Allowed	Restricted
Change Default Tablespace	Fully Supported	Internally Controlled
Manage Datafiles	DBA Managed	Oracle Managed
ASM Access	Available	Hidden
User Storage Layout	Flexible	Standardized

---

What Happens When TEST_USER Creates Objects?

Suppose the user creates a table:

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

The table will still be stored inside:

DATA tablespace

because Oracle continues enforcing DATA as the effective default tablespace.

---

How Oracle Autonomous Database Thinks Differently

Traditional DBAs often think in terms of:

• Separate tablespaces per application
• Storage tiering
• Manual datafile allocation
• Dedicated index tablespaces

Autonomous Database shifts the focus toward:

• Automation
• Self-management
• Elastic scaling
• Simplified operations
• Cloud-native administration

---

Best Practices in Autonomous Database

• Use the default DATA tablespace for user objects
• Avoid relying on manual tablespace architecture
• Allow Oracle to manage storage automatically
• Focus on schema and workload optimization
• Use OCI Console for storage scaling instead of DBA-managed datafiles

---

Key Takeaways

• New users in Autonomous Database use the DATA tablespace
• ALTER USER DEFAULT TABLESPACE may appear successful
• But Autonomous Database internally keeps DATA as the effective tablespace
• Storage management is heavily controlled by Oracle
• Autonomous Database prioritizes automation over manual storage customization

---

Conclusion

Oracle Autonomous Database fundamentally changes how storage administration works.

Even though SQL commands like:

ALTER USER ... DEFAULT TABLESPACE

may execute successfully, Oracle Autonomous Database internally enforces its managed storage architecture.

The experiment clearly demonstrates that:

Oracle Autonomous Database prefers all user workloads to remain inside the managed DATA tablespace.

This design helps Oracle deliver:

• Automatic scaling
• Self-healing infrastructure
• Performance optimization
• Simplified cloud operations

For DBAs transitioning from traditional Oracle environments, this is one of the most important architectural shifts to understand in Autonomous Database.