a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*
100,
2) percent_used
from (
select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by ((a.bytes - b.bytes) /
a.bytes) desc
查询所有表空间的总容量、已经使用、剩余、已经使用的百分比！
select a.tablespace_name,a.bytes/
1024/
1024 "Sum MB",(a.bytes-b.bytes)/
1024/
1024 "used MB",b.bytes/
1024/
1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*
100,
2)
"percent_used"
from
(select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=
b.tablespace_name
order by ((a.bytes-b.bytes)/
a.bytes) desc
一般来说可以把上面的复杂的查询语句放入一个文件中，需要时再调用，或者创建一个试图，需要时可以查询。
1 写入文件:#vi /home/mzl/
percent_used_tablespace.sql
内容：
select a.tablespace_name,a.bytes/
1024/
1024 "Sum MB",(a.bytes-b.bytes)/
1024/
1024 "used MB",b.bytes/
1024/
1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*
100,
2)
"percent_used"
from
(select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=
b.tablespace_name
order by ((a.bytes-b.bytes)/
a.bytes) desc
2 导入：
SQL> @/home/mzl/
percent_used_tablespace.sql
SQL>
l
1 select a.tablespace_name,a.bytes
"Sum",a.bytes-b.bytes
"used",b.bytes
"free",
2 round(((a.bytes-b.bytes)/a.bytes)*
100,
2)
"percent_used"
3 from
4 (
select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
5 (
select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
6 where a.tablespace_name=
b.tablespace_name
7* order by ((a.bytes-b.bytes)/
a.bytes) desc
SQL> /
或者创建视图：
create view percent
as
select a.tablespace_name,a.bytes/
1024/
1024 "Sum MB",(a.bytes-b.bytes)/
1024/
1024 "used MB",b.bytes/
1024/
1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*
100,
2)
"percent_used"
from
(select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=
b.tablespace_name
order by ((a.bytes-b.bytes)/
a.bytes) desc;
SQL>
select *
from percent;
查看表空间的数据文件是否是自动扩展：
SQL>
select file_name,tablespace_name,autoextensible
from dba_data_files
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/
risenet.dbf RISENET
/u01/app/oracle/oradata/orcl/
perfstat.dbf PERFSTAT NO
/u01/app/oracle/oradata/orcl/
example01.dbf EXAMPLE YES
/u01/disk1/
users01.dbf USERS YES
/u01/app/oracle/oradata/orcl/
sysaux01.dbf SYSAUX YES
/u01/app/oracle/oradata/orcl/
undotbs01.dbf UNDOTBS1
/u01/disk2/
system01.dbf SYSTEM YES
/u01/app/oracle/oradata/orcl/
undotbs02.dbf UNDOTBS2 NO
/u01/disk1/
pioneer_data.dbf PIONEER_DATA YES
/u01/disk2/
pioneer_indx.dbf PIONEER_INDX NO
/u01/disk3/
pioneer_undo.dbf PIONEER_UNDO NO
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/
paul01.dbf PAUL NO
/u01/disk1/
wenchuan.dbf WENCHUAN NO
13 rows selected.
比如表空间PIONEER_INDX已经用了83.33％，数据文件不能自动扩展，可以修改成自动扩展，以免数据写满数据文件。
SQL> alter database datafile
‘/u01/disk2/pioneer_indx.dbf‘ autoextend on;
Database altered.
SQL>
select file_name,tablespace_name,autoextensible
from dba_data_files
where tablespace_name=
‘PIONEER_INDX‘;
FILE_NAME TABLESPACE_NAME AUT
--------------------------------------------- ------------------------------ ---
/u01/disk2/
pioneer_indx.dbf PIONEER_INDX YES
或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘，可以增加多个数据文件（这样多数据库系统的并发性比较好）
SQL>
alter tablespace pioneer_indx add datafile size 30M;
Tablespace altered.
SQL>
select file_name,tablespace_name,bytes/
1024/
1024 "MB"
from dba_data_files
where tablespace_name=
‘PIONEER_INDX‘;
oracle查询表空间的空间占用情况
标签：max pre 调用 altered 扩展 app oracl creat where
