查询语句:
select a.file_id "FileNo",
a.file_name,
a.tablespace_name "Tablespace_name",
round(a.bytes / 1024 / 1024, 4) "Total MB",
round((a.bytes - sum(nvl(b.bytes, 0))) / 1024 / 1024, 4) "Used MB",
round(sum(nvl(b.bytes, 0)) / 1024 / 1024, 4) "Free MB",
round(sum(nvl(b.bytes, 0)) / a.bytes * 100, 4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name, a.file_id, a.file_name, a.bytes
order by a.tablespace_name;
a.file_name,
a.tablespace_name "Tablespace_name",
round(a.bytes / 1024 / 1024, 4) "Total MB",
round((a.bytes - sum(nvl(b.bytes, 0))) / 1024 / 1024, 4) "Used MB",
round(sum(nvl(b.bytes, 0)) / 1024 / 1024, 4) "Free MB",
round(sum(nvl(b.bytes, 0)) / a.bytes * 100, 4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name, a.file_id, a.file_name, a.bytes
order by a.tablespace_name;
增加表空间:
Meathod1:给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
Meathod2:新增数据文件,并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod3:允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
Meathod4:手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
RESIZE 100M;