Question

如何用两种方法查看oracle表空间物理文件的名称和大小

如何用两种方法查看oracle表空间物理文件的名称和大小

Answer 1

方法一：
[ sys@ORCL ]$COL NAME FOR A50
[ sys@ORCL ]$SELECT FILE#,STATUS,NAME FROM V$DATAFILE;

FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSTEM01.DBF
2 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSAUX01.DBF
3 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\UNDOTBS01.DBF
4 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS01.DBF
5 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\EXAMPLE01.DBF
6 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\RCOWNER.DBF
7 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS02.DBF
8 ONLINE D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\HIS_UNDO.DBF
方法二：

[ sys@ORCL ]$col file_name for a50;
[ sys@ORCL ]$SELECT FILE_NAME,FILE_ID,TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS01.DBF 4 USERS
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\UNDOTBS01.DBF 3 UNDOTBS1
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSAUX01.DBF 2 SYSAUX
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\RCOWNER.DBF 6 RCOWNER
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS02.DBF 7 USERS
D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\HIS_UNDO.DBF 8 HIS_DATA_UNDO
方法三：借助RMAN
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 720 SYSTEM *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSTEM01.DBF
2 680 SYSAUX *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\SYSAUX01.DBF
3 255 UNDOTBS1 *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\UNDOTBS01.DBF
4 6532 USERS *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\EXAMPLE01.DBF
6 20 RCOWNER *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\RCOWNER.DBF
7 20 USERS *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\USERS02.DBF
8 50 HIS_DATA_UNDO *** D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\HIS_UNDO.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 25 TEMP 25 D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\TEMP01.DBF
2 5 TEMP 5 D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\TEMP02.DBF