ITPub博客

oracle create function的两个实例:[@more@]create function的两种例子:CREATE OR REPLACE FUNCTION "HISSAAS"."GETDEPARTNAME" (vDepartCode in varchar2) return varchar2asDepartName varchar2( 1000);begin select tenant_name into DepartName from saas.SYY_TENANT where tenant_id = vDepartCode; return DepartName;end;/=================================================================================CREATE OR REPLACE FUNCTION "HISSAAS"."GETEMPNAME_1" (v_date1 in var

1,asmcmd command type[@more@]asmcmd instance management command: dsget,dsset,lsct,lsop,lspwusr,orapwusr,shutdownspbackup,spcopy,spget,spmove,spset,startupasmcmd file management commands: cd,cp,du,find,ls,lsof,mkalias,pwd,rm,rmaliasasmcmd disk group management commands: chdg,chkdg,dropdg,iostat,lsattr,lsdg,lsdsk,lsod,md_backup,md_restore,mkdg,mount,offline,online,rebal,remap,setattr,umountasmcmd template management commands: chtmpl,lstmpl,mktmpl,rmtmplasmcmd file access control commands:

今天想用电脑资源管理打开ftp上传东西的时候，总是跳到浏览器上。[@more@]上网查了一下:查到了几种方法1,在电脑桌面的空白处，新建shortcut，然后在弹出的框框中输入 %SystemRoot%explorer.exe ftp://192.168.18.131 然后点击下一步，给新建的shortcut取一个名字。 然后在桌面上就会出现一个ftp的图标。 双击打开即可2,和1的步骤差不多，在框框中输入 %SystemRoot%explorer.exe ftp://ftp1:password@192.168.18.131explorer.exe 和 ftp之间有空格

RMAN> backup as compressed backupset database format '/oradata/rmanbackup/%U' plus archivelog format '/oradata/rmanbackup/arch_%U';[@more@]Starting backup at 02-FEB-13current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=5 RECID=1 STAMP=806313718input archived log thread=1 sequence=6 RECID=2 STAMP=806316137input archived log thread=1 sequence=7

今天测试了一下将普通filesystem上的database迁移到asm上[@more@]1,backup databaseRMAN> backup as compressed backupset database plus archivelog;Starting backup at 02-FEB-13current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=5 RECID=19 STAMP=806364292input archived log thread=1 sequence=6 RECID=20 STAMP=806364367channel ORA_DISK_1:

1,将asm上datafile移动到其他的diskgroup上[@more@][oracle@asm ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 2 08:00:19 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database (not started)RMAN> startupOracle instance starteddatabase mounteddatabase openedTotal System Global Area 839282688 bytesFixed Size 2217992 bytesVariable Size 549455864 byt

1,asm支持file的类型[@more@]file type default templates-------------------------------------------------------------------------------------control files controlfiledata files datafileredo log files onlinelogarchive log files archivelogtemporary files temp

v$asm 相关的view[@more@] v$asm_alias contains one row for every alias present in every disk group mounted by the oracle asm instance----------------------------------------------------------------------------------------------------------------v$asm_attribute display one row for each attribute defined,in addition to attribute specified by create diskgroup and alter diskgroup statements,the view may show other

oracle11g使用pl/sql developer连接asm实例，步骤如下：[@more@]1,在tnsnames.ora 配置相应的连接字符串如下：asmasm = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.131)(PORT = 1521)) ) (CONNECT_DATA = (SID = +ASM) (SERVER = DEDICATED) ) )2,tnsping asmasm是否通:C:UsersFounder>tnsping asmasmTNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-2月 -2013 10:03:02Copyright (c) 1997, 2010, Oracle. All

可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：[@more@]select ... from tablename start with cond1 connect by cond2 where cond3;简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONN

创建diskgroup[@more@]SQL> create diskgroup flash external redundancy disk 'ORCL:FLASH01','ORCL:FLASH02';Diskgroup created.添加磁盘SQL> alter diskgroup flash add disk 'ORCL:FLASH03';Diskgroup altered.删除磁盘SQL> alter diskgroup flash drop disk flash03;Diskgroup altered.删除磁盘组SQL> drop diskgroup flash;Diskgroup dropped.SQL> create diskgroup flash normal redundancy failgroup fg1 disk 'ORCL:FLASH01' failgroup fg2 disk 'ORCL:FLASH02';Diskgroup created.===================

Fatal NI connect error 12170.[@more@] VERSION INFORMATION: TNS for Linux: Version 11.2.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production Time: 15-AUG-2012 16:09:47 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12560 nt main err code: 505 TNS-00505: Operation timed

今天在测试powerdesigner连接oracle数据库的时候出现了ora-12154错误，经过多次测试之后发现powerdesigner不能安装在C:Program Files (x86)，安装在C:Program Files之后，即可。[@more@]

在机器上做的备份脚本[@more@][oracle@dbmeso ~]$ crontab -l6 22 * * 6 /opt/backupscript/backupfull >> /opt/backupscript/oraclecrontab.log6 22 * * 0 /opt/backupscript/backupincr0 >> /opt/backupscript/oraclecrontab.log6 22 * * 1,2,3,4,5 /opt/backupscript/backupincr1 >> /opt/backupscript/oraclecrontab.log3 21 * * * du /opt/oradata/orcl/ >> /opt/backupscript/oracle_size---------------------------------------------------------------------------------------------------[oracle@dbm

今天测试了一下 stream之schema复制,如下:[@more@]source database是linux oracle10gtarget database是 window oracle11g1,配置前准备 global_names参数必须等于true 数据库必须为archive模式 查询global name的方法SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- ORACLE10G.COM.CN2,source database上执行 SQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m;Tablespace created.

网络上的等待事件[@more@]1.SQL*Net message from/to client2.SQL*Net more data from/to client3.SQL*Net message from/to dblink4.SQL*Net more data from/to dblink如果客户端和DBMS之间的网络存在通信问题，则容易产生1,2等待事件如果dbms和dbms之间的网络存在通信文件，则容易产生3,4等待事件sql执行次数过高时 执行次数过高时，oracle需要等待从客户端发送的相应。急发生sql*net message from client和sql*net more data from client

今天测试了一下oracle stream的table级复制:[@more@]1.source database SQL> alter database rename global_name to oracle10g;Database altered. SQL> show parameter global_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_names boolean TRUESQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m; Tablespace created. SQL&gt

今天测试了一下利用materialized view同步数据[@more@]source 端:SQL> create table tt (id int,name varchar2(20));Table created.SQL> insert into tt values(1,'x');1 row created.SQL> commit;Commit complete.在target 端:target端必须要有create materialized view,create database link的权限SQL> create materialized view tt_mv as select * from tt@oracle10g;Materialized view created.SQL> select * from tt@oracle10g; ID NAME---------- ---------------------------------------- 1 xSQL&g

今天测试oracle stream 创建database link 出现如下错误:[@more@]SQL> create database link test.com.cn connect to scott identified by oracle using 'test'; Database link created. SQL> select * from emp@test.com.cn; select * from emp@test.com.cn * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from TEST在网上baidu之后发现密码需要加上双引号。奇怪，为什么要加上呢????SQL> drop database link test.com.cn; Database link droppe

今天测试了一下将linux平台的oracle数据库迁移到windows平台下[@more@]1.准备pfile，将linux上的pfile copy到window下，修改相应的目录orcl.__db_cache_size=134217728orcl.__java_pool_size=4194304orcl.__large_pool_size=4194304orcl.__oracle_base='D:app'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=218103808orcl.__sga_target=411041792orcl.__shared_io_pool_size=0orcl.__shared_pool_size=247463936orcl.__streams_pool_size=8388608*._kghdsidx_count=4*.audit_file_dest='D:appadminorcladump'*.audit_tra