Oracle--查询表空间、schema、表等存储情况操作

一、对表空间的查询

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。

Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。

附：
1、oracle 用户与表空间关系
2、oracle 表空间和schema
3、关于表空间、Schema和用户
4、ORACLE 中SCHEMA的概念以及数据库，表空间，数据文件等的区别

1、查询所有表空间名

select tablespace_name from sys.dba_tablespaces;

2、查询所有表空间大小

//单位M
SELECT DD.TABLESPACE_NAME,
　　    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) "表空间大小(M)"
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME

3、查询所有表空间剩余空间大小

SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES) / (1024 * 1024), 2) "空闲空间(M)",
       ROUND(MAX(BYTES) / (1024 * 1024), 2) "最大块(M)"
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME

4、表空间已使用大小

--方法一：总表空间 - 空闲表空间
select d.tablespace_name, d.totalSize - f.freeSize "已使用空间(M)"
from 
(
    SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) totalSize
    FROM SYS.DBA_DATA_FILES DD
    GROUP BY DD.TABLESPACE_NAME
) d,
(
    SELECT TABLESPACE_NAME,
           ROUND(SUM(BYTES) / (1024 * 1024), 2) freeSize
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME
) f
where d.TABLESPACE_NAME = f.TABLESPACE_NAME

--方法二：
select TABLESPACE_NAME,round(sum(bytes)/(1024*1024),2) "已使用空间(M)" from sys.dba_segments GROUP BY TABLESPACE_NAME

注：建议采用第一种方法，第二种方法会忽略未使用的表空间，且第一种方法得到的结果普遍比第二种方法得到的结果大1M，第一种方法得到的结果应该更准确

5、增加表空间大小的方式

--第一种：格式化数据文件初始大小并设置自增长到最大值
create tablespace d_test1  datafile '/test1_data/datafile01.dbf' size 10m autoextend on next 5m maxsize 100m;

--第二种：格式化数据文件初始大小不设置自增长，当然也就没有最大值
alter tablespace d_test1 add datafile '/test1_data/datafile02.dbf' size 10m;

二、对schema的查询

1、查询所有schema

资料来源：
1、Oracle DB , 计算各个用户/schema 的磁盘占用空间

//一个用户（user）对应一个方案（schema）
select username from sys.dba_users

2、查询schema下的所有表

select table_name from sys.dba_tables where owner='schema名';

3、查询所有的schema及所属的所有表

select s.username "schema",t.table_name "table_name" from sys.dba_users s left join sys.dba_tables t on s.username=t.owner order by s.username asc

4、查询所有的schema所占空间大小

//消耗的总磁盘空间（包括索引、表空间空闲空间），单位G
select 
   owner,
   sum(bytes)/1024/1024/1024 schema_size_gig
from 
   sys.dba_segments 
group by 
   owner;

5、查询指定schema各segment_type占用空间大小

select 
   sum(bytes)/1024/1024/1024 as size_in_gig, 
   segment_type
from 
   dba_segments
where 
   owner='SCOTT' 
group by 
   segment_type;

Drop Table 之后，如果空间不能回收，需要执行下面语句

  清除当前用户的回收站：purge recyclebin;

  清除所有用户的回收站：purge dba_recyclebin;

三、查询表

1、通过指定表空间查询所属的表名

Select Table_Name, Tablespace_Name
From Dba_Tables
Where Tablespace_Name = 'USERS';

2、通过指定表空间查询所属表的占用空间

select segment_name, bytes/1024||'KB' "占用空间"
from user_segments 
where segment_type='TABLE' and tablespace_name='USERS' order by segment_name asc

资料来源：
1、Oracle DB , 计算各个用户/schema 的磁盘占用空间
2、oracle表空间总大小 剩余大小