Which Tablespace Does a New User Use in Oracle Autonomous Database?

When working with Oracle Autonomous Database, many DBAs and developers wonder:

“If I create a new user, which tablespace will Oracle use automatically?”

Unlike traditional Oracle databases where DBAs manually create and assign tablespaces, Oracle Autonomous Database simplifies storage management significantly.

In this article, we will explore:

• How user creation works in Autonomous Database
• What default tablespace is assigned
• How Oracle automatically manages storage
• Where user objects are stored
• How to verify the default tablespace

---

Creating a New User

Let us create a new database user:

SQL> create user test_user identified by testoracle12345U#;

User TEST_USER created.

The user was successfully created.

---

Granting Privileges

Now let us grant privileges to the user:

SQL> grant connect, dba to test_user;

Grant succeeded.

The user now has CONNECT and DBA privileges.

---

Checking DBA_USERS Metadata

Oracle stores user-related information inside the DBA_USERS data dictionary view.

Let us inspect its structure:

SQL> desc dba_users

Relevant columns include:

USERNAME
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
ACCOUNT_STATUS
CREATED
PROFILE

The most important column for this discussion is:

DEFAULT_TABLESPACE

---

Finding the Default Tablespace

Now let us check which tablespace Oracle assigned to the new user:

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

Output:

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

---

Answer: New Users Use the DATA Tablespace

In Oracle Autonomous Database, newly created users automatically use:

DATA tablespace

This means all objects created by the user will be stored in the DATA tablespace unless explicitly specified otherwise.

Examples include:

• Tables
• Indexes
• LOBs
• Partitions
• Materialized views

---

Why Does Autonomous Database Use DATA Tablespace?

Oracle Autonomous Database is designed as a fully managed cloud service.

Oracle simplifies storage management by automatically directing user objects into a managed application tablespace called:

DATA

This removes the need for:

• Manual tablespace planning
• Storage allocation management
• Datafile administration
• ASM configuration
• Storage balancing

---

Traditional Oracle vs Autonomous Database

Feature	Traditional Oracle DB	Autonomous Database
Create Custom Tablespaces	Common	Restricted
Assign User Tablespaces	Manual	Automatic
Manage Datafiles	DBA Managed	Oracle Managed
ASM Administration	Required	Hidden
Default User Storage	User-defined	DATA tablespace

---

What Happens When TEST_USER Creates a Table?

Suppose the user creates a table:

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

Oracle automatically stores this table inside:

DATA tablespace

because it is the user's default tablespace.

---

How to Verify Where Objects Are Stored

You can verify the tablespace of user objects using:

SELECT owner,
       segment_name,
       tablespace_name
FROM dba_segments
WHERE owner='TEST_USER';

Expected output:

OWNER       SEGMENT_NAME    TABLESPACE_NAME
----------- --------------- ----------------
TEST_USER   EMPLOYEES       DATA

---

Temporary Tablespace

Along with a default permanent tablespace, Oracle also assigns a temporary tablespace.

You can check it using:

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

Typically, Autonomous Database uses:

TEMP

for sorting and temporary operations.

---

Can We Change the Default Tablespace?

In traditional Oracle databases, DBAs often use:

ALTER USER test_user
DEFAULT TABLESPACE users;

However, in Autonomous Database, options are limited because Oracle manages storage internally.

Since manual tablespace creation is restricted, most users continue using the default DATA tablespace.

---

Benefits of This Approach

1. Simplified Administration

Developers do not need to worry about storage allocation.

2. Better Automation

Oracle automatically handles:

• Storage growth
• Autoextend
• Compression
• Performance optimization

3. Reduced DBA Overhead

No need to manually manage dozens of application tablespaces.

4. Cloud-Native Design

The architecture aligns with fully managed cloud database principles.

---

Important Note About DBA Role in Autonomous Database

Even though:

grant connect, dba to test_user;

was successful, Autonomous Database still restricts several administrative operations.

For example:

• CREATE TABLESPACE may fail
• ASM access is blocked
• OS-level access is unavailable
• Datafile management is restricted

This is expected behavior in Autonomous Database.

---

Best Practices

• Use the default DATA tablespace for application objects
• Avoid attempting ASM operations
• Monitor tablespace usage periodically
• Let Oracle manage storage automatically
• Focus on schema and application design instead of storage administration

---

Key Takeaways

• New users in Autonomous Database automatically use the DATA tablespace
• Oracle manages storage internally
• Manual tablespace creation is restricted
• User objects are automatically stored inside DATA
• The DBA role in Autonomous Database is still partially restricted

---

Conclusion

Oracle Autonomous Database changes the traditional DBA experience by abstracting low-level storage management.

When a new user is created, Oracle automatically assigns the DATA tablespace as the default storage location for user objects.

This cloud-native approach simplifies administration, reduces operational complexity, and allows developers and DBAs to focus more on application workloads rather than storage infrastructure.

So the next time you create a user in Autonomous Database, remember:

Oracle automatically handles the tablespace management for you.