注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

日常管理  

2011-03-28 13:35:39|  分类: 常用脚本查询 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

SELECT 'kill -9 ' || SPID
  FROM V$PROCESS
 WHERE ADDR IN (SELECT PADDR
                  FROM V$SESSION S
                 WHERE S.MACHINE IN ('WORKGROUP\OEM-457EDAFA'));

SELECT SS.SID,
       SS.SERIAL#,
       SS.USERNAME,
       SS.STATUS,
       SS.COMMAND,
       SS.EVENT,
       SS.BLOCKING_SESSION,
       SS.MACHINE
  FROM V$SESSION SS
 WHERE MACHINE = 'WORKGROUP\F42A2746499744E'

--查找某一台机器执行的SQL语句
SELECT ST.SQL_TEXT
  FROM V$SQLTEXT ST, V$SESSION S
 WHERE ST.SQL_ID = S.SQL_ID
   AND ST.HASH_VALUE = S.SQL_HASH_VALUE
   AND S.MACHINE = 'WORKGROUP\OEM-457EDAFA'

--查询用户登录的总共时间
SELECT S.SID,
       S.SERIAL#,
       S.MACHINE,
       S.PROGRAM,
       S.LOCKWAIT,
       S.EVENT,
       S.LOGON_TIME,
       ROUND(S.LAST_CALL_ET / 60 / 60, 2) "LOGEDHR"
  FROM V$SESSION S
 WHERE S.USERNAME IS NOT NULL
 ORDER BY LOGEDHR DESC

--查询是否有某些session阻挡了某些session的运行
1)
SELECT S.SID, S.SERIAL#, S.BLOCKING_SESSION, S.EVENT, S.USERNAME, S.MACHINE
  FROM V$SESSION S
 WHERE S.BLOCKING_SESSION > 0;

 

2)
SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME
  FROM V$SESSION
 WHERE STATE IN ('WAITING')
   AND WAIT_CLASS != 'Idle';


SELECT * FROM v$session WHERE machine='DATATEST';

SELECT * FROM v$locked_object;

--查看当前等待事件以及总数
SELECT SW.EVENT, COUNT(*) FROM V$SESSION_WAIT SW GROUP BY SW.EVENT;

--求会话等待的对象

SELECT NAME, WAIT_TIME
  FROM V$LATCH L
 WHERE EXISTS (SELECT 1
          FROM (SELECT SID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3
                  FROM V$SESSION_WAIT
                 WHERE EVENT NOT LIKE '%SQL%'
                   AND EVENT NOT LIKE '%rdbms%'
                   AND EVENT NOT LIKE '%mon%') X
         WHERE X.P1 = L.LATCH#);


--查找锁定资源的信息
SELECT s.SID,s.BLOCKING_SESSION,s.EVENT,s.USERNAME,s.MACHINE,s.PROGRAM FROM v$session s;

SELECT l.SID,l.ID1,l.ID2,l.LMODE,l.TYPE,l.REQUEST,l.BLOCK FROM v$lock l WHERE sid IN (1084);


--查询包名为XXX的内容
SELECT text FROM Dba_Source WHERE NAME LIKE 'PG_IVR%';

 

SELECT * FROM v$sgastat;

SELECT * FROM v$session WHERE sid=103;

SELECT s.SQL_TEXT FROM v$sql s,v$session t WHERE s.SQL_ID=t.SQL_ID AND s.HASH_VALUE=t.SQL_HASH_VALUE AND t.SID=

--锁对象进程
select sess.sid,
   sess.serial#,
   lo.oracle_username,
   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
   from v$locked_object lo,
   dba_objects ao,
   v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

--查看数据的命中率
SELECT 1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /
       (SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +
       (SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) "Read Hit Ratio"
  FROM V$SYSSTAT;

--测试数据字典的命中率
SELECT SIZE_FOR_ESTIMATE,
       BUFFERS_FOR_ESTIMATE,
       ESTD_PHYSICAL_READ_FACTOR,
       ESTD_PHYSICAL_READS
  FROM V$DB_CACHE_ADVICE
 WHERE NAME = 'DEFAULT'
   AND BLOCK_SIZE =
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size')
   AND ADVICE_STATUS = 'ON';

--发现shared_pool_size是否设置恰当
方法一:
SELECT SUM(GETS),
       SUM(GETMISSES),
       (1 - (SUM(GETMISSES) / (SUM(GETS) + SUM(GETMISSES)))) * 100 HITRATE
  FROM V$ROWCACHE;
 
方法二:
SELECT METRIC_NAME, VALUE
  FROM V$SYSMETRIC
 WHERE METRIC_NAME = 'Library Cache Hit Ratio';
 
 
 
--查找有问题的SQL语句

--1)查询v$librarycache,看看是否重用 SQL :
SELECT SUM(PINS) "Executions",
       SUM(PINHITS) "Hits",
       ((SUM(PINHITS) / SUM(PINS)) * 100) "PinHitRatio",
       SUM(RELOADS) "Misses",
       ((SUM(PINS) / (SUM(PINS) + SUM(RELOADS))) * 100) "RelHitRatio"
  FROM V$LIBRARYCACHE;
 
--2)查询 v$sql_bind_capture,看看 average binds 是否大于15 (issue):
SELECT SQL_ID, COUNT(*) BIND_COUNT
  FROM V$SQL_BIND_CAPTURE
 WHERE CHILD_NUMBER = 0
 GROUP BY SQL_ID
HAVING COUNT(*) > 20
 ORDER BY COUNT(*) DESC;
 
--3)查找有问题的SQL并修复它
SELECT SQL_TEXT, USERS_EXECUTING, EXECUTIONS, USERS_OPENING, BUFFER_GETS
  FROM V$SQLAREA
 WHERE SQL_ID = '5q4xwhsdbcd1n'
 ORDER BY BUFFER_GETS;
 
--下面程序清单中的示例搜索那些所需空间大于100KB的对象,现时有没有放在共享池中,有可能引起问题的
SELECT NAME, SHARABLE_MEM
  FROM V$DB_OBJECT_CACHE
 WHERE SHARABLE_MEM > 100000
   AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
   AND KEPT = 'NO';
  
--查出磁盘读取大于100000
SELECT B.USERNAME USERNAME,
       A.DISK_READS READS,
       A.EXECUTIONS EXEC,
       round(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS),2) RDS_EXEC_RATIO,
       A.COMMAND_TYPE,
       A.SQL_TEXT STATEMENT
  FROM V$SQLAREA A, DBA_USERS B
 WHERE A.PARSING_USER_ID = B.USER_ID
   AND A.DISK_READS > 1000000
 ORDER BY A.DISK_READS DESC;
 
 
--该脚本显示了所有正被访问的对象,包括同名表、视图、已存储的源代码等,查找谁在访问它
SELECT A.SID, A.USERNAME, B.OWNER, B.OBJECT, B.TYPE
  FROM V$SESSION A, V$ACCESS B
 WHERE A.SID = B.SID
 
 
--使用V$OBJECT_USAGE视图来查看索引是否已被使用。也许某些索引是不需要的。

select * from v$object_usage;

--1)开始监控索引的使用
alter index HRDT_INDEX1 monitoring usage;

--2)监控一段时间后再查询
select index_name, table_name, monitoring, used, start_monitoring, end_monitoring from v$object_usage;

--3)关闭监控
ALTER INDEX HRDT_INDEX1 NOMONITORING usage;

----------------------------------------------------------------
/*
确定锁定问题将有助于定位正在等待其他某些用户或者某些东西的用户。
可以使用这个策略来确定当前被锁定在系统中的用户。这也使DBA们可以
确认一个相关的Oracle进程是否真地被锁定了,还是仅仅运行得比较慢。
您还能够识别当前的语句是否正在执行锁定用户的操作。下面的程序清单
提供了一个确定锁定问题的示例。
*/
SELECT /*+ ordered */
 B.USERNAME, B.SERIAL#, D.ID1, A.SQL_TEXT
  FROM V$LOCK D, V$SESSION B, V$SQLTEXT A
 WHERE B.LOCKWAIT = D.KADDR
   AND A.ADDRESS = B.SQL_ADDRESS
   AND A.HASH_VALUE = B.SQL_HASH_VALUE
----------------------------------------------------------------

--查询用户更新不同的行
4位用户更新相同代码块中的不同行:
SELECT /*+ ordered */
 USERNAME,
 V$LOCK.SID,
 TRUNC(ID1 / POWER(2, 16)) RBS,
 BITAND(ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
 ID2 SEQ,
 LMODE,
 REQUEST
  FROM V$LOCK, V$SESSION
 WHERE V$LOCK.TYPE = 'TX'
   AND V$LOCK.SID = V$SESSION.SID;
 
--用户试图更新相同的行:
select xid, xidusn, xidslot, xidsqn, status, start_scn from v$transaction

--查看用户登录的session数
SELECT USERNAME, COUNT(*) FROM V$SESSION GROUP BY USERNAME;

--查看当前系统等待数
SELECT EVENT,
       TOTAL_WAITS,
       TOTAL_TIMEOUTS,
       (TIME_WAITED / 100) TOT_TIME,
       (AVERAGE_WAIT / 100) AVG_TIME
  FROM V$SYSTEM_EVENT
 ORDER BY TOTAL_WAITS DESC
 
--等待事件V$视图
v$session_wait,v$system_event,v$session

--通过锁定对象查询用户在执行的语句
SELECT S2.SQL_TEXT,T.SID,T.SERIAL#,T.USERNAME,T.MACHINE
  FROM V$SQL S2,
       (SELECT *
          FROM V$SESSION S
         WHERE S.SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT)) T
 WHERE S2.HASH_VALUE = T.SQL_HASH_VALUE
   AND S2.SQL_ID = T.SQL_ID;
  
--查看谁在使用临时段,

SELECT SE.USERNAME,
       SE.SID,
       SE.SERIAL#,
       SE.SQL_ADDRESS,
       SE.MACHINE,
       SE.PROGRAM,
       SU.TABLESPACE,
       SU.SEGTYPE,
       SU.CONTENTS,
       se.BLOCKING_SESSION,
       se.EVENT
  FROM V$SESSION SE, V$SORT_USAGE SU
 WHERE SE.SADDR = SU.SESSION_ADDR
 
--查找前十条性能差的sql
SELECT * FROM
  (
   SELECT PARSING_USER_ID,
          EXECUTIONS,
          CPU_TIME,
          ELAPSED_TIME,
          SORTS,
          COMMAND_TYPE,
          DISK_READS,
          sql_text
      FROM  v$sqlarea
     ORDER BY disk_reads DESC
   ) 
  WHERE ROWNUM<10 ;
 
 
--如何监控 SGA 的命中率?
SELECT A.VALUE + B.VALUE "logical_reads",
       C.VALUE "phys_reads",
       ROUND(100 * ((A.VALUE + B.VALUE) - C.VALUE) / (A.VALUE + B.VALUE)) "BUFFER HIT RATIO"
  FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
 WHERE A.STATISTIC# = 38
   AND B.STATISTIC# = 39
   AND C.STATISTIC# = 40;

--如何监控 SGA 中字典缓冲区的命中率?
SELECT PARAMETER,
       GETS,
       GETMISSES,
       GETMISSES / (GETS + GETMISSES) * 100 "miss ratio",
       (1 - (SUM(GETMISSES) / (SUM(GETS) + SUM(GETMISSES)))) * 100 "Hit ratio"
  FROM V$ROWCACHE
 WHERE GETS + GETMISSES <> 0
 GROUP BY PARAMETER, GETS, GETMISSES;

--如何监控 SGA 中共享缓存区的命中率,应该小于1% 
SELECT SUM(PINS) "Total Pins",
       SUM(RELOADS) "Total Reloads",
       SUM(RELOADS) / SUM(PINS) * 100 LIBCACHE
  FROM V$LIBRARYCACHE;
 
--用户,角色的权限
SELECT * FROM Dba_Sys_Privs WHERE grantee='DBA';  --用户拥有的权限

SELECT * FROM Role_Sys_Privs WHERE ROLE='CONNECT'; --角色拥有的权限

 
--查出没有建立index的表:
SELECT OWNER, TABLE_NAME
  FROM ALL_TABLES
 WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') AND OWNER = UPPER ('scott')
MINUS
SELECT OWNER, TABLE_NAME
  FROM ALL_INDEXES
 WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')

--查找自己的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
  FROM V$SESSION
 WHERE AUDSID = USERENV('SESSIONID');


--返回当前的LANGUAGE信息
select userenv('language') from dual;
select userenv('lang') from dual;


--查看表的元数据,即创建表的语句
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;

----监控索引是否使用

alter index &index_name monitoring usage;

alter index &index_name nomonitoring usage;

select * from v$object_usage where index_name = &index_name;
 
--求归档日志的切换频率(生产系统可能时间会很长)

SELECT START_RECID, START_TIME, END_RECID, END_TIME, MINUTES
  FROM (SELECT TEST.*, ROWNUM AS RN
          FROM (SELECT B.RECID START_RECID,
                       TO_CHAR(B.FIRST_TIME, 'yyyy-mm-dd hh24:mi:ss') START_TIME,
                       A.RECID END_RECID,
                       TO_CHAR(A.FIRST_TIME, 'yyyy-mm-dd hh24:mi:ss') END_TIME,
                       ROUND(((A.FIRST_TIME - B.FIRST_TIME) * 24) * 60, 2) MINUTES
                  FROM V$LOG_HISTORY A, V$LOG_HISTORY B
                 WHERE A.RECID = B.RECID + 1
                   AND B.FIRST_TIME > SYSDATE - 1
                 ORDER BY A.FIRST_TIME DESC) TEST) Y
 WHERE Y.RN < 30
 
--求回滚段正在处理的事务

SELECT A.NAME, B.XACTS, C.SID, C.SERIAL#, D.SQL_TEXT
  FROM V$ROLLNAME    A,
       V$ROLLSTAT    B,
       V$SESSION     C,
       V$SQLTEXT     D,
       V$TRANSACTION E
 WHERE A.USN = B.USN
   AND B.USN = E.XIDUSN
   AND C.TADDR = E.ADDR
   AND C.SQL_ADDRESS = D.ADDRESS
   AND C.SQL_HASH_VALUE = D.HASH_VALUE
 ORDER BY A.NAME, C.SID, D.PIECE;


--求系统中较大的latch

SELECT NAME, SUM(GETS), SUM(MISSES), SUM(SLEEPS), SUM(WAIT_TIME)
  FROM V$LATCH_CHILDREN
 GROUP BY NAME
HAVING SUM(GETS) > 50
 ORDER BY 2;
 
--查询无效的对象
SELECT 'alter procedure ' || OBJECT_NAME || ' compile;'
  FROM DBA_OBJECTS
 WHERE STATUS = 'INVALID'
 
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
  FROM DBA_OBJECTS O
 WHERE STATUS = 'INVALID';
 
--获取db_link,创建,并测试其有效性
 
SELECT 'select sysdate from dual@'||SUBSTR(L.DB_LINK, 1, INSTR(L.DB_LINK, '.') - 1)||';'
  FROM DBA_DB_LINKS L;
 
SELECT L.username,L.OWNER, L.DB_LINK, SUBSTR(L.DB_LINK, 1, INSTR(L.DB_LINK, '.') - 1)
  FROM DBA_DB_LINKS L;
 
CREATE PUBLIC DATABASE LINK testreport CONNECT TO sinofriends IDENTIFIED BY testora USING 'smstest';

SELECT SYSDATE FROM dual@testreport;
 
----求表空间的未用空间

col mbytes format 9999.9999

select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;

--查询长时间事务
Select * From Gv$session_longops a,GV$SESSION B 
Where a.TIME_REMAINING>0  And A.SID=B.SID And B.SERIAL#=A.SERIAL#  And A.INST_ID=B.INST_ID;

select t.xidusn,s.sid,s.serial#,s.osuser,s.machine,s.program,t.start_time,t.status,
 s.status,q.sql_text,t.addr,q.address
from v$transaction t,v$session s,v$sql q
where t.addr=s.taddr and s.sql_address=q.address
order by t.start_time


--查询全表扫描的语句
SELECT S.SID,
       S.SERIAL#,
       S.USERNAME,
       S.TERMINAL,
       S.OSUSER,
       S.MACHINE,
       S.PROGRAM,
       S.STATUS,
       Q.SQL_TEXT,
       Q.ADDRESS,
       W.P1,
       W.P2,
       W.P3
  FROM V$SESSION_WAIT W, V$SESSION S, V$SQL Q
 WHERE S.SQL_ADDRESS = Q.ADDRESS
   AND S.SID = W.SID
   AND W.EVENT = 'db file scattered read';
  
--查询哪个SQL消耗系统资源过大
--先用top查看是哪个oracle进程消耗CPU大
--根据进程号使用SQL语句查询其对应的执行SQL语句
SELECT P.SPID,
       P.USERNAME,
       P.TERMINAL,
       S.SID,
       S.SERIAL#,
       S.USERNAME,
       S.TERMINAL,
       S.OSUSER,
       S.MACHINE,
       S.PROGRAM,
       S.STATUS,
       S.SQL_ADDRESS,
       Q.SQL_TEXT
  FROM V$PROCESS P, V$SESSION S, V$SQL Q
 WHERE P.ADDR = S.PADDR
   AND S.SQL_ADDRESS = Q.ADDRESS(+)
   AND P.SPID = '13474';
 
 
--检查消耗IO的TOP10 SQL语句
SELECT *
  FROM (SELECT SQL_TEXT,
               DISK_READS,
               EXECUTIONS,
               DISK_READS / EXECUTIONS "reads/exec",
               HASH_VALUE,
               ADDRESS
          FROM V$SQLAREA
         WHERE DISK_READS > 100000
         ORDER BY DISK_READS DESC)
 WHERE ROWNUM <= 10
 
---查看执行计划,5种方法-----
--如果PLAN_TABLE表不存在,执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建plan_table表
1.Explain plan
explain plan for
select * from aa;

select * from table(dbms_xplan.display());
2.Autotrace
Set timing on --记录所用时间
Set autottrace traceonly --自动记录执行计划
3.SQL_TRACE
1)ALTER session set sql_trace=true;
2)执行sql
3)ALTER session set sql_trace=false;
4)查看相应的sql trace文件。

4.plsqldev工具,直接查看执行计划

5.诊断事件(10046)
)alter session set events '10046 trace name context forever,level 12';
2)执行sql
3)alter session set events '10046 trace name context off';
4)查看相应的sql trace文件。
tkprof $ORACLE_BASE/admin/sid/bdump/erptest_ora_27576.trc session.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela
关于tkprof的使用看博客
 
 
--[Q]:如何快速查看某一ORA错误的cause和action
[A]:在操作系统输入以下命令:
oerr ora 00600
 
--如何查看存储过程内容?
select text from all_source where name = 'SP_112INTERFACE';

--如何查看当前SCN值?
select dbms_flashback.get_system_change_number from dual;

--如何查看数据库字符集?
select name,value$ from props$ where name like '%NLS%';

--查看db_link
SELECT l.db_link,substr(l.db_link,1,instr(l.db_link,'.')-1),l.username,l.host from Dba_Db_Links l;

--字符集转换
SELECT CONVERt(T.forddername,'ZHS16GBK','UTF8') FROM msbi.tpm_term_fordder_info t ;

--查看员工的上下层次关系

SELECT lpad(' ',3*(LEVEL-1))||ename ename,
       lpad(' ',3*(LEVEL-1))||job job FROM emp
       START WITH mgr IS NULL
       CONNECT BY mgr=PRIOR empno;
      
CREATE TABLE glade(gid INT,course VARCHAR2(20),grade INT);

ALTER TABLE glade ADD (sname varchar2(20));

INSERT INTO glade VALUES (10,'数学',85,'小花');

INSERT INTO glade VALUES (20,'数学',75,'小江');

INSERT INTO glade VALUES (30,'数学',65,'小红');

INSERT INTO glade VALUES (40,'数学',55,'小黑');

INSERT INTO glade VALUES (50,'数学',95,'小明');

SELECT * FROM glade;

--统计成绩的等级
SELECT sname,grade,
CASE
WHEN grade<60 THEN 'D'
WHEN grade<=69 THEN 'C'
WHEN grade<=79 THEN 'B'
WHEN grade<=89 THEN 'A'
ELSE 'A+' END g_level
FROM glade;

--获取系统的SCN号
SELECT dbms_flashback.get_system_change_number FROM dual;  --获取当前SCN

SELECT scn_to_timestamp(497395) FROM dual;  --SCN转换为时间

SELECT timestamp_to_scn(to_date('2010-11-24 17:02:30','yyyy-mm-dd hh24:mi:ss')) FROM dual;  --时间转换为SCN

SELECT * FROM scott.glade AS OF SCN timestamp_to_scn(to_date('2010-11-24 17:00:12','yyyy-mm-dd hh24:mi:ss'));
--等价于
SELECT * FROM scott.glade AS OF TIMESTAMP to_date('2010-11-24 17:00:12','yyyy-mm-dd hh24:mi:ss');

--如何将小表放入keep池中?
alter table xxx storage(buffer_pool keep);

--修改用户的默认表空间
ALTER USER test DEFAULT TABLESPACE test;

--移动表的表空间
ALTER TABLE MOVE TABLESPACE test;

--移动索引表空间
ALTER INDEX index_name REBUILD TABLESPACE test;

--添加主键
ALTER TABLE test ADD constriant pk_test PRIMARY KEY (a);

--表添加列
ALTER TABLE test ADD (a varchar2(20));

--添加注释
COMMENT   ON   COLUMN   TABLE_NAME.COL_NAME   IS   '列注释';  
COMMENT   ON   TABLE   TABLE_NAME   IS   '表注释';

--创建表空间,添加数据文件,修改数据文件大小
CREATE TABLESPACE test DATAFILE 'path' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;  --初始大小10G,自动扩展,但最大的大小为20G;

ALTER TABLESPACE test ADD DATAFILE 'path' SIZE 10G; --固定大小,不自己扩展

ALTER DATABASE DATAFILE 'path' RESIZE 20G;  --修改表空间下的数据文件的大小,但最好不要缩小,避免出问题

--创建日志组,日志组成员,删除日志组
1)创建日志组
alter database add logfile group 1('C:\ORACLE92\ORADATA\ORA9\REDO01a.LOG','C:\ORACLE92\ORADATA\ORA9\REDO01b.LOG') size 100m;

2)为日志组添加成员
alter database add logfile member 'C:\ORACLE92\ORADATA\ORA9\REDO01c.LOG','C:\ORACLE92\ORADATA\ORA9\REDO01d.LOG' to group 1;
注意:往组里添加成员时不能设定大小,数据库会默认将其设成一样大小

3)删除日志组成员
ALTER database drop logfile member 'C:\ORACLE92\ORADATA\ORA9\REDO01c.LOG','C:\ORACLE92\ORADATA\ORA9\REDO01d.LOG';

4)删除日志组
alter database drop logfile group 1;

 

--pfile和spfile之间的转换,可以在数据库没有启动的时候创建
CREATE SPFILE FROM PFILE='path';

CREATE PFILE='path' FROM SPFILE;


--如何改变一个字段初始定义的Check范围?
ALTER table xxx drop constraint constraint_name; --之后再创建新约束:
alter table xxx add constraint constraint_name check();

--sql 语句如何插入全年日期?
create table BSYEAR (d date);
INSERT INTO BSYEAR
  SELECT TO_DATE('20030101', 'yyyymmdd') + ROWNUM - 1
    FROM ALL_OBJECTS
   WHERE ROWNUM <= TO_CHAR(TO_DATE('20031231', 'yyyymmdd'), 'ddd');

--如何查询某天的数据?
select * from table_name where trunc(日期字段)=to_date('2003-05-02','yyyy-mm-dd');

--如何在sqlplus下改变字段大小?
alter table table_name modify (field_name varchar2(100));


--怎样查看哪些用户拥有SYSDBA、SYSOPER权限?
select * from V_$PWFILE_USERS;

--如果修改表名?
alter table old_table_name rename to new_table_name;

--如何搜索出前N条记录?
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;

--如何统计两个表的记录总数?
select (select count(id) from aa)+(select count(id) from bb) total from dual;

--怎样用Sql语句实现查找一列中第N大值?
select * from (select t.*,dense_rank() over (order by sal) rank from scott.emp t ) where rank = N;


--返回大于等于N的最小整數值?
SELECT CEIL(N) FROM DUAL;

--返回小于等于N的最小整數值?
SELECT FLOOR(N) FROM DUAL;

--返回當前月的最后一天?
SELECT LAST_DAY(SYSDATE) FROM DUAL;

--如何不同用戶间数据导入?
IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y ;

--根据某列值来判断另一列值,使用分组函数first,last
SELECT MIN(sal) KEEP (dense_rank FIRST ORDER BY comm DESC) "补助最低的员工工资",
MAX(sal) KEEP (dense_rank FIRST ORDER BY comm DESC) "补助最高的员工工资"
FROM emp;
--等价于
SELECT MIN(sal) KEEP (dense_rank LAST ORDER BY comm) "补助最低的员工工资",
MAX(sal) KEEP (dense_rank LAST ORDER BY comm) "补助最高的员工工资"
FROM emp;


--如何配置Sequence?
1)建sequence seq_custid
create sequence seq_custid start WITH 1 incrememt by 1;
2)建表时:
create table cust { cust_id smallint not null, ...}
3)insert 时:
insert into table cust values( seq_cust.nextval, ...)

--如何查询做比较大的排序的进程?
SELECT B.TABLESPACE,
       A.SID,
       B.SEGFILE#,
       B.SEGBLK#,
       B.BLOCKS,
       A.SERIAL#,
       A.USERNAME,
       A.OSUSER,
       A.STATUS
  FROM V$SESSION A, V$SORT_USAGE B
 WHERE A.SADDR = B.SESSION_ADDR
 ORDER BY B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS;

--如何查询做比较大的排序的进程的SQL语句?
SELECT /*+ ORDERED */
 SQL_TEXT
  FROM V$SQLTEXT A
 WHERE A.HASH_VALUE = (SELECT SQL_HASH_VALUE
                         FROM V$SESSION B
                        WHERE B.SID = &SID
                          AND B.SERIAL# = &SERIAL)
 ORDER BY PIECE ASC;
 
--如何查找重复记录?
SELECT *
  FROM TABLE_NAME
 WHERE ROWID != (SELECT MAX(ROWID)
                   FROM TABLE_NAME D
                  WHERE TABLE_NAME.COL1 = D.COL1
                    AND TABLE_NAME.COL2 = D.COL2);
--如何删除重复记录?
DELETE FROM TABLE_NAME
 WHERE ROWID != (SELECT MAX(ROWID)
                   FROM TABLE_NAME D
                  WHERE TABLE_NAME.COL1 = D.COL1
                    AND TABLE_NAME.COL2 = D.COL2);
                   
                   
--替代变量的使用
DECLARE
  v_char VARCHAR2(20):='&v_str';
BEGIN
  dbms_output.put_line('大写格式:'||upper(v_char));
  dbms_output.put_line('小写格式:'||lower(v_char));
  dbms_output.put_line('首字母大写:'||initcap(v_char));
END;

--获取系统时间,运算时间
ALTER SESSION SET nls_date_format='yyyy-mm-dd hh:mi:ss';
SELECT SYSDATE FROM dual;

SET timing ON;

SELECT dbms_utility.get_time/100,dbms_utility.get_cpu_time/100 FROM dual;

--查看表空间的大小
SELECT A.TABLESPACE_NAME,
       FILENUM,
       TOTAL "TOTAL (MB)",
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,
               COUNT(FILE_ID) FILENUM,
               SUM(BYTES / (1024 * 1024)) TOTAL,
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
/

--oracle 10g 回收站
1)不同用户在回收站的对象
select owner,count(*) from dba_recyclebin group by owner;
SELECT r.ts_name,COUNT(*) FROM DBA_RECYCLEBIN r GROUP BY r.ts_name;

清理回收站
purge recyclebin  --清空回收站所有内容
purge TABLESPACE users --指定清空某个表空间的所有对象
purge TABLESPACE users user scott; --清空users表空间下的用户scott的对象
purge table table_name;
PURGE INDEX index_name;


--查看系统所有段的有关buffer busy waits事件的统计:

SELECT *
  FROM v$segment_statistics s
 WHERE s.statistic_name = 'buffer busy waits'
   AND s.owner <> 'SYS'


--查询检查日志多长时间切换一次:
SELECT s.thread#,
       to_char(s.first_time, 'YYYY-MM-DD') creation_date,
       to_char(s.first_time, 'HH24:MI') TIME,
       s.sequence#,
       s.first_change# lowest_scn_in_log,
       s.next_change# highest_scn_in_log,
       s.recid controlfile_record_id,
       s.stamp controlfile_record_stamp
  FROM v$log_history s
 ORDER BY s.first_time
 
--查询各个会话中该等待事件在总等待时间中所占比率:
select a.sid,
       a.event,
       a.time_waited,
       round(a.time_waited/c.sum_time_waited*100 , 2) || '%' pct_wait_time,
       round((sysdate - b.LOGON_TIME) * 24) hours_connected
  from v$session_event a,
       v$session b,
       (select sid, sum(time_waited) sum_time_waited
          from v$session_event
        
         where event not in ('null event', 'SQL*Net message to client',
                'pmon timer', 'pipe get', 'smon timer', 'jobq slave wait',
                'rdbms ipc message', 'rdbms ipc reply', 'PX Deq: Join ACK',
                'PX Deq: Signal ACK') 
        having sum(time_waited) > 0  -- 对group by 产生结果的挑选
         group by sid) c
 where a.sid = b.sid
   and a.sid = c.sid
   and a.TIME_WAITED > 0
   and a.EVENT = 'db file sequential read'
 order by hours_connected desc, pct_wait_time

--查询系统平均等待时间:
SELECT a.event,
       a.total_waits,
       a.time_waited,
       round(a.time_waited / a.total_waits,2) average_wait,
       trunc(SYSDATE - b.startup_time) days_old
  FROM v$system_event a, v$instance b
 ORDER BY a.time_waited

--查询每个数据库文件的单块读取的时间,以此可以判断哪些文件是热点文件。
SELECT t.file_name,
       t.tablespace_name,
       round(s.singleblkrdtim / s.singleblkrds, 2) AS "单块读所费时间(cs)", 
       s.READTIM,
       s.WRITETIM
  FROM v$filestat s, dba_data_files t
 WHERE s.file# = t.file_id

--查询当前执行全局扫描的SQL语句,v$sql_plan中是否包含该事件:
select hash_value,
       child_number,
       lpad(' ', 2 * depth) || operation || ' ' || options ||
       decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,
       object_name object,
       cost,
       cardinality,
       round(bytes / 1024) kbytes
  from v$sql_plan
 where hash_value in
       (select a.sql_hash_value
          from v$session a, v$session_wait b
         where a.sid = b.sid
           and b.event = 'db file scattered read')
 order by hash_value, child_number, id

--查看索引是否压缩以及占用大小
SELECT A.INDEX_NAME, B.BYTEM, A.table_name, A.COMPRESSION
  FROM (SELECT I.OWNER, I.INDEX_NAME, I.table_name,I.COMPRESSION
          FROM DBA_INDEXES I
         WHERE I.TABLESPACE_NAME = 'SMSMAIN_IDX') A,
       (SELECT S.OWNER, S.SEGMENT_NAME, S.BYTES / 1024 / 1024 BYTEM
          FROM DBA_SEGMENTS S
         WHERE S.TABLESPACE_NAME = 'SMSMAIN_IDX') B
 WHERE A.OWNER = B.OWNER
   AND A.INDEX_NAME = B.SEGMENT_NAME

--查找数据保存后的字符集问题
  
SELECT SUBSTR(DUMP(FORDDERNAME, 1016), 27, 8) CHARSET
  FROM MSBI.TIO_TERM_MINI2FORDDER_INFO C

--删除重启多余的记录
delete from msbi.tio_term_mini2fordder_info_bak
where FORDDERCODE in (select   FORDDERCODE from msbi.tio_term_mini2fordder_info_bak group by   FORDDERCODE   having count(FORDDERCODE) > 1)
and rowid not in (select min(rowid) from   msbi.tio_term_mini2fordder_info_bak group by FORDDERCODE having count(FORDDERCODE )>1)
 
--提取汉字和非汉字
create or replace function get_chinese
(
  p_name    in varchar2,
  p_chinese in varchar2
) return varchar2
as
  v_code         varchar2(30000) := '';
  v_chinese      varchar2(4000)  := '';
  v_non_chinese  varchar2(4000)  := '';
  v_comma        pls_integer;
  v_code_q       pls_integer;
  v_code_w       pls_integer;
begin
  if p_name is not null then
  select replace(substrb(dump(p_name,1010),instrb(dump(p_name,1010),'ZHS16GBK:')),'ZHS16GBK: ','') into v_code from dual where rownum=1;
  for i in 1..length(p_name) loop
      if lengthb(substr(p_name,i,1))=2 then
         v_comma  := instrb(v_code,',');
         v_code_q := to_number(substrb(v_code,1,v_comma-1));
         v_code_w := to_number(substrb(v_code,v_comma+1,abs(instrb(v_code,',',1,2)-v_comma-1)));
         if v_code_q>=176 and v_code_q<=247 and v_code_w>=161 and v_code_w<=254 then
            v_chinese := v_chinese||substr(p_name,i,1);
         else
            v_non_chinese := v_non_chinese||substr(p_name,i,1);
         end if;
         v_code := ltrim(v_code,'1234567890');
         v_code := ltrim(v_code,',');
      else
         v_non_chinese := v_non_chinese||substr(p_name,i,1);     
      end if;
      v_code := ltrim(v_code,'1234567890');
      v_code := ltrim(v_code,',');
  end loop;
  if p_chinese = '1' then
     return v_chinese;
  else
     return v_non_chinese;
  end if;
  else
     return '';
  end if;
end;
/

--关于标点符号
SELECT to_char(2000000/100,'FM999990.00999'), FROM dual;

--查找和替换特殊字符
SELECT T.code,
      decode(instr(replace(DECODE(INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.'),
              0,
              SUBSTR(T.DEAL_SCOPE, INSTR(T.DEAL_SCOPE, ':')+1),
              SUBSTR(T.DEAL_SCOPE,
                     INSTR(T.DEAL_SCOPE, ':')+1,
                     INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.')-INSTR(T.DEAL_SCOPE, ':')-1)),'(','('),'('),0,
                     DECODE(INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.'),
              0,
              SUBSTR(T.DEAL_SCOPE, INSTR(T.DEAL_SCOPE, ':')+1),
              SUBSTR(T.DEAL_SCOPE,
                     INSTR(T.DEAL_SCOPE, ':')+1,
                     INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.')-INSTR(T.DEAL_SCOPE, ':')-1)),
      substr(DECODE(INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.'),
              0,
              SUBSTR(T.DEAL_SCOPE, INSTR(T.DEAL_SCOPE, ':')+1),
              SUBSTR(T.DEAL_SCOPE,
                     INSTR(T.DEAL_SCOPE, ':')+1,
                     INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.')-INSTR(T.DEAL_SCOPE, ':')-1)),1,instr(replace(DECODE(INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.'),
              0,
              SUBSTR(T.DEAL_SCOPE, INSTR(T.DEAL_SCOPE, ':')+1),
              SUBSTR(T.DEAL_SCOPE,
                     INSTR(T.DEAL_SCOPE, ':')+1,
                     INSTR(REPLACE(T.DEAL_SCOPE, '。', '.'), '.')-INSTR(T.DEAL_SCOPE, ':')-1)),'(','('),'(')-1)              
                     ) v3
  FROM TEST T
 
--各种数字以及小数点显示结果问题
SELECT TO_CHAR(123.0233, 'FM9999990.0099'),
       TO_CHAR(20 / 100, 'FM9999990.0099'),
       TO_CHAR(20 / 10, 'FM9999990.0099'),
       TO_CHAR(20 / 1, 'FM9999990.0099'),
       TO_CHAR(0.123, 'FM9999990.0099'),
       TO_CHAR(15.123, 'FM9999990.0099')
  FROM DUAL
 
 
/*移动表空间文件的目录*/
--1.查询对应的路径
SELECT file_name,tablespace_name FROM dba_data_files;

--2.将表空间blade2_index offline
ALTER TABLESPACE TEST OFFLINE;

--3.复制表空间对应文件到相应的目录
cp

--4.rename datafile
ALTER TABLESPACE TEST RENAME DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\TEST02.DBF' TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST02.DBF';

--5.将表空间重新置为online
ALTER TABLESPACE TEST ONLINE;
--查询一条语句执行的次数及执行记录
SELECT s.SQL_ID,s.SQL_TEXT FROM v$sqlarea s WHERE lower(s.SQL_FULLTEXT) LIKE '%delete%version%';

SELECT T.SQL_TEXT,
       T.EXECUTIONS,
       T.PARSE_CALLS,
       T.PLSQL_EXEC_TIME,
       T.USERS_EXECUTING,
       T.COMMAND_TYPE,
       T.FIRST_LOAD_TIME,
       T.LAST_LOAD_TIME
  FROM V$SQL T
 WHERE T.SQL_ID = '94u2s6jrk4h8r';

--导出全库脚本(但不导出表的数据)
exp sysman/passwd file=full.dmp full=y rows=n log=full.log

--导出用户的资料
exp userid=test/test file=test.dmp owner=test log=test.log

--查看文件的读写
SELECT NAME, PHYRDS, PHYWRTS, READTIM, WRITETIM
  FROM V$FILESTAT A, V$DATAFILE B
 WHERE A.FILE# = B.FILE#
 ORDER BY READTIM DESC
 
--查看各个表空间,使用的对象
SELECT DISTINCT S.SEGMENT_TYPE, S.TABLESPACE_NAME
  FROM DBA_SEGMENTS S
 WHERE S.TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1')
 ORDER BY S.TABLESPACE_NAME;

--DBA清理回收站
SELECT 'PURGE ' || R.TYPE || ' ' || R.OWNER || '.' || R.ORIGINAL_NAME || ';'
  FROM DBA_RECYCLEBIN R
 WHERE R.DROPTIME <
       TO_CHAR(TO_DATE('2011-01-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
               'YYYY-MM-DD HH24:MI:SS')
purge tablespace user test;  --清理某个表空间下对应用户在回收站中的信息


--检查是否有其它人员正在访问某某所依赖的对象

select * from v$ACCESS where (OWNER,OBJECT) IN
(SELECT REFERENCED_OWNER,REFERENCED_NAME FROM DBA_DEPENDENCIES
WHERE OWNER='TEST' AND NAME='');


--检查相应会话的信息
select sid,serial#,username,program,machine,terminal from v$session where sid=&id;

--检查该会话正在执行什么操作
select sql_fulltext from v$sql where address in (select sql_address from v$session where sid=&id);

--查找该会话持续的时间
select event,p1text,p1,p2text,p2,p3text,p3,seconds_in_wait from v$session_wait where sid=&id;

--通过SID查找系统进程
select spid from v$process where addr in (select paddr from v$session where sid=&id);

--杀多进程的时候
1)ps -ef | grep LOCAL=NO | cut -c 10-15 | xargs kill -9
2)也可以使用如下的语句杀进程
for i in `ps -ef | grep LOCAL=NO |grep -v grep | awk '{print $2}'`
do
     kill -9 $i
done

--压缩脚本
set pagesize 500;
set head off;
spool /home/oracle/COMPRESS.LOG
SELECT 'alter table '||d.owner||'.'||d.table_name||' move compress;'
FROM dba_tables d
WHERE d.compression='DISABLED' AND d.tablespace_name='SMSMAIN2_DEF' AND d.table_name LIKE 'BILLINGTABLE201010%';
spool OFF;
spool /home/oracle/REBUILD.LOG
SELECT 'alter index '||i.owner||'.'||i.index_name||' rebuild;' FROM Dba_Indexes i WHERE i.status='UNUSABLE';
spool OFF;

  评论这张
 
阅读(738)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017