51CTO下载-DBA常用Sql语句.pdf

查看表空间的名称及大小 : SQLselect 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; 查看表空间物理文件的名称及大小 : SQLselect tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 查看回滚段名称及大小: SQLselect segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curextCurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+)order by segment_name; 如何查看某个回滚段里面，跑的什么事物或者正在执行什么 sql 语句: SQLselect 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=?写成几就行了) 查看控制文件: SQLselect * from v$controlfile; 查看日志文件: SQL col member format a50 SQLselect * 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_name from (select p.spid from sys.v_$mystat m,sy