内容简介：最近利用空闲的时间总结了我常用的数据库管理的SQL语句：一：表空间的大小SQL> SELECT DISTINCT TABLESPACE_NAME,SUM(BYTES/1024/1024) "TABLESPACE SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
最近利用空闲的时间总结了我常用的数据库管理的 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
－－－待续－－－
觉得文章有用？立即：和朋友一起 共学习 共进步！
建议继续学习：
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
