查看ORACLE表空间使用情况

1.查看所有的表空间和大小

SQL>  SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_DATA_FILES   2  GROUP BY TABLESPACE_NAME; TABLESPACE_NAME                        MB ------------------------------ ---------- SYSAUX                                260 UNDOTBS1                              105 USERS                              101.25 RMANTBS                               100 SYSTEM                                500 EXAMPLE                               100 6 rows selected.

2.未使用的表空间大小

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE   2  GROUP BY TABLESPACE_NAME; TABLESPACE_NAME                        MB ------------------------------ ---------- UNDOTBS1                           99.625 SYSAUX                              5.375 USERS                              9.9375 RMANTBS                           99.9375 SYSTEM                            19.3125 EXAMPLE                             31.75 6 rows selected.

3.查看当前用户的segment的大小：

SQL>CONN SCOTT/TIGER; SQL> SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 MB FROM USER_EXTENTS   2  GROUP BY SEGMENT_NAME; SEGMENT_NAME                 MB -------------------- ---------- MYDEPT                    .0625 DEPT                      .0625 MYEMP                        88 EMP                       .0625 PK_DEPT                   .0625 BONUS                     .0625 PK_EMP                    .0625 SALGRADE                  .0625 8 rows selected.

4.查询ORACLE临时表空间当前使用了多少空间的大小，不是占用量，而是当前正在使用的表空间大小

SQL> SELECT SE.USERNAME,SE.SID,SU.EXTENTS,SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) AS SPACE,TABLESPACE,SEGTYPE,SQL_TEXT   2  FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S   3  WHERE P.NAME = 'db_block_size' AND SU.SESSION_ADDR = SE.SADDR AND S.HASH_VALUE = SU.SQLHASH AND S.ADDRESS = SU.SQLADDR   4  ORDER BY SE.USERNAME, SE.SID;

5.查看所有的表空间

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS EXAMPLE RMANTBS 7 rows selected.

6.查看表空间中分布的用户信息

SQL> SELECT TABLESPACE_NAME,OWNER,SUM(BYTES)/1024/1024 MB FROM DBA_SEGMENTS   2  GROUP BY TABLESPACE_NAME,OWNER; TABLESPACE_NAME                OWNER                                  MB ------------------------------ ------------------------------ ---------- SYSTEM                         SYS                                464.75 SYSTEM                         SYSTEM                             15.375 SYSAUX                         CTXSYS                              4.625 SYSAUX                         SYSMAN                                 54 SYSTEM                         OUTLN                                  .5 EXAMPLE                        SH                                     56 EXAMPLE                        OE                                   6.25 SYSAUX                         DMSYS                                 .25 SYSAUX                         MDSYS                             32.4375 SYSAUX                         OLAPSYS                           15.5625 USERS                          OE                                 2.5625 TABLESPACE_NAME                OWNER                                  MB ------------------------------ ------------------------------ ---------- USERS                          HUYONG                                .25 SYSAUX                         SYSTEM                             6.8125 EXAMPLE                        HR                                 1.5625 EXAMPLE                        PM                                   2.75 UNDOTBS1                       SYS                                5.3125 SYSAUX                         WMSYS                              5.9375 SYSAUX                         SYS                               80.4375 SYSAUX                         TSMSYS                                .25 SYSAUX                         XDB                               48.5625 SYSAUX                         DBSNMP                             1.5625 SYSAUX                         EXFSYS                              3.625 TABLESPACE_NAME                OWNER                                  MB ------------------------------ ------------------------------ ---------- SYSAUX                         ORDSYS                                 .5 USERS                          SCOTT                             88.4375 EXAMPLE                        IX                                  1.625 25 rows selected.

 

转载于:https://blog.51cto.com/dayong2015/1393464