使用oracle的一些技巧方法

将查询的结果生成到文件中：

set echo on --是否显示执行的命令内容 set feedback off --是否显示 * rows selected set heading off --是否显示字段的名称set verify off --是否显示替代变量被替代前后的语句。filset trimspool off --去字段空格set pagesize 1000 --页面大小set linesize 50//linesize设定尽量根据需要来设定，大了生成的文件也大define fil= 'e:/exp.txt'prompt *** Spooling to &filspool &filselect id||','||username||','||'"'||password||'"' from myuser;spool off;

 

 

强行杀session：

SELECT DISTINCT '(' || s.sid || ') - ' || username AS "(session) - username",
o.kglnaobj FROM V$SESSION s, sys.x$kglob o, sys.x$kglpn p WHERE upper(o.kglnaobj) LIKE ('%P_CREATE_TABLE_ZJ_USER_INFO%') AND p.kglpnhdl = o.kglhdadr AND s.SADDR =

select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid in(53);

 

查看表空间信息:

select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used", round((f.free/a.total)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_nameand a.tablespace_name = u.tablespace_name; 

 

查看回滚段信息：

select d.sql_text,a.namefrom v$rollname a,v$transaction b,v$session c,v$sqltext dwhere a.usn=b.xidusn and b.addr=c.taddrand c.sql_address= d.addressand c.sql_hash_value=d.hash_value;