oracle中查看表占空间,怎么查询Oracle中一张表占用空间

--1.表空间使用情况查询

select (select contents

from dba_tablespaces

where tablespace_name = e.tablespace_name) tablespace_type,

e.tablespace_name,

e.tablespace_size,

e.tablespace_free_size,

round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent

from (select d.tablespace_name,

sum(d.file_size) / 1024 / 1024 tablespace_size,

trunc(sum(nvl(c.file_free_size, 0)) / 1024 / 1024, 2) as tablespace_free_size

from (select a.file_id, sum(a.bytes) file_free_size

from dba_free_space a

group by a.file_id) c,

(select b.file_id,

b.file_name,

b.tablespace_name,

b.bytes file_size

from dba_data_files b) d

where d.file_id = c.file_id(+)

group by d.tablespace_name) e

union all

select upper('temporary') tablespace_type,

e.tablespace_name,

e.tablespace_size,

e.tablespace_free_size,

round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent

from (select d.tablespace_name,

d.tablespace_size / 1024 / 1024 tablespace_size,

trunc((d.tablespace_size - nvl(c.used_tablespace_size, 0)) / 1024 / 1024,

2) as tablespace_free_size

from (select a.tablespace,

sum(a.blocks) *

(select x.value

from v$parameter x

where x.name = lower('db_block_size')) used_tablespace_size

from v$sort_usage a

group by a.tablespace) c,

(select b.tablespace_name, sum(b.bytes) tablespace_size

from dba_temp_files b

group by b.tablespace_name) d

where d.tablespace_name = c.tablespace(+)) e

order by tablespace_type, tablespace_free_size;

--表空间使用查询(适用于ORACLE7.3.4)

select e.tablespace_name,

e.tablespace_size,

e.tablespace_free_size,

(e.tablespace_size - e.tablespace_free_size) tablespace_used_size,

round((1 - e.tablespace_free_size / e.tablespace_size) * 100, 2) used_percent

from (select d.tablespace_name,

sum(d.file_size) / 1024 / 1024 tablespace_size,

trunc(sum(nvl(c.file_free_size, 0)) / 1024 / 1024, 2) as tablespace_free_size

from (select a.file_id, sum(a.bytes) file_free_size

from dba_free_space a

group by a.file_id) c,

(select b.file_id,

b.file_name,

b.tablespace_name,

b.bytes file_size

from dba_data_files b) d

where d.file_id = c.file_id(+)

group by d.tablespace_name) e

order by used_percent;