查看表空间的使用多少和剩余多少

 

--

 

--查看表空间的剩余多少的视图（sm$ts_free）的创建语句

SQL> select text from dba_views where view_name='SM$TS_FREE'
  2  /

TEXT
--------------------------------------------------------------------------------
select tablespace_name, sum(bytes) bytes from dba_free_space
    group by tables

 

 

--查看表空间的使用视图（sm$ts_used）的创建语句

SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='SM$TS_USED'
  2  /

TEXT
--------------------------------------------------------------------------------
select tablespace_name, sum(bytes) bytes from dba_segments
    group by tablespa

 

 

 

 

 

 

SQL> SELECT * FROM SM$TS_FREE
  2  /
SELECT * FROM SM$TS_FREE
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在 

--这是sys的特属视图

SQL> EDI
已写入 file afiedt.buf

  1* SELECT * FROM SYS.SM$TS_FREE
SQL> /

TABLESPACE_NAME                     BYTES
------------------------------ ----------
MYTH                            104792064
UNDOTBS1                         85786624
SYSAUX                           35717120
MYDATA                          104529920
USERS                             2228224
SYSTEM                          198311936
MYSPACE                         104792064
TABLESPACE02                   1073348608
TABLESPACE03                    104726528
RMAN_TS                         200998912
TBS_USER_01                     197066752

已选择11行。

 

 

---查看表空间的详细情况

SQL> edi
已写入 file afiedt.buf

  1  select a.tablespace_name,a.bytes used,b.bytes free,(a.bytes+b.bytes) sum_bytes,
  2  to_char(round(a.bytes/(a.bytes+b.bytes)*100,2),'990.99')||'%'  使用率
  3  from sys.sm$ts_used a,sys.sm$ts_free b
  4* where a.tablespace_name=b.tablespace_name
SQL> /

TABLESPACE_NAME                      USED       FREE  SUM_BYTES 使用率
------------------------------ ---------- ---------- ---------- ----------
MYTH                               262144  104792064  105054208    0.25%
SYSAUX                          258080768   35454976  293535744   87.92%
UNDOTBS1                         24248320   85786624  110034944   22.04%
MYDATA                             393216  104529920  104923136    0.37%
USERS                            47513600    2228224   49741824   95.52%
SYSTEM                          514523136  198311936  712835072   72.18%
TABLESPACE02                       327680 1073348608 1073676288    0.03%
RMAN_TS                           8650752  200998912  209649664    4.13%
TABLESPACE03                        65536  104726528  104792064    0.06%
TBS_USER_01                      12582912  197066752  209649664    6.00%

已选择10行。