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, 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
/home/oracle/app/oracle/oradata/orcl/tbs01.dbf TBS1 100
注意,这里是查不到临时表空间的数据文件的,如果要查临时表空间的,可以使用dba_temp_files
SQL> select file_name, tablespace_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
环境: 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, 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
/home/oracle/app/oracle/oradata/orcl/tbs01.dbf TBS1 100
注意,这里是查不到临时表空间的数据文件的,如果要查临时表空间的,可以使用dba_temp_files
SQL> select file_name, tablespace_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