Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show All Data Files in the Current Database

By: FYIcenter.com

(Continued from previous topic...)

How To View Data Files in the Current Database?

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> col tablespace_name format a16;
SQL> col file_name format a36;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                           BYTES
--------------- ------------------------------- ---------
USERS           \ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            \ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840

(Continued on next topic...)

1. What Is an Oracle Tablespace?
2. What Is an Oracle Data File?
3. How a Tablespace Is Related to Data Files?
4. How a Database Is Related to Tablespaces?
5. How To View Tablespaces in the Current Database?
6. What Are the Predefined Tablespaces in a Database?
7. How To View Data Files in the Current Database?
8. How To Create a New Oracle Data File?
9. How To Create a New Tablespace?
10. How To Rename a Tablespace?
11. How To Drop a Tablespace?
12. What Happens to Data Files If a Tablespace Is Dropped?
13. How To Create a Table in a Specific Tablespace?
14. How To See Free Space of Each Tablespace?
15. How To Bring a Tablespace Offline?
16. How To Bring a Tablespace Online?
17. How To Add Another Datafile to a Tablespace?
18. What Happens If You Lost a Data File?
19. How Remove Data Files before Opening a Database?