常用的数据库管理SQL语句（一）
来源：互联网 发布：java redis缓存二进制 编辑：程序博客网 时间：2024/06/14 00:29
最近利用空闲的时间总结了我常用的数据库管理的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",六：统计数据库缓冲快取区的Hit Ratio
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
SQL> SELECT 1 - (PHY.VALUE / (CUR.VALUE + CON.VALUE)) "CACHE HIT RATIO", ROUND((1 - (PHY.VALUE / (CUR.VALUE + CON.VALUE))) * 100,2)"七：统计使用者联机时消耗的PGA大小
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
--------------- ----------
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
－－－待续－－－
- 常用的数据库管理SQL语句（一）
- SQLServer数据库管理的常用SQL语句
- 常用的数据库管理SQL语句（二）
- 数据库管理常用sql语句
- 数据库管理常用sql语句
- 数据库常用的SQL 语句（一）创建数据库，表以及添加约束
- 常用的SQL数据库语句
- 常用的数据库sql语句
- 数据库常用的SQL语句
- Sql 数据库的基础语句（一）
- MySQL数据库常用SQL语句总结一
- SQL Server数据库管理常用SQL和T-SQL语句
- SQL Server数据库管理常用SQL和T-SQL语句
- SQL Server 数据库管理常用的SQL和T-SQL语句
- SQL Server 数据库管理常用的SQL和T-SQL语句
- SQL Server 数据库管理常用的SQL和T-SQL语句 (转)
- SQL Server 数据库管理常用的SQL和T-SQL语句
- SQL Server 数据库管理常用的SQL和T-SQL语句
- JAVA反射机制
- 【大数递推】HDU 1297——Children’s Queue
- poverDesiger创建模型小结
- Android 蓝牙开发浅析
- BIT 1061 Ubiquitous Religions
- 常用的数据库管理SQL语句（一）
- 学习HTML5的体会（一）
- 7种员工最受赏识（转）
- 初涉Android蓝牙开发
- Windows7使用OpenOffice SDK的配置 (4)
- 安装Android SDK时遇到Failed to rename directory
- 计划要读的书
- android adb am命令
- @implementation NSString (URL)
