空間修改及查詢

SELECT B.FILE_ID 檔案ID號
,B.TABLESPACE_NAME 表空間名
,B.FILE_NAME 檔名
,B.BYTES / 1024 / 1024 "空間(M)"
,(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 "已使用(M)"
,SUM(NVL(A.BYTES, 0)) / 1024 / 1024 "剩餘空間(M)"
,SUM(NVL(A.BYTES, 0)) / (B.BYTES) * 100 剩餘百分比
FROM DBA_FREE_SPACE A
,DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
AND B.TABLESPACE_NAME = 'POD' --GTD
GROUP BY B.TABLESPACE_NAME
,B.FILE_ID
,B.FILE_NAME
,B.BYTES
ORDER BY B.FILE_ID;
SELECT d.status "Status"
,d.tablespace_name "Name"
,a.file_name
,d.contents "Type"
,d.extent_management "Extent Management"
,to_char(nvl(a.bytes / 1024 / 1024,
0),
'99,999,990.900') "Size (M)"
,nvl(t.bytes,
0) / 1024 / 1024 || '/' ||
nvl(a.bytes / 1024 / 1024,
0) "Used (M)"
,to_char(nvl(t.bytes / a.bytes * 100,
0),
'990.00') "Used %"
FROM d
,(SELECT tablespace_name
,file_name
,SUM(bytes) bytes
FROM

GROUP BY tablespace_name
,file_name) a
,(SELECT tablespace_name
,SUM(bytes_cached) bytes
FROM
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)

AND d.contents LIKE 'TEMPORARY'SELECT d.status "Status"
,d.tablespace_name "Name"
,a.file_name
,d.contents "Type"
,d.extent_management "Extent Management"
,to_char(nvl(a.bytes / 1024 / 1024,
0),
'99,999,990.900') "Size (M)"
,nvl(t.bytes,
0) / 1024 / 1024 || '/' ||
nvl(a.bytes / 1024 / 1024,
0) "Used (M)"
,to_char(nvl(t.bytes / a.bytes * 100,
0),
'990.00') "Used %"
FROM sys.dba_tablespaces d
,(SELECT tablespace_name
,file_name
,SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name
,file_name) a
,(SELECT tablespace_name
,SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
/*ALTER DATABASE DATAFILE '/oracle/gttest/gttestdata/pod01.dbf' RESIZE 550M;*/

[@more@]