AnySQL.net

如何防止大的DML语句?

    开发人员在开发库上更新某个表的记录时总忘了添加限制(Where)条件, 而错误地更新了数据, 几个小时或几天后来求助DBA, 给DBA制造了不少麻烦, 有没有办法限制他们一句话能更新的记录的条数呢? 下面的方法可以用来限制一条语句能更新的记录数, 首先来创建一个包. create or replace package temppkg as     rowcount number := 0; end; /     创建一个语句级(执行前)的触发器, 初始化包中的变量. create or replace trigger trg_before_update_NOTEINFO before update on NOTEINFO begin    temppkg.rowcount := 0; end;...

Posted on March 13, 2008 2:06 PM

ocidiff助力51job日常维护

    比较两个环境, 比如测试A和测试B, 或测试环境和正式环境之间的表结构差异, 是件很麻烦的事, 如果有成千上百的表, 可能会相当地耗时, 花个一两天也是有的. 正是因为不想花这么长的时间去做麻烦的事, 就偷懒地写了ocidiff这个表结构比较和自动同步程序.     一位51job的DBA朋友正好面临同样的问题, 我就推荐了这个程序给他. 早上正式使用后, 回馈说程序运行相当的快速和准确, 几分钟内完成手工几个小时才能完成的事, 并给出可读性很好很清晰的比较报告, 因此决定作为正式工具在公司内部使用, 以大大的减少了DBA在这类琐事上无谓的时间消耗. 每年年初他们网站的流都要上升个几倍, 因此需要花更多的时间面对负荷的上升, 就不能花太多的时间在手工比较表结构这种事上面. 这个角本再加上良好的SQL编写习惯, 可以防止某些低级错误.     这个工具是命令行的OCI程序, 做成命令行是为了更适合在OS上进行作业调度, 这一点虽不为大众所喜欢, 但却是很实用的. 这是一个三年前开发的工具, 已经成熟稳重了, 并且和人工处理相比, 它的结果更加准确. ocidiff是点对点的比较, 另一个工具(Compare Any Column)则可以进行多点之间的比较,...

Posted on March 4, 2008 10:54 AM

先报身高? 还是体重?

    "我身高1.76, 体重85kg"与"我体重85kg, 身高1.76"有什么不同? 在关系数据库理论中, 确明地写着实体属性的顺序并不重要, 因此在我的DBDiff程序(表结构比较)中是不比较列的顺序的. 如果说硬要说列的顺序有关系, 那只能是说明应用程序代码习惯不够好. INSERT INTO tablename VALUES (...)     只有Insert语句不带字段列表的情况下, 才与列的顺序有关系, 但这不是一个好的习惯. 比方在多表关联时, 选择列时不加表名就不是个好习惯. 去年参加年会时, 淘宝的朋友曾提及以前就遇到过这样的问题, 在一个不太重要的系统中, 关联的两个表一开始所有字段名都不同, 后来有一天在一个表上加了和另一个表中某列同名的列, 这时应用出问题了. SELECT A.COL1, B.COL2, ... FROM A, B WHERE ...     很多都是简单的事,...

Posted on March 3, 2008 9:41 PM

Rename Table的语法

    谁都知道在SQL*Plus中重命名(Rename)一个表的命令, 如下所示: RENAME old_name TO new_name     但需要注意的是这个命令是SQL*Plus的命令, 如果将它放在你自已的程序(如:Java, Perl等)中, 则是会报错的. 这时应当用标准的重命名语法: ALTER TABLE old_name RENAME TO new_name     重命名索引时, 就必须用标准的数据库命令了. ALTER INDEX old_name RENAME TO new_name     有些SQL*Plus的命令, 在前面加上ALTER之类的就成了标准的数据库命令了, 如: RECOVER STANDBY DATABASE UNTIL...

Posted on February 14, 2008 2:21 PM

Get DDL工具

    在DBMS_METADATA之前, 要获得建表语法, 估计很多人都要用Toad或PL/SQL Developer之类的程序了, 不过很多数据库运行在Unix或Linux上, 并不允许我们用这类工具去连接, 这时还真是一件难事. 就算有了DBMS_METADATA我也并不觉得它很方便, 因此不如自已写一个角本, 在我的DBDiff程序中, 已经通过exp/imp的办法实现了, 将相关部分功能独立出来, 就成了getddl工具(Windows版本).     程序经以过以下步骤来实现这个功能: 1, rows=n进行导出; 2, 用show=y加上indexfile选项生成内容; 3, 处理生成的indexfile并打印出DDL语句. 因此这个小工具需要Oracle的客户端, 并且客户端的版本不能高于服务器端. Usage: getddl table=... [user=...] (c) Copyright Lou Fangxin, 2008, all rights reserved.    ...

Posted on December 24, 2007 3:40 PM

删除4TB的表

    由于历史的原因, 居然有机会清理4TeraBytes大小的表, 而且这个表是在字典管理表空间(DMT)上的, 新建的都不会再用字典管理表空间了, 估计在其他地方是不可能遇到的了. 用最吐的CTAS方法将数据归档了一部份, 并重新设计分区的逻辑, 完成后就得将这个表删除以释放空间.     心中很是担心害怕, 不过也没有办法, 只能继续. 大致经过了如下步骤: drop index ...; truncate table ... reuse storage; alter table ... deallocate unused keep 2000000m; alter table ... deallocate unused keep 1500000m; alter table...

Posted on December 11, 2007 4:57 PM

不同数据库的Temp Table的情况

    在Oracle中, Temp Table的结构建好后, 每个会话都可以看到这个表空构, 但看不到数据. 按数据还可以分为是事务范围(On Commit Delete Rows)的还是会话范围(On Commit Preserve Rows)的. 表结构一致有好处有坏处, 好处是每个会话不需要运行建表语句(其他数据库则不然), 坏处是当这个Temp Table被其他会话使用时, 对它做些DDL时会报错.     在MySQL 5中也有专门的Temp表, 每个会话可以创建结构不同的同名临时表, 当会话结束时, 这个临时表会被自动删除. 并且, 临时表可以和普通的表同名, 并且有优先权, 这点和Oracle不一样了, 显得Name Space没有管理好. 数据没有事务级别和会话级别之分(可能没有看清楚文档).     所接触的Sybase和SQL Server还是很久以前的老版本了, 不知道现在如何了. 只能使用"Insert ......

Posted on September 14, 2007 11:54 PM

如何获得X$表上的特殊索引信息?

    充分利用X$表上的特殊索引, 可以加快对性能视图的访问速度, 从而编译高效的性能监控程序. Oracle提供了一个视图可以用来查询在X$表的那些列上有这样的索引存在, 如下所示: SQL> DESC V$INDEXED_FIXED_COLUMN Name                 Null?    Type -------------------- -------- ------------- TABLE_NAME                    VARCHAR2(30) INDEX_NUMBER                  NUMBER COLUMN_NAME                   VARCHAR2(30) COLUMN_POSITION               NUMBER     有时我感觉查询V$SYSSTAT中的记录都有些慢, 看看它上面有没有索引进技术, 先用AUTOTRACE看一下这个视图的X$表是那一个. SQL> SELECT * FROM V$SYSSTAT; ------------------------------------------------------- | Id  | Operation        | Name       | Rows  | Bytes |...

Posted on August 21, 2007 11:33 PM

USE_CONCAT对X$表有时并不工作

    上一次发现访问X$KSLEI时, 用这个Hint很有效, 后来在更多的机器上测试, 发现有的数据库上这个Hint并不工作, 如下所示: SQL> SELECT /*+ USE_CONCAT */ * FROM X$KSLEI WHERE INDX IN (1,2); ---------------------------------------------------- | Id  | Operation        | Name    | Rows  | Bytes | ---------------------------------------------------- |   0 | SELECT STATEMENT |         |    20 |  1680 | |*  1 |  FIXED TABLE...

Posted on August 12, 2007 12:20 PM

如何以较好的性能访问Oracle内核表?

    Oracle通常在一些内核表的INDX列有特殊的索引, 如果能用到这些索引, 则访问很快, 否则在会话数很多的数据库上很慢. 最简单的访问就是用等于去访问. SQL> SELECT INDX, KSLESWTS,KSLESTIM FROM X$KSLEI WHERE INDX=1;       INDX   KSLESWTS   KSLESTIM ---------- ---------- ----------          1          0          0 Elapsed: 00:00:00.01     换成IN看看? 有点慢, 因为这时不能使用索引. SQL> SELECT INDX, KSLESWTS,KSLESTIM FROM X$KSLEI WHERE INDX IN (100,200);...

Posted on August 2, 2007 1:39 PM

问题解答 -- 如何得知一张表的纪录有变化?

    网上有一个问题, 要想知道那些表记录发生了变化? 对于一些更新不是很多(一秒种有几百条上下的)的表, 我会建议用实体化视图日志(Materialize View Log/Snapshot Log)去实现. 其基本原理是能过一个内部(Internal)的内核(Kernal)一级的行级触发器(Row Level Trigger), 将变化过的记录的ROWID或主键值保存到另一个表中去. 实体化视图的增量刷新(Fast Refresh)正是基于这个技术.     假设我们需要监控的表名为T_OBJECTS, 主键为OBJECT_ID, 如果没有主键则用ROWID, 则可以用如下语句来创建实体化视图日志. -- Using Primary Key CREATE MATERIALIZED VIEW LOG ON T_OBJECTS   TABLESPACE users WITH PRIMARY KEY, SEQUENCE; -- Using ROWID...

Posted on July 17, 2007 11:22 PM

AUL功能改进, 生成建表的SQL文件

    在有SYSTEM的情况下, 生成DMP格式时, 会自动包含一个建表的语句, 而在导出成文本文件时, 则没有生成建表语句. 从以往的经历来看, 文本方式的导出更稳定一些, 另外DBA找不表建表语句的情况也常发生, 出于这两点, 我对AUL作了一点改进, 在以文本方式导出时, 自动生成一个建表的SQL文件.     在AUL中早就可以看表结构了: AUL> desc anysql.emp Storage(OBJ#=10560 OBJD=10560 TS=4 FILE=4 BLOCK=627 CLUSTER=0) No. SEQ INT Column Name                   Type --- --- --- ----------------------------- ----------------   1   1   1...

Posted on May 31, 2007 10:55 PM

Organization Index之后是Organization Cluster

    最近常研究和分析Hash Cluster表, 发现要选建一个Cluster, 再在上面建表, 过程麻烦, 明明只有一个表, 却要分成两步, 或三步走. 在电视广告上什么都说一片能顶三片或五片的时代下, Oracle也应当进步一下. 我总是只在一个Cluster上建一个表, 称为Single Table Cluster吧.     IOT是很好, 可是他的主要缺点是必需要有主键, 另外由于没有物理的ROWID, 因此在建第二个索引时, 从效率和维护代价上来看有些高. 而Index Cluster则不需要一定是主键, Hash Cluster则根本就不需要Index, 都是很好的东西.     其实出一个Organization Cluster(COT)不是很好吗? Organization Cluster (column, ...) SIZE ... Organization Cluster...

Posted on April 28, 2007 10:48 PM

对Hash Cluster表的一些进一步测试之二

    今天早上用真实的数据进行了第二次测试, 一是验证我这种定义SIZE和HASHKEYS参数的方法有没有问题, 另外一个是难证Oracle自带的Hash函数是否高效. 为此我选取了1000万条真实的记录, 表的大小大约是1.5个G, 我将Cluster的HASHKEYS * SIZE设成2G左右, 语句如下: CREATE CLUSTER C_USER_HOST_ID_LOOKUP (USER_ID NUMBER(38,0))     SIZE 8192 SINGLE TABLE HASHKEYS 262144;     用sqlldr装载这些数据却用了1个小时, 这是因为你不能用Direct方式, 而且每条记录是随机存放的, 而不是连续存放的, 所以比较慢. 装载完成后, 用如下脚本进行验证. 其中的SELECT语句可是要执行大约1000万次的. DECLARE    TEMP VARCHAR2(64); BEGIN    FOR REC...

Posted on April 28, 2007 1:18 PM

对Hash Cluster表的一些进一步测试之一

    在前面的例子中, 我们都只是做了一些小批量数据, 如果我的Hash Cluster表有几个GB或几十个GB的大小时, 又怎么样呢? 下面进行的是我在笔记本上的测试, 用于测试的表的大小是100MB, 进行测试时的数据是50万条. 测试的目的是为了要证明Hash Cluster是否可能用于大数据量的表中, 即验证Oracle内建的Hash函数是否是高效的. 创建Hash Cluster的句语是: CREATE CLUSTER C_T_OBJECTS (OBJECT_ID NUMBER(38,0)) SIZE 8192 SINGLE TABLE HASHKEYS 12800;         在100MB的DB_CACHE_SIZE下进行测试: 21:48:10 SQL> SELECT HASH_VALUE, BUFFER_GETS, EXECUTIONS 21:48:54   2  FROM V$SQL WHERE HASH_VALUE=3523526785;...

Posted on April 27, 2007 11:19 PM

如何创建Single Hash Cluster的表?

    Hash Cluster的表可以在没有索引的情况下, 获得对表的极快访问, 这种访问的逻辑读比维一性索引还有效. 在这儿有一张表T_OBJECTS, 其中的OBJECT_ID是主键, 大量的SQL语句都是根据OBJECT_ID去访问其他字段, 通过索引的情况下每次执行的逻辑读已经只有3了(Index Root->Leaf->Table), 但是就是这样一句简单的SQL的逻辑读占据了大半. 因此考虑到使用Single Hash Cluster表来进行调优. 应当如何来创建这个Cluster呢? 决定Cluster性能的主要有两个因素: SIZE和HASHKEYS.     下面是我的思路, 首先分析一下现在的表, 获得比较准确的块数. SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';     BLOCKS ----------        118     因为在这个情况下, 数据分布均匀, 因此我只要将HASHKEYS定义为块数就已经比较好了, 实际上为了更安全,...

Posted on April 24, 2007 10:36 PM

如何定义Oracle外部表中的DATE类型字段?

    Oracle的外部表是一个很不错的功能, 可以轻松地将一个格式化的文本文件虚拟成数据库的表, 并可以用SELECT语句去访问, 正在被越来越多的人使用. 早上有人问我如何在外部表中定义一个日期类型的列, 我问他知不知道SQLLDR如何装载日期字段, 他说是知道的, 可他就是没有将这个日期在外部表上搞出来. 还是我做一个演示吧!     先建一个目录对象: SQL> conn / as sysdba Connected. SQL> create directory anysql as 'c:\anysql'; Directory created. SQL> grant read on directory anysql to anysql; Grant succeeded.    ...

Posted on March 1, 2007 4:21 PM