ORACLE常用语句

查看每个表空间的大小
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 num_rows * avg_row_len/1024/1024  as 这张表多大单位M
from user_tables
where table_name = 'PUB_TABTIMEDATA';