oracle表空间查询

谁借莪１个温暖的怀抱￠ 2022-07-30 13:48 146阅读 0赞

\--修改表空间为自动增长修改语句  
alter database datafile '文件路径' autoextend on;  
alter database datafile '文件路径' autoextend on;  
alter database datafile '/oracle/oip/aaa.dbf' autoextend on;  
  
SELECT tablespace\_name,FILE\_NAME,TABLESPACE\_NAME,AUTOEXTENSIBLE FROM dba\_data\_files;  
  
\--2、查看表空间物理文件的名称及大小   
SELECT tablespace\_name,   
file\_id,   
file\_name,   
round(bytes / (1024 \* 1024), 0) total\_space   
FROM dba\_data\_files   
ORDER BY tablespace\_name;   
\--6、查看表空间的使用情况   
SELECT SUM(bytes) / (1024 \* 1024) AS free\_space, tablespace\_name   
FROM dba\_free\_space   
GROUP BY tablespace\_name;   
SELECT a.tablespace\_name,   
a.bytes total,   
b.bytes used,   
c.bytes free,   
(b.bytes \* 100) / a.bytes "% USED ",   
(c.bytes \* 100) / a.bytes "% FREE "   
FROM sys.sm$ts\_avail a, sys.sm$ts\_used b, sys.sm$ts\_free c   
WHERE a.tablespace\_name = b.tablespace\_name   
AND a.tablespace\_name = c.tablespace\_name;   
  
  
\--1G=1024MB   
\--1M=1024KB   
\--1K=1024Bytes   
\--1M=11048576Bytes   
\--1G=1024\*11048576Bytes=11313741824Bytes   
SELECT a.tablespace\_name "表空间名",   
total "表空间大小",   
free "表空间剩余大小",   
(total - free) "表空间使用大小",   
total / (1024 \* 1024 \* 1024) "表空间大小(G)",   
free / (1024 \* 1024 \* 1024) "表空间剩余大小(G)",   
(total - free) / (1024 \* 1024 \* 1024) "表空间使用大小(G)",   
round((total - free) / total, 4) \* 100 "使用率 %"   
FROM (SELECT tablespace\_name, SUM(bytes) free   
FROM dba\_free\_space   
GROUP BY tablespace\_name) a,   
(SELECT tablespace\_name, SUM(bytes) total   
FROM dba\_data\_files   
GROUP BY tablespace\_name) b   
WHERE a.tablespace\_name = b.tablespace\_name