oralce

教你Oracle数据库怎样删除重复记录



　　
作者:互联网 来自:互联网 点击:38 时间:2007-7-3



 
    关键词：oracle 数据库　删除重复记录

    做项目的时候，一位同事导数据的时候，不小心把一个表中的数据全都搞重了，也就是说，这个表里所有的记录都有一条重复的。这个表的数据是千万级的，而且是生产系统。也就是说，不能把所有的记录都删除，而且必须快速的把重复记录删掉。对此，总结了一下删除重复记录的方法，以及每种方法的优缺点。
   为了陈诉方便，假设表名为Tbl，表中有三列col1，col2，col3，其中col1，col2是主键，并且，col1，col2上加了索引。

1、通过创建临时表

    可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：



creat table tbl_tmp (select distinct* from tbl);
truncate table tbl;//清空表记录
insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。

    这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

2、利用rowid

    在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：

delete from tbl where rowid in (
select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 

    如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。

3、利用max或min函数

这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下

delete from tbl awhere rowid not in (
select max(b.rowid)
from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以 

或者用下面的语句

delete from tbl awhere rowid<(select max(b.rowid)
from tbl b where a.col1=b.col1 and a.col2 = b.col2);
//这里如果把max换成min的话，前面的where子句中需要把"<"改为">" 

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。SQL语句如下：

deletefrom tbl where rowid not in (select max(rowid)
from tbl tgroup by t.col1, t.col2);
delete from tbl where (col1, col2) in (select col1,col2 from tblgroup bycol1,col2havingcount(*) >1)
and rowidnotin(selectnin(rowid)fromtblgroup bycol1,col2havingcount(*) >1) 

   还有一种方法，对于表中有重复记录的记录比较少的，并且有索引的情况，比较适用。假定col1，col2上有索引，并且tbl表中有重复记录的记录比较少，SQL语句如下4、利用group by，提高效率

4、Group By方法（我经常所用的一种方法）
一、查数据
    Select count(Num) ,max(Name) from student   --列出重复记录，并列出属性
    Group By Name
    Having count(Num) >1    --按Name分组后找出表中Num列重复，即出现次数大于1

二、删数据
    Delete from student Group By Name Having count(Num) > 1



========================================================



















============================================================

ORACLE之FAQ，将在不断更新中
其实，看了chinaunix上linux的一句话问答之后，就有一个想法，把oracle的很多问题，整理成FAQ的形式，并保持不断更新中，慢慢完善。
 
这里我已经整理了一部分FAQ，当然，不保证都是正确的，大家可以提意见，我然后不断的修改，大家也可以提出新的FAQ，然后我将加入并整理。
 
整理一个完善的FAQ，这个是一个漫长的过程，欢迎大家要多参与。
 
现在的FAQ，基本上分了五个部分，如果有需要，以后再细分
UID428 帖子1026 精华17 积分1216 流通积分1216 点 金钱0 O元 阅读权限200 在线时间0 小时 注册时间2003-1-23 最后登录2007-7-5 查看详细资料
TOP


piner
荣誉版主





个人空间 发短消息 加为好友 当前离线  2楼 大 中 小 发表于 2003-11-13 10:45  只看该作者
回复： ORACLE之FAQ，将在不断更新中
第一部分、SQL&L/SQL
[Q]怎么样查询特殊字符，如通配符%与_
[A]select * from table where name like 'A\_%' escape '\'
 
[Q]如何插入单引号到数据库表中
[A]可以用ASCII码处理，其它特殊字符如&也一样，如 
insert into t values('i'||chr(39)||'m');  -- chr(39)代表字符'
或者用两个单引号表示一个
or insert into t values('I''m');  -- 两个''可以表示一个'
 
[Q]怎样设置事务一致性
[A]set transaction [isolation level] read committed;  默认语句级一致性
    set transaction [isolation level] serializable;
                             read only;   事务级一致性
 
[Q]怎么样利用游标更新数据
[A]cursor c1 is 
           select * from tablename 
                  where name is null for update [of column]
    ……
    update tablename set column = ……
                  where current of c1;
 
[Q]怎样自定义异常
[A] pragma_exception_init(exception_name,error_number);
    如果立即抛出异常
    raise_application_error(error_number,error_msg,true|false);
其中number从-20000到-20999，错误信息最大2048B
    异常变量
SQLCODE     错误代码
SQLERRM     错误信息
 
[Q]十进制与十六进制的转换
[A]8i以上版本：
to_char(100,'XX')
to_number('4D','XX')
8i以下的进制之间的转换参考如下脚本
create or replace function to_base( p_dec in number, p_base in number ) 
return varchar2 
is 
   l_str varchar2(255) default NULL; 
   l_num number default p_dec; 
   l_hex varchar2(16) default '0123456789ABCDEF'; 
begin 
   if ( p_dec is null or p_base is null ) then 
      return null; 
   end if; 
   if ( trunc(p_dec) &lt;> p_dec OR p_dec &lt; 0 ) then 
     raise PROGRAM_ERROR; 
   end if; 
   loop 
     l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; 
     l_num := trunc( l_num/p_base ); 
     exit when ( l_num = 0 ); 
   end loop; 
   return l_str; 
end to_base; 
/ 
create or replace function to_dec 
( p_str in varchar2, 
    p_from_base in number default 16 ) return number 
is 
   l_num number default 0; 
   l_hex varchar2(16) default '0123456789ABCDEF'; 
begin 
   if ( p_str is null or p_from_base is null ) then 
     return null; 
   end if; 
   for i in 1 .. length(p_str) loop 
     l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; 
   end loop; 
   return l_num; 
end to_dec; 
/ 
 
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询，你就明白了
select
         SYS_CONTEXT('USERENV','TERMINAL') terminal,
         SYS_CONTEXT('USERENV','LANGUAGE') language,
         SYS_CONTEXT('USERENV','SESSIONID') sessionid,
         SYS_CONTEXT('USERENV','INSTANCE') instance,
         SYS_CONTEXT('USERENV','ENTRYID') entryid,
         SYS_CONTEXT('USERENV','ISDBA') isdba,
         SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
         SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
         SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
         SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
         SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
         SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
         SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
         SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
         SYS_CONTEXT('USERENV','SESSION_USER') session_user,
         SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
         SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
         SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
         SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
         SYS_CONTEXT('USERENV','DB_NAME') db_name,
         SYS_CONTEXT('USERENV','HOST') host,
         SYS_CONTEXT('USERENV','OS_USER') os_user,
         SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
         SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
         SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
         SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
         SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
         SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
         SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
  from dual
 
[Q]怎么获得今天是星期几，还关于其它日期函数用法
[A]可以用to_char来解决，如 
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 
在获取之前可以设置日期语言，如
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
还可以在函数中指定
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; 
其它更多用法，可以参考to_char与to_date函数
如获得完整的时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from  dual;
随便介绍几个其它函数的用法：
本月的天数
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天数 
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
下个星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual
 
[Q]随机抽取前N条记录的问题
[A]8i以上版本
select * from (select * from tablename order by sys_guid()) where rownum &lt; N;
select * from (select * from tablename order by dbms_random.value) where rownum&lt; N;
注：dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以产生100到200范围的随机数
 
[Q]抽取从N行到M行的记录，如从20行到30行的记录
[A]select * from (select rownum id,t.* from table where …… 
and rownum &lt;= 30) where id > 20;
 
[Q]怎么样抽取重复记录
[A]select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2 
where t1.id=t2.id and t1.name=t2.name)
或者
select count(*), t.col_a,t.col_b from table t
   group by col_a,col_b
having count(*)>1
如果想删除重复记录，可以把第一个语句的select替换为delete
 
[Q]怎么样设置自治事务
[A]8i以上版本，不影响主事务
pragma autonomous_transaction;
……
commit|rollback;
  
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如：dbms_lock.sleep(5);表示暂停5秒。
 
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE 
   start_time NUMBER;
   end_time NUMBER;
   start_redo_size NUMBER;
   end_redo_size NUMBER;
BEGIN
   start_time := dbms_utility.get_time;
   SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s 
              WHERE m.STATISTIC#=s.STATISTIC#
                 AND s.NAME='redo size';
   --transaction start
   INSERT INTO t1 
      SELECT * FROM All_Objects;   
   --other dml statement
   COMMIT;
   end_time := dbms_utility.get_time;
   SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s 
              WHERE m.STATISTIC#=s.STATISTIC#
                 AND s.NAME='redo size';
   dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
   dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
 
[Q]怎样创建临时表
[A]8i以上版本
create global temporary tablename(column list)
    on commit preserve rows;  --提交保留数据 会话临时表
    on commit delete rows;    --提交删除数据 事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
 
[Q]怎么样在PL/SQL中执行DDL语句
[A]1、8i以下版本dbms_sql包
    2、8i以上版本还可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
 
[Q]怎么样获取IP地址
[A]服务器(817以上)：utl_inaddr.get_host_address
    客户端：sys_context('userenv','ip_address')
 
[Q]怎么样加密存储过程
[A]用wrap命令，如（假定你的存储过程保存为a.sql）
wrap iname=a.sql
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000.  All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
 
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
VARIABLE jobno number;
BEGIN
    DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
    commit;
END;
之后，就可以用以下语句查询已经提交的作业
select * from user_jobs;
 
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1, 
to_char(current_timestamp) time2 from dual;
 
TIME1                         TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000    24-OCT-03 10.48.45.656000 AM +08:00
可以看到，毫秒在to_char中对应的是FF。
8i以上版本可以创建一个如下的java函数
SQL>create or replace and compile 
java source
named "MyTimestamp"
as
import java.lang.String;
import java.sql.Timestamp;
 
public class MyTimestamp
{
public static String getTimestamp()
    {
      return(new Timestamp(System.currentTimeMillis())).toString();
    }
};
SQL>java created.
注：注意java的语法，注意大小写
SQL>create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/
SQL>unction created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;
MY_TIMESTAMP             ORACLE_TIME
------------------------ -------------------
2003-03-17 19:15:59.688  2003-03-17 19:15:59  
如果只想获得1/100秒(hsecs)，还可以利用dbms_utility.get_time
 
[Q]如果存在就更新，不存在就插入可以用一个语句实现吗
[A]9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select …… from dual的子查询。
语法为：
MERGE INTO table
   USING data_source
     ON (condition)
     WHEN MATCHED THEN update_clause
     WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO course c
    USING (SELECT course_name, period,
        course_hours
    FROM course_updates) cu
      ON (c.course_name = cu.course_name
      AND c.period = cu.period)
    WHEN MATCHED THEN
       UPDATE
       SET c.course_hours = cu.course_hours
    WHEN NOT MATCHED THEN
       INSERT (c.course_name, c.period,
       c.course_hours)
       VALUES (cu.course_name, cu.period,
       cu.course_hours);
 
[Q]怎么实现左联，右联与外联
[A]在9i以前可以这么写:
左联：
select a.id,a.name,b.address from a,b 
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b 
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上，已经开始支持SQL99标准，所以，以上语句可以写成：
默认内部联结：
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id) 
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id 
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id 
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id 
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
 
[Q]怎么实现一条记录根据条件多表插入
[A]9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
   WHEN (id=1) THEN
     INTO table_1 (id, name)
       values(id,name)
   WHEN (id=2) THEN
     INTO table_2 (id, name)
       values(id,name)
   ELSE
     INTO table_other (id, name)
       values(id, name)
   SELECT id,name
   FROM a;
如果没有条件的话，则完成每个表的插入，如
INSERT ALL
     INTO table_1 (id, name)
       values(id,name)
     INTO table_2 (id, name)
       values(id,name)
     INTO table_other (id, name)
       values(id, name)
     SELECT id,name
     FROM a;
 
[Q]如何实现行列转换
[A]1、固定列数的行列转换
如
student  subject grade
---------------------------
student1  语文 80
student1  数学 70
student1  英语 60
student2  语文 90
student2  数学 80
student2 英语 100
……
转换为 
语文  数学  英语
student1   80    70     60
student2   90    80     100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
             sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
     from table
     group by student
 
2、不定列行列转换
如
c1     c2
--------------
1      我
1      是
1      谁
2      知
2      道
3      不
……
转换为
1   我是谁
2   知道
3   不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) 
RETURN VARCHAR2 
IS 
Col_c2 VARCHAR2(4000); 
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP  
Col_c2 := Col_c2||cur.c2; 
END LOOP; 
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2; 
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
 
[Q]怎么样实现分组取前N条记录
[A]8i以上版本，利用分析函数
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from 
(select depno,ename,sal,row_number() over (partition by depno
     order by sal desc) rn 
from emp)
    where rn&lt;=3
 
[Q]怎么样把相邻记录合并到一条记录
[A]8i以上版本，分析函数lag与lead可以提取后一条或前一天记录到本记录。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
          (partition by deptno order by hiredate,ename) last_hire
from emp
     order by depno,hiredate
 
[Q]如何取得一列中第N大的值？
[A]select * from 
(select t.*,dense_rank() over (order by t2 desc) rank from t) 
where rank = [$N]
 
[Q]怎么样把查询内容输出到文本
[A]用spool如
如sqlplus –s  " / as sysdba" &lt;&lt;EOF
set heading off
set feedback off
spool temp.txt
　 select * from tab;
   dbms_output.put_line(‘test’);
spool off
exit
EOF
 
[Q] 如何在SQL*PLUS环境中执行OS命令？
[A] 比如进入了SQLPLUS，启动了数据库，忽然想起监听还没有启动，此时不用退出SQLPLUS，也不用另外起一个命令行窗口，直接输入： 
SQL> host lsntctl start 
或者unix/linux平台下
SQL>&lt;OS command>
windows平台下
SQL>$&lt;OS command>
总结：HOST &lt;OS command>可以直接执行OS命令。 
备注：cd命令无法正确执行。
 
[Q]怎么设置存储过程的调用者权限
[A]普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
    procedure ……()
AUTHID CURRENT_USER
    As
     begin
……
     end;
UID428 帖子1026 精华17 积分1216 流通积分1216 点 金钱0 O元 阅读权限200 在线时间0 小时 注册时间2003-1-23 最后登录2007-7-5 查看详细资料
TOP


piner
荣誉版主





个人空间 发短消息 加为好友 当前离线  3楼 大 中 小 发表于 2003-11-13 10:50  只看该作者
回复： ORACLE之FAQ，将在不断更新中
第二部分、ORACLE构架体系
[Q]ORACLE的有那些数据类型
[A]常见的数据类型有
CHAR固定长度字符域，最大长度可达2000个字节  
NCHAR多字节字符集的固定长度字符域，长度随字符集而定，最多为2000个字符或2000个字节  
VARCHAR2可变长度字符域，最大长度可达4000个字符  
NVARCHAR2多字节字符集的可变长度字符域，长度随字符集而定，最多为4000个字符或4000个字节  
DATE用于存储全部日期的固定长度(7个字节)字符域，时间作为日期的一部分存储其中。除非  
通过设置init.ora文件的NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以  
DD-MON-YY格式表示，如13-APR-99表示1999.4.13  
NUMBER可变长度数值列，允许值为0、正数和负数。NUMBER值通常以4个字节或更少的字节存储，最多21字节  
LONG可变长度字符域，最大长度可到2GB  
RAW表示二进制数据的可变长度字符域，最长为2000个字节  
LONGRAW表示二进制数据的可变长度字符域，最长为2GB  
MLSLABEL只用于TrustedOracle，这个数据类型每行使用2至5个字节  
BLOB二进制大对象，最大长度为4GB  
CLOB字符大对象，最大长度为4GB  
NCLOB多字节字符集的CLOB数据类型，最大长度为4GB  
BFILE外部二进制文件，大小由操作系统决定  
ROWID表示RowID的二进制数据，Oracle8RowID的数值为10个字节，在Oracle7中使用的限定  
RowID格式为6个字节  
UROWID用于数据寻址的二进制数据，最大长度为4000个字节  
 
[Q]Oracle有哪些常见关键字，不能被用于对象名
[A]以8i版本为例，一般保留关键字不能用做对象名
ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
详细信息可以查看v$reserved_words视图
 
[Q]怎么查看数据库版本
[A]select * from v$version
包含版本信息，核心版本信息，位数信息(32位或64位)等
至于位数信息，在linux/unix平台上，可以通过file查看，如
file $ORACLE_HOME/bin/oracle
 
[Q]怎么查看数据库参数
[A]show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
或者select * from v$parameter
除了这部分参数，Oracle还有大量隐含参数，可以通过如下语句查看:
SELECT NAME  
  ,VALUE  
  ,decode(isdefault, 'TRUE','Y','N') as "Default"  
  ,decode(ISEM,'TRUE','Y','N') as SesMod  
  ,decode(ISYM,'IMMEDIATE', 'I',  
  'DEFERRED', 'D',  
  'FALSE', 'N') as SysMod  
  ,decode(IMOD,'MODIFIED','U',  
  'SYS_MODIFIED','S','N') as Modified  
  ,decode(IADJ,'TRUE','Y','N') as Adjusted  
  ,description  
  FROM ( --GV$SYSTEM_PARAMETER  
  SELECT x.inst_id as instance  
  ,x.indx+1  
  ,ksppinm as NAME   
  ,ksppity  
  ,ksppstvl as VALUE   
  ,ksppstdf as isdefault  
  ,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM  
  ,decode(bitand(ksppiflg/65536,3),  
  1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM  
  ,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD  
  ,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ  
  ,ksppdesc as DESCRIPTION   
  FROM x$ksppi x  
  ,x$ksppsv y  
  WHERE x.indx = y.indx  
  AND substr(ksppinm,1,1) = '_'  
  AND x.inst_id = USERENV('Instance')  
  )  
  ORDER BY NAME
 
[Q]怎么样查看数据库字符集
[A]数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。
   客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，
表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
   会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
   客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
   字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。
 
[Q]怎么样修改字符集
[A]8i以上版本可以通过alter database来修改字符集，但也只限于子集到超集，不建议修改props$表，将可能导致严重错误。
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;
 
[Q]怎样建立基于函数索引
[A]8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
 
[Q]怎么样移动表或表分区
[A]移动表的语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M 
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
   [update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move 
lob(lobsegname) store as (tablespace newts);
 
[Q]怎么获得当前的SCN
[A]9i以下版本
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;
如果是9i以上版本，还可以通过以下语句获取
select dbms_flashback.get_system_change_number from dual;
 
[Q]ROWID的结构与组成
[A]8以上版本的ROWID组成
OOOOOOFFFBBBBBBRRR
8以下ROWID组成（也叫受限Rowid）
BBBBBBBB.RRRR.FFFF
其中，O是对象ID，F是文件ID，B是块ID，R是行ID
如果我们查询一个表的ROWID，根据其中块的信息，可以知道该表确切占用了多少个块，进而知道占用了多少数据空间（此数据空间不等于表的分配空间）
 
[Q]怎么样获取对象的DDL语句
[A]第三方工具就不说了主要说一下9i以上版本的dbms_metadata
1、获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;
如果获取整个用户的脚本，可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然，如果是索引，则需要修改相关table到index
 
[Q]如何创建约束的索引在别的表空间上
[A]1、先创建索引，再创建约束
2、利用如下语句创建
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;
 
[Q]怎么知道那些表没有建立主键
[A]一般的情况下，表的主键是必要的，没有主键的表可以说是不符合设计规范的。
SELECT table_name
FROM User_tables t 
WHERE NOT EXISTS 
(SELECT table_name
FROM User_constraints c
WHERE constraint_type = 'P'
AND t.table_name=c.table_name)
其它相关数据字典解释
user_tables        表
user_tab_columns   表的列
user_constraints    约束
user_cons_columns  约束与列的关系
user_indexes       索引
user_ind_columns   索引与列的关系
 
[Q]dbms_output提示缓冲区不够，怎么增加
[A]dbms_output.enable(20000);
另外，如果dbms_output的信息不能显示，
需要设置
set serveroutput on
 
[Q]怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName 
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName 
     SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName 
     DROP (ColumnName) CASCADE CONSTRAINTS
 
[Q]怎么样给sqlplus安装帮助
[A]SQLPLUS的帮助必须手工安装，shell脚本为$ORACLE_HOME/bin/helpins
在安装之前，必须先设置SYSTEM_PASS环境变量，如：
$ setenv SYSTEM_PASS SYSTEM/MANAGER 
$ helpins
如果不设置该环境变量，将在运行脚本的时候提示输入环境变量
当然，除了shell脚本，还可以利用sql脚本安装，那就不用设置环境变量了，但是，我们必须以system登录。
$ sqlplus system/manager 
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安装之后，你就可以象如下的方法使用帮助了
SQL> help index
 
[Q]怎么样快速下载Oracle补丁
[A]我们先获得下载服务器地址，在http页面上有
ftp://updates.oracle.com
然后用ftp登录，用户名与密码是metalink的用户名与密码
如我们知道了补丁号3095277 (9204的补丁集)，则
ftp> cd 3095277 
250 Changed directory OK. 
ftp> ls 
200 PORT command OK. 
150 Opening data connection for file listing. 
p3095277_9204_AIX64-5L.zip 
p3095277_9204_AIX64.zip 
……
p3095277_9204_WINNT.zip 
226 Listing complete. Data connection has been closed. 
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec. 
ftp> 
知道了这个信息，我们用用flashget，网络蚂蚁就可以下载了。 
添加如下连接 
ftp://updates.oracle.com/3095277/p3095244_9204_AIX64-5L.zip
或替换后面的部分为所需要的内容
注意，如果是flashget，网络蚂蚁请输入认证用户名及密码，就是你的metalink的用户名与密码！
 
[Q]如何移动数据文件
[A]1、关闭数据库，利用os拷贝
a.shutdown immediate关闭数据库
   b.在os下拷贝数据文件到新的地点
   c.Startup mount 启动到mount下
   d.Alter database rename datafile '老文件' to '新文件';
   e.Alter database open; 打开数据库
2、利用Rman联机操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
说明：利用OS拷贝也可以联机操作，不关闭数据库，与rman的步骤一样，利用rman与利用os拷贝的原理一样，在rman中copy是拷贝数据文件，相当于OS的cp，而switch则相当于alter database rename，用来更新控制文件。
 
[Q]如果管理联机日志组与成员
[A]以下是常见操作，如果在OPA/RAC下注意线程号
增加一个日志文件组
Alter database add logfile [group n] '文件全名' size 10M;
在这个组上增加一个成员
Alter database add logfile member '文件全名' to group n;
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database drop logfile group n;
 
[Q]怎么样计算REDO BLOCK的大小
[A]计算方法为(redo size + redo wastage) / redo blocks written + 16
具体见如下例子
SQL> select name ,value from v$sysstat where name like '%redo%'; 
NAME VALUE 
---------------------------------------------------------------- ---------- 
redo synch writes 2 
redo synch time 0 
redo entries 76 
redo size 19412 
redo buffer allocation retries 0 
redo wastage 5884 
redo writer latching time 0 
redo writes 22 
redo blocks written 51 
redo write time 0 
redo log space requests 0 
redo log space wait time 0 
redo log switch interrupts 0 
redo ordering marks 0 
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual; 
Redo black(byte)
------------------ 
512
 
[Q]控制文件包含哪些基本内容
[A]控制文件主要包含如下条目，可以通过dump控制文件内容看到
DATABASE ENTRY 
CHECKPOINT PROGRESS RECORDS 
REDO THREAD RECORDS 
LOG FILE RECORDS 
DATA FILE RECORDS 
TEMP FILE RECORDS 
TABLESPACE RECORDS 
LOG FILE HISTORY RECORDS 
OFFLINE RANGE RECORDS 
ARCHIVED LOG RECORDS 
BACKUP SET RECORDS 
BACKUP PIECE RECORDS 
BACKUP DATAFILE RECORDS 
BACKUP LOG RECORDS 
DATAFILE COPY RECORDS 
BACKUP DATAFILE CORRUPTION RECORDS 
DATAFILE COPY CORRUPTION RECORDS 
DELETION RECORDS 
PROXY COPY RECORDS
INCARNATION RECORDS
 
[Q]如果发现表中有坏块，如何检索其它未坏的数据
[A]首先需要找到坏块的ID（可以运行dbverify实现），假设为&lt;BID>，假定文件编码为&lt;FID>。运行下面的查询查找段名： 
SELECT segment_name,segment_type,extent_id,block_id, blocks 
from dba_extents t
where 
file_id = &lt;FID>
AND &lt;BID> between block_id and (block_id + blocks - 1) 
一旦找到坏段名称，若段是一个表，则最好建立一个临时表，存放好的数据。若段是索引，则删除它，再重建。
create table good_table 
as 
select from bad_table where rowid not in 
(select rowid 
from bad_table where substr(rowid,10,6) = &lt;BID>)
在这里要注意8以前的受限ROWID与现在ROWID的差别。
还可以使用诊断事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
创建一个临时表good_table的表中除坏块的数据都检索出来
SQL>CREATE TABLE good_table as select * from bad_table;
最后关闭诊断事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
关于ROWID的结构，还可以参考dbms_rowid.rowid_create函数。
 
[Q]我创建了数据库的所有用户，我可以删除这些用户吗
[A]ORACLE数据库创建的时候，创建了一系列默认的用户和表空间，以下是他们的列表
·SYS/CHANGE_ON_INSTALL or INTERNAL
系统用户，数据字典所有者，超级权限所有者(SYSDBA)
创建脚本：?/rdbms/admin/sql.bsq and various cat*.sql
建议创建后立即修改密码
此用户不能被删除
·SYSTEM/MANAGER
数据库默认管理用户，拥有DBA角色权限
创建脚本：?/rdbms/admin/sql.bsq
建议创建后立即修改密码
此用户不能被删除
·OUTLN/OUTLN
优化计划的存储大纲用户
创建脚本：?/rdbms/admin/sql.bsq
建议创建后立即修改密码
此用户不能被删除
---------------------------------------------------
·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
实验、测试用户，含有例表EMP与DEPT
创建脚本：?/rdbms/admin/utlsampl.sql
可以修改密码
用户可以被删除，在产品环境建议删除或锁定
·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
实验、测试用户，含有例表EMPLOYEES与DEPARTMENTS
创建脚本：?/demo/schema/mksample.sql
可以修改密码
用户可以被删除，在产品环境建议删除或锁定
·DBSNMP/DBSNMP
Oracle Intelligent agent
创建脚本：?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改变密码--需要放置新密码到snmp_rw.ora文件
如果不需要Intelligent Agents，可以删除
---------------------------------------------------
以下用户都是可选安装用户,如果不需要，就不需要安装
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)管理用户
创建脚本：?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
创建脚本：?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
创建脚本：?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
创建脚本：?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
创建脚本：?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
创建脚本：?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
创建脚本：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
创建脚本：?/rdbms/admin/statscre.sql
UID428 帖子1026 精华17 积分1216 流通积分1216 点 金钱0 O元 阅读权限200 在线时间0 小时 注册时间2003-1-23 最后登录2007-7-5 查看详细资料
TOP


wanghai
总版主



个人空间 发短消息 加为好友 当前离线  4楼 大 中 小 发表于 2003-11-13 10:52  只看该作者
回复： ORACLE之FAQ，将在不断更新中
[已被删除]
UID395 帖子1669 精华17 积分1840 流通积分1840 点 金钱0 O元 阅读权限255 来自杭州 在线时间48 小时 注册时间2003-1-21 最后登录2008-4-14 查看详细资料
TOP


piner
荣誉版主





个人空间 发短消息 加为好友 当前离线  5楼 大 中 小 发表于 2003-11-13 10:53  只看该作者
回复： ORACLE之FAQ，将在不断更新中
第三部分、备份与恢复
 
[Q]如何开启/关闭归档
[A]如果开启归档，请保证log_archive_start=true开启自动归档，否则只能手工归档，如果是关闭了归档，则设置该参数为false
注意：如果是OPS/RAC环境，需要先把parallel_server = true注释掉，然后执行如下步骤，最后用这个参数重新启动
1、开启归档
a.  关闭数据库shutdown immediate
b.  startup mount
c.  alter database archivelog
d.  alter database opne
2、禁止归档
a.  关闭数据库shutdown immediate
b.  startup mount
c.  alter database noarchivelog
d.  alter database open
归档信息可以通过如下语句查看
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           E:\oracle\ora92\database\archive
Oldest online log sequence     131
Next log sequence to archive    133
Current log sequence          133
 
[Q]怎样设置定时归档
[A]9i以上版本，保证归档的最小间隔不超过n秒
    设置Archive_lag_target = n   
单位：秒 范围：0~7200
 
[Q]不同版本怎么导出/导入
[A]导出用低版本，导入用当前版本
   如果版本跨越太大，需要用到中间版本过渡
 
[Q]不同的字符集之前怎么导数据
[A]a.前条件是保证导出/导入符合其他字符集标准，如客户环境与数据库字符集一致。
   b.修改dmp文件的2、3字节为目标数据库的字符集，注意要换成十六进制。
   参考函数（以下函数中的ID是十进制的）：
nls_charset_name  根据字符集ID获得字符集名称
             nls_charset_id    根据字符集名称获得字符集ID
 
[Q]怎么样备份控制文件
[A]再线备份为一个二进制的文件
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
备份为文本文件方式
alter database backup controlfile to trace [resetlogs|noresetlogs]; 
 
[Q]控制文件损坏如何恢复
[A]1、如果是损坏单个控制文件
只需要关闭数据库，拷贝一个好的数据文件覆盖掉坏的数据文件即可
或者是修改init.ora文件的相关部分
2、如果是损失全部控制文件，则需要创建控制文件或从备份恢复
创建控制文件的脚本可以通过alter database backup controlfile to trace获取。
 
[Q]怎么样热备份一个表空间
[A]Alter tablespace 名称 begin backup;
host cp 这个表空间的数据文件 目的地;
    Alter tablespace 名称 end backup;
如果是备份多个表空间或整个数据库，只需要一个一个表空间的操作下来就可以了。
 
[Q]怎么快速得到整个数据库的热备脚本
[A]可以写一段类似的脚本
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
   for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
      dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
   end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/
 
[Q]丢失一个数据文件，但是没有备份，怎么样打开数据库
[A]如果没有备份只能是删除这个数据文件了，会导致相应的数据丢失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意：该数据文件不能是系统数据文件
 
[Q]丢失一个数据文件，没有备份但是有该数据文件创建以来的归档怎么恢复
[A]保证如下条件
a.  不能是系统数据文件
b.  不能丢失控制文件
如果满足以上条件，则
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -文件号
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;
 
[Q]联机日志损坏如何恢复
[A]1、如果是非当前日志而且归档，可以使用
Alter database clear logfile group n来创建一个新的日志文件
如果该日志还没有归档，则需要用
Alter database clear unarchived logfile group n
2、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据
如果有备份，可以采用备份进行不完全恢复
如果没有备份，可能只能用_allow_resetlogs_corruption=true来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。
 
[Q]怎么样创建RMAN恢复目录
[A]首先，创建一个数据库用户，一般都是RMAN，并给予recovery_catalog_owner角色权限
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然后，用这个用户登录，创建恢复目录
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后，你可以在恢复目录注册目标数据库了
rman catalog rman/rman target backdba/backdba
RMAN> register database;
 
[Q]怎么样在恢复的时候移动数据文件，恢复到别的地点
[A]给一个RMAN的例子
run { 
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf'
to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf'
to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'
to '/u02/oracle/prod/tmp1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}
 
[Q]执行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下标超界，怎么办
[A]完整错误信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory') 
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END; 
* 
ERROR 位于第 1 行: 
ORA-06532: 下标超出限制 
ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793 
ORA-06512: 在line 1 
解决办法为：
1.编辑位于"$ORACLE_HOME/rdbms/admin"目录下的文件"bmslmd.sql" 
改变行: 
TYPE col_desc_array IS VARRAY(513) OF col_description; 
为
TYPE col_desc_array IS VARRAY(700) OF col_description; 
并保存文件
2. 运行改变后的脚本
SQLPLUS> Connect internal 
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql 
3.重新编译该包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;
 
[Q]执行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份，这个是什么原因
[A]我们分析start_logmnr包
PROCEDURE start_logmnr( 
startScn IN NUMBER default 0 , 
endScn IN NUMBER default 0, 
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'), 
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'), 
DictFileName IN VARCHAR2 default '', 
Options IN BINARY_INTEGER default 0 ); 
可以知道，如果TO_DATE('01-jan-1988','DD-MON-YYYY')失败，将导致以上错误
所以解决办法可以为
1、Alter session set NLS_LANGUAGE=American
2、用类似如下的方法执行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:\temp2\TESTDICT.ora', starttime => TO_DATE( 
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));
UID428 帖子1026 精华17 积分1216 流通积分1216 点 金钱0 O元 阅读权限200 在线时间0 小时 注册时间2003-1-23 最后登录2007-7-5 查看详细资料
TOP


piner
荣誉版主





个人空间 发短消息 加为好友 当前离线  6楼 大 中 小 发表于 2003-11-13 10:55  只看该作者
回复： ORACLE之FAQ，将在不断更新中
第四部分、性能调整
[Q]如果设置自动跟踪
[A]用system登录
执行$ORACLE_HOME/rdbms/admin/utlplan.sql创建计划表
执行$ORACLE_HOME/rdbms/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用，则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想让自动跟踪的角色让每个用户都能使用，则
SQL> grant plustrace to public;
通过如下语句开启/停止跟踪
SET AUTOTRACE ON |OFF 
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
 
[Q]如果跟踪自己的会话或者是别人的会话
[A]跟踪自己的会话很简单
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora'  filename
     FROM
         v$process p,
         v$session s,
         v$parameter p1,
         v$parameter p2
     WHERE p1.name = 'user_dump_dest'
     AND p2.name = 'db_name'
     AND p.addr = s.paddr
     AND s.audsid = USERENV ('SESSIONID')
最后，可以通过Tkprof来解析跟踪文件，如
Tkprof 原文件 目标文件 sys=n
 
[Q]怎么设置整个数据库系统跟踪
[A]其实文档上的alter system set sql_trace=true是不成功的
但是可以通过设置事件来完成这个工作，作用相等
alter system set events
   '10046 trace name context forever,level 1';
如果关闭跟踪，可以用如下语句
alter system set events
   '10046 trace name context off';
其中的level 1与上面的8都是跟踪级别
level 1：跟踪SQL语句，等于sql_trace=true
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件
 
[Q]怎么样根据OS进程快速获得DB进程信息与正在执行的语句
[A]有些时候，我们在OS上操作，象TOP之后我们得到的OS进程，怎么快速根据OS信息获得DB信息呢？
我们可以编写如下脚本：
$more whoit.sh
#!/bin/sh
sqlplus /nolog &lt;&lt;EOF      
  connect / as sysdba 
  col machine format a30 
  col program format a40 
  set line 200 
  select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')  
           from v\$session where paddr in 
           ( select addr from v\$process where spid in($1)); 
             
   select sql_text from v\$sqltext_with_newlines 
                   where hash_value in 
                   (select SQL_HASH_VALUE from v\$session where 
                     paddr in (select addr from v\$process where spid=$1) 
                     ) 
                     order by piece; 
   
exit; 
EOF
然后，我们只要在OS环境下如下执行即可
$./whoit.sh Spid
 
[Q]怎么样分析表或索引
[A]命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
     FOR TABLE
     FOR ALL COLUMNS
     FOR ALL INDEXES
     FOR ALL INDEXED COLUMNS;
等等。
如果想分析整个用户或数据库，还可以采用
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(ownname=>User,estimate_percent=>100,cascade=> TRUE);
这是对命令与工具包的一些总结
1．  对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。 
a)  可以并行进行，对多个用户，多个Table 
b)  可以得到整个分区表的数据和单个分区的数据。 
c)  可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 
d)  可以倒出统计信息 
e)  可以用户自动收集统计信息 
2．  DBMS_STATS的缺点 
a)  不能Validate Structure 
b)  不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。 
c)  DBMS_STATS  默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True 
3．  对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
 
[Q]怎么样快速重整索引
[A]通过rebuild语句，可以快速重整或移动索引到别的表空间
rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数
语法为
  alter index index_name rebuild tablespace ts_name
        storage(……);
如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改
SQL> set heading off
SQL> set feedback off
SQL> spool d:\index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off
另外一个合并索引的语句是
alter index index_name coalesce，这个语句仅仅是合并索引中同一级的leaf block
消耗不大，对于有些索引中存在大量空间浪费的情况下，有一些作用。
 
[Q]如何使用Hint提示
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如用hint指定使用某个索引
 
   select /*+ index(cbotab) */ col1 from cbotab;
   select /*+ index(cbotab cbotab1) */ col1 from cbotab;
   select /*+ index(a cbotab1) */ col1 from cbotab a;
   其中
    TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
  INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
    如果索引名或表名写错了，那这个hint就会被忽略;
 
[Q]怎么样快速复制表或者是插入数据
[A]快速复制表可以指定Nologging选项
如：Create table t1 nologging 
as select * from t2;
快速插入数据可以指定append提示，但是需要注意
noarchivelog模式下，默认用了append就是nologging模式的。 
在archivelog下，需要把表设置程Nologging模式。
如insert /*+ append */ into t1 
            select * from t2
注意：如果在9i环境中并设置了FORCE LOGGING，则以上操作是无效的，并不会加快，当然，可以通过如下语句设置为NO FORCE LOGGING。
Alter database no force logging;
是否开启了FORCE LOGGING，可以用如下语句查看
SQL> select force_logging from v$database;
 
[Q]怎么避免使用特定索引
[A]在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：
表test,有字段a,b,c,d，在a,b,c上建立联合索引inx_a(a,b,c)，在b上单独建立了一个索引Inx_b(b)。
在正常情况下，where a=? and b=? and c=?会用到索引inx_a，
where b=?会用到索引inx_b
但是，where a=? and b=? and c=? group by b会用到哪个索引呢？在分析数据不正确（很长时间没有分析）或根本没有分析数据的情况下，oracle往往会使用索引inx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。
当然，我们可以通过如下的技巧避免使用inx_b，而使用inx_a。
where a=? and b=? and c=? group by b||''  --如果b是字符
where a=? and b=? and c=? group by b+0  --如果b是数字
通过这样简单的改变，往往可以是查询时间提交很多倍
当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
select /*+ no_index(t,inx_b) */ * from test t
                where a=? and b=? and c=? group by b
 
[Q]Oracle什么时候会使用跳跃式索引扫描
[A]这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).
例如表有索引index(a,b,c)，当查询条件为
where b=?的时候，可能会使用到索引index(a,b,c)
如，执行计划中出现如下计划：
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:
1 优化器认为是合适的。
2 索引中的前导列的唯一值的数量能满足一定的条件（如重复值很多）。
3 优化器要知道前导列的值分布(通过分析/统计表得到)。
4 合适的SQL语句
等。
 
[Q]怎么样创建使用虚拟索引
[A]可以使用nosegment选项，如
create index virtual_index_name on table_name(col_name) nosegment;
如果在哪个session需要测试虚拟索引，可以利用隐含参数来处理
alter session set "_use_nosegment_indexes" = true;
就可以利用explain plan for select ……来看虚拟索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划
最后，根据需要，我们可以删除虚拟索引，如普通索引一样
drop index virtual_index_name;
注意：虚拟索引并不是物理存在的，所以虚拟索引并不等同于物理索引，不要用自动跟踪去测试虚拟索引，因为那是实际执行的效果，是用不到虚拟索引的。
 
[Q]怎样监控无用的索引
[A]Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引
语法为：
开始监控：alter index index_name monitoring usage;
检查使用状态：select * from v$object_usage;
停止监控：alter index index_name nomonitoring usage;
当然，如果想监控整个用户下的索引，可以采用如下的脚本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER; 
spool off 
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER; 
spool off 
set heading on
set echo on
set feedback on
 
[Q]怎么样能固定我的执行计划
[A]可以使用OUTLINE来固定SQL语句的执行计划
用如下语句可以创建一个OUTLINE
Create oe replace outline OutLn_Name on
   Select Col1,Col2 from Table
       where ……
如果要删除Outline，可以采用
Drop Outline OutLn_Name;
对于已经创建了的OutLine，存放在OUTLN用户的OL$HINTS表下面
对于有些语句，你可以使用update outln.ol$hints来更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
           where ol_name in ('TEST1','TEST2');
这样，你就把Test1 OUTLINE与Test2 OUTLINE互换了
如果想利用已经存在的OUTLINE，需要设置以下参数
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true
 
[Q]v$sysstat中的class分别代表什么
[A]统计类别
1 代表事例活动
2 代表Redo buffer活动
4 代表锁
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
 
[Q]怎么杀掉特定的数据库会话
[A] Alter system kill session 'sid,serial#';
或者
alter system disconnect session 'sid,serial#' immediate;
在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程）
在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程
 
[Q]怎么快速查找锁与锁等待
[A]数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
               'TX','ROW LOCK',
               NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
        o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
 
[Q] 如何有效的删除一个大表(extent数很多的表)
[A] 一个有很多(100k)extent的表，如果只是简单地用drop table的话，会很大量消耗CPU（Oracle要对fet$、uet$数据字典进行操作），可能会用上几天的时间，较好的方法是分多次删除extent，以减轻这种消耗：
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
   ....
4. drop table big-table;
 
[Q]如何收缩临时数据文件的大小
[A]9i以下版本采用
ALTER DATABASE DATAFILE 'file name' RESIZE 100M类似的语句
9i以上版本采用
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
注意，临时数据文件在使用时，一般不能收缩，除非关闭数据库或断开所有会话，停止对临时数据文件的使用。
 
[Q]怎么清理临时段
[A]可以使用如下办法
1、 使用如下语句查看一下认谁在用临时段
SELECT username,sid,serial#,sql_address,machine,program,
       tablespace,segtype, contents 
  FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr      
2、   那些正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空间回缩一下
SQL>Alter tablespace TEMP coalesce;
还可以使用诊断事件
1、 确定TEMP表空间的ts#
SQL> select ts#, name FROM v$tablespace;
TS# NAME 
----------------------- 
0 SYSYEM 
1 RBS 
2 USERS 
3* TEMP 
……
2、 执行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
说明： 
temp表空间的TS# 为 3*, So TS#+ 1= 4
如果想清除所有表空间的临时段，则 
TS# = 2147483647
 
[Q]怎么样dump数据库内部结构，如上面显示的控制文件的结构
[A]常见的有
1、分析数据文件块，转储数据文件n的块m
alter system dump datafile n block m
2、分析日志文件
alter system dump logfile logfilename;
3、分析控制文件的内容
alter session set events 'immediate trace name CONTROLF level 10'
4、分析所有数据文件头
alter session set events 'immediate trace name FILE_HDRS level 10'
5、分析日志文件头
alter session set events 'immediate trace name REDOHDR level 10'
6、分析系统状态，最好每10分钟一次，做三次对比
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、分析进程状态
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、分析Library Cache的详细情况
alter session set events 'immediate trace name library_cache level 10'
 
[Q]如何获得所有的事件代码
[A] 事件代码范围一般从10000 to 10999，以下列出了这个范围的事件代码与信息
  SET SERVEROUTPUT ON
  DECLARE 
       err_msg VARCHAR2(120);
  BEGIN
       dbms_output.enable (1000000);
       FOR err_num IN 10000..10999
       LOOP
         err_msg := SQLERRM (-err_num);
         IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
           dbms_output.put_line (err_msg);
         END IF;
       END LOOP;
  END;
  /
在Unix系统上，事件信息放在一个文本文件里 
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下脚本查看事件信息 
     event=10000
     while [ $event -ne 10999 ]
     do
         event=`expr $event + 1`
         oerr ora $event
     done
对于已经确保的/正在跟踪的事件，可以用如下脚本获得
SET SERVEROUTPUT ON
     DECLARE
         l_level NUMBER;
     BEGIN
         FOR l_event IN 10000..10999
         LOOP
             dbms_system.read_ev (l_event,l_level);
             IF l_level > 0 THEN
                 dbms_output.put_line ('Event '||TO_CHAR (l_event)||
                 ' is set at level '||TO_CHAR (l_level));
             END IF;
         END LOOP;
     END;
     /
 
[Q]什么是STATSPACK，我怎么使用它？
[A]Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息
可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。 
安装Statspack: 
    cd $ORACLE_HOME/rdbms/admin
    sqlplus "/ as sysdba" @spdrop.sql  -- 卸载，第一次可以不需要
    sqlplus "/ as sysdba" @spcreate.sql  -- 需要根据提示输入表空间名
使用Statspack: 
    sqlplus perfstat/perfstat
    exec statspack.snap;      -- 进行信息收集统计，每次运行都将产生一个快照号
    -- 获得快照号，必须要有两个以上的快照，才能生成报表
    select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; 
    @spreport.sql        -- 输入需要查看的开始快照号与结束快照号
其他相关脚本s: 
spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计 
sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号
sptrunc.sql -

评论

1 楼 sfwhhp 2010-08-04  

营养丰富，有待慢慢消化！