![](https://img-blog.csdnimg.cn/20201014180756757.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
Oracle数据库学习
文章平均质量分 69
yuast1234
这个作者很懒,什么都没留下…
展开
-
oracle中;和 / 的区别
在sqlplus中, oracle 用/ 或者 ; 作为sql语句的终结符。 注意一个符号就可以终结! 而在plsql中, 终结符就只有/,因此如begin dbms_output.put_line(Hello!);end; 这段代码不会运行。 而正确的写法是begin dbms_output.put_line(Hello!);end;/ 之原创 2009-08-11 15:42:00 · 1527 阅读 · 0 评论 -
PL/SQL Best Practice----On BULK COLLECT
On BULK COLLECT By Steven Feuerstein Oracle ACE Best practices for knowing your LIMIT and kicking %NOTFOUND I have started using BULK COLLECT whenever I need to fetch large volumes of data. Thi转载 2010-05-11 14:34:00 · 576 阅读 · 0 评论 -
Integrating Oracle database applications with WebSphere MQ applications
This article shows you how to integrate an Oracle database with IBM® WebSphere® MQ applications, using Oracle Advanced Queuing (AQ), which is the messaging engine provided with the Oracle database.转载 2010-05-13 17:21:00 · 813 阅读 · 0 评论 -
Advanced Queue—Local
Preparition: conn / as sysdba create role my_aq_adm_role ; grant connect,resource, aq_administrator to my_aq_adm_role; create role my_aq_user_role; grant connect, resource,aq_user_role to原创 2010-05-17 19:15:00 · 544 阅读 · 0 评论 -
Advanced Queue Remote
Diagram From the diagram, we can see two following point:queue1 and queue2 should enable both enqueue and dequeue operation.when dequeue from queue2@db2, consumer name should be same as s原创 2010-05-18 14:59:00 · 481 阅读 · 0 评论 -
Connect By –From Lily examples
Sample1:with temp as ( select Z001 userno, Jeff username,X059 usermgrno,1 useractive from dual union select X059 userno, Kevin username,X043 usermgrno,1 useractive from dual原创 2010-05-24 14:32:00 · 408 阅读 · 0 评论 -
常用到的query
<br />how to have one session id in oracle:<br />SQL> select sys_context('USERENV','sid') from dual; <br />HOw to have the lock of this session<br />select s.osuser,l.id1,l.id2,l.lmode,l.request,l.block,l.type <br />from v$session s, v$lock l <原创 2010-11-04 16:32:00 · 877 阅读 · 0 评论 -
Oracle读书列表
<br />--读过的<br />oracle expert one on one<br /> <br />--在读的<br />troubleshooting oracle performance <br /> <br />--将要读的<br />Optimizing oracle performance原创 2010-11-22 21:43:00 · 454 阅读 · 0 评论 -
Oracle中有关Latch的介绍
<br /><br />Oracle中有关Latch的介绍<br />作者: 理智交错, 出处:blog, 责任编辑: 王晓晨, <br />2008-08-15 08:00<br /> Latch是Oracle提供的轻量级锁资源,用于快速,短时间的锁定资源,防止多个并发进程同时修改访问某个共享资源,他只工作在内存中,我们可以不大准确的说,内存中资源的锁叫latch,数据库对象(表,索引等)的锁叫Lock。<br />");<br /> <br /> 本文向各位阐述Oracle的Latch机制,Lat转载 2011-05-16 17:42:00 · 488 阅读 · 0 评论 -
physical rowid and logical rowid
<br />physical rowid represent one row in heap table(other table except IOT).<br />IOT(index organized table) use physical rowid to represet one row to fulfill the same usage. this rowid was calcuated by oracle by using some hash fucnion based on primary k原创 2011-05-21 13:59:00 · 436 阅读 · 0 评论 -
flashback机制
<br />flash query,flash table自然用的是undo。<br />flashback database却是和Undo毫无关系。<br />flashback database的机制是:<br />oracle将所有改变块的before image保留在flashback log里。<br />当flahback database to scn的时候<br />oracle会检查flashback log里的块的信息,找到那些最接近这个scn号的块,然后用这个块替换实际数据文件的块,再原创 2011-05-30 16:32:00 · 451 阅读 · 0 评论 -
How to check invalid objects and broken job in multiple database
<br />Recently i have to do release support. I need to check the invalid objects and broken jobs in nine environment, which is very tedious and time consuming. Thus, i wrote a shell script to do such thing. <br /> <br />#!/bin/bash<br /><br /><br />#Env原创 2011-04-18 19:51:00 · 577 阅读 · 0 评论 -
ORACLE MODEL子句学习笔记
ORACLE 10G中新增的MODEL子句可以用来进行行间计算。MODEL子句允许像访问数组中元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。 1、MODEL子句示例下面这个查询获取2003年内由员工#21完成的产品类型为#1和#2的销量,并根据2003年的销售数据预测出2004年1月、2月、3月的销量。 select prd_type_id,转载 2011-06-20 11:24:00 · 461 阅读 · 0 评论 -
用sys_context 和Native Dynamic SQL实现变量绑定
假如要实现以下的一个Procedure, 这个procedure动态生成一个where clause,然后运行合成后的查询而获得一些查询信息。create or replace procedure do_query(cname_in dbms_sql.varchar2_table, operator_in d原创 2010-05-10 18:26:00 · 612 阅读 · 0 评论 -
不用select * 的原因收集
1.减少parse开销2.防止mess data when using invoker right.原创 2010-05-07 16:42:00 · 1533 阅读 · 0 评论 -
运用dbms_lock和autonomous_transaction实现insert contention.
当两个或多用户向同一个表中出入一样的数据时(比如有相同的primary key), 第二个session的insert 会hang住。如果第一个操作不commit或者rollback, 第二个session的insert会一直hang下去。 因此在实际应用中, 这个特性不能提供很好的用户交互。 dbms_lock可以弥补这一缺憾。 dbms_lock给用户提供了这样一个机制。 它允许用户自己定原创 2010-04-28 19:50:00 · 436 阅读 · 0 评论 -
between..and-->open bound or close bound?
WITH temp AS(SELECT LEVEL lv FROM dual CONNECT BY LEVEL)SELECT * FROM temp WHERE lv BETWEEN 20 AND 30;--BETWEEN..and =[]原创 2009-08-12 17:40:00 · 467 阅读 · 0 评论 -
SQLPLUS用法【一】:define和variable用法的比较
define usage:define定义的是用户变量。define X=32pass a char type whose value is 32 to the variable X.Use &X to implement in pl/sql block;define xdisplay the value of user variable x. eg. DEFINE X原创 2009-08-21 11:36:00 · 1784 阅读 · 0 评论 -
recompiling invalid objects
1) $ORACLE_HOME/rdbms/admin/utlrp.sql2) EXEC DBMS_UTILITY.COMPILE_SCHEMA(my_schema);转载 2009-08-30 13:00:00 · 463 阅读 · 0 评论 -
oracle concurrency -- serializable and read only
oracle中最严格的控制并发的两个特性: Set transaction read only: session1session2set transaction read only name rxyu; select * from emp;update emp set mgr_id=100 where emp_id=100;co原创 2009-08-31 14:15:00 · 1081 阅读 · 0 评论 -
ENDPOINT_NUMBER column in histograms
-- Thanks for the question regarding "How to interpret strange values in ENDPOINT_NUMBER column in histograms", version 9.2.0Submitted on 6-Aug-2008 0:29 Central time zoneToms latest followup |转载 2009-10-14 12:34:00 · 624 阅读 · 0 评论 -
Page in each database
Oracle:select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE -- including the order by ) a where rownum where rnum >= MIN_ROWS/ SQL Server:select * fro原创 2009-12-14 15:33:00 · 339 阅读 · 0 评论 -
Point for Binding In-list
1. invoker right: 需要给调用者显式的权限。 (authid current_user)definer right: procedure运行使用创建者的权限。 特别对dynamic sql, 使用definer right是很危险的。 有可能调用者可以drop table. 一般使用invoker right. 2. ORA-02030: can only s原创 2010-03-11 15:33:00 · 444 阅读 · 0 评论 -
10g New Feature--Collect function
10g New Feature--Collect function Collect Functio can use in sql query to convert multiple rows of data to a collection of data. Oracle create a SYS owned type for it. -->tested it in Wal原创 2010-03-23 13:48:00 · 660 阅读 · 0 评论 -
Study to use Materialized View--笔记一
Study to Use Materialized ViewHere today i am here to study Materialize View. As the information explosion, many GB,TB data is stored in Database, techniquely in tables. For these big talbe, it原创 2010-03-26 16:44:00 · 568 阅读 · 0 评论 -
SQLPLUS的常用用法[三]
ColCOL col_name CLEARclear the column format and set it to the default settingCOL col_name FOR[MAT] formatformat column, tipical usage have:COL col_name FORMAT A99 --> VA原创 2010-04-13 16:46:00 · 555 阅读 · 0 评论 -
Stream Advanced Queue--学习笔记一
ContentIntroductionPart I: PL/SQL and Oracles Native AQ for JavaThe Point-to-Point ModelQueue CreationUsing a Queue with PL/SQL in a Point-to-Point ModelIntroducing the Java Sampl转载 2010-04-01 19:30:00 · 1395 阅读 · 0 评论 -
Study to use Materialized View--笔记二
MV的刷新方式及MV log----还没写完!原创 2010-04-02 17:22:00 · 366 阅读 · 0 评论 -
repeatable read and phantom read in Oracle
数据库有四种隔离级别:read uncommited,read commited, repeatable read,phantom readoracle提供三种隔离级别:read commited, serializable, read only在oracle怎么实现repeatable read 呢?select for update保证在读取数据时改数据不能被改写,从而保证原创 2012-03-28 17:54:02 · 768 阅读 · 0 评论