如何查看Oracle表空间

如何查看Oracle表空间

1、查看数据库所有表空间

select tablespace_name, sum(bytes)/1024/1024 proportion from dba_data_files group by tablespace_name;

2、查看具体表单所占空间

Select Segment_Name,Sum(bytes)/1024/1024 proportion From User_Extents Group By Segment_Name

3、查看所有表空间占用率

SELECT 
c.ts#, c.name,  
d.contents, d.extent_management, 
e.file_bytes, c.used,
SUBSTR (c.used / e.file_bytes * 100, 1, 5) proportion --占比
FROM (SELECT name, ts#, SUM(used) used
          FROM (SELECT a.allocated_space * (SELECT value  -- 查询db_block_size当前值
                                              FROM v$parameter
                                             WHERE name = 'db_block_size') / 1024/ 1024 used,
                        b.ts#, b.name
                  FROM v$filespace_usage a, v$tablespace b
         WHERE a.tablespace_id = b.ts#)
         GROUP BY name, ts#
) c,dba_tablespaces d,
(SELECT ts#, SUM(bytes) / 1024/ 1024 file_bytes FROM v$datafile GROUP BY ts#) e      
WHERE c.name = d.tablespace_name
   AND e.ts# = c.ts#
ORDER BY ts#

4、查看具体dbf表空间占用率

select b.file_id,/*文件ID*/
　　b.tablespace_name,/*表空间*/
　　b.file_name,/*物理文件名*/
　　b.bytes,/*总字节数*/
　　(b.bytes-sum(nvl(a.bytes,0))) Used,/*已使用*/
　　sum(nvl(a.bytes,0)) surplus,/*剩余*/
　　sum(nvl(a.bytes,0))/(b.bytes)*100　proportion/*剩余百分比*/
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name

5、检查数据文件路径

Select * From dba_data_files t where t.TABLESPACE_NAME = 'CREAMMINT'; 

6、追加数据文件

Alter Tablespace CREAMMINT Add Datafile '/data3/oradata/creammint/creammint001.dbf' SIZE 20G; 

7、扩容原数据文件

alter database datafile '/ssddata/oradata/creammint/creammint001.dbf' RESIZE 20G;