Oralce Study Notes: Query statements to view information about data files

■ How to view various database data files?

1) view data files

SQL> select file_name, Bytes/(1024*1024) M0 from dba_data_files;


2) view the control file

SQL> select name, status from V $ controlfile;


3) view log files

SQL> select group #, Member, status from V $ logfile;
■ How to view the free space of each tablespace?

SQL> DESC dba_free_space

SQL> select tablespace_name, sum (bytes)/1024/1024 free_mbytes

From dba_free_space

Group by tablespace_name

Order by free_mbytes;
■ How to view the path of the data file?

SQL> select tablespace_name, file_id, Bytes/1024/1024, file_name

From dba_data_files order by file_id;

■ How can I check whether data files are automatically extended?

SQL> Col file_name format A40

SQL> Col tablespace_name format A20

SQL> select file_id, file_name, tablespace_name, autoextensible from dba_data_files order by file_id;

■ How can I view the disk usage of each tablespace?

SQL> Col tablespace format A20

SQL> select

B. file_id "file_id ",

B. tablespace_name "tablespace_name ",

B. bytes "byte_num ",

(B. bytes-sum (nvl (A. bytes, 0) "used ",

Sum (nvl (A. bytes, 0) "remaining_space ",

Sum (nvl (A. bytes, 0)/(B. bytes) * 100 "percent"

From dba_free_space A, dba_data_files B

Where a. file_id = B. file_id

Group by B. tablespace_name, B. file_id, B. bytes

Order by B. file_id;