ITPub博客

DBA经常需要用如下SQL来查看某个数据块数据哪个SEGMENT，但是在10g里这句SQL返回结果通常是空的。select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;[@more@]SOLUTION：SQL> alter session set "_optimizer_push_pred_cost_based"=false;Session alteredSQL> select * from dba_extents where file_id=30 and block_id<=1000 and block_id+blocks>=1000;OWNER SEGMENT_NAME

包括隐含参数[@more@]SQL> col KSPPINM for a40SQL> col KSPPSTVL for a40SQL> set lines 100SQL> set pages 200SQL> list 1 Select a.ksppinm, b.ksppstvl 2 from x$ksppi a, x$ksppsv b 3 where a.indx = b.indx 4 order by 1比如 _kghdsidx_countSQL> Select a.ksppinm, b.ksppstvlfrom x$ksppi a, x$ksppsv bwhere a.indx = b.indxand a.ksppinm = '_kghdsidx_count'; 2 3 4KSPPINM-------------------------------------------------------------------------KSPPSTVL-----------------------

Oracle instance异常终止的情况下，如crash, shutdown abort等， 有时候memory,semaphores等资源并不能及时释放，这会影响下一次数据库的启动。[@more@]参见http://zhyuh.itpub.net/resource/334/26709

alter session set timed_statistics=true /alter session set max_dump_file_size=unlimited /alter session set tracefile_identifier='Identifier'/alter session set events '10046 trace name context forever, level 12' /SQL/alter session set events '10046 trace name context off'/[@more@]see http://zhyuh.itpub.net/resource/334/26661

关于min(), max()函数访问索引的方法。[@more@]参见http://zhyuh.itpub.net/resource/334/26409

When a database has severe performance problems, the cause of the problem may be a HANG. Using trace files produced by HANGANALYZE, you will be able to quickly determine if two or more processes are deadlocked and how many other processes may be affected. By using HANGANALYZE, you may also determine if a performance analysis will be required to diagnose the real problem when a HANG is taking place.[@more@]Steps to generate HANGANALYZE trace files -- Metalink 175006.1Interpreting HANGANALYZE trac

下面的sql，利用v$session视图里的 PREV_SQL_ADDR， PREV_HASH_VALUE列，确定某个SID或者spid对应的完整SQL[@more@]select /*+ ORDERED */ sql_text from v$sqltext awhere (a.hash_value,a.ADDRESS) in( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)from v$session bwhere b.sid=&SID)order by piece asc或者以操作系统的process ID为参数。select /*+ ORDERED */ sql_text from v$sqltext awhere (a.hash_value,a.ADDRESS) in( select decode(sql_hash_val

很多shell脚本需要动态从终端读入用户名和密码。用户名可以在输入时显示在屏幕上，但是密码不可以。下面的脚本就是实现这个功能。[@more@]$ cat echo1#!/bin/sh echo "User:"read userstty -echo echo "Password:"read pswdstty echo echo "User is $user, Password is $pswd. Done!" 执行结果如下：$ ./echo1User:zhyuhPassword:User is zhyuh, Password is my_paswd. Done! 可以看到用户名zhyuh显示在屏幕上，而密码被盖掉了。最后一句echo命令用来显示输入的全部信息。

Shell脚本经常需要传入一些参数。下面的脚本可以作为一个通用模块，在写shell时用来1. 指定参数的类型2. 允许用户灵活指定参数顺序，或者省掉某几个参数3. 通过shift命令，将参数传给指定的变量 注意1. getopt a:b:c $*   $*和c之间有空格（必须）2. basename用处3. shift用法   [@more@]$ cat getopt1#!/bin/sh set -- `getopt a:b:c: $*` getopt_rc=$? if [ "$getopt_rc" -ne "0" ]; then  pgm=`basename $0`  echo "$pgm $getopt_rc Process failed during getopt attempt - illegal parame

关于单节点Windows系统中ASM试验的完整Word版本，请参见 http://zhyuh.itpub.net/resource/334/20793[@more@]

=============================用DB Control管理ASM Instance=============================[@more@]ASM Instance建起来后，DB Control并不会自动管理该实例。可以用下面步骤将ASM Instance加到DB Control中。C:$oracle$product$10.2.0$db_1$database>set oracle_sid=orcl C:$oracle$product$10.2.0$db_1$database>emctl config agent addtarget c:$addasm.txtOracle Enterprise Manager 10g Database Control Release 10.2.0.1.0Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved. 其中addasm.txt文件内容

===================在线删除一块磁盘===================[@more@]SQL> alter diskgroup dgroup1  2  drop disk dgroup1_0005; 磁盘组已变更。 随后察看各磁盘的空余空间，可以看到dgroup1_0005的空余空间逐渐变大，直至1000M（即整个磁盘C:ASMDISKSDISK6的大小），再将disk6状态mount变成closed；而其余几个磁盘的在差不多等量地变小。 SQL> select name,total_mb,free_mb from v$asm_disk; NAME              TOTAL_MB    FREE_M

=============================数据库关闭状态下增加一块磁盘=============================[@more@]C:asmdisks>asmtool -create c:asmdisksdisk6 1000 --asm instance中SQL> startupASM 实例已启动 Total System Global Area   79691776 bytesFixed Size                  1247396 bytesVariable Size              53278556 bytesASM Cache  &nb

========================二、将数据库转移到ASM上========================[@more@]我们用RMAN实现这一功能。在转移数据库以前，先设若干跟文件存储位置相关的参数。 C:￥oracle￥product￥10.2.0￥db_1￥database>set oracle_sid=orcl C:￥oracle￥product￥10.2.0￥db_1￥database>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 12月 28 10:38:52 2006 Copyright (c) 1982, 2005, Oracle.  All rights reserved. SQL> connect/ as sysdba已连接。SQL> show parameter db_create_file_des

ASM (Automatic Storage Management) 是Oracle 10g的一个重要新特性，在实现数据库I/O自动负载平衡，数据文件镜像等方面起着重要的作用。即使是单节点的数据库，Oracle也推荐使用ASM。但一般文档上提到ASM时，都需要有磁盘组或者多块磁盘才能实现，这使得很多人无法建立学习和实践的环境。下面的实验将带领你在windows xp的操作系统中建立ASM环境，并将已有的数据库转移到ASM中，同时该实验1. 不需要磁盘组或者多块磁盘2. 不需要在Windows xp中安装虚拟机，Linux等复杂的步骤[@more@]=====================一、建立ASM Instance==========

要写一个TRIGGER，把一张表的所有列，修改前后的值，都记录到另外一张修改历史表中。想从user_tab_columns里动态地获取列名，然后用循环自动获取每一列的修改前后值。于是写了一个trigger（仅以scott.emp表的sal列为例）[@more@]CREATE OR REPLACE TRIGGER Print_salary_changesBEFORE DELETE OR INSERT OR UPDATE ON EmpFOR EACH ROWWHEN (new.Empno > 0)DECLAREcursor cur_emp is select * from user_tab_columnswhere table_name='EMP';col_name varchar2(20);sal_new number;state1 varchar2(500);cursor_handle integer;execute_s

Windows XP上的Oracle10g，在DB control中，有些操作会要求用户进行Host Login(比如start listener, instance startup)。但是用户输入正确的user ID/password后，系统仍然提示错误E"RROR: Wrong password for user"，并且登入失败。[@more@]解决办法：1. Control Panel, Administrative Tools.2. Click on Local Security Policy.3. Local Policies, User Rights Assignment.4. Double click, 'Log on as a batch job'.5. Add your local user that you are supplying for the Host Credentials

一台PC上搭建Data Guard，并做Switch-over测试[@more@]具体过程参见http://zhyuh.itpub.net/resource/334/16864中的文档Fail-over测试尚未开始

我见过最好的数据仓库入门文章，忍不住花些时间翻译了一下[@more@]http://zhyuh.itpub.net/resource/334/15824 原文请见 http://www.ciobriefings.com/whitepapers/StarSchema.asp

The latest train list for Shanghai South Railway Station( revised on July. 1st, 2006 )[@more@]Train NumberDestinationDeparture timeGuangzhou 1312Nanjing xi00:01Fuzhou 2002Nanjing xi05:50Ganzhou 2006Suzhou 11:00Yiwu T756Taizhou 12:01T714Nanjing xi12:20Ningbo K256Baotou 12:32Huaihua 1608Wuxi 12:54Hangzhou 1342Qiqihaer17:57Ningbo K76Jilin 22:19Hangzhou T32Beijing 23:45Jilin K75Ningbo 02:11Beijing T31Hangzhou 05:33Qiqihaer 1341Hangzhou06:10N891Jiaxing06:20N521Hangzhou08:30K111Guiyang 08:40N559yiwu09