How to Check Default Tablespace of a User in Oracle ?

## 📘 What Are Data Dictionary Tables?

In Oracle, **data dictionary tables** are system-owned tables that store metadata about the database.

They contain information such as:

* Users and roles

* Tables and indexes

* Tablespaces

* Privileges and permissions

These tables are owned by the `SYS` user and are accessed through **views** like:

* `DBA_USERS`

* `ALL_USERS`

* `USER_USERS`

👉 Think of them as a **database about your database**.

---

## 🔍 Query to Check Default Tablespace of a User

The most common way to check a user’s default tablespace is using the `DBA_USERS` view.

### ✅ Basic Query

```sql

SELECT username, default_tablespace

FROM dba_users

WHERE username = 'YOUR_USERNAME';

```

🔹 Replace `'YOUR_USERNAME'` with the actual username (in uppercase).

---

### ✅ Example

```sql

SELECT username, default_tablespace

FROM dba_users

WHERE username = 'HR';

```

**Output:**

```

USERNAME   DEFAULT_TABLESPACE

--------   ------------------

HR         USERS

```

---

### ⚠️ Note

* You need **DBA privileges** to query `DBA_USERS`.

* If you don’t have access, use:

```sql

SELECT username, default_tablespace

FROM user_users;

```

or

```sql

SELECT username, default_tablespace

FROM all_users;

```

---

## 🧊 What Is Default Tablespace?

A **default tablespace** is the location where a user’s objects are stored if no tablespace is explicitly specified.

### 📌 Example:

```sql

CREATE TABLE employees (

  id NUMBER,

  name VARCHAR2(100)

);

```

👉 This table will be created in the user’s **default tablespace**.

---

## 🔄 Temporary Tablespace Explained

A **temporary tablespace** is used for **temporary operations**, such as:

* Sorting data

* Hash joins

* Global temporary tables

---

### 🔍 Check Temporary Tablespace

```sql

SELECT username, temporary_tablespace

FROM dba_users

WHERE username = 'HR';

```

---

## ⚖️ Default vs Temporary Tablespace

| Feature          | Default Tablespace       | Temporary Tablespace              |

| ---------------- | ------------------------ | --------------------------------- |

| Purpose          | Stores permanent objects | Used for temporary operations     |

| Data Persistence | Permanent                | Temporary (cleared automatically) |

| Example Use      | Tables, indexes          | Sorting, joins                    |

| Storage Type     | Datafiles                | Tempfiles                         |

---

## 🧠 Key Differences Explained

* **Default Tablespace**

  * Stores actual database objects

  * Data remains until explicitly deleted

* **Temporary Tablespace**

  * Used only during query execution

  * Data disappears after operation completes

---

## 🚀 Pro Tips

✔ Always assign a **dedicated default tablespace** for users instead of using `SYSTEM`

✔ Monitor tablespace usage regularly to avoid issues like space exhaustion

✔ Use separate temporary tablespaces for heavy workloads to improve performance

---

## 🧾 Conclusion

Understanding default and temporary tablespaces is essential for efficient database management in Oracle. By using simple queries on data dictionary views like `DBA_USERS`, you can quickly identify where user data is stored and how temporary operations are handled.

---

If you want, I can also:

* Add **images/diagrams for your blog**

* Create **SEO title + tags**

* Suggest **related Oracle topics** to grow your blog traffic