oracle 空间大小查询详解程序员

一、表空间大小

select b.file_name 物理文件名,

       b.tablespace_name 表空间,

       b.bytes / 1024 / 1024 大小M,

       (b.bytes – sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,

       substr((b.bytes – sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率

  from dba_free_space a,

       dba_data_files b

       where a.file_id = b.file_id

       group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;

查看数据文件和表空间对应的大小：

select file_id,file_name,tablespace_name,status,bytes/1024/1024 as “bytes MB” from dba_data_files;

临时表空间查询：

1.查看默认的临时表空间

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';或 SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

2.临时表空间信息

SELECT FILE# AS FILE_NUMBER,
       NAME AS NAME,
       CREATION_TIME AS CREATION_TIME,
       BLOCK_SIZE AS BLOCK_SIZE,
       BYTES / 1024 / 1024 / 1024 AS “FILE_SIZE(G)”,
       CREATE_BYTES / 1024 / 1024 / 1024 AS “INIT_SIZE(G)”,
       STATUS AS STATUS,
       ENABLED AS ENABLED
  FROM V$TEMPFILE;

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

用sys管理员账户登录查看

select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

SELECT temp_used.tablespace_name,
      total – used as “Free”,
       total as “Total”,
       round(nvl(total – used, 0) * 100 / total, 3) “Free percent”
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name
 ORDER BY B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS;

 

查看每个表空间的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 from Dba_Segments Group By Tablespace_Name
1.查看剩余表空间大小
 
SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M from dba_free_space GROUP BY tablespace_name;
 
2.检查系统中所有表空间总体空间
select b.name,sum(a.bytes/1000000)总空间 from v$datafile a,v$tablespace b where a.ts#=b.ts# group by b.name;
 
3.查询整个数据库剩余和使用的表空间大小使用情况：
select df.tablespace_name “表空间名”,totalspace “总空间M”,freespace “剩余空间M”,round((1-freespace/totalspace)*100,2) “使用率%”
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name=fs.tablespace_name;

二、表的实际大小

分配给表的大小:

select sum(bytes)/1024/1024 “表大小(M)” from user_segments where segment_name=’table_name’;–注，仅表数据的大小，不含索引、分区、LOB类型

select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name having Segment_Name=’table_name’;

select segment_name, bytes from user_segments where segment_type = ‘TABLE’;或者
   Select Segment_Name,Sum(bytes)/1024/1024 from User_Extents Group By Segment_Name;以上查询均是分配给表的大小

实际使用大小查询：select num_rows * avg_row_len from user_tables where table_name = ‘EMP’;

有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：

select segment_name, bytes
from user_segments
where segment_type = ‘TABLE’;
或者
   Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

另一种表实际使用的空间。这样查询：

analyze table emp compute statistics;
select num_rows * avg_row_len
from user_tables
where table_name = ‘EMP’;

查看每个表空间的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

 

表空间可以按4种方式分类

一、按数据文件的类型，分为：

大文件表空间（bigfile tablespace）此为10g新增功能
小文件表空间（smallfile tablespace）此为创建时默认值

大文件表空间的优点有：

1、在一个表空间里只有一个大的数据文件，以后不需要再去管理数据文件；
2、一个大的数据文件相当于1024个小的数据文件，这样一来，在一个块大小为32K时，整个数据库可以达到（4g*32K）128TB，不过在实际环境中还是要受到操作系统的影响；
3、使用一个大的数据文件可以代替多个小数据文件，这样对数据文件的管理就少多了；
4、当打开数据库，发生检查点，执行DBWR进程时使用大文件表空间会增强性能。

大文件表空间的的需要注意有：

1、要使用在ORACLE的ASM（自动存储管理）的存储空间或者分散(striping)存储的LVM中，或者RAID阵列上；
2、不要把大文件表空间建立在不能分散(striping)存储的系统上；
3、不要把大文件表空间建立在没有空间（剩余空间少）的磁盘组上；
4、建立大文件表空间时不推荐建立在不能扩展的存储空间里；
5、大文件表空间只支持本地管理表空间（LMT）和本地段空间管理（ASSM）；
6、在临时表空间与回滚段表空间，只能用手动段空间管理；
7、自动扩展数据文件必须是起用的，而且最大文件大小必须是不限制；
8、系统表空间和系统辅助（SYSAUX）表空间不能使用大文件表空间；
9、每个表空间只能包含一个数据文件。如果试图添加新的文件，则会报告 ora-32771 错误；
10、在 bft 上存储的表的 rowid 和 smallfile 表空间上的 rowid 结构有些不同的。要正确得到 rowid 信息，dbms_rowid 包增加了一个新的参数　ts_type_in　来解决这个问题。参考这个范例：

sql> select dbms_rowid.rowid_block_number (rowid, bigfile) 2 from foo;
dbms_rowid.rowid_block_number(rowid,bigfile)
———————————————-
24
sql>

二、按管理方式，分为：

本地管理表空间（LMT）
数据字典管理表空间（DMT）

本地管理表空间：
  一种比较先进的管理扩展(extent)的方式；
  是用bitmap来管理表空间里的所有的extent；
  当使用本地管理表空间时是使用6个块（从第三个到第八个）来标识整个表空间里的每一个扩展（extent）；
  其中的每一位(bit)来表示每个扩展的状态。1为已被分配，0为可被分配。
  在本地管理表空间的方式里可以选择每个extent的大小是固定(Uniform)的或是自动的：在自动管理，系统一般是刚开始一个extent8个block，然后逐渐增加；固定大小为每个extent都是固定大小的，推荐使用。

字典管理表空间：这种方式是为了与之前版本兼容而提供的。不推荐使用。

10G里是不能创建字典管理表空间的，被强制推荐，只能倒入老版本中的字典管理表空间。

三、按使用类型，分为：

永久段表空间（）
临时段表空间（）
回滚段表空间（）

永久表空间：
  一般存储数据的表空间；
  系统表空间，普通用户使用的表空间都为永久表空间；
  永久表空间的状态有三种：读写，只读，脱机；
  只有在永久表空间，才能配置ASSM管理模式；
  可以设置记录日志模式。建立选yes，以便数据库出问题后进行恢复；
  以设置为系统默认表空间。这样，创建用户时，没有指定默认表空间，就自动设置为系统默认表空间；
  设置默认表空间的命令：

  ALTER DATABASE DEFAULT TABLESPACE ;

临时表空间：
  一般用来排序和创建索引时使用；
  临时表空间里不存放实际的数据，所以，即使出了问题，也不需要恢复，而且，也不需要备份，因此也不需要记录日志；
  临时表空间只能使是读写模式，而且只能为手动管理段空间模式；
  可以设置为系统默认临时表空间。这样，创建用户时，没有指定默认临时表空间，就自动设置为系统默认临时表空间，命令如下：

  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ;

回滚段表空间：
  用来存放修改中数据的原数据；
  回滚段表空间是用来保证数据读一致性的；
 

四、在永久表空间中按存储内容方式，分为：

系统表空间（SYSTEM TABLESPACE）
系统辅助表空间（SYSAUX TABLESPACE）
非系统表空间