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;