查看Oracle数据库中表空间信息的命令方法

发布时间：2020-12-12 14:28:51 所属栏目：百科来源：网络整理

导读：select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes/1024/1024||'M'字节数, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用, sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间, 100 - sum(nvl(a.bytes,0))/(b.bytes)*100 占用百分比 from

select 
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes/1024/1024||'M'字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用,
sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 占用百分比 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.bytes 
order by b.file_id;

1.未使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024from dba_free_space group by tablespace_name;

2.查看所有表空间大小 select tablespace_name,sum(bytes)/1024/1024from dba_data_files group by tablespace_name;
3.查询所有的表空间 select tablespace_name from dba_tablespaces;
4.在oracle数据库中，临时表空间主要用于用户在使用order by 、group by语句进行排序和汇总时所需的临时工作空间。要查询数据库中临时表空间的名称，大小及数据文件，可以查询数据字典dba_tablespaces及dba_data_files。命令如下： select a.tablespace_name space_name,b.bytes bytes,b.file_name filename from dba_tablespaces a,dba_data_files b where a.tablespace_name=b.tablespace_name and a.contents='TEMPORARY';
5.查看Oracle数据库中表空间信息的命令方法： select a.a1 space_name,c.c2 type,c.c3 quM,b.b2/1024/1024 space_size,
(b.b2-a.a2)/1024/1024 used,substr((b.b2-a.a2)/b.b2*100,1,5) rating
from
(select tablespace_name a1,sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;
6.查看Oracle数据库中表空间使用情况的命令方法：

SELECT upper(f.tablespace_name) "Tablespacename",
d.Tot_grootte_Mb "TablespaceSize",
d.Tot_grootte_Mb - f.total_bytes "Used",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "Rate",
f.total_bytes "Avail",
f.max_bytes "MaxBlock"
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;

--------------------------------
1.查看临时表空间使用情况： SELECT d.status "Status",d.tablespace_name "Name",d.contents "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024,0),'99,999,990.900') "Size (M)",
NVL(t.bytes,0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024,0) "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100,'990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
从oracle 9i开始，可以创建Temporary tablespace类表空间，即“临时“表空间，这类表空间使用临时文件。临时文件的信息被存储在数据字典V$tempfile中。命令如下： select file#,status,name from v$tempfile;
2.查询oracle系统用户的默认表空间和临时表空间 select default_tablespace,temporary_tablespace from dba_users; ---------------------------------------------------
3.分享: 如何查找正在使用临时表空间的session/sqltext
--------------------------------------------------- select
a.username,a.sid,a.serial#,a.process,
b.tablespace,b.segtype,b.userblocks,
d.current_users,d.total_blocks,d.used_blocks,d.free_blocks,
d.max_used_blocks,d.max_sort_blocks,
e.physizemb,e.phyblocks,round(d.max_used_blocks/e.phyblocks*100,2) max_used_ratio,
round(d.used_blocks/e.phyblocks*100,2) current_used_ratio,
round(b.userblocks/e.phyblocks*100,2) user_ratio,
c.sql_text
from
v$session a,
(select session_addr,tablespace,segtype,sum(blocks) userblocks from v$tempseg_usage group by session_addr,segtype) b,
v$sqltext c,
v$sort_segment d,round(sum(bytes)/1024/1024,2) physizemb,sum(blocks) phyblocks
from dba_temp_files group by tablespace_name) e
where
a.saddr = b.session_addr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
and c.piece = 0
and b.tablespace = d.tablespace_name
and b.tablespace = e.tablespace_name;
4.查询用户表空间的表 select * from user_tables;

获取创建表空间的语句：
select dbms_metadata.get_ddl('TABLESPACE','MLOG_NORM_SPACE') from dual;

--4确认磁盘空间足够，增加一个数据文件
alter tablespace MLOG_NORM_SPACE
add datafile '/Oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'
size 10M autoextend on maxsize 20G

--5验证已经增加的数据文件
select file_name,file_id,tablespace_name from dba_data_files
where tablespace_name='MLOG_NORM_SPACE'

--6如果删除表空间数据文件，如下：
alter tablespace MLOG_NORM_SPACE
drop datafile '/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!