ITPub博客

http://space.itpub.net/17997/viewspace-255421利用此连接中楼主提到的办法能够顺利解决，同样用老杨提到的修改client端host文件没有解决，版本10.2,我同意byfree的观点，终归就是dns解析的问题，如果都改成ip的话相信不会出现该问题，遗憾的是，测试了一下没能成功。[@more@]http://space.itpub.net/17997/viewspace-255421最根本的解决办法：分别在节点1和节点2上修改local_listener参数：节点1：SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.217)(PORT = 1521))' SID='racdb1';System altered

10g em中给出了memory advisor，其中如果设置了sga_target的值的话，那么该顾问通过图表的形式给出的是sga的建议值，该建议值是如何被计算的呢，其实是通过下面公式：select sga_size,100* round(((estd_db_time1 - estd_db_time)/estd_db_time1),2)from (select sga_size,estd_db_time ,(select estd_db_time from v$sga_target_advice where sga_size_factor=1) estd_db_time1 from v$sga_target_advice where sga_size_factor<>1order by sga_size)通过上面查询可以获得sga_size以及db_time的改善百分比，在excel中以sga_size为横

利用这个功能结合参数skip_unusable_indexes 在做大数据量的dml操作时可能会非常的有用！[@more@]SQL> select * from t2;ID----------12SQL> create index idx_t2 on t2(id);索引已创建。SQL> select index_name, status from dba_indexes where index_name='IDX_T2';INDEX_NAME STATUS------------------------------ --------IDX_T2 VALIDSQL> alter index idx_t2 unusable;索引已更改。SQL> select index_name, status from dba_indexes where index_name='IDX_T2';INDEX_NAME STATUS------------------------------ --

在oracle中使用update进行交叉更新时一直都觉得很不方便，当然这是和sql server or sybase相比较而言的。[@more@]SQL> select *from a1; ID N SFZHAO---------- - ------------------ 1 a 1 c 2 a 2 c 3 c 123SQL> select *from a2; ID N SFZHAO---------- - ------------------ 1 c 111 2 d 111--如果使用下面的sql来更新表a1的话，我们发现出现了我们不希望出现的结果，把a1中id=3所对应的sfzhao由原来的123更新成了null，这是我们不希望的。SQL> upda

doc的原话，随手记录！The SQL Access Advisor's recommendations are significantly improved if you gather structural statistics about table and index cardinalities, and the distinct cardinalities of every dimension level column, JOIN KEY column, and fact table key column. You do this by gathering either exact or estimated statistics with the DBMS_STATS package. Because gathering statistics is time-consuming and extreme statistical accuracy is not required, it is generally preferable to estimate sta

doc原话，随手记录一下！http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/storage003.htm#sthref369[@more@]Using the Undo AdvisorThe amount of undo data that can be retained depends on the size of your undo tablespace. If your tablespace is set to auto-extend, then Oracle automatically acquires space as needed. If you choose to disable auto-extend, however, then you are responsible for ensuring that the undo tablespace has enough space. In this situation, Oracle configures the undo re

doc的原话，随手记录一下！[@more@]Tuning FAST_START_MTTR_TARGET and Using MTTR AdvisorTo determine the appropriate value for FAST_START_MTTR_TARGET for your database, use the following four step process:Calibrate the FAST_START_MTTR_TARGETDetermine the Practical Range for FAST_START_MTTR_TARGETEvaluate Different Target Values with MTTR AdvisorDetermine Optimal Size for Redo LogsCalibrate the FAST_START_MTTR_TARGETThe FAST_START_MTTR_TARGET initialization parameter causes the database to calculate

可以通过em使用Sql Tuning Advisor来寻找sql的优化建议，这里使用了package：DBMS_SQLTUNE获得sql优化的建议，只是想大致了解下Sql Tuning Advisor的过程。[@more@]SQL> create table t1(id int , name varchar2(10));表已创建。SQL> create table t2(id int , name char(1000));表已创建。SQL> begin 2 for i in 1..10000 loop 3 insert into t1 values(i,'a'||i); 4 insert into t2 values(i,'b'||i); 5 end loop 6 ; 7 commit; 8 end; 9 /PL/SQL 过程已成功完成。SQL> DECLARE 2 my_task_name VARCHAR2

尽管可以通过em使用Segment Advisor，不过我还是想通过调用dbms_advisor来简单的了解一下Segment Advisor的大致流程！[@more@]SQL> create table tt1 as select * from dba_objects;表已创建。SQL> SET SERVEROUTPUT ONdeclareuf_blocks number ;uf_bytes number;fs1_blocks number;fs1_bytes number;fs2_blocks number;fs2_bytes number;fs3_blocks number;fs3_bytes number;fs4_blocks number;fs4_bytes number;full_blocks number;full_bytes number;begindbms_space.space_usage('TEST','TT1','TABLE',uf_blocks ,uf_bytes ,fs1

pmon只会动态注册port等于1521的监听，否则pmon不能动态注册listener，要想让pmon动态注册listener，需要设置local_listener参数。[@more@]下面是大致测试过程。1.listener.ora内容如下：--=======================================SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = E:oracleproduct10.2.0db_1)(SID_NAME = orcl))(SID_DESC =(GLOBAL_DBNAME = test)(ORACLE_HOME = E:oracleproduct10.2.0db_1)(SID_NAME = test)))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xys)(PORT =

在windows的服务中stop listener口令不起作用，但是如果为listener设置了口令，那么在lsnrctl中执行stop，save_config，change_password命令操作不论时local还是remote listener时都需要通过set password来输入口令，只有口令正确才能执行上述命令。[@more@]环境：9i测试如下：我事先已经为listener设置了口令system，为listener设置口令可以通过change_password命令，修改口令也是该命令。--修改口令LSNRCTL> change_passwordOld password:输入systemNew password:输入managerReenter new password:输入manager正在连接到 (DESCRIPTIO

什么是rba？[@more@]Redo Byte Address (RBA)Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of the log file sequence number (4 bytes) the log file block number (4 bytes) the byte offset into the block at which the redo record starts (2 bytes) RBAs are not necessarily unique within their thread, because the log file sequence number may be reset to 1 in all threads if a database is opened with the RESETLOGS option. R

oracle的x$表非常重要，记录一下，备查！[@more@][K]ernel Layer[2]-Phase Commit [G]lobal [T]ransaction [E]ntryX$K2GTE - Current 2PC tx X$K2GTE2 - Current 2PC tx [C]ache Layer [B]uffer Management Buffer [H]ash X$BH - Hash Table Buffer LRU Statistics X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extendedBuffer [WAIT]s X$KCBWAIT - Waits by block class X$KCBFWAIT - Waits by File[W]orking Sets - 7.3 or higher X$KCBW

表与表之间错误的连接方式会对性能产生巨大影响。[@more@]SQL> create table t(id int , name char(10));表已创建。SQL> create table tt(id int , name char(2000));表已创建。SQL> begin 2 for i in 1..10000 loop 3 insert into t values(i,'test'); 4 end loop; 5 commit; 6 end; 7 /PL/SQL 过程已成功完成。SQL> begin 2 for i in 1..10000 loop 3 insert into tt values(i,'test1'); 4 end loop; 5 commit; 6 end; 7 /PL/SQL 过程已成功完成。SQL> exec dbms_stats.gather_table_stats('TES

详细解释了备份恢复的原理！[@more@]--controlfile中记录的checkpoint_change#其实一句话就可以说明白：那就是数据文件的头上不仅仅包含了checkpoint_change#,更重要的是它包含了这个checkpoint_change#所在的logfile的sequence＃，准确的说是rba。结果花了很大篇幅，只想以试验的方式做个简单的验证，便于大家理解。欢迎拍砖！另外提个问题：是否存在一些view它是源于redo的？SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 1951985--datafile中记录的checkpoint_change#SQL> se

记录一下！http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx[@more@]http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx

讲课讲到不完全恢复（找回drop table）时，经常需要确定drop table的确切时间，所以经常需要用到dbms_logmner，记录一下大致过程，备查！[@more@]SQL> desc dbms_logmnr_dPROCEDURE BUILD参数名称 类型 输入/输出默认值?------------------------------ ----------------------- ------ -------- DICTIONARY_FILENAME VARCHAR2 IN DEFAULT DICTIONARY_LOCATION VARCHAR2 IN DEFAULT OPTIONS NUMBER

How Oracle Works[@more@]How Oracle WorksThe following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the user and associated server process are on separate computers (connected through a network). An instance has started on the computer running Oracle (often called the host or database server).A computer running an application (a local computer or client workstation) runs the application in a user process. The client appl

物理读的情况是比较复杂的：1、会受到参数db_file_multiblock_read_count的影响已经不用多少了，为了减少物理读，需要设置db_file_multiblock_read_count为最大临界值。doc上提到绝大多数系统是1m，那么db_file_multiblock_read_count=系统的(max I/O size)/db_block_size系统的(max I/O size)如何准确的获得，也是我的疑问2、db_cache_size如果很小的话，那么物理读通常都会显著的高，原因是一次读进来的block无法或者是很少能找到连续的memory来容纳，这样只能通过多次来读取，读进来的block只能离散的分布在memory中，这也正是scattere

本人目前从事自由职业，oracle培训经验丰富，实践经验服务。oracle 10g ocp提供专业、全面的oracle培训、服务。联系方式：tel：13840879243mail：yongsheng.xie@gmial.commsn：xys_666888@hotmail.comqq：9020630blog：http://warehouse.itpub.net个人简介：http://warehouse.itpub.net/post/777/403336[@more@]联系方式：tel：13840879243mail：yongsheng.xie@gmial.commsn：xys_666888@hotmail.comqq：9020630blog：http://warehouse.itpub.net个人简介：http://warehouse.itpub.net/post/777/403336