Question

怎么查看oracle表空间类型和大小？

查看oracle表空间属于永久、临时、UNDO哪种，通过cmd命令行查看，命令是什么？表空间最大大小怎么查看？当前大小怎么查看？

网上查找的方法我只看到当前大小，使用的。
没有看到最大大小

Answer 1

一、查看临时表空间
--查看所有临时表空间名字及文件名
select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

--查看当前用户默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--查看所有用户的默认表空间和默认临时表空间
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

--查看所有临时表空间大小
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "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(+);

二、查看所有表空间大小、使用
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a19 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 999,999,999,999 HEADING "KBytes"
COLUMN used FORMAT 999,999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999,999 HEADING "Largest"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT
set pagesize 2000
set line 300
--SPOOL tablespace_size.lst
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by pct_used desc
/
三、查看undo表空间大小、使用
select * from (select
a.tablespace_name,
sum(a.bytes)/(1024*1024) total_space_MB,
round(b.free,2) Free_space_MB,
round(b.free/(sum(a.bytes)/(1024*1024))* 100,2) percent_free
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) free from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name,b.free)
where tablespace_name = 'UNDOTBS1';