05 2014 档案
摘要：关于group by 排序问题10g 以前sort group by 需要排序10g 以后 hash group by 不需要排序SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> select owner,coun...
阅读全文
摘要：当往一个已经满了的索引块中插入新的索引条目时，将发生索引块的split，在9i下，分两种不同的情况进行split：（1）如果插入的索引键值不是最大的，将发生50-50的split，也就是说有block中一半的索引键值将被移到一个新的block中。就是说热点增长也始终在热点的右边,导致类似右手增长状况...
阅读全文
摘要：介绍Linux下的IO统计工具如iostat, nmon等大多数是只能统计到per设备的读写情况, 如果你想知道每个进程是如何使用IO的就比较麻烦.iotop 是一个用来监视磁盘 I/O 使用状况的 top 类工具。iotop 具有与 top 相似的 UI，其中包括 PID、用户、I/O、进程等相关...
阅读全文
摘要：多块读的操作：全表扫描,index fast full scan ,merge,rowid range scan单块读:index unique scan ,index full scan ,index range scan,逻辑读
阅读全文
摘要：SELECT * FROM F_PTY_INDIV_TMP O WHERE EXISTS (SELECT 1 FROM F_PTY_INDIV F WHERE O.PTY_ID = F.PTY_ID ...
阅读全文
摘要：在10g中当主查询的谓词信息,被错误的放入子查询中,会导致子查询无法展开explain plan for UPDATE DWF.F_PTY_INDIV O SET END_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD') WHERE EXISTS (SELECT ...
阅读全文
摘要：UPDATE Business_Duebill SET RelativeSerialNo1 = :1, RelativeSerialNo2 = :2, CustomerID = :3, BusinessType = :4, ...
阅读全文
摘要：一、B-Tree索引的分裂1. 创建测试表SQL> create table split_tab (id number, name varchar2(100));表已创建。SQL> alter table split_tab add constraint pk_split_tab primary k...
阅读全文
摘要：Reverse Key IndexesCreating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keep...
阅读全文
摘要：Reverse Key IndexesCreating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keep...
阅读全文
摘要：首先:oracle有自动Job,进行shrink spaceSQL> select client_name,status from dba_autotask_client;CLIENT_NAME STATUS--------------------------------------...
阅读全文
摘要：SQL> select department_namefrom hr.departments deptwhere department_id IN (select department_id from hr.employees emp); 2 3 DEPARTMENT_NAME-----...
阅读全文
摘要：SQL> select substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt from ( ...
阅读全文
摘要：FIELD DESCRIPTION FOR VM MODE Procs r: The number of processes waiting for run time.表示运行队列,就是说多少个进程真的分配到CPU b: The number of processes i...
阅读全文
摘要：sar -n DEV 1 10 -n { keyword [,...] | ALL } Report network statistics. IFACE Name of the network interface for which ...
阅读全文
摘要：我们先来看看 library cache: mutex X 。 是个什么东西 The library cache mutex is acquired for similar purposes that the library cache latches were acquired in pr...
阅读全文
摘要：SQL> conn cowork_czsh/cowork_czshConnected.SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> select workitemhi0_.PERFORMER as col_0_0...
阅读全文
摘要：This latch comes into play when user processes are attempting to access or update the cached data dictionary values.SolutionsProblem:To determine if t...
阅读全文
摘要：TRANSLATE(expr, from_string, to_string)from_string 与 to_string 以字符为单位，对应字符一一替换。SQL> SELECT TRANSLATE('ab你好abcdefg', 'abcdefg', '1234567') AS NEW_STR ...
阅读全文
摘要：转义字符:要求一：查出vname中包含字符串“CED”的行SQL> select * from v where vname like '%CED%';VNAME-------ABCEDF_BCEDF_\BCEDF要求二：查出vname中包含字符串“_BCE”的行SQL> SELECT * FROM ...
阅读全文
摘要：SQL> select 'xxxx'oooo' from dual;ERROR:ORA-01756: quoted string not properly terminatedSQL> select "xxxx'oooo" from dual;select "xxxx'oooo" from dual...
阅读全文
摘要：coalesce 函数 :Oracle COALESCE函数语法为COALESCE(表达式1,表达式2,...,表达式n)，n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。注意：所有表达式必须为同一类型或者能转换成同一类型。返回第一个不为空的CREATE OR REPLAC...
阅读全文
摘要：SQL> desc TEST_IDX Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(3...
阅读全文
摘要：运行top后,按P键就按CPU排序,按M键就按内存排序P – 以 CPU 占用率大小的顺序排列进程列表M – 以内存占用率大小的顺序排列进程列表在系统维护的过程中，随时可能有需要查看 CPU 使用率，并根据相应信息分析系统状况的需要。在 CentOS 中，可以通过 top 命令来查看 CPU 使用状...
阅读全文
摘要：SQL> select count(*) from test_idx; COUNT(*)---------- 19087751SQL> select segment_name,segment_type,bytes/1024/1024 as MB from user_segments wher...
阅读全文
摘要：[oracle@OAPRIMARY shell]$ cat expect.sh while read linedouser=`echo $line | awk '{print $1}'`ip=`echo $line |awk '{print $2}'`passwd=`echo $line | awk...
阅读全文
摘要：AAAAAAAAA(A)/app/cbsrun/sbin> cat reloadtuxconfig.exp puts "Start"set i 1set max_i [lindex $argv 0]set passwd [lindex $argv 1]spawn r...
阅读全文
摘要：[oracle@june ~]$ cat continue.sh for i in a b c d e f gdoif [ "$i" = "c" ]thenecho xxxxxxxxxxxxxxecho "跳过的字符为"$icontinuefidone[oracle@june ~]$ sh -x...
阅读全文
摘要：date=`echo $1 | tr -d '-'`date1=`echo $1`date_end=`get_date $2 +1 | sed 's/-//g'`while [ 1 ]dodateecho $date1 if [ ! -d "/home/dataun/ETL_init/DATA/...
阅读全文
摘要：create table page as select * from dba_objects; create index idx_page on page(object_id);create index idx_page_1 on page(owner,object_id);create index...
阅读全文
摘要：SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, ...
阅读全文
摘要：create table test1 as select * from dba_objects;create table test2 as select * from dba_objects;create unique index test1_idx1 on test1(object_id);sel...
阅读全文
摘要：今天在做mkdir操作时报错：Invalid file system control data detected。检查用户和权限没问题，再检查磁盘空间也没问题。最后在网上找到如下信息：【problem】# mkdir aaamkdir: 0653-358 Cannot create aaa.aaa:...
阅读全文
摘要：spawn rsync -avH --delete /home/dwetl/bin dwetl@10.128.8.151:/home/dwetl/binsending incremental file listbin/bin/ftpgj.logbin/ftpjx.logbin/perl_add_gm...
阅读全文
摘要：drop table test1;create table test1 as select * from dba_objects where rownum 'SCOTT', tabname => 'TEST1', ...
阅读全文
摘要：select * from test a where object_id in (select department_id from hr.dept_1 dept where department_id IN (select department_id from hr.employees_...
阅读全文
摘要：FILTER的适用范围:1. 主表返回的记录数较少2.子查询返回记录数较小下面做实验证明:select department_name from hr.dept_1 dept where department_id IN (select department_id from hr.employee...
阅读全文
摘要：包用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成,包规范用于定义公用的常量变量,过程和函数,在SQL*PLUS中建立包规范可以使用CREATE PACKAGE命令。实例如下:CREATE OR REPLACE PACKAGE emp_pkg IS PROCEDURE update_...
阅读全文