开贴讨论命令行操纵Oracle数据库脚本集锦，跟贴有分

Question

^_^先来一个，见笑！
如何取出某一用户的密码，再原封不动的改回去
conn system/manager
select password from dba_users where username='USER1';(用户名一定要用大写)
结果：1F8E5A929B6861AC(这就是此用户的密码)
alter user USER1 identified by aaa;(改变此用户的密码为aaa)
conn user1/aaa(连接到用户上，？？？)
执行你想要的操作...
conn system/manager
alter user USER1 identified by values '1F8E5A929B6861AC';(改回原有密码

Answer 1

A Simple Monitoring Tool
This tool loops a specified number of times, displaying memory usage along with user process counts for a specific username. 
--=================================================
--
-- proc_ora_monitor
--
-- parm1:  username to count
-- parm2:  number of loops, 5 sec duration
--=================================================
set serveroutput on ;create or replace procedure 
  proc_ora_monitor ( user1  in  varchar, reps1 in integer )
isi            number ;
usercount1   number ;
memory1      number ;
date1        varchar(20) ;
msg          varchar(99) ;begini := 0 ;while ( i < reps1 )
 loop
 msg := '=> ' || to_char(SYSDATE, 'HH:MM:SS PM'); select count(1) 
 into usercount1
 from sys.v_$session
 where username = user1 ;  msg := msg || ', ' || user1 || ': ' ||  usercount1 ; select round(sum(bytes)/1024/1024 ,2)
 into memory1
 from sys.v_$sgastat
 where  pool = 'shared pool' and
        name = 'free memory' ; msg := msg || ', free mb = ' || memory1 ; select round(sum(bytes)/1024/1024 ,2)
 into memory1
 from sys.v_$sgastat
 where  pool = 'shared pool' and
        name = 'processes' ; msg := msg || ', processes mb = ' || memory1 ; dbms_output.put_line(msg) ; dbms_lock.sleep(5) ; i := i + 1 ;
end loop ;end;
/

Answer 2

选自csdn内某兄弟的,一并分享。主题： 如何写根据时间执行JOB?
假设有一个存储过程p_test，每20分钟执行一次
解答：
1、把init<sid>.ora中如下两个参数打开
 JOB_QUEUE_INTERVAL=60
 JOB_QUEUE_PROCESSES=4
 然后重启一个库，如果原来已经打开了则不用这步了
2、示例,以下由sqlplus 来执行，具体参照一下相关的文档
 VARIABLE jobno number;
 BEGIN
   DBMS_JOB.SUBMIT(:jobno,
     'p_test;'
     SYSDATE,'SYSDATE + 1/72');
   commit;
 END;

Answer 3

SVRMGRL
SVRMGRL>CONNECT INTERNAL
SVRMGRL>SHUTDOWN IMMEDIATE
SVRMGRL>STARTUP MOUNT
SVRMGRL>ALTER DATABASE OPEN;
看这几步是哪一步报错，错误代码是什么？

Answer 4

察看表空间都有哪些表A; select table_name  from dba_tables where tablespace_name='xxx';<> 一个不常见的错误：
$ sqlplus
exec(): 0509-036 Cannot load program sqlplus because of the following errors:
      0509-130 Symbol resolution failed for sqlplus because:
      0509-136   Symbol pw_post (number 272) is not exported from
                 dependent module /unix.
      0509-136   Symbol pw_wait (number 273) is not exported from
                 dependent module /unix.
      0509-136   Symbol pw_config (number 274) is not exported from
                 dependent module /unix.
      0509-136   Symbol aix_ora_pw_version3_required (number 275) is not exported from
                 dependent module /unix.
      0509-192 Examine .loader section symbols with the
               'dump -Tv' command

Answer 5

1：检查系统中锁的简单脚本：
 Select s.username, s.sid,   l.type,   l.id1,    l.id2,    l.lmode,    l.request,   p.spid PID 
From v$lock l, v$session s, v$process p 
Where s.sid = l.sid And p.addr = s.paddr And s.username is not null 
Order By id1, s.sid,request; 
获取用户SID，PID，锁的种类，锁的类型等信息2：获取数据库锁的信息（用户ID，OBJECT，SQL）REM *****************************************************************
REM TITLE : Generic Script which displays SQL Text, REM SID 
              and Object name of the locks currently REM being held in the database. 
REM MODULE : lock_held.sql 
Set pagesize 60 
Set linesize 132 
select s.username username, a.sid sid, a.owner||'.'||a.object object, s.lockwait, t.sql_text SQL
from v$sqltext t, v$session s, v$access a 
where t.address = s.sql_address and t.hash_value = s.sql_hash_value 
  and s.sid = a.sid and a.owner != 'SYS' 
and upper(substr(a.object,1,2)) != 'V$' ;
REM REM End of "Lock Monitoring Script" REM 3：产生等待锁的用户报告
SELECT sn.username,m.sid, m.type, DECODE(m.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 
              3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', lmode, 
              ltrim(to_char(lmode,'990'))) lmode, 
             DECODE(m.request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 
             5, 'S/Row Excl.', 6, 'Exclusive', request, ltrim(to_char(m.request,'990'))) request,
            m.id1, m.id2 
FROM v$session sn, v$lock m |
WHERE (sn.sid = m.sid AND m.request != 0) 
OR ( sn.sid = m.sid AND m.request = 0 AND lmode != 4 AND (id1, id2)   IN (SELECT s.id1, s.id2 
FROM v$lock s 
WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )
ORDER BY id1, id2, m.request; 
4：显示持有锁的信息：
select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID||','||S.SERIAL# Kill, U1.NAME||'.'||substr(T1.NAME,1,20) tab, 
decode(L.LMODE, 1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 
5,'Share Row Exclusive', 6,'Exclusive',null) lmode, 
decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 
5,'Share Row Exclusive', 6,'Exclusive',null) request 
from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 
where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' 
order by 1,2,5 

Answer 6

以下脚本摘自《ORACLE 24X7技术与技巧》：
--MAXEXTENTS到达
select owner,segment_name,segment_type,extents,max_extents,tablespace_name 
  from dba_segments 
  where extents>=(max_extents-5);--无法分配下一个扩展内容
select owner,tablespace_name,segment_name,next_extent 
  from dba_segments ds 
  where next_extent > (select max(bytes) from dba_free_space 
                        where tablespace_name=ds.tablespace_name)
  order by 1,2,3;--检查各表空间的数据文件的整个分配大小
select tablespace_name, file_name, bytes/1024/1024 "allocated (MB)" from dba_data_files order by tablespace_name,file_name;--检查各表空间的自由空间数
select tablespace_name, sum(bytes)/1024/1024 "Free Space(MB)", Max(bytes)/1024/1024 "Largest chunk(MB)"
  from dba_free_space
  group by tablespace_name;--查会话数
select username, osuser, server, status, count(*)
  from v$session
  where username is NOT NULL
--    and username <>'SYS' and username <>'SYSTEM'
  group by username, osuser, server, status;--查特定会话执行的SQL语句。
select sql_text 
  from v$sqltext_with_newlines
  where (hash_value, address) in (select sql_hash_value, sql_address from v$session
                                    where sid=? )
  order by address,piece;--检测错误书写的SQL，避免在库Cache中共享SQL语句？？？
select sql_text, loaded_versions, version_count, sharable_mem
  from v$sqlarea
  where loaded_versions>5
  order by sharable_mem;--检测发生在数据库中对SQL语句的过多分析。如果值连续的快速增加，特别是在高峰期，那么可能在库Cache中出现了SQL问题。
select name, value from v$sysstat where name like 'parse count%';--???如果语句执行的次数比语法分析次数的2 倍还少，那么说明语法分析的次数过多了
select substr(sql_text,1,130), parse_calls, executions
  from v$sqlarea
  where executions < (parse_calls*2);--如果前面访问中的GETHITRATIO 很低（少于8 5 %），那么就要引起注意
select gethitratio from v$librarycache 
  where namespace='SQL AREA';
--查出哪个进程正在使用哪一个回滚段
column "Oracle UserName" format a15
column "RBS Name" format a15
select r.name "RBS NAME", p.spid, l.sid "ORACLE PID", s.username "Oracle UserName"
  from v$lock l, v$process p, v$rollname r, v$session s
  where s.sid = l.sid and l.sid = p.pid(+)
    and r.usn = trunc(l.id1(+)/65536)
    and l.type(+) = 'TX' and l.lmode(+) = 6
   order by r.name;--表空间内容的映射
set lines 150 pages 1000
column "Fil_ID" format 999 heading "Fil|ID"
column "Fil" format A55 heading "Fil-name"
column "Segment" format a55
column "Start blk" format 999999 heading "Strt|blk"
column "# blocks" format 999,999 heading "#|blks"
select d.file_id "Fil_ID", d.file_name "Fil", segment_type || ' ' || owner ||'.'||
       segment_name "Segment", e.block_id "Start blk", e.blocks "# blocks"
  from dba_extents e, dba_data_files d
  where e.tablespace_name = upper('&tblspc_name')
    and d.tablespace_name = e.tablespace_name
    and d.file_id = e.file_id
union
select s.file_id, d.file_name, 'Free chunk', s.block_id, s.blocks
  from dba_free_space s, dba_data_files d
  where s.tablespace_name = upper('&tblspc_name')
    and d.tablespace_name = s.tablespace_name
    and d.file_id = s.file_id
order by 1,4,5;--所有会话都会遇到的当前等待
column event format a40
select event, sum(decode(wait_time,0,1,0)) "Currently Waiting", count(*) "Total Waits"
  from v$session_wait
  group by event
  order by 3;select event, sid, sum(decode(wait_time,0,1,0)) "Currently Waiting", count(*) "Total Waits"
  from v$session_wait
  group by event,sid;--数据库中到目前为止遇到的等待
select event, total_waits from v$system_event order by 2;--监控临时段使用情况
select tablespace_name, added_extents, free_extents from v$sort_segment;--监控回滚段头块的等待
select class, count from v$waitstat where class in ('undo header', 'system undo header');--通过检验已经分配的扩展数跟踪相关模式中的段增长情况
set linesize 140
column owner format a15
column segment_name format a30
column segment_type format a5 heading 'TYPE'
select owner, segment_name, segment_type, initial_extent/1024 "Initial (KB)",
       next_extent/1024 "Next (KB)", extents "# extents"
  from dba_segments
  where segment_type in ('TABLE','INDEX')
    and owner in ('OPER_LZ')
  order by owner, segment_type, extents desc;--列出数据库等待的事件
select substr(event,1,30) "Event",
       round(time_waited/100) "Time (secs)",
       total_waits "Tot waits", total_timeouts "Timeouts",
       round(average_wait/100,2) "Avg (secs)"
  from v$system_event
  order by 2 desc;
-- system-wide latch acquistion
set lines 240
column name format a40
select l.name, l.addr, l.gets, l.misses, l.sleeps,
       l.immediate_gets, l.immediate_misses, h.pid
  from v$latch l, v$latchholder h
  where l.addr = h.laddr(+)
  order by l.latch#;-- Soecific latch statistics (by latch name)
column name format a40
set lines 240
select l.name, l.addr, l.gets, l.misses, l.sleeps,
       l.immediate_gets, l.immediate_misses, h.pid
  from v$latch l, v$latchholder h
  where l.addr=h.laddr(+)
    and upper(l.name) like upper('%&latch_name_in_upper_case%')
  order by l.latch#;--查看库函数C a c h e 命中率
select sum(pins)/(sum(pins)+sum(reloads))*100 from v$librarycache;--确定行C a c h e 的命中率：
select sum(gets)/(sum(gets)+sum(getmisses))*100 from v$rowcache;

Answer 7

select * from tab where rownum<7 minus select * from tab where rownum<5 ;(取5到7之间的记录）