-
编辑 删除
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_host_name v$instance.host_name%type; /*UTL_INADDR useful package added in oracle 9i and...
-
编辑 删除
依据统计信息确定多块读被使用
大家都知道:Full table scan 和 Index fast full scan会用到多块读,下面就依据统计信息来确定这点;physical read total multi block requests:Total number of Oracle instance read requests which read in two o...
-
编辑 删除
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 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. It's like you're always stuck in second gear. When it hasn't been your day. your week your month or even your year, But, I'll be there for you (When the rain starts to pour) I'll be there for you (Like I've been there before) I'll be there for you ('Cause you're there for me too) You're
-
编辑 删除
About storage parameter INITRANS and MAXTRANS
About storage parameter INITRANS and MAXTRANS INITRANS: The space you would like to reserve for transaction entries compared to the space you would reserve for database data ; the default value (1 fo...
-
编辑 删除
Instance allocated memory (SGA+PGA)
Checking oracle usage memory is quite difficult on unix: when using 'ps aux' , RSS shows process memory + shared memory for each process. You need to use svmon or pmap to substract the shared part. Th...
-
编辑 删除
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_test; 8 begi...
-
编辑 删除
Fetch limit and %NOTFOUND attribute
SQL> create table t_test as select * from user_objects where rownum < 1;Table createdSQL> SET serverout on;SQL> SQL> CREATE OR REPLACE PROCEDURE p_limit_test(p_array_size IN NUMBER DEFAULT 20) IS&nb...
-
编辑 删除
About Merge statement
Merge 语句就是依据条件update或者insert数据到目的表中,语句结构如下:MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED TH...
-
编辑 删除
Oracle提供的密码复杂度函数
Oracle公司提供的验证密码复杂度的函数......CREATE OR REPLACE FUNCTION verify_function(username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; diff...
-
编辑 删除
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 off and on #lsnrctl lsnrctl> set log_status off lsnrctl> set log_status on ...
-
编辑 删除
view about redo&undo
关于redo&undo的几个动态性能视图...... select a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name = 'redo size';V$ROLLSTATUSN: Rollback segment number RSSIZE: Current...
-
编辑 删除
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.txt','R'); loop utl_file.get_line(f,s); dbms_output.put_line(s...
-
编辑 删除
关于object_id 和data_object_id
对于这两个子段,Oracle给出了定义:OBJECT_ID: Object number of the object DATA_OBJECT_ID: Dictionary object number of the segment that contains the object 也就是说,object_id是schema下唯一的对象的标识,...
-
编辑 删除
using index
创建primary or unique constraint时可以使用using index来对相应的index定义......SQL> create table t1(c1 number primary key 2 using index tablespace users initrans 40 pctfree 10 3 storage(initial 10k...
-
编辑 删除
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;SQL> create table t1 (c1 varchar2(10),c2 varchar2(10));Table created.SQL> alter table t...
-
编辑 删除
查看隐含参数信息
结合 x$ksppi , x$ksppsv 系统视图查看系统隐含canshu......select name, value, decode(isdefault, 'TRUE', 'Y', 'N') as "Default", decode(ISEM, 'TRUE', 'Y', 'N') as SesMod, decode(I...
-
编辑 删除
一些系统视图的介绍
一些系统视图的介绍--逐步整理select *from v$session_longops;对操作超过6秒的session作相应的记录;select *from v$controlfile_record_section ;控制文件各部分记录信息;
-
编辑 删除
Virtual index
"Virtual" index 是Oracle undocumented的一个特性:创建后dba_segment/dba_indexes(9i)数据字典中没有记载,不会影响对应的sql,只是在系统隐含参数“_use_nosegment_indexes”设为true时发挥作用......SQL> create ta...
-
编辑 删除
About ASCIISTR Function
Oracle 9i引入的一个字符函数功能:返回字符对应的ASCII码,对于非ASCII码转化为Unicode并前置符号'/';Oracle 9i引入的一个字符函数功能:返回字符对应的ASCII码,对于非ASCII码转化为Unicode并前置符号'/';select">test...
-
编辑 删除
dbms_shared_pool
dbms_shared_pool提供以下功能:将object 或者 "sql statement" pin到shared pool......keep过程可以将对象pin入shared_pool,而不进入LRU 机制;unkeep过程将pinned对象unpin,从而进入LRU机制;SQL> alter system flus...
