表空间22

7.8    表空间
--查询表空间
select tablespace_name,
       count(*) as extends,
       round(sum(bytes) / 1024 / 1024, 2) as MB,
       sum(blocks) as blocks from dba_free_space group by tablespace_name;
--查询表空间总容量
select tablespace_name,
       sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
--查询表空间使用率
select total.tablespace_name,
       round(total.MB, 2) as Total_MB,
       round(total.MB - free.MB, 2) as Used_MB,
       round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from (select tablespace_name,
                                                                               sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
       (select tablespace_name,
               sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name;
--增加表空间
alter tablespace xxxx add datafile '/u01/xxxx'  size 1000M;


---查看表空间2
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) "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 * from dba_data_files;
修改表空间
alter database datafile '/opt/oracle/oradata/sdprpt/rsdp_rpt_rpt_user_data_0001.dbf' resize 1024M￡?