07 2014 档案
摘要：For PL/SQL1)Create Directory Where BLOB resides.create or replace directory temp as '/oradata2'; -- if the directory name you created not qoted, then ...
阅读全文
摘要：1. How does the invalid object come?The Oracle database will invalidate objects if a dependent object is changed. If I rebuild a table, the indexes on...
阅读全文
摘要：十进制与十六进制的转换十进制-->十六进制select to_char(100,'XX') from dual;十六进制-->十进制select to_number('7D','XX') from dual;
阅读全文
摘要：Example to show the dead lock caused by lack of index on foreign key of child table.Session 1:create table p ( x int primary key );create table c ( x ...
阅读全文
摘要：http://blog.csdn.net/liqfyiyi/article/details/7727641http://www.askmaclean.com/archives/category/oracle/lockhttp://database.51cto.com/art/201004/19729...
阅读全文
摘要：1. enable traceDBCC TRACEON (trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]trace# Is the number of the trace flag to turn on.n Is a placeholder that...
阅读全文
摘要：1创建一个测试表，test，并且插入10000行数据； SQL> create table test (id int); SQL> begin 2 for i in 1..10000 loop 3 insert into test values(i) 4 end loop; 5 end; ...
阅读全文
摘要：With below three situation, we can use the pseudocolumncolumn_value to refer the column value.an XMLTABLEconstruct without the columnsclauseTABLE func...
阅读全文
摘要：我们在10046生产的trace 文件里经常看到下面的信息. 表示系统在等待散列读取某个文件号的某个块开始的8个块.WAIT #6: nam='db file scattered read' ela= 438472 file#=6 block#=2641 blocks=8WAIT #6: nam='...
阅读全文
摘要：Format:column column_name new_value var_nameMeaning: use the column_name of a select statment to construct a variable, later we can use &var_name to r...
阅读全文
摘要：以前写过用external table来加载trace文件,详情参考下面链接.http://www.cnblogs.com/princessd8251/p/3779145.html今天要做到是用UTL_FILE包来读取一个directory下面的文件，比如我们的trace文件.先定义get_trac...
阅读全文
摘要：Question:I want to understand when to export and import by dbms_stats statistics and learn when it is a good idea to export and import statistics.Answ...
阅读全文
摘要：from http://www.itpub.net/thread-1852897-1-1.html有论坛朋友在上面的帖子里问SQL为什么不走索引,正好这两天我也刚刚在看SQL优化，于是试着回答了一下.下面是原来的SQL:select o.order_id as orderIdfrom order_i...
阅读全文
摘要：Running total for Oracle:SELECT somedate, somevalue,SUM(somevalue) OVER(ORDER BY somedateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS RunningTo...
阅读全文
摘要：CREATE PROCEDURE SP_EXEC_WITH_LOG(@I_TICKETNO VARCHAR(10),@I_SQLSTR nvarchar(max))ASBEGIN DECLARE @QUOTATION_TRANSFER_STR nvarchar(max); SET @QUOTATIO...
阅读全文
摘要：The server file is created when you install TortoiseSVN, Eclipse or command-line Subversion. Use the appropriate path from the installation folder to ...
阅读全文
摘要：数据库设置a.设置UNDO_RETENTION的初始值（在短时间内）代表你想要查过去多远的时间。b.设置初始化参数UNDO_MANAGEMENT=AUTO。c.建立一个还原点空间，要有足够的空间存储需要的数据。有多少数据更新，就需要多少空间。权限FLASHBACK_SCN和FLASHBACK_TIM...
阅读全文
摘要：For Class.getResourceAsStream(String name), if the name parameter doesn't start with a "/", then it's a relative path to the class's package. If the n...
阅读全文
摘要：来自讨论贴 http://www.itpub.net/thread-1877111-1-1.html准备数据表2014-07-20 01:38:10>create table tb_1 as select * from dba_objects where rownumcreate table tb_...
阅读全文
摘要：from http://www.itpub.net/thread-1876506-4-1.htmlSQL> defineDEFINE _DATE = "20-7月 -14" (CHAR)DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)DEFINE _USER = ...
阅读全文
摘要：在ITPUB 上看到一个帖子 http://www.itpub.net/thread-1875212-1-1.html同一条SQL语句，只有查询条件不一样，查询返回的结果集都为0，一个走了全表扫描，一个走索引。查看全表扫描的SQL语句：SQL走全表，产生了2422609个逻辑读，cost为535KS...
阅读全文
摘要：笔者的公司搭建了一个Nexus服务器,用来管理我们自己的项目Release构件和Site文档.今天的问题是当用户访问一个Site里的PDF文件的时候，报错说“detected that the network has been closed.”但是用右键点击，然后另存为，却每次都能成功下载下来.本来...
阅读全文
摘要：要想给一个Oracle实例配置多个监听，首先要定义多个监听器，因为是多个监听，势必会有一些监听端口不是1521.现在服务端的listener.ora文件中定义如下监听器:LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS =...
阅读全文
摘要：原始代码如下：begin DECLARE @SQL_STMT NVARCHAR(300),@V_CLIENT_ID INT,@V_PGNAME VARCHAR(1000),@V_LOGID INT;DECLARE C2 CURSOR FOR SELECT PGNAME,CLIENT_ID FROM ...
阅读全文
摘要：原SQL如下：SQL的主要问题是红色部分居然通过标量查询，反复的查找与SQL相同的基表，很显然这个可以用case when来简化。select a.TRAN_ID,a.AMOUNT,a.BALANCE,a.INVAMT,a.PROMISED,a.INVNO,a.RCLNUM,b.PROBLEM_ID...
阅读全文
摘要：在我们用dbms_job包进行定时Job的时候，需要设置时间间隔，所以需要知道时间的基本加减方法.SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';会话已更改。SQL> select sysdate, sysdate+1/2...
阅读全文
摘要：并不是用了绑定变量就一定都会游标共享，下面我们介绍的就是一种例子。BIND_MISMATCH导致VERSION COUNT过多的原因解释：This is due to the bind buffer mismatch of the current child cursor. If oracle is...
阅读全文
摘要：我们都知道可是使用 alter system flush shared_pool 来清除shared pool 信息，当时不能指定清除某个对象。因为在系统繁忙的时侯 使用 alter system flush shared_pool 是很危险的，在oracle 10.2.0.4 以及 11g 有了新...
阅读全文
摘要：Command Line JVM SettingsThe proxy settings are given to the JVM via command line arguments: java -Dhttp.proxyHost=proxyhostURL -Dhttp.proxyPort=proxy...
阅读全文
