Oracle使用dba_data_files查看表空间大小及数据文件位置

Oracle中可以使用dba_data_files查询表空间的名称、大小、数据文件位置等其他信息。

环境: Oracle 12c
测试过程：
sql> col file_name for a60
sql> col tablespace_name for a15
sql> set pagesize 100
sql> set linesize 150
sql> SELEct file_name,tablespace_name,BYTES/1024/1024 AS MB from dba_data_files;

file_name tablespace_name MB
------------------------------------------------------------ --------------- ----------
/home/Oracle/app/Oracle/oradata/orcl/users01.dbf USERS 5
/home/Oracle/app/Oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 70
/home/Oracle/app/Oracle/oradata/orcl/system01.dbf SYstem 810
/home/Oracle/app/Oracle/oradata/orcl/sysaux01.dbf SYSAUX 490

sql> create tablespace tbs1 DATAFILE '/home/Oracle/app/Oracle/oradata/orcl/tbs01.dbf' size 100m autoextend on maxsize 1000m ;

tablespace created.


sql> SELEct file_name,BYTES/1024/1024 AS MB from dba_data_files;

file_name tablespace_name MB
------------------------------------------------------------ --------------- ----------
/home/Oracle/app/Oracle/oradata/orcl/users01.dbf USERS 5
/home/Oracle/app/Oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 70
/home/Oracle/app/Oracle/oradata/orcl/system01.dbf SYstem 810
/home/Oracle/app/Oracle/oradata/orcl/sysaux01.dbf SYSAUX 490
/home/Oracle/app/Oracle/oradata/orcl/tbs01.dbf TBS1 100

注意，这里是查不到临时表空间的数据文件的，如果要查临时表空间的，可以使用dba_temp_files
sql> SELEct file_name,BYTES/1024/1024 AS MB from dba_temp_files; file_name tablespace_name MB ------------------------------------------------------------ --------------- ---------- /home/Oracle/app/Oracle/oradata/orcl/temp01.dbf TEMP 131