oracle表空间相关记录

记录oracle表空间相关
select upper(f.tablespace_name) “表空间名”,
d.tot_grootte_mb “表空间大小(m)”,
d.tot_grootte_mb - f.total_bytes “已使用空间(m)”,
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
‘990.99’) “使用比”,
f.total_bytes “空闲空间(m)”,
f.max_bytes “最大块(m)”
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 4 desc;

select dbf.tablespace_name,
dbf.totalspace “总量(M)”,
dbf.totalblocks as 总块数,
dfs.freespace “剩余总量(M)”,
dfs.freeblocks “剩余块数”,
(dfs.freespace / dbf.totalspace) * 100 “空闲比例”
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

增大表空间
方法1.扩容表空间
alter database datafile 表空间位置 resize 10G 如：
alter database datafile ‘/data/is2/is201.dbf’ resize 10G
方法2.增加数据文件
alter tablespace add datafile ‘数据文件地址’ size 1000M 如：
alter tablespace ZHZY add datafile ‘/u01/app/oracle/oradata/orcl/zs01.dbf’ size 10G

查询表空间名字和位置
select tablespace_name ,file_id,file_name,round(bytes / (1024*1024),0) total_space from sys.dba_data_files order by tablespace_name;

设置表空间自动扩展
alter database datafile ‘数据文件位置’ autoextend on next 自动扩展大小 maxsize 最大扩展大小
alter database datafile '/data/is2/is202.dbf’autoextend on next 100M maxsize 10G

查看表空间是否自动扩展
select file_name,autoextensible,increment_by from dba_data_files

查看用户所属表空间
select username,default_tablespace from dba_users where username = ‘YONGHU’–用户名大写

查询表空间路径
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files