最近利用空闲的时间总结了我常用的数据库管理的SQL语句：

    一：表空间的大小

    SQL> SELECT DISTINCT TABLESPACE_NAME,SUM(BYTES/1024/1024) "TABLESPACE SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

    TABLESPACE_NAME                TABLESPACE SIZE

    ------------------------------ ---------------

    SL_DATA_TS                                5120

    SYSAUX                                       1024

    UNDOTBS1                                   1345

    USERS                                         8000

    TEST                                            3096

    SYSTEM                                       1024

    UNDOTBS2                                   1100

    BCLOG                                         1048

    8 rows selected

    二：统计数据库的每月增长量

    SQL> SELECT TO_CHAR(CREATION_TIME, \'RRRR MONTH\') "MONTH",

     2  SUM(BYTES) /1024/1024/1024 "GROWTH SIZE"

     3  FROM SYS.V_$DATAFILE

     4  WHERE CREATION_TIME > SYSDATE - 365

     5  GROUP BY TO_CHAR(CREATION_TIME, \'RRRR MONTH\');

    MONTH       GROWTH SIZE

    ----------- -----------

    2008 12月   10.12109375

    三：统计数据文件的每月增长量

    SQL> SELECT A.TS# AS "TABLESPACE NUMBER",

     2   B.NAME AS "TABLESPACE NAME",

     3  TO_CHAR (A.CREATION_TIME, \'RRRR MONTH\') "MONTH",

     4  SUM (A.BYTES) /1024/1024/1024 "GROWTH SIZE"

     5  FROM SYS.V_$DATAFILE A, SYS.V_$TABLESPACE B

     6  WHERE A.CREATION_TIME > SYSDATE - 365

     7  AND A.TS# = B.TS#

     8  GROUP BY A.TS#,B.NAME, TO_CHAR(A.CREATION_TIME,\'RRRR MONTH\');

    TABLESPACE NUMBER TABLESPACE NAME                MONTH       GROWTH SIZE

    ----------------- ------------------------------ ----------- -----------

      5 UNDOTBS2                       2008 12月    1.07421875

      6 SL_DATA_TS                     2008 12月             5

      7 TEST                                 2008 12月     3.0234375

      8 BCLOG                              2008 12月     1.0234375

    四.统计表空间的空闲空间

    SQL> SELECT A.TABLESPACE_NAME,SUM((A.TOTS) /1024/1024) "TOTAL SIZE", SUM((A.SUMB) /1024/1024) "TOTAL FREE SIZE",

     2   SUM(A.SUMB) * 100 / SUM(A.TOTS) PCT_FREE, SUM(A.LARGEST) "MAX FREE SIZE",SUM(A.CHUNKS) "FREE EXTENT NUMBER"

     3  FROM (SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,MAX(BYTES) LARGEST,COUNT(*) CHUNKS FROM DBA_FREE_SPACE A

     4  GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME,SUM(BYTES) TOTS, 0,0,0 FROM DBA_DATA_FILES

     5  GROUP BY TABLESPACE_NAME) A

     6  GROUP BY A.TABLESPACE_NAME;

    TABLESPACE_NAME                TOTAL SIZE TOTAL FREE SIZE   PCT_FREE MAX FREE SIZE FREE EXTENT NUMBER

    ------------------------------ ---------- --------------- ---------- ------------- ------------------

    SL_DATA_TS                           5120       1355.3125 26.4709472      24117248                977

    SYSAUX                               1024         49.9375 4.87670898      37683200                 55

    UNDOTBS1                             1345       1243.5625 92.4581784     500105216                720

    TEST                                 3096         1170.25 37.7987726    1226768384                  2

    USERS                                8000         1905.25  23.815625     880803840                898

    SYSTEM                               1024          72.375 7.06787109      75431936                  2

    BCLOG                                1048        1029.375 98.2228053    1079377920                  1

    UNDOTBS2                             1100             392 35.6363636     218103808                252

    8 rows selected

    五：统计数据文件使用情况

    SQL>  SELECT D.TABLESPACE_NAME TABLESPACE,D.FILE_NAME FILENAME,D.BYTES "TOTAL FILE SIZE",

     2   NVL((D.BYTES - S.BYTES),D.BYTES) "USED SIZE",TRUNC(((NVL((D.BYTES - S.BYTES),D.BYTES)) / D.BYTES) * 100)

     3  PCT_USED FROM SYS.DBA_DATA_FILES D,V$DATAFILE V,(SELECT FILE_ID,SUM(BYTES) BYTES FROM SYS.DBA_FREE_SPACE

     4   GROUP BY FILE_ID) S WHERE (S.FILE_ID(+) = D.FILE_ID)AND(D.FILE_NAME = V.NAME)UNION

     5  SELECT D.TABLESPACE_NAME TABLESPACE,D.FILE_NAME FILENAME,D.BYTES "TOTAL FILE SIZE",

     6  NVL (T.BYTES_CACHED, 0) "USED SIZE",TRUNC((T.BYTES_CACHED / D.BYTES) * 100) PCT_USED

     7  FROM SYS.DBA_TEMP_FILES D,V$TEMP_EXTENT_POOL T,V$TEMPFILE V WHERE (T.FILE_ID(+) = D.FILE_ID)

     8  AND (D.FILE_ID = V.FILE#);

    TABLESPACE                     FILENAME                                                                         TOTAL FILE SIZE  USED SIZE   PCT_USED

    ------------------------------ -------------------------------------------------------------------------------- --------------- ---------- ----------

    BCLOG                          +DG1/nuage/datafile/bclog.733.674146297                                               1098907648   19529728          1

    SL_DATA_TS                     +DG1/nuage/datafile/sl_data_ts.286.673284017                                          5368709120 3947560960         73

    SYSAUX                         +DG1/nuage/datafile/sysaux.271.673276271                                              1073741824 1021378560         95

    SYSTEM                         +DG1/nuage/datafile/system.270.673276271                                              1073741824  997851136         92

    TEMP                           +DG1/nuage/tempfile/temp.277.673276343                                                1627389952          0

    TEST                           +DG1/nuage/datafile/test.390.673287537                                                3246391296 2019295232         62

    UNDOTBS1                       +DG1/nuage/datafile/undotbs1.272.673276271                                            1410334720  106364928          7

    UNDOTBS2                       +DG1/nuage/datafile/undotbs2.278.673276373                                            1153433600  364969984         31

    USERS                          +DG1/nuage/datafile/users.273.673276271                                               8388608000 6390808576         76

    9 rows selected

    六：统计数据库缓冲快取区的Hit Ratio

    SQL> SELECT 1 - (PHY.VALUE / (CUR.VALUE + CON.VALUE)) "CACHE HIT RATIO", ROUND((1 - (PHY.VALUE / (CUR.VALUE + CON.VALUE))) * 100,2)"

     2  RATIO" FROM V$SYSSTAT CUR,V$SYSSTAT CON,V$SYSSTAT PHY WHERE CUR.NAME = \'DB BLOCK GETS\'

     3  AND CON.NAME = \'CONSISTENT GETS\' AND PHY.NAME = \'PHYSICAL READS\'

     4  ;

    CACHE HIT RATIO    

    RATIO

    --------------- ----------

    七：统计使用者联机时消耗的PGA大小   

    SQL>  SELECT USERNAME,NAME,VALUE FROM  V$STATNAME N,V$SESSION S,V$SESSTAT T WHERE S.SID = T.SID

     2   AND N.STATISTIC# = T.STATISTIC# AND S.TYPE = \'USER\' AND S.USERNAME IS NOT NULL

     3  AND USERNAME NOT LIKE \'SYSTEM\' AND T.VALUE > 3000;

    USERNAME                       NAME                                                                  VALUE

    ------------------------------ ---------------------------------------------------------------- ----------

    SOLEILFUZHOU                   opened cursors cumulative                                              3103

    SYSMAN                         opened cursors cumulative                                             48811

    DBSNMP                         opened cursors cumulative                                            109464

    SOLEILFUZHOU                   opened cursors cumulative                                             43283

    SOLEILFUZHOU                   opened cursors cumulative                                             18675

    SOLEILFUZHOU                   opened cursors cumulative                                              7322

    SOLEILFUZHOU                   opened cursors cumulative                                             11344

    SOLEILFUZHOU                   opened cursors cumulative                                              7032

    SYSMAN                         opened cursors cumulative                                             18297

    SOLEILFUZHOU                   opened cursors cumulative                                              7212

    SOLEILFUZHOU                   opened cursors cumulative                                             30667

    SOLEILFUZHOU                   opened cursors cumulative                                             10834

    SOLEILFUZHOU                   opened cursors cumulative                                             43139

    SOLEILFUZHOU                   opened cursors cumulative                                              4499

    SOLEILFUZHOU                   opened cursors cumulative                                             38522

    SOLEILFUZHOU                   opened cursors cumulative                                             11850

    SOLEILFUZHOU                   opened cursors cumulative                                             38710

    SYS                            opened cursors cumulative                                              4417

    SYSMAN                         user commits                                                          20726

    DBSNMP                         user commits                                                         105709

    2154 rows selected

    八：查询程序的连接错误信息

    SQL> SELECT TYPE,OWNER,NAME,SEQUENCE,LINE,POSITION,TEXT || CHR(10) || CHR(10) TEXT

     2  FROM DBA_ERRORS ORDER BY 1,2,3;

    TYPE         OWNER                          NAME                             SEQUENCE       LINE   POSITION TEXT

    ------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------

    PROCEDURE    NUAGE                          DO_EXPLAIN                              8         46          7 PL/SQL: SQL Statement ignored

    PROCEDURE    NUAGE                          DO_EXPLAIN                              4         31         10 PL/SQL: Statement ignored

    PROCEDURE    NUAGE                          DO_EXPLAIN                              5         44         21 PLS-00201: identifier \'V$SESSION\' must be declared

    PROCEDURE    NUAGE                          DO_EXPLAIN                              1         24         14 PL/SQL: ORA-00942: table or view does not exist

    PROCEDURE    NUAGE                          DO_EXPLAIN                              2         23          7 PL/SQL: SQL Statement ignored

    PROCEDURE    NUAGE                          DO_EXPLAIN                             10         51          7 PL/SQL: Statement ignored

    PROCEDURE    NUAGE                          DO_EXPLAIN                              9         51         14 PLS-00320: the declaration of the type of this expression is incomplete or malfo

    PROCEDURE    NUAGE                          DO_EXPLAIN                              3         31         38 PLS-00364: loop index variable \'SQL_PIECES_REC\' use is invalid

    PROCEDURE    NUAGE                          DO_EXPLAIN                              7         49         42 PL/SQL: ORA-00942: table or view does not exist

    PROCEDURE    NUAGE                          DO_EXPLAIN                              6         44         21 PL/SQL: Item ignored

    PROCEDURE    NUAGE                          PROC_ALL_FACT_SUM_ALL_TM                1          0          0 ORA-04052: error occurred when looking up remote object FX.FACT_SUM_ALL_TEST_MAR

                                                                                                  ORA-00604: error occurred at recursive SQL level 1

                                                                                                  ORA-12169: TNS:Net service name given as connect identifier is too long

    33 rows selected

    －－－待续－－－

建议继续学习：

1. 常用的数据库管理SQL语句（二）    (阅读：1888)