ITPub博客

listern配置[@more@][root@rac1 admin]# more listener.ora # listener.ora.rac1 Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac1 # Generated by Oracle configuration tools. LISTENER_RAC1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.141)(PORT = 1521)(IP = FIRST)) ) ) SID_LIST_LISTENER_RAC1 = (SID_LIST = (SID_DE

今天flashback table的时候出现了ORA-08194错误，临时办法将错误操作前的数据保存到另外一张表上面。下面是自己测试的笔记。SQL> create table tt (id int,name varchar2(20));Table created.[@more@]SQL> insert into tt values(1,'bruce');1 row created.SQL> commit;Commit complete.SQL> create materialized view tt_mv as select * from tt;Materialized view created.SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ----------------------------

1,先使用expdp parallel导出文件[@more@]C:UsersFounder>expdp xxxx/founder2012 directory=temp_dir dumpfile=xxxx_%U.dmp parallel=3 Export: Release 11.2.0.1.0 - Production on 星期五 3月 8 09:48:53 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 启动 "xxxx"."SYS_EXPORT_SCHEMA_01

今天打开oracle数据库的时候出现了如下错误:[@more@]Errors in file d:appdiagrdbmstesttesttracetest_cjq0_4368.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select procedure#,entrypoint...","SQLA","tmp")不能shutdown immediateSQL> shutdown immediate ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ index(idl_sb4$ i_...",&qu

可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：[@more@]select ... from tablename start with cond1 connect by cond2 where cond3;简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONN

Fatal NI connect error 12170.[@more@] VERSION INFORMATION: TNS for Linux: Version 11.2.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production Time: 15-AUG-2012 16:09:47 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12560 nt main err code: 505 TNS-00505: Operation timed

今天测试了一下 stream之schema复制,如下:[@more@]source database是linux oracle10gtarget database是 window oracle11g1,配置前准备 global_names参数必须等于true 数据库必须为archive模式 查询global name的方法SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- ORACLE10G.COM.CN2,source database上执行 SQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m;Tablespace created.

今天测试了一下oracle stream的table级复制:[@more@]1.source database SQL> alter database rename global_name to oracle10g;Database altered. SQL> show parameter global_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_names boolean TRUESQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m; Tablespace created. SQL&gt

今天测试了一下利用materialized view同步数据[@more@]source 端:SQL> create table tt (id int,name varchar2(20));Table created.SQL> insert into tt values(1,'x');1 row created.SQL> commit;Commit complete.在target 端:target端必须要有create materialized view,create database link的权限SQL> create materialized view tt_mv as select * from tt@oracle10g;Materialized view created.SQL> select * from tt@oracle10g; ID NAME---------- ---------------------------------------- 1 xSQL&g

今天测试oracle stream 创建database link 出现如下错误:[@more@]SQL> create database link test.com.cn connect to scott identified by oracle using 'test'; Database link created. SQL> select * from emp@test.com.cn; select * from emp@test.com.cn * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from TEST在网上baidu之后发现密码需要加上双引号。奇怪，为什么要加上呢????SQL> drop database link test.com.cn; Database link droppe

1,打开text importer pl/sql developer的tools --> text importer2,然后在弹出的对话框中选择data to oracle3,选择owner,table4,然后打开open data file 选择需要导入的cvs文件5,然后单击import即将需要导入的数据，导入到oracle数据库中[@more@]

1.rollup,cube 多维汇总函数2.sampling 抽样函数3.rank 排名行数 rank,percentile,ntile,top,bottom4.moving window 计算累计值和动态值，avg,sum,min,max,count,variance,stddev,firstvalue5.period-over-period comparsions:比较函数6.ratio-to-report:比率计算行数7.lag/lead functions:按时间进行记录值的比较。8.statistical functions：协方差，线性回归等统计函数，如covariance,correlation,linear regression.[@more@]

11gR2中Flashback_transaction_query的undo_sql列为空的解决办法，查看别人的blog之后，自己测试了一下。[@more@]SQL> select table_name,operation,row_id,undo_sql from flashback_transaction_query where table_name='TT'; TABLE_NAME OPERATION ROW_ID UNDO_SQL -------------------- -------------------- -------------------- -------------------- TT UNKNOWN TT UNKNOWN TT UNKNOWN TT UNKNOWN TT

SQL> alter session set events '10053 trace name context forever,level 1'; Session altered. SQL> alter session set events '10053 trace name context off'; Session altered. cluf--聚合因子： 它表示索引中的键值和原表上的数据分布的一种关系，当索引键值和表中的排列顺序大致相同时，他意味着索引键指向的数据块越集中，这个因子就越小，越有利用索引的使用。相反，当索引键之指向的数据块越多时，这个因子就越多，越不利于索引的使用。 [@more@]

sql_trace and 10046事件[@more@]1).trace_filealter session set tracefile_identifier='mytest' 可以设置目前session的trace file的名字。alter session set sql_trace=true2)[oracle@oracle10g ~]$ tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use 'schema.tablename' with 'explain=' option. explain=user/password Connect to ORACLE and issue EXPLAIN PLAN. print=integer List only the first 'in

logminer的一点测试[@more@]logminer的基本组成packagedbms_logmnr_d:包含一个用于提取数据字典信息的过程，即dbms_logmnr_d.build() 还包含一个重建logminer表的过程,dbms_logmnr_d.set_tablespace,默认情况下， longminer表建立在system表空间中dbms_logmnr: add_logfile(name varchar2,options number) 添加/删除用于分析的日志文件 start_logmnr(start_scn number,end_scn number,start_time number,end_time number,

set feedback off heading off verify off trimspool offset pagesize 0 linesize 600define dir='/u01/wb'define ws = '/u01/ws.ql'spool &wsselect sql_undo from v$logmnr_contents;spool off[@more@]

今天kill session的时候，想写脚本kill session发现一只不能写好，一直卡在单引号当中折腾了半天之后，写出来了如下：select 'alter system kill session ''' || sid || ','||serial# ||'''' ||';' from v$session转载网上其他人写的资料(如下):[@more@]oracle多个单引号的处理在ORACLE中，单引号有两个作用，一是字符串是由单引号引用，二是转义。单引号的使用是就近配对，即就近原则。而在单引号充当转义角色时相对不好理解。下面转载1、从第二个单引号开始被视为转义符，如果第二个单引号后面还有单引号（哪怕只有一个）。SQL>

今天exp数据的时候出现错误:EXP-00091: Exporting questionable statistics.[@more@]今天exp数据的时候出现错误:EXP-00091: Exporting questionable statistics.查看一下错误:[oracle@testdb ~]$ oerr exp 00091 00091, 00000, "Exporting questionable statistics." // *Cause: Export was able export statistics, but the statistics may not be // usuable. The statistics are questionable because one or more of // the following happened during export: a row error occurred, client //

fast block cleanout 快速清除，如果提交时修改过的数据块仍然在buffer cache中，那么oracle就可以清楚ITL信息。快速清除有关限制，当修改的快数量超过10%的时候，则对超过10%的部分不进行快速块清除。delayed block cleanout，如果提交时，修改过的数据块已经协会数据文件(超过10%部分)，再次读出数据块进行修改时，就选择delayed block cleanou,等到下次访问该block的时候清除ITL信息。[@more@] fast block cleanout 快速清除，如果提交时修改过的数据块仍然在buffer cache中，那么oracle就可以清楚ITL信息。快速清除有关限制，当修