ORACLE Management
jxzkin
这个作者很懒,什么都没留下…
展开
-
Oracle返回表集合函数
create or replace type type_col as object(e varchar2(36 char),n varchar2(100 char));create or replace type type_tab as table of type_col;create or replace function f_return_rowtype(v_id varchar2)原创 2015-08-26 09:23:53 · 1662 阅读 · 0 评论 -
latch: cache buffers chains-热块的简单模拟实验
1.创建测试数据.create table t1 as select rownum id from dba_objects;CREATE INDEX T1_IDX ON T1(ID) ;2.创建两个存储过程.create or replacePROCEDURE pselect2ASl_num number;BEGIN FOR i IN 1..1000000 LOOP原创 2012-12-14 20:31:17 · 1078 阅读 · 0 评论 -
Oracle-Lock Query
set pages 1000 lin 126col kaddr heading 'lock|address'col username heading 'lock|holder|username' for a18col sid heading 'lock|holder|session id' format 9999999999col type heading 'lock|type' form原创 2012-12-24 09:58:23 · 860 阅读 · 0 评论 -
Shell-检查Oracle数据库是否正常
#!/bin/bash# Author:Zhang Jianlog(){ echo "$@" >> checkdatabase.log}main(){ (sqlplus "system/kin" << EOFSELECT STATUS FROM v\$instance;EOF) | grep OPEN > /dev/null 2>&1 if [ $?原创 2012-12-07 16:19:47 · 3258 阅读 · 0 评论 -
latch: cache buffers chains
http://www.xifenfei.com/1109.html当一个数据块读入sga区,相应的buffer header会被放置到hash列表上,我们称其这hash chains,chain在中文的意为链条或串的意思,表达就是关连性.如果一个进程想访问或修改hash chain上的block,它首先要获得”cache buffers chains” latch。原因一:低效率的SQ转载 2012-12-06 11:07:29 · 502 阅读 · 0 评论 -
Oracle Hexadecimal to Decimal Function
create or replaceFUNCTION hextodec (hexnum in char) RETURN number IS x number; digits number; result number := 0; current_digit char(1); current_digit_dec number; BEGIN digits := lengt原创 2012-12-05 14:10:09 · 601 阅读 · 0 评论 -
JDBC连接RAC
jdbcracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=)(PROTOCOL=TCP)(PORT=1521))(ADDRESS=(HOST=)(PROTOCOL=TCP)(PORT=1521))(LOAD_BALANCE=yes)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE原创 2012-11-22 09:45:34 · 696 阅读 · 0 评论 -
RAC-ORA-15055: unable to connect to ASM instanceORA-12547: TNS:lost contact
http://space.itpub.net/22664653/viewspace-706623【RAC】ORA-15055: unable to connect to ASM instanceORA-12547: TNS:lost contactrac 安装完成,关闭之后重启数据库遇到如下错误:oracle@rac1:/tmp>sqlplus "/as sysdba"转载 2012-09-26 11:16:04 · 3820 阅读 · 0 评论 -
Oracle dbms_backup_restore恢复数据库
Oracle Version:Oracle 10.2.0.5 64bitOS:RHEL5.4 64bit1.fullbackup[oracle@Kin ~]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Mon Sep 17 17:00:14 2012Copyrigh原创 2012-09-21 23:43:36 · 1374 阅读 · 0 评论 -
Oracle修改dblink连接数
alter system set open_links=10 scope=spfile;alter system set open_links_per_instance=10 scope=spfile;OPEN_LINKSPropertyDescriptionParameter typeIntegerDefaul原创 2012-09-21 09:26:26 · 5693 阅读 · 0 评论 -
Flash Recovery Area空间使用查询
col name format a32 heading 'file name'col spc_lmt_mb format 9999.99 heading 'space|limit|(mb)'col spc_usd_mb format 9999.99 heading 'space|used|(mb)'col spc_rcl_mb fo原创 2012-09-20 13:54:22 · 1199 阅读 · 0 评论 -
SQL递归查询
http://space.itpub.net/118838/viewspace-735995connect by是结构化查询中用到的,其基本语法是:select ... from tablename start by cond1connect by cond2where cond3;简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:id,parentid转载 2012-09-14 13:49:41 · 1052 阅读 · 0 评论 -
Required Support Diagnostics for Hanging Databases [ID 452358.1]
Required Support Diagnostics for Hanging Databases [ID 452358.1] In this Document Goal Solution ReferencesApplies to:Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 1原创 2012-09-29 12:44:38 · 895 阅读 · 0 评论 -
Database Creation on 11.2 Grid Infracture with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 )
Database Creation on 11.2 Grid Infracture with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) [ID 1084186.1]In this DocumentGoalFixReferencesApplies to:Oracle Server - Enterpri原创 2012-09-28 12:33:47 · 9316 阅读 · 0 评论 -
Oracle查询动、静态参数
SQL> desc v$parameter; Name Null? Type ----------------------------------------- -------- ---------------------------- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBE原创 2012-09-11 10:53:32 · 872 阅读 · 0 评论 -
Oracle 行转列(pivot、wm_concat、decode)使用总结
1.创建测试数据CREATE TABLE CC (Student NVARCHAR2(2),Course NVARCHAR2(2),Score INT );INSERT into CC select N'张三',N'语文',78 from dual union allselect N'张三',N'数学',87 from dual union allselect N'张原创 2012-09-06 10:32:13 · 9897 阅读 · 4 评论 -
install sqlplus command-line help
1. install sqlplus command-line helplog into as system user@?/sqlplus/admin/help/hlpbld.sql helpus.sql2. remove sqlplus command-line help@?/sqlplus/admin/help/helpdrop.sql原创 2012-09-03 16:56:34 · 763 阅读 · 0 评论 -
Oracle加减日期
SELECT to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss') now, to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss') +1 next_day, to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss') +1/24 next_hour, to_date(SYSDATE,'yyyy-mm-d原创 2012-07-27 17:24:59 · 715 阅读 · 0 评论 -
Oracle Hints-/*+cardinality()*/模拟表的数据量
1* select /*+cardinality(t 1000000000)*/count(*) from t,t t1 where t.object_id=t1.object_idSQL> /Execution Plan----------------------------------------------------------Plan hash value: 791582492原创 2012-12-17 16:58:35 · 3421 阅读 · 0 评论 -
Oracle-模拟Log File Sync等待事件
session1:create table sync (x int);begin for i in 1 .. 10000 loop insert into sync values (i); commit work write wait immediate; end loop;end;session2:select sid,eve原创 2012-12-29 22:21:24 · 1086 阅读 · 0 评论 -
Oracle分析函数学习
http://f.dataguru.cn/thread-41808-1-1.htmlrow_number() over ([partition by col1] order by col2) )1.SQL> SELECT deptno 部门, ename 姓名, SAL 薪水, SUM(SAL) OVER (ORDER BY ENAME) 累加, SUM转载 2012-12-21 10:00:32 · 664 阅读 · 0 评论 -
Oracle-多版本环境变量切换
ORACLE_BASE=/home/oracle/appecho "(1) Oracle10g"echo "(2) Oracle11g"read -n1 KEYecho ""case $KEY in1)ORACLE_HOME=$ORACLE_BASE/Oracle10gORACLE_SID=SID;;2)ORACLE_HOME=$ORACLE_BASE/Oracle原创 2015-03-17 08:52:16 · 1521 阅读 · 0 评论 -
Oracle-expdp&impdp小结
export NLS_LANG=american_america.AL32UTF8create or replace directory dmp as '/tmp';select * from dba_directories;grant read,write on directory dmp to xxx;expdp xxx/xxx dumpfile=xxx.dmp logfile原创 2013-06-27 14:19:37 · 1181 阅读 · 0 评论 -
Oracle11g(CentOS6)-pdksh
wget http://mirror.centos.org/centos/5/os/x86_64/CentOS/pdksh-5.2.14-37.el5_8.1.x86_64.rpmllrpm -ivh pdksh-5.2.14-37.el5_8.1.x86_64.rpm rpm -e ksh-20120801-21.el6.1.x86_64rpm -ivh pdksh-5.2.14原创 2014-12-18 14:16:09 · 1247 阅读 · 1 评论 -
Oracle-SQL_TRACE使用
select * from v$session where schemaname='SCOTT';select p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid = &1 and s.serial# = &2; exec dbms_monitor.sessio原创 2014-11-25 21:30:31 · 787 阅读 · 0 评论 -
Oracle-oradebug使用小记
SYS@rac1>select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1); PID SPID---------- -----------------------------------原创 2013-12-30 09:27:11 · 737 阅读 · 0 评论 -
Oracle 11g New Features-密码过期&密码区分大小写
SELECT * FROM dba_profiles WHERE PROFILE = 'DEFAULT' AND resource_name = 'PASSWORD_LIFE_TIME';ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;SELECT * FROM dba_profiles WHERE PR原创 2012-02-02 09:58:12 · 524 阅读 · 0 评论 -
Oracle Data Guard支持跨平台列表
SQL> select platform_id, platform_name from v$database;PLATFORM_ID PLATFORM_NAME----------- -------------------------------------------------- 13 Linux x86 64-bitPLATFORM_IDPLA原创 2013-02-26 10:34:02 · 1617 阅读 · 0 评论 -
Oracle-TIMESTAMP&DATE互转
-- SWTICH UNIX TIMESTAMP TO DATECREATEOR REPLACE FUNCTION unix_to_oracle(in_number NUMBER)RETURN DATE ISBEGIN RETURN( TO_DATE('19700101', 'yyyymmdd')+ in_number / 86400 + TO_NUMBER( SUBSTR(原创 2013-03-05 09:20:50 · 1045 阅读 · 0 评论 -
sql trace & event 10046,10053使用方法
event 10046alter session set events '10046 trace name context forever,level 1|4|8|12';alter session set events '10046 trace name context off';orexec dbms_monitor.session_trace_enable(sid,seria原创 2012-12-13 15:33:41 · 852 阅读 · 0 评论 -
oracle中日期类型与unix 时间戳的转换
oracle中日期类型与unix 时间戳的转换 Unix时间戳记是从'1970-01-01 00:00:00'GMT开始的秒数,表现为整数型。 Oracle中的时间是Date型,以下函数提供了两种时间转换的Oracle函数 (1)从Unix时间戳记转换为Oracle时间 create or replace function unix_to_orac转载 2013-01-21 18:16:48 · 5808 阅读 · 0 评论 -
Oracle-创建Job
SQL> grant execute on dbms_job to kin;Grant succeeded.SQL> conn kin/kin;Connected.--create jobSQL> declare 2 v_job number; 3 begin 4 dbms_job.submit(v_job,'P_CREATETABNAME;',sysdate,'sy原创 2013-01-19 13:43:31 · 668 阅读 · 0 评论 -
Oracle-Latch events
SELECT EVENT, WAIT_CLASS, TIME_WAITED_MICRO,ROUND(TIME_WAITED_MICRO*100/S.DBTIME,1) PCT_DB_TIME FROM V$SYSTEM_EVENT E, V$EVENT_NAME N, (SELECT VALUE DBTIME FROM V$SYS_TIME_MODEL WHERE STA原创 2013-01-28 14:56:43 · 620 阅读 · 0 评论 -
Oracle-Wait Events and Potential Causes
Wait Events and Potential CausesWait EventGeneral AreaPossible CausesLook for / Examinebuffer busy waitsBuffer cache, DBWRDepends on buffer type. For example, w原创 2013-01-24 10:52:01 · 649 阅读 · 0 评论 -
Oracle-Scattered Read, Sequential Read, and Direct Path Read
db file sequential read (single block read into one SGA buffer)db file scattered read (multiblock read into many discontinuous SGA buffers)direct read (single or multiblock read into t原创 2013-01-24 14:24:55 · 778 阅读 · 0 评论 -
Oracle-flashback query drop procedure
-- find object_idSELECT a.Obj#, b.Object_Name, b.Object_Type, b.OwnerFROM (SELECT Obj#, Name, Type#, OWNER# FROM Obj$ AS OF TIMESTAMP To_Timestamp('2012-05-15 23:35:00', 'YYYY-原创 2013-01-08 17:40:46 · 837 阅读 · 0 评论 -
Oracle-IMPDP-TABLE_EXISTS_ACTION参数
TABLE_EXISTS_ACTIONDefault: SKIP (Note that if CONTENT=DATA_ONLY is specified, then the default is APPEND, not SKIP.)PurposeTells Import what to do if the table it is trying to create al原创 2013-01-07 11:02:04 · 1225 阅读 · 0 评论 -
Linux释放内存及手动释放Oracle共享内存段
free -m#echo 3 > /proc/sys/vm/drop_caches#cat /proc/sys/vm/drop_caches3free -m手动删除Oracle共享内存段$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9$ ipcs -m | grep ora原创 2012-08-10 12:23:46 · 3809 阅读 · 0 评论 -
查询Oracle使用的特性
select name,detected_usages from dba_feature_usage_statistics where detected_usages>0;原创 2012-06-26 12:57:56 · 551 阅读 · 0 评论 -
Oracle MA SQL
Query session trace file directory:9iR2&10g: select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace from v$process a, v$session b, v$parameter c, v$in原创 2011-08-18 10:03:53 · 696 阅读 · 0 评论