查看Oracle表空间SQL语句
1.查看所有表空间大小
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024tablespacesize_M FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;
2.未使用的表空间大小
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024TABSPACE_FREE_SIZE_M FROM DBA_FREE_SPACE
group by TABLESPACE_NAME;
3.所有使用空间可以这样计算
SELECT a.tablespace_name,a.total,b.free,a.total-b.free used from
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024TOTAL FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME)A,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)B
where a.tablespace_name=b.tablespace_name;
4.下面这条语句查看所有段的大小
select segment_nam,sum(bytes)/1024/1024from USER_EXTENTS GROUP BY segment_name;
5.在命令行情况下如何将结果放到一个文件里用到了telnet
SET TRIMSPOOL ON
SET LINESIZE2000
SET PAGESIZE2000
SET NEWPAGE1
SET HEADING OFF
SET TERM OFF
SPOOL D:\EXP.TXT
SELECT*FROM V$DATABASE;
spool off
6.查看当前正在使用的临时表空间大小
SELECT http://www.doczj.com/doc/0c225841f01dc281e43af009.htmlERNAME,SE.SID,SU.BLOCKS*TO_NUMBER(RTRIM(P.VALUE))AS SPACE,tablespace,segtype,sql_text
FROM V$SORT_USAGE SU,V$PARAMETER P,V$SESSION SE,V$SQL S
WHERE http://www.doczj.com/doc/0c225841f01dc281e43af009.html='db_block_size'
AND SU.SESSION_ADDR=SE.SADDR
AND S.HASH_VALUE=SU.SQLHASH
AND S.ADDRESS=SU.SQLADDR