Oracle DB
文章平均质量分 69
田攀
tian.pan@qq.com
展开
-
Select for update, nowait, skip locked
FOR UPDATE NOWAITBEGIN SELECT x FROM table FOR UPDATE NOWAIT;EXCEPTION WHEN OTHERS THEN END;FOR UPDATE(Wait model)BEGIN SELECT x FROM table FOR UPDATE;EXCEPTION WHEN O原创 2012-04-05 16:17:53 · 4052 阅读 · 0 评论 -
事务时间(Sysdate)不对的解决方法
做库存事务,发现Transaction Date是2016年的一个时间然后查了Sysdate,发现数据的时间确实不对但查了下Unix Server的时间没有问题,是当前时间-bash-3.2$ dateSun Mar 4 23:25:45 PST 2012解决方法:先设定一个固定的时间到系统SQL> alter system set fixed_date原创 2012-04-06 15:35:03 · 3816 阅读 · 0 评论 -
EBS表后缀的意义
Object NameFunction of the objectE.g._ALLUnderlying table with all the valuesPO_DISTRIBUTIONS_ALL_BBase of underlying table, same as _ALLMTL_SYSTEM_ITEMS_B_TLLanguage t原创 2012-04-26 17:38:13 · 3414 阅读 · 0 评论 -
TNSNAMES.ORA, LISTENER.ORA and SQLNET.ORA
Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration.Oracle Net is configured by configuration files. These include (but are not limited to):TN原创 2012-05-29 00:38:53 · 3083 阅读 · 0 评论 -
Initialization Parameter files: PFILEs vs. SPFILEs
Initialization Parameter files: PFILEs vs. SPFILEsSubmitted by admin on Sun, 2003-08-03 19:29 RDBMS ServerWhen an Oracle Instance is started, the characteristics of the Instance are establ转载 2012-05-30 11:27:50 · 2206 阅读 · 0 评论 -
Exadata概览
为什么叫Exadata为啥叫Exadata? 这是数据量的单位:从字节开始往上按照千分位递进,分别就是KiloByte(KB)、MegaByte(MB)、GigaByte(GB)、TeraByte(TB)、PetaByte(PB)、ExaByte(EB)、ZettaByte(ZB)、YottaByte(YB)。Exadata这个名字就是从ExaByte(EB)获取的灵感。一些关原创 2012-06-05 14:43:44 · 2810 阅读 · 0 评论 -
The Tom Kyte Blog: When the explanation doesn't sound quite right...
Refer:http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.htmlI was asked recentlyUnder what conditions, autotrace & explain plan can not give the correct execution plan o转载 2012-06-19 19:14:47 · 2078 阅读 · 0 评论 -
Oracle EBS SQL Trace日志收集的方法
Raw Trace的收集方法1. 打开Trace,Help > Diagnostics > Trace > Trace > Trace with Binds and WaitsTrace项代表的意思No Trace – turns trace off.Regular Trace – generates a regular SQL trace by performin原创 2012-06-19 17:22:48 · 5157 阅读 · 0 评论 -
PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
这段时间遇到一个问题,程序里明明插入了一条记录,但在后边的一段Procedure中却查不到刚刚插入的记录,最后发现这个Procedure的定义中加入了PRAGMA AUTONOMOUS_TRANSACTION。PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”(翻译的还算好理解),对于定义成自治事务的Procedure,实际上相当于一段独立运行的程序段,这段程原创 2012-06-19 10:52:25 · 34652 阅读 · 0 评论 -
关于TNS_ADMIN环境变量
很多oracle产品都有自己的TNS文件,如果你的系统里装了多个Oracle的产品的话,那么客户端连instance的时候,到底是使用那个tnsnames.ora呢?这个时候就需要看TNS_ADMIN这个参数了。Question: What is the tns_admin parameter and how do I use tns_admin to define a common原创 2012-06-28 15:44:57 · 74536 阅读 · 2 评论 -
V$PROCESS和V$SESSION,以及使用这两个视图能做什么
使用V$PROCESS,V$SESSION能做什么查看哪些用户连到了DB上,使用什么Program连接SET LINESIZE 100COLUMN spid FORMAT A10COLUMN username FORMAT A10COLUMN program FORMAT A45SELECT s.sid, --Session identifier s.seria原创 2012-07-10 10:47:12 · 7673 阅读 · 0 评论 -
ORA-12560: TNS:protocol adapter error(TNS:协议适配器错误)
这种问题一般是因为客户端不知道要连那个instance或者不知道要使用哪个TNS Alias可能的原因1.服务没有起来下边是Tom的一个Test Case来说明OracleServiceSID服务没有起来的话,是会引起ORA-12560错误的。C:\Documents and Settings\tkyte>sqlplus scott/tigerSQL*Plus: Relea原创 2012-06-28 15:11:38 · 114705 阅读 · 5 评论 -
多个ORACLE HOME的情况,默认的ORACLE HOME是哪个,以及如何更改HOME
如果系统里安装了多个ORACLE产品,那么在注册表里,有可能也会有多个ORACLE HOME,在不设置系统环境变量的情况下,默认情况使用哪个ORACLE HOME?HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\DEFAULT_HOME这个值就是默认的ORACLE HOME,有了DEFAULT_HOME的值,你可以在HOME0或HOME1或HOME2.原创 2012-07-15 17:22:31 · 16474 阅读 · 0 评论 -
Oracle Redo log
Redo Logs概述The redo log records all changes made to data, including both uncommitted and committed changes.Oracle通过Redo来保证数据库的事务可以被重演,从而使得在故障之后,数据可以被恢复。在数据库中,Redo的功能主要通过3个组件来实现:Redo Log Bu原创 2012-07-31 12:31:39 · 9794 阅读 · 0 评论 -
Oracle Undo & Redo
UndoUndo is used to refer to undo segments. So, what are segments? Segments refer to any type of thing that takes up storage space in the database ( like a table, or an index ). So an undo segment i原创 2012-07-30 16:14:47 · 3057 阅读 · 0 评论 -
Oracle EM(Enterprise Manager)的端口忘记了,如何找回端口号
在ORACLE_HOME目录下的install目录中有一个portlist.ini文件,这个文件中就记录着这台数据库Server上所有实例的Enterprise Manager Console HTTP Port和Enterprise Manager Agent Port的内容(还有isqlplus的端口信息)。X:\oracle\product\10.1.0\Db_1\install\p原创 2012-07-28 18:06:28 · 12563 阅读 · 0 评论 -
Undo Segment
原创 2012-08-24 00:01:31 · 2233 阅读 · 0 评论 -
Linux下使用ps命令来查看Oracle DB以及EBS相关的进程
Linux下可以使用ps命令来查看Oracle相关的进程使用ps来查看Oracle数据库相关的服务Oracle Listener这个命令会列出Oracle Net Listener的进程[oracle@bej301441 ~]$ ps -ef | grep tnslsnroracle 1999 29986 0 09:47 pts/2 00:00:00原创 2012-07-11 01:02:42 · 15865 阅读 · 0 评论 -
enq: TX - Row Lock Contention
遇到了一个 enq: TX - row lock contention的问题,从tkprof看,update真正执行的时间(cpu)很快,但elapsed时间非常长,在最后发现 enq: TX - row lock contention时间很长。UPDATE MTL_ITEM_LOCATIONS SET STATUS_ID = :b1 WHERE INVENTORY_LO原创 2012-09-06 15:24:10 · 2343 阅读 · 0 评论 -
ORA-01843: 无效的月份
执行下边的脚本CREATE TABLE MYEMP ( EMP_ID NUMBER, EMP_NAME VARCHAR2(10), SAL NUMBER, Joined_date DATE, DEPT_NO NUMBER);INSERT INTO MYEMP VALUES (3, 'THREE'原创 2012-09-27 10:47:40 · 16929 阅读 · 0 评论 -
Oracle SQL Trace
SQL Trace收集方法Current Session Level1.Enable Sql Tracealter session set sql_trace = true;alter session set statistics_level = ALL;alter session set timed_statistics = true;alter session set原创 2012-09-28 13:52:55 · 3808 阅读 · 0 评论 -
SQL*Plus FAQ
source:http://www.orafaq.com/wiki/SQL*Plus_FAQContents [hide] 1What is SQL*Plus and where does it come from?2How does one use the SQL*Plus utility?3What commands can be executed from转载 2012-10-09 11:28:19 · 2343 阅读 · 0 评论 -
Prompt & Accept in sqlplus
With prompt & accept, it is possible to interactively set a value for a user variable in SQL*Plus.Sample 1set echo off;set serveroutput on size 999999;Prompt ptian test scriptprompt Ple原创 2012-10-09 12:24:39 · 2633 阅读 · 0 评论 -
[Oracle]如何查看SQL的执行计划 - DBMS_XPLAN Package
查看执行计划除了AUTOTRACE的方法外,还可以使用DBMS_XPLAN Package来查看1.执行"explain Plan"语法:SQL> Explain plan forTry this command:SQL> explain plan for select * from mtl_system_items_b where inventory_item_i原创 2012-09-28 14:18:46 · 4192 阅读 · 0 评论 -
ASCII & CHR Function
The ASCII function returns the NUMBER code that represents the specified character.ASCII( single_character )SELECT ASCII('A') FROM dual;Output:AThe CHR function is the opposite of the asci原创 2012-10-09 17:11:55 · 1937 阅读 · 0 评论 -
让Sql Plus环境变量的设置永久生效
每次打开Sql Plus都要设置环境变量,很麻烦,这里有个方法可以永久的保存你的环境变量的设置。设置$ORACLE_HOME/sqlplus/admin/glogin.sql文件,在后边加上你自己的个性化设置。这样每次启动Sqlplus(不管是官方的sqlplus客户端,还是cmd命令行启动的sqlplus),都会读取glogin.sql的设置。下边是我glogin.sql里的设置:原创 2012-10-11 11:22:57 · 5485 阅读 · 0 评论 -
SQL*Plus commands
The following commands can be issued in SQL*Plus (in addition to the standardSQL commands.) @pathname Run (START) an SQL Script @MyScript.sql parameter1 parameter2 parameter3转载 2012-10-12 11:42:45 · 2398 阅读 · 0 评论 -
.trc and .trm files in Oracle 11g
What is .trc file?Ans:Sql Trace Collection fileWhat is .trm file?Ans:Trace map (.trm) file.Trace files(.trc) are sometimes accompanied by corresponding trace map (.trm) files, which contain st原创 2012-10-12 11:33:05 · 8265 阅读 · 0 评论 -
SQL Trace - TRACEFILE_IDENTIFIER
sql trace默认生成到user_dump_dest下,这个目录有可能存在很多sql trace,所以要找到你的trace文件有时候要费一些功夫。Oracle提供了一个有用的参数,可以让你快速的找到你的trace文件--TRACEFILE_IDENTIFIEROracle SQL Trace收集方法:1. Set the tracefile identifier as you want原创 2012-09-24 16:16:43 · 3610 阅读 · 0 评论 -
Oracle Events & Level Dictionary
Sample:alter session set events '10046 trace name context forever, level 12';Event #Level Description8186-ALTER SESSION SET EVENTS '8186 trace name context OFF' - VER原创 2012-09-24 16:21:03 · 2403 阅读 · 0 评论 -
ORA-12557: TNS:protocol adapter not loadable
启动SQL Tools尝试连接Oracle数据库,报ORA-12557: TNS:protocol adapter not loadable的错.解决方法:把C:\oracle\product\10.2.0\client_2\BIN目录加到环境变量Path中,再重启SQLTools,就解决问题了。原创 2012-09-26 12:09:48 · 11887 阅读 · 0 评论 -
Script to compile invalid objects in DB
REM: Script to compile invalid objects in DB after refreshingREM:REM:*****************************************REM:REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.REM: Author will not be responsible原创 2012-10-29 19:18:58 · 2167 阅读 · 0 评论 -
OS & Oracle is 32-bit or 64-bit?
如何判断Oracle数据库是32位还是64位?Script 1:SELECT length(addr)*4 || '-bits' word_length FROM v$process where ROWNUM =1;Script 2:select * from v$version;output like:Oracle Database 11g Enterprise Edition Releas原创 2012-10-30 11:53:33 · 2536 阅读 · 0 评论 -
ODBC连Oracle数据库报错,SQLState=IM004
Window7下使用ODBC连Oracle数据库(11g的),然后报下边的错误:Unable to connectSQLState=IM004[Microsoft][ODBC 驱动程序管理器]驱动程序的 SQLAllocHandle on SQL_HANDLE_ENV 失败解决方法:原来是Oracle_Home指向错了,我连得是Oracle11g的数据库,但环境变量里指定的Oracle_Home为原创 2012-10-31 15:49:45 · 10662 阅读 · 0 评论 -
如何查看Oracle数据库版本
方法一:v$versionSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Pr原创 2012-09-16 11:28:50 · 24055 阅读 · 0 评论 -
Oracle数据库的历史
Here is the list of important events that took place in the last 30 years of Oracle history. Oracle is celebrating its 30 wonderful years in the industry (from 1977 to 2007). This article was updated转载 2012-11-05 19:06:10 · 1955 阅读 · 0 评论 -
TNSPING只用于测试DB Listener是否可用,特别注意它不能检测数据库是否已经启动。
TNSPING位于ORACLE HOME/bin下,TNSPING只用于测试Listener是否可用(检查Socket是否可以连通),特别注意它不能检测数据库是否已经启动。原理:类似于TCP/IP工具ping,tnsping会发送一个Message给DB Server Listener,DB listener会给出一个回复。如果返回TNS-12541: TNS: no listener,一般表示服原创 2012-07-02 13:31:22 · 3261 阅读 · 0 评论 -
optimizer_features_enable
if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.2.0.4 optimizer behavior, you can do so by setting this parameter to 10.2.0.4. At a later time, you原创 2012-11-14 12:38:48 · 3784 阅读 · 0 评论 -
Oracle: Network Waits
IntroductionUnfortunately, what Oracle calls "Network Waits" have little to do with Network but and almost exclusively to do with the time it takes to pack messeges for the network before they are sen转载 2012-11-14 14:30:23 · 2925 阅读 · 0 评论 -
使用DBMS_UTILITY.GET_TIME来捕获某段代码的执行时间
DBMS_UTILITY.GET_TIME常用于计算某段代码间的时间消耗,DBMS_UTILITY.GET_TIME返回值本身并不是指当前的时间,而是一个the number of 100ths of seconds that have elapsed from an arbitrary time.Usage NotesYou should not use GET_TIME to establis原创 2012-12-03 16:49:04 · 5424 阅读 · 0 评论