0 like 0 dislike
154 views

Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (4k points)  

Viewing Information about Tablespaces and Datafiles

Oracle has provided many Data dictionaries to view information about tablespaces and datafiles.  Some of them are:

To view information about Tablespaces in a database give the following query

SQL> select * from dba_tablespaces ;
SQL> select * from v$tablespace;

To view information about Datafiles

SQL> select * from dba_data_files;
SQL> select * from v$datafile;

To view information about Tempfiles

SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;

To view information about free space in datafiles

SQL> select * from dba_free_space;

To view information about free space in tempfiles

SQL> select * from V$TEMP_SPACE_HEADER;
0 like 0 dislike
answered by (4k points)  

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> set lines 200;
SQL> set pages 200;
SQL> /

TABLESPACE_NAME                FILE_NAME                                                       used MB
------------------------------ ------------------------------------------------------------ ----------
SYSTEM                         /oradata/CDMPROD/system01.dbf                                       820
SYSAUX                         /oradata/CDMPROD/sysaux01.dbf                                      1110
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_01.dbf                              32765
USERS                          /oradata/CDMPROD/users01.dbf                                   16266.25
IDX_CDM_01                     /oradata/CDMPROD/idx_cdm_01.dbf                                       5
UNDOTBS1                       /oradata/CDMPROD/undotbs01.dbf                                     9805
DAI_SECDATA                    /oradata/CDMPROD/dai_secdata01CDMPROD.dbf                            10
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_02.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_03.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_04.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_05.dbf                              32765
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_06.dbf                               8768
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_07dbf                                8768
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_08.dbf                               8704

14 rows selected.

Related questions

0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
...