表空间、数据文件对应关系

select t2.ts#,a.tablespace_name,a.file_id, a.file_name,a.bytes / 1024 / 1024 as total_mb,
       t.fee_mb,
       a.autoextensible
  from sys.dba_data_files a
  left join (select ts#,name from v$tablespace )  t2
  on a.tablespace_name=t2.name
  left join (select b.tablespace_name,b.file_id,sum(b.bytes)/1024/1024 as fee_mb from sys.dba_free_space b group by  b.tablespace_name,b.file_id) t
    on a.file_id = t.file_id
    where a.tablespace_name='TEST0309'；

 

select t2.ts#,a.tablespace_name,a.file_id, a.file_name,a.bytes / 1024 / 1024 as total_mb,
       nvl(t.fee_mb,0),
       a.autoextensible
  from sys.dba_data_files a
  left join (select ts#,name from v$tablespace )  t2
  on a.tablespace_name=t2.name
  left join (select b.tablespace_name,b.file_id,sum(b.bytes)/1024/1024 as fee_mb from sys.dba_free_space b group by  b.tablespace_name,b.file_id) t
    on a.file_id = t.file_id
    where a.tablespace_name='TEST0312'；

 

select t2.ts#,a.tablespace_name,a.file_id, a.file_name,a.bytes / 1024 / 1024 as total_mb,
       nvl(t.free_mb,0) free_mb,
       a.autoextensible
  from sys.dba_data_files a
  left join (select ts#,name from v$tablespace )  t2
  on a.tablespace_name=t2.name
  left join (select b.tablespace_name,b.file_id,sum(b.bytes)/1024/1024 as free_mb from sys.dba_free_space b group by  b.tablespace_name,b.file_id) t
    on a.file_id = t.file_id
    where a.tablespace_name='TEST0319';

 

转载于:https://www.cnblogs.com/iyoume2008/p/6526878.html