- 博客(98)
- 收藏
- 关注
RSS订阅转载 How to get server IP address
How to get server IP address...UTL_INADDR useful package in oracle 9i&10g;declare v_ip_address varchar2(50); v...
2019-07-14 15:45:07
438
转载 依据统计信息确定多块读被使用
大家都知道:Full table scan 和 Index fast full scan会用到多块读,下面就依据统计信息来确定这点;physical read total multi block requests:Tot...
2019-07-14 15:06:04
198
转载 I'll be there for you
So no one told you life was gonna be this way. Your job's a joke.You're broke. Your love life's D.O.A.[@more@]So n...
2019-07-14 12:39:05
231
转载 About storage parameter INITRANS and MAXTRANS
About storage parameter INITRANS and MAXTRANS INITRANS: The space you would like to reserve for transaction entrie...
2019-07-12 15:48:07
132
转载 Instance allocated memory (SGA+PGA)
Checking oracle usage memory is quite difficult on unix: when using 'ps aux' , RSS shows process memory + shared m...
2019-07-11 12:12:05
132
转载 PLS-00231 private function is not in scope in SQL
SQL> declare 2 lv_test number; 3 function fn_test return number 4 is 5 begin 6 return 1; 7 end fn_...
2019-07-10 20:12:05
201
转载 Fetch limit and %NOTFOUND attribute
SQL> create table t_test as select * from user_objects where rownum < 1;Table createdSQL> SET servero...
2019-07-08 11:57:05
145
转载 About Merge statement
Merge 语句就是依据条件update或者insert数据到目的表中,语句结构如下:MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table...
2019-07-08 10:45:04
255
转载 Oracle提供的密码复杂度函数
Oracle公司提供的验证密码复杂度的函数......CREATE OR REPLACE FUNCTION verify_function(username varchar2, password varchar2, old_...
2019-07-07 20:45:07
964
转载 Enable/disable listener logging
if you found listener.log, that hung. you stop and start but it's down time. so, you can force listener logging of...
2019-07-05 20:57:06
79
转载 view about redo&undo
关于redo&undo的几个动态性能视图...... select a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# ...
2019-07-05 12:39:05
71
转载 Interactions with PL/SQL(2) How to read multiple lines from the text file using
读取TXT文件并显示......declare f utl_file.file_type; s varchar2(200);begin f := utl_file.fopen('e:test','sample....
2019-07-03 15:42:03
58
转载 关于object_id 和data_object_id
对于这两个子段,Oracle给出了定义:OBJECT_ID: Object number of the object DATA_OBJECT_ID: Dictionary object number of the segme...
2019-07-01 19:30:07
146
转载 using index
创建primary or unique constraint时可以使用using index来对相应的index定义......SQL> create table t1(c1 number primary key 2 ...
2019-06-28 12:33:05
157
转载 Create logic constraints
Table t1(c1,c2), c1 not null; if c2 is null then c1 c2 can be identical,if c2 not null then c1,c2 must be unique;S...
2019-06-27 13:51:07
91
转载 查看隐含参数信息
结合 x$ksppi , x$ksppsv 系统视图查看系统隐含canshu......select name, value, decode(isdefault, 'TRUE', 'Y', 'N') as...
2019-06-26 16:36:05
114
转载 一些系统视图的介绍
一些系统视图的介绍--逐步整理select *from v$session_longops;对操作超过6秒的session作相应的记录;select *from v$controlfile_record_section ;控制文...
2019-06-25 16:33:04
72
转载 Virtual index
"Virtual" index 是Oracle undocumented的一个特性:创建后dba_segment/dba_indexes(9i)数据字典中没有记载,不会影响对应的sql,只是在系统隐含参数“_use_nosegm...
2019-06-22 16:48:04
148
转载 About ASCIISTR Function
Oracle 9i引入的一个字符函数功能:返回字符对应的ASCII码,对于非ASCII码转化为Unicode并前置符号'/';Oracle 9i引入的一个字符函数功能:返回字符对应的ASCII码,对于非ASCII码转化为Unic...
2019-06-20 11:48:07
123
转载 dbms_shared_pool
dbms_shared_pool提供以下功能:将object 或者 "sql statement" pin到shared pool......keep过程可以将对象pin入shared_pool,而不进入LRU 机制;unkee...
2019-06-18 18:03:05
168
转载 library cache lock &library cache pin
Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.Lock比pin具有更高的级别.Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.锁定主要有三种模式:...
2019-06-18 12:57:06
117
转载 DISTINCT statement with Order by clause
SQL> desc test;Name Type Nullable Default Comments ---------- ------------ -------- ------- -----...
2019-06-17 18:39:06
91
转载 Oracle helpful url list
Oracle helpful url list www.petefinnigan.com/orasec.htm This site is good for learning and exploring the Oracle se...
2019-06-17 12:24:06
214
转载 windows下server process 和client process(Deciated mode)
SQL> select a.spid dedicated_server 2 ,b.process clintpid 3 from v$process a,v$session b 4 w...
2019-06-08 16:18:06
846
转载 ORA_NLS33
环境变量ora_nls33定义'locale data'所在的目录......Oracle对不同字符集的支持是通过nls运行库来实现的,nls运行库的运行需要'local data'的支持,而环境变量ora_nls33就是定义'...
2019-06-06 22:12:05
314
转载 世界末日
世界末日什么时候呢......SQL> select sysdate + 12*30*8000 from dual;SYSDATE+12*30*8000-------------------9893-02-25 15:29...
2019-06-01 22:39:04
76
转载 用dbms_stats收集统计信息
CBO优化器基于三个方面评估SQL cost:selectivity,cardinality and cost(CPU&MEM&I/O),利用dbms_stats收集column柱状图信息,CBO依据柱状图可以得...
2019-06-01 17:12:05
181
转载 Pragma RESTRICT_REFERENCES
To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to c...
2019-05-31 16:00:08
136
转载 重建临时表空间
建立新的临时表空间,再设定系统默认临时表空间为新建空间;SQL> create temporary tablespace tem tempfile 'D:oracleoradatabjmobiletem01.dbf' si...
2019-05-30 20:30:05
124
转载 ORA-04021
ORA-04021 timeout occurred while waiting to lock object stringstringstringstringstringCause: While waiting to lock...
2019-05-30 07:03:08
293
转载 Deferrable constraints
对deferrable constraints可定义其检查为immediate or deferred......SQL> create table t1(c1 number);Table createdSQL> a...
2019-05-28 20:06:08
110
转载 ASCII Character Set
ASCII Character SetASCII Character SetSymbolDecimal valueSymbolDecimal valueblank32;59!33<60"34=61#35>62$36?...
2019-05-28 10:21:06
149
转载 tailgate and piggyback
遇到两个有意思的单词：tailgate and piggyback, 查了下发现两者有相同的意思:Piggyback-跟随某人进入需要特定权限方许进入的地方 比如，楼下有门禁，但是你没...
2019-05-27 12:45:04
169
转载 Busting the Oracle Myth Busters(zz)
by Donald K. BurlesonThe Oracle database of the early 1990s is very different from the database of the early 21st ...
2019-05-26 12:09:06
232
转载 表空间时间点恢复练习
表空间时间点(TSPITR)恢复步骤1: alter tablespace test read only;SQL> select * from test.t_date1; C1 C2---------- --...
2019-05-24 12:54:06
82
转载 Error of Install on linux
[oracle@myredhat Disk1]$ ./runInstallerYou do nothave permission to write to the inventory /oraInventory. Instal...
2019-05-22 22:21:05
56
转载 parent cursor child cursor shared cursor
每个SQL语句在解析时,会在Library cache中查找同一是否存在同样的SQL,如果没有就创建parent cursor and child cursor,如果有那还要看相关变量类型、环境参数等是否一致,如果一致就重用该s...
2019-05-21 14:42:05
119
转载 Buffer pool and alter table cache
我们都知道：oracle有3中buffer pool1. default pool2. keep pool3. recycle pool对于这三种pool,是基于block的热度来进行管理的,系统默认使用的,就用...
2019-05-20 19:54:03
374
转载 system change number
system change number(scn)就好比oracle内部时钟,它以增加的方式变动着,DB所有的变动都以它为时间基准,它在整个系统中也是唯一的;Connected to Oracle8i Enterprise Ed...
2019-05-19 14:39:07
158
转载 An example of ref cursor(1)
An example of ref cursorSQL> create table t1(c1 varchar2(20),c2 date);Table created.SQL> create or replace p...
2019-05-19 09:48:04
63
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人