ORRCLE查看表空间

Love The Way You Lie 2022-03-08 03:59 217阅读 0赞

1------------- 查看表空间和使用情况  
SELECT a.tablespace\_name,   
a.bytes/(1024\*1024\*1024)||'G' total\_G,  
b.bytes/(1024\*1024\*1024)||'G' used\_G,   
c.bytes/(1024\*1024\*1024)||'G' free\_G,   
(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;

2---------------查看表空间物理文件的名称及大小

SELECT tablespace\_name,  
       file\_id,  
       file\_name,  
       round(bytes / (1024 \* 1024), 0)||'M' total\_space\_M  
  FROM dba\_data\_files  
 ORDER BY tablespace\_name;

\--------------

\--3、查看回滚段名称及大小   
SELECT segment\_name,   
tablespace\_name,   
r.status,   
(initial\_extent / 1024) initialextent,   
(next\_extent / 1024) nextextent,   
max\_extents,   
v.curext curextent   
FROM dba\_rollback\_segs r, v$rollstat v   
WHERE r.segment\_id = v.usn(+)   
ORDER BY segment\_name;

\--4、查看控制文件   
SELECT NAME FROM v$controlfile;

\--5、查看日志文件   
SELECT MEMBER FROM v$logfile;

\--6、查看数据库库对象   
SELECT owner, object\_type, status, COUNT(\*) count\#  
  FROM all\_objects  
 GROUP BY owner, object\_type, status;

\--7、查看数据库的版本　   
SELECT version  
  FROM product\_component\_version  
 WHERE substr(product, 1, 6) = 'Oracle';

\--8、查看数据库的创建日期和归档方式   
SELECT created, log\_mode, log\_mode FROM v$database;

\--9 查看临时表空间  
SELECT TABLESPACE\_NAME, FREE\_SPACE/1024/2014 AS "FREE SPACE(M)" FROM DBA\_TEMP\_FREE\_SPACE  
\--10 清空临时表空间  
alter tablespace TEST\_TEMP shrink space;

\-- 查看临时表数据信息

select c.tablespace\_name,

to\_char(c.bytes/1024/1024/1024,'99,999.999') total\_gb,

to\_char( (c.bytes-d.bytes\_used)/1024/1024/1024,'99,999.999') free\_gb,

to\_char(d.bytes\_used/1024/1024/1024,'99,999.999') use\_gb,

to\_char(d.bytes\_used\*100/c.bytes,'99.99') || '%'use

from  (select tablespace\_name,sum(bytes) bytes

from dba\_temp\_files GROUP by tablespace\_name) c,

(select tablespace\_name,sum(bytes\_cached) bytes\_used

from v$temp\_extent\_pool GROUP by tablespace\_name) d

where c.tablespace\_name = d.tablespace\_name;