SQL语句大全—查看表空间（一）

查看表空间的名称及大小: SQL><wbr></wbr>SELECT<wbr></wbr>T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024<wbr></wbr>*<wbr></wbr>1024)),<wbr></wbr>0) TS_SIZE FROM<wbr></wbr>DBA_TABLESPACES T, DBA_DATA_FILES D WHERE<wbr></wbr>T.TABLESPACE_NAME = D.TABLESPACE_NAME GROUP<wbr></wbr>BY<wbr></wbr>T.TABLESPACE_NAME; 查看表空间物理文件的名称及大小: SQL><wbr></wbr>SELECT<wbr></wbr>TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024<wbr></wbr>*<wbr></wbr>1024),<wbr></wbr>0) TOTAL_SPACE FROM<wbr></wbr>DBA_DATA_FILES ORDER<wbr></wbr>BY<wbr></wbr>TABLESPACE_NAME; 查看回滚段名称及大小: SQL><wbr></wbr>SELECT<wbr></wbr>SEGMENT_NAME, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>TABLESPACE_NAME, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>R.STATUS, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>(INITIAL_EXTENT /<wbr></wbr>1024) INITIALEXTENT, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>(NEXT_EXTENT /<wbr></wbr>1024) NEXTEXTENT, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>MAX_EXTENTS, <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>V.CUREXT CUREXTENT FROM<wbr></wbr>DBA_ROLLBACK_SEGS R, V$ROLLSTAT V WHERE<wbr></wbr>R.SEGMENT_ID = V.USN(+) ORDER<wbr></wbr>BY<wbr></wbr>SEGMENT_NAME; 如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句: SQL><wbr></wbr>SELECT<wbr></wbr>D.SQL_TEXT, A.NAME FROM<wbr></wbr>V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D WHERE<wbr></wbr>A.USN = B.XIDUSN <wbr><wbr><wbr></wbr></wbr></wbr>AND<wbr></wbr>B.ADDR = C.TADDR <wbr><wbr><wbr></wbr></wbr></wbr>AND<wbr></wbr>C.SQL_ADDRESS = D.ADDRESS <wbr><wbr><wbr></wbr></wbr></wbr>AND<wbr></wbr>C.SQL_HASH_VALUE = D.HASH_VALUE <wbr><wbr><wbr></wbr></wbr></wbr>AND<wbr></wbr>A.USN =<wbr></wbr>1; (备注：你要看哪个，就把usn=?写成几就行了) 查看控制文件: SQL><wbr></wbr>SELECT<wbr></wbr>*<wbr></wbr>FROM<wbr></wbr>V$CONTROLFILE; 查看日志文件: SQL> COL<wbr></wbr>MEMBER<wbr></wbr>FORMAT A50 SQL>SELECT<wbr></wbr>*<wbr></wbr>FROM<wbr></wbr>V$LOGFILE; 如何查看当前SQL*PLUS用户的sid和serial#: SQL>SELECT<wbr></wbr>SID, SERIAL#, STATUS<wbr></wbr>FROM<wbr></wbr>V$SESSION<wbr></wbr>WHERE<wbr></wbr>AUDSID=USERENV('SESSIONID'); 如何查看当前数据库的字符集:<wbr></wbr> SQL>SELECT<wbr></wbr>USERENV('LANGUAGE')<wbr></wbr>FROM<wbr></wbr>DUAL;<wbr></wbr> SQL>SELECT<wbr></wbr>USERENV('LANG')<wbr></wbr>FROM<wbr></wbr>DUAL; 怎么判断当前正在使用何种SQL优化方式: 用EXPLAIN PLAN產生EXPLAIN PLAN¡檢查PLAN_TABLE中ID=0的POSITION列的值 SQL>SELECT<wbr></wbr>DECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO')<wbr></wbr>FROM<wbr></wbr>PLAN_TABLE<wbr></wbr>WHERE<wbr></wbr>ID=0; 如何查看系统当前最新的SCN号： SQL>SELECT<wbr></wbr>MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB)<wbr></wbr>FROM<wbr></wbr>X$KTUXE; 在ORACLE中查找TRACE文件的脚本: SQL>SELECT<wbr></wbr>U_DUMP.VALUE<wbr></wbr>||<wbr></wbr>'/'<wbr></wbr>||<wbr></wbr>INSTANCE.VALUE<wbr></wbr>||<wbr></wbr>'_ORA_'<wbr></wbr>||<wbr></wbr> V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID,<wbr></wbr>'_'<wbr></wbr>|| V$PROCESS.TRACEID,<wbr></wbr>NULL<wbr></wbr>) ||<wbr></wbr>'.TRC'"TRACE FILE"<wbr></wbr>FROM<wbr></wbr>V$PARAMETER U_DUMP<wbr></wbr>CROSS<wbr></wbr>JOIN<wbr></wbr>V$PARAMETER<wbr></wbr>INSTANCE<wbr></wbr>CROSS<wbr></wbr>JOIN<wbr></wbr>V$PROCESS<wbr></wbr>JOIN<wbr></wbr>V$SESSION<wbr></wbr>ON<wbr></wbr>V$PROCESS.ADDR = V$SESSION.PADDR<wbr></wbr>WHERE<wbr></wbr>U_DUMP.NAME<wbr></wbr>=<wbr></wbr>'USER_DUMP_DEST'<wbr></wbr>AND<wbr></wbr> INSTANCE.NAME<wbr></wbr>=<wbr></wbr>'INSTANCE_NAME'<wbr></wbr>AND<wbr></wbr>V$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID'); SQL>SELECT<wbr></wbr>D.VALUE<wbr></wbr>||<wbr></wbr>'/ORA_'<wbr></wbr>|| P.SPID ||<wbr></wbr>'.TRC'<wbr></wbr>TRACE_FILE_NAME FROM<wbr></wbr>(SELECT<wbr></wbr>P.SPID<wbr></wbr>FROM<wbr></wbr>SYS.V_$MYSTAT M,SYS.V_$SESSION S, SYS.V_$PROCESS P<wbr></wbr>WHERE<wbr></wbr>M.STATISTIC# =<wbr></wbr>1<wbr></wbr>AND S.SID = M.SID<wbr></wbr>AND<wbr></wbr>P.ADDR = S.PADDR) P,(SELECT<wbr></wbr>VALUE<wbr></wbr>FROM<wbr></wbr>SYS.V_$PARAMETER<wbr></wbr>WHERE<wbr></wbr>NAME<wbr></wbr>='USER_DUMP_DEST') D; 如何查看客户端登陆的IP地址: SQL>SELECT<wbr></wbr>SYS_CONTEXT('USERENV','IP_ADDRESS')<wbr></wbr>FROM<wbr></wbr>DUAL; 如何在生产数据库中创建一个追踪客户端IP地址的触发器： SQL>CREATE<wbr></wbr>OR<wbr></wbr>REPLACE<wbr></wbr>TRIGGER<wbr></wbr>ON_LOGON_TRIGGER<wbr></wbr>AFTER<wbr></wbr>LOGON<wbr></wbr>ON<wbr></wbr>DATABASE BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV',<wbr></wbr>'IP_ADDRESS')); END; REM<wbr></wbr>记录登陆信息的触发器 CREATE<wbr></wbr>OR<wbr></wbr>REPLACE<wbr></wbr>TRIGGER<wbr></wbr>LOGON_HISTORY<wbr></wbr> AFTER<wbr></wbr>LOGON<wbr></wbr>ON<wbr></wbr>DATABASE<wbr></wbr>--WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'<wbr></wbr> BEGIN<wbr></wbr> INSERT<wbr></wbr>INTO<wbr></wbr>SESSION_HISTORY<wbr></wbr>SELECT<wbr></wbr>USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM<wbr></wbr>FROM<wbr></wbr>V$SESSION<wbr></wbr>WHERE<wbr></wbr>AUDSID = USERENV('SESSIONID');<wbr></wbr> END; 查询当前日期:<wbr></wbr> SQL><wbr></wbr>SELECT<wbr></wbr>TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS')<wbr></wbr>FROM<wbr></wbr>DUAL; 查看所有表空间对应的数据文件名： SQL>SELECT<wbr></wbr>DISTINCT<wbr></wbr>FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE<wbr></wbr>FROM<wbr></wbr>DBA_DATA_FILES; 查看表空间的使用情况: SQL>SELECT<wbr></wbr>SUM(BYTES)/(1024*1024)<wbr></wbr>AS<wbr></wbr>FREE_SPACE,TABLESPACE_NAME<wbr></wbr> FROM<wbr></wbr>DBA_FREE_SPACE<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>TABLESPACE_NAME; SQL>SELECT<wbr></wbr>A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM<wbr></wbr>SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE<wbr></wbr>A.TABLESPACE_NAME=B.TABLESPACE_NAME<wbr></wbr>AND<wbr></wbr>A.TABLESPACE_NAME=C.TABLESPACE_NAME;<wbr></wbr> COLUMN<wbr></wbr>TABLESPACE_NAME FORMAT A18;<wbr></wbr> COLUMN<wbr></wbr>SUM_M FORMAT A12;<wbr></wbr> COLUMN<wbr></wbr>USED_M FORMAT A12;<wbr></wbr> COLUMN<wbr></wbr>FREE_M FORMAT A12;<wbr></wbr> COLUMN<wbr></wbr>PTO_M FORMAT<wbr></wbr>9.99;<wbr></wbr> SELECT<wbr></wbr>S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M'<wbr></wbr>SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M'<wbr></wbr>USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M'<wbr></wbr>FREE_M,<wbr></wbr>SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED<wbr></wbr>FROM<wbr></wbr>(SELECT<wbr></wbr>B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE,<wbr></wbr>SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) *<wbr></wbr>100<wbr></wbr>FREEPERCENTRATIO<wbr></wbr>FROM<wbr></wbr>SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B<wbr></wbr>WHERE<wbr></wbr>A.FILE_ID(+)=B.FILE_ID<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>B.FILE_ID,B.TABLESPACE_NAME,B.BYTES<wbr></wbr>ORDER<wbr></wbr>BY<wbr></wbr>B.TABLESPACE_NAME) S<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>S.TABLESPACE_NAME<wbr></wbr>ORDER<wbr></wbr>BY<wbr></wbr>SUM(S.FREESPACE)/SUM(S.BYTES)<wbr></wbr>DESC; 查看数据文件的hwm（可以resize的最小空间）和文件头大小: SELECT<wbr></wbr>V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE, NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD<wbr></wbr> FROM<wbr></wbr> (SELECT<wbr></wbr>FILE_NAME,FILE_ID,SUM(BYTES) NUM1<wbr></wbr>FROM<wbr></wbr>DBA_DATA_FILES<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_NAME,FILE_ID) V1, (SELECT<wbr></wbr>FILE_ID,SUM(BYTES) NUM2<wbr></wbr>FROM<wbr></wbr>DBA_EXTENTS<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_ID) V2, (SELECT<wbr></wbr>FILE_ID,SUM(BYTES) NUM3<wbr></wbr>FROM<wbr></wbr>DBA_FREE_SPACE<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_ID) V3 WHERE<wbr></wbr>V1.FILE_ID=V2.FILE_ID(+)<wbr></wbr>AND<wbr></wbr>V1.FILE_ID=V3.FILE_ID(+); 数据文件大小及头大小: SELECT<wbr></wbr>V1.FILE_NAME,V1.FILE_ID,<wbr></wbr> NUM1 TOTLE_SPACE,<wbr></wbr> NUM3 FREE_SPACE,<wbr></wbr> NUM1-NUM3 USED_SPACE,<wbr></wbr> NVL(NUM2,0) DATA_SPACE,<wbr></wbr> NUM1-NUM3-NVL(NUM2,0) FILE_HEAD<wbr></wbr> FROM<wbr></wbr> (SELECT<wbr></wbr>FILE_NAME,FILE_ID,SUM(BYTES) NUM1<wbr></wbr>FROM<wbr></wbr>DBA_DATA_FILES<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_NAME,FILE_ID) V1,<wbr></wbr> (SELECT<wbr></wbr>FILE_ID,SUM(BYTES) NUM2<wbr></wbr>FROM<wbr></wbr>DBA_EXTENTS<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_ID) V2,<wbr></wbr> (SELECT<wbr></wbr>FILE_ID,SUM(BYTES) NUM3<wbr></wbr>FROM<wbr></wbr>DBA_FREE_SPACE<wbr></wbr>GROUP<wbr></wbr>BY<wbr></wbr>FILE_ID) V3<wbr></wbr> WHERE<wbr></wbr>V1.FILE_ID=V2.FILE_ID(+)<wbr></wbr> AND<wbr></wbr>V1.FILE_ID=V3.FILE_ID(+); (运行以上查询，我们可以如下信息：<wbr></wbr> Totle_pace:该数据文件的总大小，字节为单位<wbr></wbr> Free_space:该数据文件的剩于大小，字节为单位<wbr></wbr> Used_space:该数据文件的已用空间，字节为单位<wbr></wbr> Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位<wbr></wbr> File_Head:该数据文件头部占用空间，字节为单位) http://hi.baidu.com/wanhh/blog/category/access��mssql更多相关内容