warehouse客栈

ITPUB认证区版主

  • 博客访问: 4685492
  • 博文数量: 851
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-07 15:08
  • 认证徽章:
个人简介

了解并联系warehouse: http://blog.itpub.net/19602/viewspace-1059211/

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(851)

文章存档

2017年(7)

2016年(20)

2015年(19)

2014年(42)

2013年(65)

2012年(66)

2011年(87)

2010年(68)

2009年(103)

2008年(140)

2007年(142)

2006年(38)

2005年(53)

2004年(1)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

发布时间:2008-12-27 18:50:51

自治事务允许我们在"事务中创建事务"[@more@]SQL> truncate table tt7;表被截断。SQL> create or replace procedure proc_autotrans 2 is 3 pragma autonomous_transaction; 4 begin 5 insert into tt7 values(1,'autonomous'); 6 commit; 7 end; 8 /过程已创建。SQL> begin 2 insert into tt7 values(1,'niminblock'); 3 proc_autotrans; 4 rollback; 5 end; 6 /PL/SQL 过程已成功完成。SQL> select * from tt7; ID NAME---------- ---------- 1 autonomous--只插入......【阅读全文】

阅读(148230) | 评论(0) | 转发(0)

发布时间:2008-12-26 23:17:08

事务的原子性是指事务中的动作要么发生,要么不发生.不想做太多的解释,想了解可以看tom的力作,这里只是随手记录个测试过程.[@more@]SQL> alter table tt8 add constraint pk_tt8 primary key (id);表已更改。SQL> edit已写入 file afiedt.buf 1 create or replace procedure proc_tt8 2 is 3 begin 4 insert into tt8 values(1,'a'); 5 insert into tt8 values(1,'a'); 6* end;SQL> /过程已创建。SQL> begin 2 proc_tt8; 3 end; 4 /begin*第 1 行出现错误:ORA-00001: 违反唯一约束条件 (SYS.PK_TT8)ORA-06512......【阅读全文】

阅读(149127) | 评论(0) | 转发(0)

发布时间:2008-12-26 22:44:21

呵呵,都不知道起个啥名字好,这个操作我渴望已久了,但遗憾的是自己才发现,其实oracle可能一直就支持,汗,以前没有细读过tom的书,doc也看的不细...[@more@]SQL> desc tt7 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID NUMBER(38) NAME VARCHAR2(10)SQL> select * from tt7; ID NAME---------- ---------- 1 a ......【阅读全文】

阅读(3278) | 评论(0) | 转发(0)

发布时间:2008-12-19 17:00:28

在assm下,shrink时可以结合dbms_space.space_usage来观察空间的shrink情况[@more@]  alter table tbname row movement--=======================================  保持HWM  alter table tbname shrink space compact;  回缩表与HWM  alter table tbname shrink space;  回缩表与相关索引  alter table tbname shrink space cascade;  回缩索引  alter index idxname shrink space;如果index space被shrink的话,index的hwm也会下降,这一点可以通过dump index segment header block 观察到,另外值得注意的是shrink之......【阅读全文】

阅读(68853) | 评论(0) | 转发(0)

发布时间:2008-12-19 16:32:48

简单的包装一下,使用时调用,放在这里备查! [@more@]SQL> create or replace procedure show_space_assm(2 segment_owner varchar2,3 segment_name varchar2,4 segment_type varchar2)5 is6 UNFORMATTED_BLOCKS NUMBER;7 UNFORMATTED_BYTES NUMBER;8 FS1_BLOCKS NUMBER;9 FS1_BYTES NUMBER;10 FS2_BLOCKS NUMBER;11 FS2_BYTES NUMBER;12 FS3_BLOCKS NUMBER;13 FS3_BYTES NUMBER;14 FS4_BLOCKS NUMBER;15 FS4_BYTES NUMBER;16 FULL_BLOCKS NUMBER;17 FULL_BYTES NUMBER;18 begin19 dbms_space.space_usage(20 segment_owner,21 segmen......【阅读全文】

阅读(4217) | 评论(0) | 转发(0)

发布时间:2008-12-17 13:51:20

在线重建一个index时无意中发现了这样的问题,varchar2类型的列不能超过3201,这可能是一个内部限制。SQL> alter index idx_t rebuild online;alter index idx_t rebuild online*第 1 行出现错误:ORA-00604: 递归 SQL 级别 1 出现错误ORA-01450: 超出最大的关键字长度 (3215)[@more@]SQL> desc t名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NUMBER(38)NAME VARCHAR2(3202)SQL> alter index idx_t rebuild online;alter index idx_t rebuild online*第 1 行出现错误:......【阅读全文】

阅读(4077) | 评论(0) | 转发(0)

发布时间:2008-12-14 22:34:35

11g v$process中增加了一个有用的字段tracefile![@more@]11g,v$process中增加了一个tracefile字段,该字段记录的是进程产生的tracefile,一个看起来不起眼的字段却给我带来了很大的方便,以前我们要确认进程所产生的trace文件需要通过下面方法:1.首先需要知道当前的sessionidSQL> select sid from v$mystat where rownum=1; SID---------- 1442.之后需要知道该session所对应的process addressSQL> select paddr from v$session where sid=144;PADDR--------226958503.根据process address进一步需要知道os process(t......【阅读全文】

阅读(3268) | 评论(0) | 转发(0)

发布时间:2008-12-10 14:43:17

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......【阅读全文】

阅读(4062) | 评论(0) | 转发(0)

发布时间:2008-12-10 11:37:36

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为横......【阅读全文】

阅读(4159) | 评论(0) | 转发(0)

发布时间:2008-12-09 11:46:56

利用这个功能结合参数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------------------------------ --......【阅读全文】

阅读(4056) | 评论(0) | 转发(0)

发布时间:2008-12-09 09:45:00

在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......【阅读全文】

阅读(3761) | 评论(0) | 转发(0)

发布时间:2008-11-12 13:32:54

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......【阅读全文】

阅读(3341) | 评论(0) | 转发(0)

发布时间:2008-11-10 12:24:15

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......【阅读全文】

阅读(4027) | 评论(0) | 转发(0)

发布时间:2008-11-10 11:20:03

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......【阅读全文】

阅读(7724) | 评论(0) | 转发(0)

发布时间:2008-11-05 13:54:53

可以通过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......【阅读全文】

阅读(82425) | 评论(0) | 转发(0)

发布时间:2008-11-05 13:49:24

尽管可以通过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......【阅读全文】

阅读(4818) | 评论(0) | 转发(0)

发布时间:2008-10-25 20:45:02

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 =......【阅读全文】

阅读(9391) | 评论(0) | 转发(0)

发布时间:2008-10-24 20:47:25

在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......【阅读全文】

阅读(4081) | 评论(0) | 转发(0)

发布时间:2008-10-19 23:21:15

什么是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......【阅读全文】

阅读(3493) | 评论(0) | 转发(0)

发布时间:2008-10-19 19:01:18

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......【阅读全文】

阅读(3858) | 评论(0) | 转发(0)

发布时间:2008-10-13 22:48:47

表与表之间错误的连接方式会对性能产生巨大影响。[@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......【阅读全文】

阅读(149237) | 评论(0) | 转发(0)

发布时间:2008-10-02 08:55:16

详细解释了备份恢复的原理![@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......【阅读全文】

阅读(3793) | 评论(0) | 转发(0)

发布时间:2008-09-30 23:19:34

记录一下!http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx[@more@]http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx......【阅读全文】

阅读(2907) | 评论(0) | 转发(0)

发布时间:2008-09-26 15:37:12

讲课讲到不完全恢复(找回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 ......【阅读全文】

阅读(7544) | 评论(0) | 转发(1)

发布时间:2008-09-25 08:27:30

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......【阅读全文】

阅读(2743) | 评论(0) | 转发(0)
给主人留下些什么吧!~~
留言热议
请登录后留言。

登录 注册