oracle提高查询效率的34条方法
oracle提高查询效率的34条方法
1、选择最有效率的表名顺序
　　(只在基于规则的优化器中有效)：
　　ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
2、WHERE子句中的连接顺序 &www.2cto.com &
　　ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
3、SELECT子句中避免使用 & * &
　　ORACLE在解析的过程中, 会将&*& 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
4、减少访问的次数
　　ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等;
5、在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数
　　可以增加每次数据库访问的检索数据量 ,建议值为200
6、使用DECODE函数来减少处理时间
　　使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
7、整合简单、无关联的数据库访问
　　如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
8、删除重复记录
　　最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
　　DELETE FROM EMP E WHERE E.ROWID & (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
9、用TRUNCATE替代DELETE
　　当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.
10、尽量多使用COMMIT
　　只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。COMMIT所释放的资源有下列几种:
　　①回滚段上用于恢复数据的信息.
　　②被程序语句获得的锁
　　③ redo log buffer 中的空间
　　④ORACLE为管理上述3种资源中的内部花费
11、用Where子句替换HAVING子句
　　避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
12、减少对表的查询
　　在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
　　SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
13、通过内部函数提高SQL效率
　　复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
14、使用表的别名(Alias)
　　当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
15、用EXISTS替代IN、用NOT EXISTS替代NOT IN
　　在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
　　例子：
　　//(高效)
　　SELECT * FROM EMP (基础表)
WHERE EMPNO & 0 AND EXISTS (SELECT &X& FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = &MELB&)
　　//(低效)SELECT * FROM EMP (基础表)
WHERE EMPNO & 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = &MELB&)
16、识别&低效执行&的SQL语句
　　虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
　　SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
　　ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
　　ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
　　SQL_TEXT
　　FROM V$SQLAREA
　　WHERE EXECUTIONS&0
　　AND BUFFER_GETS & 0
　　AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8
ORDER BY 4 DESC;
17、用索引提高效率
　　索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：
　　ALTER INDEX REBUILD
18、用EXISTS替换DISTINCT
　　当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：
　　//(低效):
　　SELECT DISTINCT DEPT_NO,DEPT_NAME
　　FROM DEPT D , EMP E
　　WHERE D.DEPT_NO = E.DEPT_NO
　　//(高效):
　　SELECT DEPT_NO,DEPT_NAME
　　FROM DEPT D
　　WHERE EXISTS ( SELECT &X&
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
19、sql语句用大写的
　　因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
20、在java代码中尽量少用连接符&+&连接字符串!
21、避免在索引列上使用NOT
　　通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE&遇到&NOT,他就会停止使用索引转而执行全表扫描.
22、避免在索引列上使用计算.
　　WHERE子句中，如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
　　//低效：
　　SELECT & FROM DEPT WHERE SAL * 12 & 25000;
　　//高效:
　　SELECT & FROM DEPT WHERE SAL & 25000/12;
23、用&=替代&
　　//高效:
　　SELECT * FROM EMP WHERE DEPTNO &=4
　　//低效:
SELECT * FROM EMP WHERE DEPTNO &3
　　两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
24、用UNION替换OR (适用于索引列)
　　通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
　　//高效:
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE REGION = &MELBOURNE&
　　//低效:
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
WHERE LOC_ID = 10 OR REGION = &MELBOURNE&
　　如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
25、用IN来替换OR
　　这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的.
　　//低效:
　　SELECT&. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
　　//高效:
SELECT& FROM LOCATION WHERE LOC_IN IN (10,20,30);
26、避免在索引列上使用IS NULL和IS NOT NULL
　　避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引.对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中.举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
　　//低效: (索引失效)
　　SELECT & FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
　　//高效: (索引有效)
SELECT & FROM DEPARTMENT WHERE DEPT_CODE &=0;
27、总是使用索引的第一个列
　　如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
28、 用UNION-ALL 替换UNION
　　( 如果有可能的话)
　　当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量
　　//低效：
　　SELECT ACCT_NUM, BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = &31-DEC-95&
　　UNION &www.2cto.com &
　　SELECT ACCT_NUM, BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = &31-DEC-95&
　　//高效:
　　SELECT ACCT_NUM, BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = &31-DEC-95&
　　UNION ALL
　　SELECT ACCT_NUM, BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = &31-DEC-95&
29、用WHERE替代ORDER BY
　　ORDER BY 子句只在两种严格的条件下使用索引;ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.;ORDER BY中所有的列必须定义为非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
　　//表DEPT包含以下列:
　　DEPT_CODE PK NOT NULL
　　DEPT_DESC NOT NULL
　　DEPT_TYPE NULL
　　//低效: (索引不被使用)
　　SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
　　//高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE & 0
30、避免改变索引列的类型.
　　当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
　　假设 EMPNO是一个数值类型的索引列.
SELECT & FROM EMP WHERE EMPNO = &123&
　　实际上,经过ORACLE类型转换, 语句转化为:
SELECT & FROM EMP WHERE EMPNO = TO_NUMBER(&123&)
　　幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
　　现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT & FROM EMP WHERE EMP_TYPE = 123
　　这个语句被ORACLE转换为:
　　WHERETO_
SELECT & FROM EMP NUMBER(EMP_TYPE)=123
　　因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
31、需要当心的WHERE子句
　　某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子. 在下面的例子里,
　　&!=& 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.
　　&||&是字符连接函数. 就象其他函数那样, 停用了索引.
　　&+&是数学函数. 就象其他数学函数那样, 停用了索引.
　　相同的索引列不能互相比较,这将会启用全表扫描.
32、如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
　　在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
33、避免使用耗费资源的操作
　　带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎,执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强.
34、优化GROUP BY
　　提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
　　//低效: &www.2cto.com &
　　SELECT JOB , AVG(SAL)
　　FROM EMP
　　GROUP JOB
　　HAVING JOB = &PRESIDENT&
　　OR JOB = &MANAGER&
　　//高效:
　　SELECT JOB , AVG(SAL)
　　FROM EMP
　　WHERE JOB = &PRESIDENT&
　　OR JOB = &MANAGER&
　　GROUP JOB博客访问： 970843
博文数量： 542
博客积分： 12626
博客等级： 上将
技术积分： 5694
注册时间：
从事IT基础架构多年，发现自己原来更合适去当老师……喜欢关注新鲜事物，不仅限于IT领域。
分类： Oracle 17:08:54
[Q]如何限定特定IP访问数据库
[A]可以利用登录触发器、cmgw或者是在$OREACLE_HOME/network/admin下新增一个protocol.ora文件（有些os可能是. protocol.ora），9i可以直接修改sqlnet.ora：
增加如下内容：
tcp.validnode_checking=yes
#允许访问的ip
tcp.inited_nodes=(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)
[Q]如何穿过防火墙连接数据库
[A]这个问题只会在WIN平台出现，UNIX平台会自动解决。
解决方法：
在服务器端的SQLNET.ORA应类似
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = 16
注册表的HOME0加[HKEY_LOCAL_MACHINE]
USE_SHARED_SOCKET=TRUE
[Q]如何利用hostname方式连接数据库
host name方式只支持tcp/ip协议的小局域网
修改listener.ora中的如下信息
(SID_DESC =
(GLOBAL_DBNAME = ur_hostname) --你的机器名
(ORACLE_HOME = E:\oracle\ora92) --oracle home
(SID_NAME = orcl) --sid name
然后在客户端
的sqlnet.ora中，确保有
NAMES.DIRECTORY_PATH= (HOSTNAME)
你就可以利用数据库服务器的名称访问数据库了
[Q]dbms_repcat_admin能带来什么安全隐患
[A]如果一个用户能执行dbms_repcat_admin包，将获得极大的系统权限。
以下情况可能获得该包的执行权限：
1、在sys下grant execute on dbms_repcat_admin to public[|user_name]
2、用户拥有execute any procedure特权（仅限于9i以下，9i必须显示授权）
如果用户通过执行如下语句：
exec sys.dbms_repcat_admin.grant_admin_any_schema('user_name');
该用户将获得极大的系统特权
可以从user_sys_privs中获得详细信息
[Q]在不知道用户密码的时候，怎么样跳转到另外一个用户执行操作后并不影响该用户?
[A]我们通过如下的方法，可以安全使用该用户，然后再跳转回来，在某些时候比较有用
需要Alter user权限或DBA权限：
SQL> select password from dba_users where username='SCOTT';
-----------------------------
SQL> alter user scot
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F02B67';
User altered.
SQL> connect scott/tiger
Connected.
[Q]如何加固你的数据库
[A]要注意以下方面
1. 修改sys, system的口令。
2. Lock，修改，删除默认用户： dbsnmp,ctxsys等。
3. 把REMOTE_OS_AUTHENT改成False，防止远程机器直接登陆。
4. 把O7_DICTIONARY_ACCESSIBILITY改成False。
5. 把一些权限从PUBLIC Role取消掉。
6. 检查数据库的数据文件的安全性。不要设置成666之类的。检查其他dba 用户。
7. 把一些不需要的服务（比如ftp, nfs等关闭掉）
8. 限制数据库主机上面的用户数量。
9. 定期检查Metalink/OTN上面的security Alert。比如：http://otn.oracle.com/deploy/security/alerts.htm
10. 把你的数据库与应用放在一个单独的子网中，要不然你的用户密码很容易被sniffer去。或者采用advance security，对用户登录加密。
11. 限止只有某些ip才能访问你的数据库。
12. lsnrctl 要加密码，要不然别人很容易从外面关掉你的listener。
13. 如果可能，不要使用默认1521端口
[Q]如何检查用户是否用了默认密码
[A]如果使用默认密码，很可能就对你的数据库造成一定的安全隐患，那么可以使用如下的查询获得那些用户使用默认密码
select username "User(s) with Default Password!"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'4A3BA55E08595C81', -- outln
'F02B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DAB81', -- tracesvr
'DC75E', -- oas_public
'A9E29E', -- websys
'AC0EB', -- lbacsys
'E7B5D', -- rman
'AC95', -- perfstat
'66F4EF', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4CDC6A', -- sys
'D4DF') -- system
[Q]如何修改默认的XDB监听端口
[A] Oracle9i默认的XML DB把HTTP的默认端口设为8080，这是一个太常用的端口了，很多别的WebServer都会使用这个端口，
如果我们安装了它，最好修改一下，避免冲突，如果不使用呢，就最好不要安装
提供三种修改的方法
1.dbca，选择你的数据库，然后Standard Database Features->Customize->Oracle XML DB option，进入这个画面你应该就知道怎么改了。
2.OEM console，在XML Database 的配置里面修改
3.用oracle提供的包：
-- 把HTTP/WEBDAV端口从8080改到8081
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/te xt()',8081))
-- 把FTP端口从2100改到2111
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text ()',2111))
SQL> exec dbms_xdb.cfg_
-- 检查修改是否已经成功
SQL> select dbms_xdb.cfg_
[Q]怎么捕获用户登录信息，如SID，IP地址等
[A]可以利用登录触发器，如
CREATE OR REPLACE TRIGGER tr_login_record
AFTER logon ON DATABASE
miUserSid NUMBER;
mtSession v$session%ROWTYPE;
CURSOR cSession(iiUserSid IN NUMBER) IS
SELECT * FROM v$session
WHERE sid=iiUserS
SELECT sid INTO miUserSid FROM v$mystat WHERE rownumcreate public synonym plan_table for plan_
SQL> grant all on plan_
如果想让自动跟踪的角色让每个用户都能使用，则
SQL> grant
通过如下语句开启/停止跟踪
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
[Q]如果跟踪自己的会话或者是别人的会话
[A]跟踪自己的会话很简单
Alter session set sql_trace true|false
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
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
sqlplus /nolog <100 ,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,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
select /*+ index(cbotab cbotab1) */ col1
select /*+ index(a cbotab1) */ col1
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 datab
是否开启了FORCE LOGGING，可以用如下语句查看
SQL> select force_logging from v$
[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)
如果在哪个session需要测试虚拟索引，可以利用隐含参数来处理
alter session set "_use_nosegment_indexes" =
就可以利用explain plan for select ……来看虚拟索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划
最后，根据需要，我们可以删除虚拟索引，如普通索引一样
drop index virtual_index_
注意：虚拟索引并不是物理存在的，所以虚拟索引并不等同于物理索引，不要用自动跟踪去测试虚拟索引，因为那是实际执行的效果，是 用不到虚拟索引的。
[Q]怎样监控无用的索引
[A]Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引
开始监控：alter index index_n
检查使用状态：select * from v$object_
停止监控：alter index index_nam
当然，如果想监控整个用户下的索引，可以采用如下的脚本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||''
FROM dba_indexes
WHERE owner = USER;
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||''
FROM dba_indexes
WHERE owner = USER;
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_N
对于已经创建了的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活动
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
[Q]怎么杀掉特定的数据库会话
[A] Alter system kill session 'sid,serial#';
alter system disconnect session 'sid,serial#'
在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-
2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n);
3. alter table big-table deallocate unused keep 1500
4. drop table big-
[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
还可以使用诊断事件
1、 确定TEMP表空间的ts#
SQL> select ts#, name FROM v$
-----------------------
2、 执行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
temp表空间的TS# 为 3*, So TS#+ 1= 4
如果想清除所有表空间的临时段，则
[Q]怎么样dump数据库内部结构，如上面显示的控制文件的结构
[A]常见的有
1、分析数据文件块，转储数据文件n的块m
alter system dump datafile n block m
2、分析日志文件
alter system dump
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
err_msg VARCHAR2(120);
dbms_output.enable (1000000);
FOR err_num IN 1
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
在Unix系统上，事件信息放在一个文本文件里
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下脚本查看事件信息
event=10000
while [ $event -ne 10999 ]
event=`expr $event + 1`
oerr ora $event
对于已经确保的/正在跟踪的事件，可以用如下脚本获得
SET SERVEROUTPUT ON
l_level NUMBER;
FOR l_event IN 1
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));
[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. -- 进行信息收集统计，每次运行都将产生一个快照号
-- 获得快照号，必须要有两个以上的快照，才能生成报表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 输入需要查看的开始快照号与结束快照号
其他相关脚本s:
spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号
sptrunc.sql - 清除(truncate)所有统计信息
发表于 @ 日 12:54 PM | 评论 (0)
oracle faq----3
第三部分、备份与恢复
[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 名称
host cp 这个表空间的数据文件 目的地;
Alter tablespace 名称
如果是备份多个表空间或整个数据库，只需要一个一个表空间的操作下来就可以了。
[Q]怎么快速得到整个数据库的热备脚本
[A]可以写一段类似的脚本
SQL>set serveroutput on
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||'');
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/');
dbms_output.put_line('alter tablespace '||bk_ts.name||'');
[Q]丢失一个数据文件，但是没有备份，怎么样打开数据库
[A]如果没有备份只能是删除这个数据文件了，会导致相应的数据丢失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name'
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name'
注意：该数据文件不能是系统数据文件
[Q]丢失一个数据文件，没有备份但是有该数据文件创建以来的归档怎么恢复
[A]保证如下条件
a. 不能是系统数据文件
b. 不能丢失控制文件
如果满足以上条件，则
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ...
SQL> -文件号
SQL>recover datafile 'file name';
[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 rma
SQL> alter user rman default tablespace tools tempo
SQL> alter user rman quot
SQL> grant connect, resource, recovery_catalog_
然后，用这个用户登录，创建恢复目录
rman catalog rman/rman
RMAN> create cata
最后，你可以在恢复目录注册目标数据库了
rman catalog rman/rman target backdba/backdba
[Q]怎么样在恢复的时候移动数据文件，恢复到别的地点
[A]给一个RMAN的例子
set until time 'Jul 01 :00';
allocate channel d1
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';
sql "alter database mount";
sql "alter database open resetlogs";
release channel d1;
[Q]怎么从备份片（backuppiece）中恢复(restore)控制文件与数据文件
[A]可以使用如下方法，在RMAN中恢复备份片的控制文件
restore controlfile
如果是9i的自动备份，可以采用如下的方法
restore control
但是，如果控制文件全部丢失，需要指定DBID，如SET DBID=?
自动备份控制文件的默认格式是%F，这个格式的形式为
c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII就是DBID
至于恢复(restore)数据文件，oracle 816开始有个包dbms_backup_restore
在 nomount 状态下就可以执行，可以读 815甚至之前的备份片，读出来的文件用于恢复
可以在SQLPLUS中运行，如下
SQL>startup nomount
SQL> DECLARE
2 devtype varchar2(256);
5 devtype := dbms_backup_restore.deviceallocate('', params=>'');
6 dbms_backup_restore.
7 dbms_backup_restore.restorecontrolfileto('E:\Oracle\oradata\ penny\control01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:\Oracle\oradata\p enny\system01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:\Oracle\oradata\p enny\UNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:\ORACLE\ORADATA\P ENNY\USERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:\orabak\BACKUP_1_4 _04F4IAJT.PENNY',done=>done);
PL/SQL 过程已成功完成。
[Q]Rman的format格式中的%s类似的东西代表什么意义
[A]可以参考如下
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)
[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"目录下的文件"dbmslmd.sql"
TYPE col_desc_array IS VARRAY(513) OF col_
TYPE col_desc_array IS VARRAY(700) OF col_
并保存文件
2. 运行改变后的脚本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.重新编译该包
SQLPLUS> alter package DBMS_LOGMNR_D
[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'));
发表于 @ 日 12:53 PM | 评论 (0)
oracle faq----2
第二部分、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表示
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
,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
,ksppinm as NAME
,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$表，将可能导致严重错误。
Alter data
Alter system enabl
Alter system set job_queue_process=0;
Alter database character set zhs16
[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$
如果是9i以上版本，还可以通过以下语句获取
select dbms_flashback.get_system_change_
[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')
如果获取整个用户的脚本，可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_
当然，如果是索引，则需要修改相关table到index
[Q]如何创建约束的索引在别的表空间上
[A]1、先创建索引，再创建约束
2、利用如下语句创建
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
[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
如果不设置该环境变量，将在运行脚本的时候提示输入环境变量
当然，除了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的用户名与密码
如我们知道了补丁号04的补丁集)，则
ftp> cd 3095277
250 Changed directory OK.
200 PORT command OK.
150 Opening data connection for file listing.
p4_AIX64-5L.zip
p4_AIX64.zip
p4_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
知道了这个信息，我们用用flashget，网络蚂蚁就可以下载了。
添加如下连接
ftp://updates.oracle.com/...04_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.A 打开数据库
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';
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 '文件全名'
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database
[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 ()/51 + 16 '"Redo black(byte)"
Redo black(byte)
------------------
[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实现），假设为，假定文件编码为。运行下面的查询查找段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
between block_id and (block_id + blocks - 1)
一旦找到坏段名称，若段是一个表，则最好建立一个临时表，存放好的数据。若段是索引，则删除它，再重建。
create table good_table
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) =
在这里要注意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_
最后关闭诊断事件
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
发表于 @ 日 12:49 PM | 评论 (0)
oracle faq----1
第一部分、SQL&PL/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] 默认语句级一致性
set transaction [isolation level]
事务级一致性
[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
l_str varchar2(255) default NULL;
l_num number default p_
l_hex varchar2(16) default 'ABCDEF';
if ( p_dec is null or p_base is null ) then
if ( trunc(p_dec)
p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
l_num number default 0;
l_hex varchar2(16) default 'ABCDEF';
if ( p_str is null or p_from_base is null ) then
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;
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询，你就明白了
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
[Q]怎么获得今天是星期几，还关于其它日期函数用法
[A]可以用to_char来解决，如
select to_char(to_date('','yyyy-mm-dd'),'day')
在获取之前可以设置日期语言，如
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
还可以在函数中指定
select to_char(to_date('','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American')
其它更多用法，可以参考to_char与to_date函数
如获得完整的时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
随便介绍几个其它函数的用法：
本月的天数
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 < N;
select * from (select * from tablename order by dbms_random.value) where rownum< 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
[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_
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如：dbms_lock.sleep(5);表示暂停5秒。
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
start_time := dbms_utility.get_
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_O
--other dml statement
end_time := dbms_utility.get_
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');
[Q]怎样创建临时表
[A]8i以上版本
create global temporary tablename(column list)
--提交保留数据 会话临时表
o --提交删除数据 事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
[Q]怎么样在PL/SQL中执行DDL语句
[A]1、8i以下版本dbms_sql包
2、8i以上版本还可以用
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 . All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
DBMS_JOB.SUBMIT(:jobno, 'ur_',SYSDATE,'SYSDATE + 1');
之后，就可以用以下语句查询已经提交的作业
select * from user_
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp) time2
TIME1 TIME2
----------------------------- ------------------------------------------------------------ ----
10:48:45.-OCT-03 10.48.45.656000 AM +08:00
可以看到，毫秒在to_char中对应的是FF。
8i以上版本可以创建一个如下的java函数
SQL>create or replace and compile
java source
named "MyTimestamp"
import java.lang.S
import java.sql.T
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>function created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME
MY_TIMESTAMP ORACLE_TIME
------------------------ -------------------
19:15:59.688
如果只想获得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_
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
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
WHERE a.id = b.id(+)
SELECT b.id,'' name,b.address
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
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)
INTO table_other (id, name)
values(id, name)
SELECT id,name
如果没有条件的话，则完成每个表的插入，如
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
[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、不定列行列转换
--------------
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
Col_c2 VARCHAR2(4000);
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
SQL> select distinct c1 ,get_c2(c1) cc2即可
[Q]怎么样实现分组取前N条记录
[A]8i以上版本，利用分析函数
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
where rn<=3
[Q]怎么样把相邻记录合并到一条记录
[A]8i以上版本，分析函数lag与lead可以提取后一条或前一天记录到本记录。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
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" < host lsntctl start
或者unix/linux平台下
windows平台下
总结：HOST 可以直接执行OS命令。
备注：cd命令无法正确执行。
[Q]怎么设置存储过程的调用者权限
[A]普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure ……()
AUTHID CURRENT_USER
[Q]怎么快速获得用户下每个表或表分区的记录数
[A]可以分析该用户，然后查询user_tables字典，或者采用如下脚本即可
SET SERVEROUTPUT ON SIZE 20000
miCount INTEGER;
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miC
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
[A]怎么在Oracle中发邮件
[Q]可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序
/*********************************************************** *****************
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
desc: ·发送邮件到指定邮箱
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
************************************************************ ****************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
conn utl_smtp.
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(conn);
WHEN OTHERS THEN
WHEN OTHERS THEN
END sp_send_
[A]怎么样在Oracle中写操作系统文件，如写日志
[Q]可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
/*********************************************************** ***************
parameter:textContext in varchar2 日志内容
desc: ·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
************************************************************ ****************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
file_handle utl_file.file_
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
--close file
utl_file.fclose(file_handle);
WHEN OTHERS THEN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
WHEN OTHERS THEN
END sp_Write_
发表于 @ 日 12:48 PM | 评论 (0)
oracle没有自增字段
oracle没有自增字段
以下是一种解决办法，但问题是这个自增总归是一个有限的自增
create sequence seq_name
increment by 1
start with 1
maxvalue 999999;
Insert into table_name(Id,...)values(seq_name.nextval,...)
阅读(2737) | 评论(4) | 转发(0) |
给主人留下些什么吧！~~
其实上面的很多东西是平时用不到的，有的也不是最好的。不过毕竟每个人遇到的问题不同嘛。感谢来访：）
虽然大部分都已经了解，但还是感谢贡献！
也不算复杂吧，熟悉了oracle本身结构后就变得简单了。以上只是些小技巧而已，方便查询。
有点复杂呀，我看不懂呀
请登录后评论。