Show All Data Files in the Current Database in Oracle

Q

How To View Data Files in the Current Database in Oracle?

✍: FYIcenter.com

A

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

⇒ Create a New Data File in Oracle

⇐ Built-in Tablespaces in a Database in Oracle

⇑ Managing Oracle Tablespaces and Data Files

⇑⇑ Oracle Database Tutorials

2019-04-13, 3718🔥, 0💬

Keep Data Files When Tablespace Dropped in Oracle
What Happens to Data Files If a Tablespace Is Dropped in Oracle? If a tablespace is dropped, what happens to its data files? By default, data files will remain in OS file system, even if the tablespace they are mapped is dropped. Of course, you delete the data files using OS commands, if they are no... 2019-01-01, 3005🔥, 0💬

Show All Tablespaces in the Current Database in Oracle
How To View Tablespaces in the Current Database in Oracle? 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> SELECT TABLESP... 2019-01-20, 2773🔥, 0💬

Built-in Tablespaces in a Database in Oracle
What Are the Predefined Tablespaces in a Database in Oracle? When you create a new database, Oracle server will create 4 required tablespaces for the new database: SYSTEM Tablespace - Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is cr... 2019-01-20, 2350🔥, 0💬

Create a New Data File in Oracle
How To Create a New Oracle Data File in Oracle? There is no dedicated statement to create a data file. Data files are created as part of statements that manages other data structures, like tablespace and database. ⇒ Create a New Tablespace in Oracle ⇐ Show All Data Files in the Current Database i... 2019-04-13, 2206🔥, 0💬

Oracle Data File - Unit of Physical Storage in Oracle
What Is an Oracle Data File in Oracle? An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace. ⇒ Relations between a Tablespace and Data Files in Oracle ⇐ Oracle Ta... 2019-01-20, 2185🔥, 0💬

Create a New Tablespace in Oracle
How To Create a New Tablespace in Oracle? If you want a new dataspace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script: SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> SELECT TABLESPACE_NAME, S... 2019-04-13, 2024🔥, 0💬

Drop a Tablespace in Oracle
How To Drop a Tablespace in Oracle? If you have an existing tablespace and you don't want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below: SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created.... 2019-04-13, 2019🔥, 0💬

Rename a Tablespace in Oracle
How To Rename a Tablespace in Oracle? You can easily rename a tablespace by using the ALTER TABLESPACE ... RENAME TO statement as shown in the example below: SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> ALTER TABLESPACE my_space REN... 2019-04-13, 1953🔥, 0💬

Relation between Database and Tablespaces in Oracle
How a Database Is Related to Tablespaces in Oracle? A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consi... 2019-01-20, 1924🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.