oracle查询表空间信息
方式一，通过SQLPLUS查看，适用于无PLSQL等工具
sqlplus / as sysdba
set line 200
set lines 200
col tablespace_name for a20
col SUM_SPACE(M) for a15
col USED_SPACE(M) for a15
col USED_RATE(%) for a15
col FREE_SPACE(M) for a15
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
方式二，PLSQL工具查询
select b.tablespace_name,
trunc(nvl(a.bytes,0) / 1024 / 1024) FREE_SPACE_MB,
trunc(b.bytes / 1024 / 1024) TABLESPACE_SIZE_MB,
100 * round(1 -nvl(a.bytes,0)/ b.bytes, 4) "used%"
from (select tablespace_name, sum(nvl(bytes, 0)) bytes
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(nvl(bytes, 0)) bytes
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name
order by 4 desc;
--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,tablespace_name,r.status,(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,max_extents,v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
--10.
--查看表所占空间大小
show parameter db_block_size
select owner，table_name，NUM_ROWS，BLOCKS*db_block_size/1024/1024 "Size M"，EMPTY_BLOCKS，LAST_ANALYZED
from dba_tables where table_name like '%his_inf_not';
--11查看表空间的大表
select segment_name,
tablespace_name,
partition_name,
bytes / 1024 / 1024 MB
from user_segments
where tablespace_name iXXXX');
删除表空间
drop tablespace TBS_CCARE_DAT including contents and datafiles;
dba_segments中bytes的大小是这个表实际占用的空间大小。
通过dba_tables中统计的是表预计要占用的大小。
--12 查询临时表空间使用者信息
Select distinct s.SQL_ID,
se.MACHINE,
se.OSUSER,
se.username,
se.sid,
su.extents,
su.blocks,
su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
and tablespace = 'TEMP'
order by se.username, se.sid;