For those of you who read my previous blog from last week, or read the recent White Paper entitled “Simplifying Oracle Database Administration” written by Mike Ault, Bert Scalzo and myself, you’ll have seen from the survey carried out by James F Koopmann that about 20% of a typical DBAs time is spent fire fighting and responding to user related problems. Some production DBAs I have spoken to put it even higher.

So, here is a process which, I’m sure many of you follow; essentially detect (or discover) that a problem exists, diagnose the root cause of the problem to understand what needs to be done and finally resolve the problem using appropriate methods, or the classic “top down” approach.

I’m going to briefly walk you through the minimum steps necessary using Toad’s DBA Module to achieve this simply, rapidly and effectively.

1. Detect

If your telephone is already ringing off the hook when you get to the office, it is essential that you have a systematic, repeatable approach to determining the root cause of a stoppage or performance slow-down which any of your team can follow.

1.1 Monitor the OS to see if something is running external to Oracle
                 (batch jobs, etc) – it is essential that this is ruled out first before
                  looking at the database.

Here’s an example of the Unix Monitor showing activity on a Linux operating system while executing an industry standard TPC-C Benchmark (see Figure 1)

Figure 1 – Monitoring the Linux operating system

The graph on the right shows both IO devices have heavy activity and the process list shows both the Oracle DBWR (database writer) and LGWR (log writer) processes are top resource consumers. Both CPU Usage and Process Queue are quite reasonable – and thus not an issue.

Toad’s Unix Monitor works for Linux, Solaris, AIX, HP-UX and Tru64.

1.2. Monitor the Oracle instances on the server to determine which is
                   the one with the problem

Toad’s Database Browser lets you report on multiple instances to assess which one has the problem. (see Figure 2)

Figure 2 – Monitoring multiple Oracle instances

Focus on one instance and:

1.3. Check the server, database and TNS Listener are running (see
                   Figure 3) 

  Figure 3 – Monitoring the Oracle Server, Listener and Database

Toad pings each server, TNS pings the selected databases and checks to see if the TNS Listener process is running and provides simple up/down indicators.

1.4. Monitor that instance for problems (see Figure 4)

Figure 4 – Monitoring key Oracle Database metrics 

            1.5. Check Alert Logs – Toad has an FTP interface if your alert logs are 
                   stored on a UNIX or Linux server.
            1.6. Verify Rollback Segments are online – Toad’s Schema Browser will
                   give you a quick indication of this.
            1.7. Check ADDM and AWR for instance performance problems –
                   check-out Toad’s Reports menu for a list of these and other
                   reports.
            1.8. Examine Top Sessions for resource usage (see Figure 5)

Figure 5 – Determining Top User Sessions by CPU consumption

1.9. Monitor specific sessions (Long operations, SQL Statement
                   execution, etc) – Toad’s Session Browser will relate the SID
                   to a user to show full session data including the SQL statement
                   the user is executing.     
            1.10. Monitor Tablespace usage for fragmentation, etc (see Figure
                     6)

Figure 6 – Monitoring Tablespace consumptio

2. Diagnose

Having determined where the issue is, you now need to quickly and effectively diagnose the exact cause of the problem and get user(s) up and running again.

In this screenshot of Toad for Oracle’s Database Probe window (see Figure 7)), you get real-time display of instance activity with alert markers indicating potential problems. Clicking on a marker displays a description of the problem – in this case excessive Redo Log Buffer size.

Figure 7 – Diagnosing an instance configuration problem

Another example might be slow database performance due to a user executing a very inefficient SQL statement (exposed by looking at top sessions by CPU for example). Having a view of all Oracle sessions with the ability to obtain information about each session (in this case the SQL statement the user is executing) enables a rapid way to resolve a problem.

Where the statement has already completed execution, you will need to look in the SGA or access the Oracle Trace files. (Database – Monitor – SGA Trace/Optimization or Database – Diagnose – TKProf)

In all cases, access to Toad’s SQL Optimizer directly from the SQL statement is essential in order to simplify the process of improving execution time. (see Figure 8)

Figure 8 – Diagnosing a users SQL execution problem

3. Resolve

Having diagnosed the root cause of the problem, whether it be database configuration, tablespace or rollback segment problems, or SQL execution performance, it is important to resolve the problem quickly and effectively.

Integration points in Toad for Oracle help enormously here (as you see in the screenshot above where SQL Optimizer can be launched directly from the diagnosis window.

In the case of a resolution which requires an improvement to the execution time for a SQL statement, this in itself may be time consuming and problematic.

Quest’s SQL Optimizer is a semi-automatic SQL and PL/SQL optimization tool which can be configured according to the business process being used. It is self-levelling in terms of the skill level of the person using it but, at all times, keeps you informed what it will do the database. (see Figure 9)

It is also batch oriented giving DBAs the freedom to let the tool optimize SQL according to rules they set without being involved, and also the manner in which the solution is deployed back into the application.

Figure 9 – Resolving a users SQL execution performance problem

Another essential characteristic which you will see in Toad’s DBA Module is workflow navigation. Tasks are presented in a clear, logical way – following a functional paradigm which makes sense and is easily repeatable without having to make notes !

Here is a sample of some of the menu features offered by Toad showing how they are offered in the functional paradigms of Administer, Compare, Create, Diagnose, etc (see Figure 10)

The order in which the menu items are presented can also be customized to suit a particular team – indeed, items could be removed or even an entirely new menu sub-system created to suit the workflow and range of tasks required.

Figure 10 : Administration Task presentation is clear and function based

摄影/陈晓卿（中央电视台《见证》制片人）

我在博客里常拎出来开涮的人有老六和土摩托，很长一段时间，我做到了一件事，以妖魔化的方式让土摩托受到很多人关注。感谢互联网，感谢信息过剩的时代喜欢用白痴方式思考问题的人们，土摩托越来越受到关注。

然后我又想，如果再过上几十年，几百年，有个人研究土摩托的生平，我写的这些会成为参考素材么？如果会，那将是太可怕的一件事情，因为那些虚构出来的故事，真假搀合在一起，会误导多少人呢？又假如，我很真实地去描述土摩托，所见即所得，他仍然可以被妖魔化，每个人的眼睛都是一个变形金刚，会把感知的事物转化成另一种东西，所谓想象与思考就是这个道理。在信息过剩的年代，人们的想象因为这些信息而扭曲，在信息匮乏的年代，人们的想象也因缺少证据而扭曲。总之，你无法真正去了解一个人，尤其是通过文字的时候。

土摩托也好，老六也好，究竟是什么人，像我这样经常与他们的打交道的人，都不知道。更何况你们？再仔细想想，人不都是这样吗，相互之间形成的社会关系，其实是很脆弱的，他人不仅是地狱，他人也是幻觉。

反正土摩托已经被我妖魔化了，早已把他的人格给格式化了，所以，我就继续，我尝试过，在我的笔下将他还原回来，但是，还原后的土摩托比妖魔化后的还要荒诞不经，所以就不刺激大家了。

今天跟土摩托等老男人吃饭，我说我这次要抖点土摩托的隐私，不然人们老以为我在编故事。以前我编过一个故事，说土摩托被朱伟发到非洲大草原上采访，同行的还有一个法国记者和瑞典记者，后来误闯入一个部落，被一群陈晓卿老师的同乡擒获，准备把他们烤着吃了。后来那个法国记者嚷嚷，被其中一个人听到了（这地方是原法属殖民地），于是酋长放弃了吃他们的念头，但是要出道题考他们，答不对再烤了他们。这个题目是，让这三个家伙脱掉裤子，然后比一比他们的小弟弟非勃起状态下的长度，如果加起来有48乌古（注：乌古是当地部落的计量单位，1乌古≈0.33厘米），就放了他们，否则吃掉。测量结果如下，法国人18乌古，瑞典人27乌古，土摩托3乌古，酋长一算，正好，就把他们给放了。逃出部落的路上，法国人说，要不是我这么长，咱们都得死。瑞典人一听就急了，没我你们都得死。土摩托一听也急了，妈的，刚才要不是我偷偷勃起，你们丫都得死。

这个故事是我根据民间故事改编的，知道分子不用以先知的姿态告诉我你以前听过看过。当我把这个故事转给土摩托看，土摩托在SMN上就跟我急了：你敢诬蔑我，说我小！我可以实话告诉你，勃起的时候有23厘米。哦，天哪，69乌古。就在那一瞬间，我突然找到了自己的理想，虽然在过去二十多年我一直在为理想这两个字苦苦追寻，但是土摩托一句话，让我豁然开朗，他简直是人中“翘杵”，从那一刻，我就发誓，我他妈将来一定要拍一个毛片，让土摩托做男主角，这样有天分的人，不做男主角，真是浪费了。

2002年，土摩托带着他那本《来自民间的叛逆》回到祖国，回国后的第一件事是从书稿里找出一大部分情节交给了国家安全部门，这些在我校对时准被删掉的内容其实都是美国的高科技秘密。然后，他就成了《三联生活周刊》的记者。

我来介绍一下土摩托这个人，土摩托原产中国，后随商船移植到美国，因土壤和气候比较适宜，故在美国生长的比较好，美国激素和生长素造就了这个移植后的东方人的下半身，要不然土摩托今天事事讲究科学，就是因为他是科学的受益者。他身材魁梧高大，兴趣广泛，热爱文艺，喜欢旅游、摄影，喜食转基因食品，无论从外观还是内心，都深受女士们的喜爱。缺点是他跟女性聊天时都喜欢问人家说话有什么科学依据，比如，女孩说：“我最近胖了，太难看了。”土摩托会问：“胖了就难看？有什么科学依据？”所以，女孩子在跟土摩托对话的时候一定要想好下一句，否则会把你折磨到安定医院去。当然，瑜不掩瑕，土摩托身上有很多光辉灿烂和“源远流长”的优点，这足以使任何一个女孩子为之动心。

当年土摩托回国的时候，我曾问过他，是否有心仪对象？他摇摇头说：“我先后跟圣多美和普林西比、捷克和斯洛伐克、特立尼达和多巴哥的女孩谈过恋爱，现在不想谈了。”我想也是，跟六个国家的女孩谈过恋爱，也不错了。但是，人到中年，也是需要照顾和安慰的，土老师整天云里来风里去的，总不能像孙悟空一样生活，土摩托他也是人啊，虽然我一直以为他是外星人，可外星人也是人啊。接下去的话我就不说了，点到为止。

土摩托的多才多艺，我说过很多了，其实他还有另一个鲜为人知的特长，就是他对体育非常了解。在美国，土摩托一直关注各种体育运动，这些运动在中国都不为人知，他打高尔夫球、冰球、棒球和曲棍球，从来不用球棍和球杆，凡是需要打到门里和洞里的运动他都相当擅长。还有橄榄球，所有跟球有关的项目他都了如指掌。最近，中央电视台开始转播美国职业联盟的橄榄球赛，土摩托看完后差点背过气去，因为解说员根本不了解橄榄球——当然，中央电视台从来都是让不了解的人当主持人。为此，我在这里发出一个倡议，热爱土摩托的人，你们希望他上电视解说橄榄球、棒球、篮球、冰球比赛吗？去给中央电视台写信，强烈要求土老师主持节目，这样，你以后打开电梯就能看到土摩托坐在那里了。这个我绝对不是虚构，在中国，一个同时能了解橄榄球、篮球、冰球、棒球、弹球的人，我只发现了土摩托。这样的人才，整天让他往荒无人烟的地方钻，实在是大材小用了。

最后，我再说说明年计划拍的毛片，今天吃饭，男女主角都来了，有照片为证。我在饭桌上给他们讲解了这部电影的重要性和艰苦性，不过他们都很有信心，作为中国毛片的先驱，他们深知担子很重，责任很大，并表示，一定要放下包袱，轻装上阵，一定要拍一部让大家都看得懂的国产电影。我也鼓励土摩托，将来，你就不叫土摩托了。他说，那我叫什么？我说，人们都会亲切地称你为“兔摩托”。

最近忙的是一塌糊涂啊,好长时间不更新了,上来踏一下

dbanotes posted a photo:

　　下班时听北京新闻台的“边走边聊”。节目里提到西安有一家10平米的小店，店主将店铺分割成120个小格子，之后分别租给40多名“下家”展示销售商品，这让我立刻想起了两年前Alex Tew红极一时的“百万美元格子”。晚上上网查了一下，这种概念源自日本、从去年开始流行于香港的“格仔铺”，已成为港人一种新的“创业途径”。今年内地陆续出现了一些跟风仿效的店铺，相关的网站也应运而生。在这家网站的论坛里，可以看到不少正在招商的“格仔铺”。北京西单文化广场地下二层开了一家格仔铺，每格的月租是140～280元。右图这家是昨天刚在广州开业的Life Box，据说已经签约入货的“格子”们基本都开单成交了，而生意最好的是那排扭蛋机。
　　这种小店看上去与“百万格子”网站确有相似之处，也八成会引发一阵模仿热潮，不过它的生命力难言持久。毕竟网络上的超链接具有充分的延展可能，而偏于一隅的实体小格子就没有太多的想像空间了。

Someone posted a comment on another blog entry.  It pointed me to a cartoon they thought I could relate to.  It was pretty funny :)

Last week, a friend of mine sent me a link.  He sent me a URL to a restaurant so we could meet there.  Thunderbird broke the URL in half for some reason and when I clicked on the link, I received an error page that contained in part this text:

 Element RESTAURANT is undefined in URL.
 
The error occurred in /........../something.cfm: line 7

5 : select SectionName, Words
6 : from restaurants
7 : where Restaurant='#url.Restaurant#'
8 : and Section='#url.Section#'
9 : 

Interesting I thought... I had some fun with that - discovered that restaurant was not only SQL injectable but HTML injectable as well (they used the text in SQL and in their generated HTML - you can do some interesting things with that)...

If you do not know what SQL Injection is - please read this.  And then get rid of string concatention in your code where ever you can!

Just to close on a sadly funny note - (warning, not entirely safe for work language is on this page) check this out

©作者：Fenng 发布在 dbanotes.net

我在 DBAnotes.net 上记录过不少比较大的网站架构分析(eg: eBay [1], eBay [2]) ，Amazon 一直找不到太多的资料。国庆期间读到了一篇关于 Amazon Dynamo 的论文，非常精彩。Amazon Dynamo 这个高可用、可扩展存储体系支撑了Amazon 不少核心服务.

先看一个示意图:

从上图可以看出，Amazon 的架构是完全的分布式，去中心化。存储层也做到了分布式。

Dynamo 概述

Dynamo 的可扩展性和可用性采用的都比较成熟的技术，数据分区并用改进的一致性哈希(consistent hashing)方式进行复制，利用数据对象的版本化实现一致性。复制时因为更新产生的一致性问题的维护采取类似 quorum 的机制以及去中心化的复制同步协议。 Dynamo 是完全去中心化的系统，人工管理工作很小。

强调一下 Dynamo 的"额外"特点：
1) 总是可写
2) 可以根据应用类型优化

关键词

Key: Key 唯一代表一个数据对象，对该数据对象的读写操通过 Key 来完成.
节点(node)：通常是一台自带硬盘的主机。每个节点有三个 Java 写的组件：请求协调器(request coordination)、成员与失败检测、本地持久引擎(local persistence engine)
实例(instance)；每个实例由一组节点组成，从应用的角度看，实例提供 IO 能力。一个实例上的节点可能位于不同的数据中心内, 这样一个数据中心出问题也不会导致数据丢失。

上面提到的本地持久引擎支持不同的存储引擎。Dynamo 上最主要的引擎是 Berkeley Database Transactional Data Store(存储处理数百K的对象更为适合），其他还有 BDB Java Edition、MySQL 以及 一致性内存 Cache 等等。

三个关键参数 (N,R,W)

第一个关键参数是 N，这个 N 指的是数据对象将被复制到 N 台主机上，N 在 Dynamo 实例级别配置，协调器将负责把数据复制到 N-1 个节点上。N 的典型值设置为 3.

复制中的一致性，采用类似于 Quorum 系统的一致性协议实现。这个协议有两个关键值：R 与 W。R 代表一次成功的读取操作中最小参与节点数量，W 代表一次成功的写操作中最小参与节点数量。R + W>N ，则会产生类似 quorum 的效果。该模型中的读(写)延迟由最慢的 R(W)复制决定，为得到比较小的延迟，R 和 W 有的时候的和又设置比 N 小。

(N,R,W) 的值典型设置为 (3, 2 ,2),兼顾性能与可用性。R 和 W 直接影响性能、扩展性、一致性，如果 W 设置 为 1，则一个实例中只要有一个节点可用，也不会影响写操作，如果 R 设置为 1 ，只要有一个节点可用，也不会影响读请求，R 和 W 值过小则影响一致性，过大也不好，这两个值要平衡。对于这套系统的典型的 SLA 要求 99.9% 的读写操作在 300ms 内完成。

--待续--

相关文章|Related Articles

• Digg 网站架构
• 《大型 Web2.0 站点构建技术初探 》一文系抄袭
• Fotolog.com 的技术信息拾零
• WordPress.com 的数据量

评论数量(0)|Add Comments

本文网址：http://www.dbanotes.net/techmemo/amazon_dynamo.html

车东 posted a photo:

需要设置防火墙：允许google updater

具体路径就是：
控制面板 ==> Windows 防火墙 ==> 例外 ==> 添加程序

然后找到：
C:\Program Files\Google\Common\Google Updater下的GoogleUpdaterService.exe

估计Google的离线RSS阅读器下载失败也是这个原因。

from 朱伟blog

短线长线

By 朱伟 on 杂碎

李鸿谷培养记者有一些办法，他在说到记者采访不能深入时，爱说短线与长线——你也找到了采访对象，但采访到的都是短线。找了不少采访对象，但采访无法递进，都在一个圆心周围徘徊，就都是线头，无法形成容量。

如何是长线？我以为是逻辑推断能力，也就是顺着一种关系，带着你自己的问号，能追问探究到多远。任何一种关系都有因果，因果成因果。能否顺因果探究，需要耐心，耐心是建立在沉潜的基础上，不沉潜其中难成钻研，不成钻研就难曲径通幽。

长线在文章中能构成气度，没有气度的文章就像地上随意留下的一汪水，太阳使劲一照，连水迹都不会留下。音乐评论家在评论一个好的作曲家时，经常说他有绵长的美丽的旋律线，这绵长的美丽往往是特别舒展的气息。如果你通过潜心通幽，一篇文章蜿蜒曲折，常常是一条长线绵延起伏一气呵成，令人回肠荡气、无法自制。那长度因是你追逐因果的不断深入，就在舒展中构成了丰盈的容量。短线是指不屑深入、不能深入，就像猴子，急促从一个树干腾挪到另一树干，短线交杂，采到果子都咬一口就扔掉，什么滋味都没得到，得到的只是无为的气喘吁吁。

我以为，不能在短线间窥见因果，以致不能将短线连成长线，乃境界问题，境界是时间熬出来的。境界不够，就看到各处、无数新鲜，看不到其中的九九归一。境界宽远，气度自然舒展；境界憋屈，思维翅膀展不开，呼吸自然就急促。赵汀阳好多年前有一本书叫《一个或无数问题》，说的就是这境界问题，一与无数的关系看到了，长线就是你观察这关系的一条路径。

Alan Tien

Alan Tien is the China GM for Geni. Prior to Geni, Alan was the China Country Product Manager for PayPal, an eBay company. During his 5 years at PayPal, he launched PayPal China and PayPal’s first set of APIs. He graduated from Stanford University with a BS in Electrical Engineering.

Alan Tien是Geni中国区总经理 。在此之前，曾任Paypal中国产品经理，在为PayPal工作的5年中，推出了贝宝和PayPal的第一套API，Alan毕业于斯坦福大学并获电子工程学士学位。

    In Pro*C or OCI, we can use an host array as a bind value to submit array operations, it will reduce the network round trips, user calls and execute count, it can greatly improve the performance. In Java there is a "executeBatch" interface, and Perl also support bulk operation when using latest Oracle DBD (version 1.19 or above) with "execute_array" interface.

    I will tell you how to perform bulk delete in PL/SQL now, just copy and modify it as your requests.

DECLARE
  TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tbrows ARRROWID;
  row    PLS_INTEGER;
  cursor delete_table is select row_id from testobj_rowid ORDER BY ROWID;
BEGIN
  open delete_table;
  loop
     fetch delete_table bulk collect into tbrows limit 250;
     FORALL row IN 1 .. tbrows.count()
       DELETE TEST.TESTOBJ WHERE rowid = tbrows(row);
     commit;
     -- dbms_lock.sleep(1); /* if delete too fast */
     exit when delete_table%notfound;
  end loop;
  close delete_table;
END;
/

    Now you should be able to write out bulk insert or bulk update code, it's easy.

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_bulk_delete.html

    Few days ago, I told one of my friends that the XMLTYPE column equals to CLOB type, that is not true, it's an object type. Later he ask me how to modify the cache property of XMLTYPE column, I had no idea of this data type, the first thing I did was describe the XMLTYPE object.

SQL> DESC XMLTYPE

METHOD
------
STATIC FUNCTION CREATEXML RETURNS XMLTYPE
Argument Name    Type    In/Out Default?
---------------- ------- ------ --------
XMLDATA          CLOB    IN
......

    I found that almost every member functions has a parameter called "XMLDATA", then I make suggestion to treat the "column.XMLDATA" as a CLOB object. Later I test it in a test database, found that my suggestion is absolutely correct.

SQL> create table t_xmltype (col1 xmltype);

Table created.

SQL> ALTER TABLE T_XMLTYPE MODIFY LOB (COL1.XMLDATA) (CACHE);

Table altered.

    Then I told he to read more books, which will enable people find out the final solution.

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_xmltype_columns.html

    Hash cluster table can get much performance improve for equal access, however proper SIZE and HASHKEYS option is required. In OLTP, we always have some tables, the query use equal condition and with unique or very effective index on it, but the execution frequency is really high, half of the total consistent gets is due to these simple queries.

    I will first analyze the table and get the current blocks with rows :

SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';

    BLOCKS
----------
       118

    For this table, data distribution is even enough, so I set the SIZE option to data block size, and set the HASHKEYS a value larger than the data blocks, in this case it's 144.

SQL> CREATE CLUSTER C_T_OBJECTS (OBJECT_ID NUMBER(38,0))
  2  SIZE 8192 SINGLE TABLE HASHKEYS 144;

Cluster created.

    Now create a hash cluster table CT_OBJECTS with the same structure as T_OBJECTS, and insert the rows from T_OBJECTS. I run the following PL/SQL to access all the rows in hash cluster table to detect the performance improve.

Read Full Text of【How to choose the SIZE and HASHKEYS of hash cluster table?】

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_hash_table_options.html

    I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now:

TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- ---------- ---------------
T_OBJECTS
T_OBJECTS_IX1

    Now I receive a task to create a second index on this table, I add the compute statistics option :

SQL> CREATE INDEX T_OBJECTS_IX2 ON T_OBJECTS
  2  ( OBJECT_NAME ) compute statistics;

Index created.

    Let's query the table's statistics data again :

TABLE_NAME       NUM_ROWS LAST_ANA
-------------- ---------- --------
T_OBJECTS             555 00:30:54
T_OBJECTS_IX1
T_OBJECTS_IX2         555 00:30:54

    Because of the table is analyzed, but another index is not analyzed, so the optimizer choose the wrong execution plan, and get the server in performance trouble. Later I test it on 10g version, the table will not be analyzed. So when you create index with compute statistics option in 9i, you should be really carefully, because table will be analyzed too, while another indexes are not analyzed, it may make the optimizer choose the wrong execution plan.

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/create_index_statistics.html

    When creating or rebuilding indexes on hot tables, we will use parallel option to make the it quickly, but please remember to disable the parallel after creating or rebuilding, else it will make the relative SQL run in parallel. On busy OLTP system this will make the oracle instance crash (we hit this before). But you may hit resource busy error when you are trying to disable the parallel option of the index. How to make sure the modification succeed? Please use the following PL/SQL scripts:

alter index ...... rebuild ... parallel ... ONLINE;

declare
  resource_busy exception;
  pragma exception_init (resource_busy,-54);
begin
loop
   begin
     execute immediate 'alter index ...... noparallel';
     exit;
   exception
    when resource_busy then
     dbms_lock.sleep(1);
   end;
end loop;
end;
/

    These scripts can also be used in other cases, for example adding column on hot tables. Or you can change the error code to process another kind of error, it's all up to you!

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_resource_busy.html

    The following connection format method is supported by 8i/9i/10g:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:40:22 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn anysql/anysql@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=TEST)))
Connected.
SQL>

    Yong Huang, one of my friends said that there is another format in 10g, then I think of it should be the same as new JDBC url format, the following format works on 10g, but does not work on 8i/9i:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 5 17:44:38 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn anysql/anysql@localhost:1521/TEST
Connected.
SQL>

    If you are in trouble with the tnsnames.ora configuration, try this format. OCI and Pro*C program can use this format also, it's implemented at Oracle network layer.

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_conn_notnsnames.html

    Online redefinition can be used to reduce the database down time in OLTP system. The first step is to check whether we can redefine it.

SQL>  exec dbms_redefinition.can_redef_table( USER, 'T_DEF1') ;

PL/SQL procedure successfully completed.

    Then we create a new table as target table with same structure as the source table, indexes can be created later, and grant the access to application schema. Now we start the data initialization.

SQL> exec dbms_redefinition.START_REDEF_TABLE( USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    Now you need to create the required indexes, and then schedule a job (run every 5 mins) to synchronize them.

SQL> exec dbms_redefinition.sync_interim_table( USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    During outage or after prime time, we can finish the online redefinition with less affection to the application.

SQL> exec dbms_redefinition.FINISH_REDEF_TABLE(USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    The base technology of online redefinition is Oracle's materialized view.

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_online_redefinition.html