常用SQL语句1.doc

查看表空间的名称及大小: ? SQL SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE?FROM DBA_TABLESPACES T, DBA_DATA_FILES D?WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME?GROUP BY T.TABLESPACE_NAME;查看表空间物理文件的名称及大小: SQL SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE?FROM DBA_DATA_FILES?ORDER BY TABLESPACE_NAME;查看回滚段名称及大小:?SQL SELECT SEGMENT_NAME,?????? TABLESPACE_NAME,?????? R.STATUS,?????? (INITIAL_EXTENT / 1024) INITIALEXTENT,?????? (NEXT_EXTENT / 1024) NEXTEXTENT,?????? MAX_EXTENTS,?????? V.CUREXT CUREXTENT?FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V?WHERE R.SEGMENT_ID = V.USN(+)?ORDER BY SEGMENT_NAME;如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:SQL SELECT D.SQL_TEXT, A.NAME?FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D?WHERE A.USN = B.XIDUSN?? AND B.ADDR = C.TADDR?? AND C.SQL_ADDRESS = D.ADDRESS?? AND C.SQL_HASH_VALUE = D.HASH_VALUE?? AND A.USN = 1;(备注：你要看哪个，就把usn=?写成几就行了) ? 查看控制文件:SQL SELECT * FROM V$CONTROLFILE;查看日志文件:SQL COL MEMBER FORMAT A50SQLSELECT * FROM V$LOGFILE;如何查看当前SQL*PLUS用户的sid和serial#:SQLSELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV(SESSIONID);如何查看当前数据库的字符集:?SQLSELECT USERENV(LANGUAGE) FROM DUAL; SQLSELECT USERENV(LANG) FROM DUAL;怎么判断当前正在使用何种SQL优化方式:用EXPLAIN PLAN產生EXPLAIN PLAN?檢查PLAN_TABLE中ID=0的POSITION列的值SQLSELECT DECODE(NVL(POSITION,-1),-1,RBO,1,CBO) FROM PLAN_TABLE WHERE ID=0;如何查看系统当前最新的SCN号：SQLSELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE;在ORACLE中查找TRACE文件的脚本: SQLSELECT U_DUMP.VALUE || / || INSTANCE.VALUE || _ORA_ || V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID,?_ || V$PROCESS.TRACEID, NULL ) || .TRCTRACE FILE FROM V$PARAMETER U_DUMP CROSS JOIN V$PARAMETER INSTANCE CROSS JOIN V$PROCESS JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR WHERE U_DUMP.NAME = USER_DUMP_DEST AND INSTANCE.NAME = INSTANCE_NAME AND V$SESSION.AUDSID=SYS_CONTEXT(USERENV,SESSIONID);SQLSELECT D.VALUE || /ORA_ || P.SPID || .TRC TRACE_FILE_NAMEFROM (SELE