ITPub博客

http://docs.google.com/Doc?id=dgdk3pdk_35d99nrb[@more@]http://docs.google.com/Doc?id=dgdk3pdk_35d99nrb

数据库不能启动问题解决一例[@more@]开发人员说10.10.10.14的ldrk数据库(windows平台),在服务启来后，pl/sql不能连接。处理过程：1 登录到主机，查看alert.log文件,文件结尾没有报错的内容。2 进入CMD，设置SID，然后startup，报600错误； shutdown immediate 报没有实例内存 startup nomount 仍然报同样错误3 怀疑spfile有问题，使用建库时的pfile进行启动SQL> conn sys/oracle@LDRK as sysdbaConnected to an idle instance.SQL> startup pfile='D:oracleadminLDRKpfileinit.ora.5192006163241'ORACLE instance started.Total S

select .... from ... where lxr is null;this statement can't use the idnex!!! [@more@]grant query rewrite to scott;alter system set query_rewrite_enabled=true; --the default is falsealter system set query_rewrite_integrity=trusted; -- the default is enforcedcreate index idx_c_khxx_zb_lxr on c_khxx_zb(case when lxr is null then 1 end);-- alter the index created, gather the table's statsbegin dbms_stats.gather_table_stats(user,'C_KHXX_ZB',cascade=>true);end;/set autotrace traceonly explain (

各类对象有哪些可用权限?[@more@]privilegeobjectalterdeleteexecuteindexinsertreadreferenceselectupdateDirectoryyesFunctionyesProcedureyesPackageyesDB ObjectyesLibraryyesOperatoryesSequenceyesyesTableyesyesyesyesyesyesyesTypeyesViewyesyesyesyes

创建强制视图可更新视图视图相关的数据字典[@more@]--- 视图中的列数限制1000The maximum number of columns that can be defined in a view is 1000, justas for a table.--- 创建有错误的视图(force view)SQL> create force view test_view as 2 select c1,c2 from test_table;Warning: View created with compilation errors.SQL> desc test_viewERROR:ORA-24372: invalid object for describeSQL> drop view test_view;View dropped.SQL> desc test_viewERROR:ORA-04043: object test_view does not exist--- 带约束的视

过程的调用,同义词的有效性,降序列默认最大值[@more@]存储过程的执行方法：（包括函数）在sql*plus中使用EXEC 过程名SQL> exec dbms_stats.gather_table_stats(ownname=>'SG',tabname=>'EMPLOYEES',cascade=>true,degree=>4);函数直接调用Functions are called anywhere an expression is valid:==》 In an assignment:order_volume := open_orders(SYSDATE, 30);==》 In a Boolean expression:IF (open_orders(SYSDATE, 30) < 500 )THEN …==》 In a default value assignment:DECLAREorder_volume NUMBERDEFAULT open

sys与system的区别对象权限与系统权限角色权限的区别与权限相关的数据字典[@more@]一句话知识：1 sysdba 比 sysoper 多的权限是建库和更改字符集对授权后的处理grant object privileges 之后用户的删除造成的影响：情景一：A授权给B with grant optionB授权给C if drop user B then C lost privilege on A.tab情景二：A授权给B和D with grant optionB授权给C D也授权给Cif drop user B then C lost one of two privileges on A.tabC can select a.tab through D's grantgrant system and role privilege之后与上面的差异如下：if Oliver g

工作中需要sql server与ORACLE互联,考虑使用透明网关[@more@]http://docs.google.com/Doc?id=dgdk3pdk_19xzkrzf

http://docs.google.com/Doc?id=dgdk3pdk_12fjqbsc[@more@]

RedHat LINUX 9下安装配置OMS笔记参考http://www.puschitz.com/InstallingOracle9i.shtml#PatchingOracleIntelligentAgentOnRHAS3[@more@]1 以root用户 xhost +2 以oracle用户 export DISPLAY=127.0.0.13 为了防止dbca时乱码 unset LANG 原先设置的是zh_CN.GB180304 emca 一路默认即可5 下载补丁，解决不能启动agentctl的问题，详细见后。打补丁前要关闭实例6 启动OEMREP库、启动OMS服务 、启动agentct、启动OEM控制台startup OEMREPnohup oemctl start oms &agentctl startoemapp consoleoemrep的初始口令sysman/oem_temp7 添加节点

窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。http://docs.google.com/Doc?id=dgdk3pdk_16f26gbv [@more@]http://docs.google.com/Doc?id=dgdk3pdk_16f26gbv http://docs.google.com/Doc?id=dgdk3pdk_9frqv3f

下面这个例子说明了CUME-DIST（）与PERCENT-RANK（）的使用，它得到的是销量的累积分布和百分比排名：[@more@]SQL>select prd_type_id,sum(amount), cume_dist() over(order by sum(amount) desc) as cume_dist, percent_rank() over(order by sum(amount) desc) as percent_rank from all_sales where year=2003 group by prd_type_id PRD_TYPE_IDSUM(AMOUNT)CUME_DISTPERCENT_RANK5null.14285714304699054.57.285714286.1666666671349535.92.428571429.3333333337349529.76.571428571.56349529.66.714285714.66666666

NTILE（）函数把记录结果集分成N部分[@more@]这个函数的参数为NTILE（buckets），这个bucket参数指定了分片的片数，下面我们看例子来说明SQL> SELECT 2 prd_type_id,SUM(amount), 3 NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile 4 FROM all_sales 5 WHERE year=2003 6 AND amount IS NOT NULL 7 GROUP BY prd_type_id 8 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) NTILE----------- ----------- ---------- 1 905081.84 1 2 186381.22 2 3 4782

PERCENTILE-DISC（X）函数[@more@]PERCENTILE-DISC（X）函数与CUME-DIST相反，它在每一个分组中检查累积分布的数值，直到找到大于或等于X的值。PERCENTILE-CONT（X）函数与PERCENT-RANK（）相反，在每一个分组中检查百分比排名的值，直到找到大于或等于X的值。下面我们来看个例子获取百分点大于等于0.6的销售总量：SQL> SELECT 2 PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_cont, 3 PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_disc 4 FROM all_sales

ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number[@more@]SQL> SELECT 2 prd_type_id,SUM(amount), 3 ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number 4 FROM all_sales 5 WHERE year=2003 6 GROUP BY prd_type_id 7 ORDER BY prd_type_id;PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER----------- ----------- ---------- 1 905081.84 2 2 186381.22 5 3 478270.91 3 4 402751.16 4 5

ANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位[@more@]RANK函数小结RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位源数据，建表脚本见附件:SQL> select prd_type_id,sum(amount) from all_sales where year=2003 group by prd_type_id; PRD_TYPE_ID SUM(AMOUNT)----------- ----------- 1 349535.92

学习分析函数时所用的建表脚本,初始数据我是手工输进去的[@more@]prompt PL/SQL Developer import fileprompt Created on 2007年4月23日 by newset feedback offset define offprompt Creating ALL_SALES...create table ALL_SALES( YEAR NUMBER(38) not null, MONTH NUMBER(2) not null, PRD_TYPE_ID NUMBER not null, EMP_ID NUMBER not null, AMOUNT NUMBER(8,2));prompt Loading ALL_SALES...insert into ALL_SALES values (2003, 1, 2, 21, 10034.84);insert into ALL_SALES values (2003, 2, 2, 21, 15

处理一个查询语句的三个阶段以及相关后台进程的简单介绍[@more@]sql之28 sql的执行 处理一个查询语句的三个阶段第一阶段：Parse: Search for identical statement in the shared pool Check syntax, object names, and privileges Lock objects used during parse Create and store execution plan第二阶段：Execute: Identify rows selected第三阶段：Fetch: Return rows to user process 对shared pool的理解The shared pool has two primary components：Library cache and Data dictionary cache在shared pool中主要有两种区域：一

Writing Advanced Scripts时需要注意的细节[@more@]1 在使用SQL输出SQL之前，需要注意的问题是以下设置SQL> set echo offSQL> set feedback offSQL> set pagesize 0SQL> select 'drop table '||object_name|| ';'……..否则会出现headfeedback等不需要的内容等价的操作SQL> set echo off feedback off heading off 2 关于默认的输出路径如果是从CMD命令行启动的SQLPLUS，则输出在启动时所在目录；如果是使用sqlplus快捷方式启动的sqlplusw ，则输出在D:oracleora92bin;也与启动命令时所在目录一致。 Char(10) 换行符 3 定义

行编辑命令及文件操作命令,在没有PLSQL或TOAD图形工具的情况下,就靠它混了[@more@]行编辑命令Command DescriptionA[PPEND] text Adds text to the end of the current lineC[HANGE] / old / new Changes old text to new in the current lineC[HANGE] / text / Deletes text from the current lineCL[EAR] BUFF[ER] Deletes all lines from the SQL bufferDEL Deletes current lineDEL n Deletes line nDEL m n Deletes lines m to n inclusiveI[NPUT] Inserts an indefinite number of linesI[NPUT] text Inserts a line consisting of t