常用的数据库管理SQL语句（一）

栏目: 数据库 · 发布时间: 5年前

内容简介：最近利用空闲的时间总结了我常用的数据库管理的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

－－－待续－－－

觉得文章有用？立即：和朋友一起 共学习共进步！

建议继续学习：

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

QQ技术交流群：445447336，欢迎加入！

扫一扫订阅我的微信号：IT技术博客大学习

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

C陷阱与缺陷

凯尼格 / 高巍 / 人民邮电出版社 / 2008-2-1 / 30.00元

作者以自己1985年在Bell实验室时发表的一篇论文为基础，结合自己的工作经验扩展成为这本对C程序员具有珍贵价值的经典著作。写作本书的出发点不是要批判C语言，而是要帮助C程序员绕过编程过程中的陷阱和障碍。.. 全书分为8章，分别从词法分析、语法语义、连接、库函数、预处理器、可移植性缺陷等几个方面分析了C编程中可能遇到的问题。最后，作者用一章的篇幅给出了若干具有实用价值的建议。.. 本书......一起来看看《C陷阱与缺陷》这本书的介绍吧!

码农工具

RGB转16进制工具

RGB HEX 互转工具

MD5 加密

MD5 加密工具