查询表空间使用率

复制代码
代码如下:selectb.tablespace_name,mbytes_alloc,mbytes_free

SQL> select TABLESPACE_NAME,sum(bytes/1024/1204) MB from  dba_data_files group by TABLESPACE_NAME; ----查询表空间的总大小
TABLESPACE_NAME                        MB
------------------------------ ----------
UNDO_ABC                       170.099668
T4K                            1.70099668
SYSAUX                         535.813953
USERS                          1995.48173
TBS_LJB2                       85.0498339
TBS_LJB3                       85.0498339
SYSTEM                         663.388704
EXAMPLE                        280.664452
A                              170.099668
TBS_LJB_16K                    85.0498339
TS_A                           85.0498339

from(select round(sum(bytes)/1024/1024) mbytes_free,tablespace_name

 

from dba_free_space

 

group by tablespace_name) a,

SQL> select TABLESPACE_NAME,sum(bytes/1024/1204) MB from  dba_free_space group by TABLESPACE_NAME; ----查询表空间的空闲大小

TABLESPACE_NAME                        MB
------------------------------ ----------
T4K                            .850498339
UNDO_ABC                       30.6710963
SYSAUX                          31.627907
USERS                          121.355482
TBS_LJB2                       76.5448505
TBS_LJB3                       84.1993355
SYSTEM                         3.61461794
EXAMPLE                        17.1162791
A                              94.3521595
TBS_LJB_16K                    16.1594684
TS_A                            73.461794

(select round(sum(bytes)/1024/1024) mbytes_alloc,tablespace_name

 

from dba_data_files

 

group by tablespace_name) b

SQL> select TABLESPACE_NAME,sum(bytes/1024/1204) MB from  dba_segments group by TABLESPACE_NAME; ---查询表空间使用的大小

TABLESPACE_NAME                        MB
------------------------------ ----------
UNDO_ABC                       138.578073
SYSAUX                         503.335548
USERS                          1908.25249
TBS_LJB2                       8.50498339
SYSTEM                         658.923588
EXAMPLE                        262.697674
A                              74.9501661
TBS_LJB_16K                    68.0398671
TS_A                           31.3089701

where a.tablespace_name(+)=b.tablespace_name

—查询表空间数据文件的朗朗上口,地点,使用比例,空闲大小; 
编号A

and b.tablespace_name=’USERS’

select
b.file_name,b.tablespace_name,b.bytes/1024/1024/1024
totalG,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024
userdG,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) percer
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;

/

/home/oracle/tbs_ljb3_01.dbf TBS_LJB3                       
.09765625                                                0 1

/home/oracle/tbs_ljb3_02.dbf TBS_LJB3                       
.01953125                                                0 5

 

—实验一战败

select
a.tablespace_name,sum(a.bytes)/1024/1024/1024
totalG,sum(b.bytes)/1024/1024/1024 userdG from dba_data_files
a,dba_segments b where a.tablespace_name=b.tablespace_name group by
a.tablespace_name; XXX

—参照他事他说加以侦查英特网的

select
a.total/1024/1024/1024 totalG,b.userd/1024/1024/1024 userdG,userd/total
from (select tablespace_name,sum(bytes) total
from dba_data_files group by tablespace_name) a,(select
tablespace_name,sum(bytes) userd from dba_segments group by
tablespace_name) b where
a.tablespace_name=b.tablespace_name

   
TOTALG     USERDG USERD/TOTAL


  .1953125 .011108398     .056875