行云无鸣

2011-04-30

在Mac OS X 10.6.7上安装Oracle 10g

Filed under: 乱语 — 标签:, — hellyguo @ 00:07

安装成功!
步骤是按这篇blog来的
补充如下:
在oracle用户下能使用后,尚需要一条命令将自己的用户加入oinstall组,否则,自己的用户就算设置了$ORACLE_HOME等也无法调用sqlplus,报权限不足:

dscl . -append /Groups/oinstall GroupMembership [username]

2010-10-11

JDBC连接报ORA-12505,sid无法找到

Filed under: 乱语 — 标签:, , , , — hellyguo @ 16:24

用JDBC连接ORACLE

jdbc:oracle:thin:@192.168.10.111:1521:ora10g

报错

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
192.168.10.111:1521:ora10g

用下面这个串就是可以的

jdbc:oracle:oci8:@(description=(address=(host=192.168.10.111)(protocol=tcp)(port=1521))(connect_data=(service_name=ora10g)(server=DEDICATED)))

最后确认,该服务器是RAC,如果单联,需要用

jdbc:oracle:thin:@192.168.10.111:1521:ora10g1

or

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.111)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.112)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ora10g)))

over.

2010-06-24

[转]Oracle中to_char函数处理数字时前面的空格问题

Filed under: 乱语 — 标签:, — hellyguo @ 17:41

自打我用to_char来转换数字就发现了这个问题,即转换结果字符串最前面多了一个空格。我一直用trim再处理一下,没有关注过原因。今天上网查了一下,看到了一个说法及解决方案。

Oracle to_char():数字转字符串,结果添加空格:
select to_char(12,’00’) from dual
字符串是 ‘ 12′,前面多了一个空格,
select length(to_char(12,’00’)) from dual
返回结果是:3
返回的字符串总是前面有个空格。原因:
那个空格位置是放符号的,正的数字就空了,负的就是一个‘-’号而没有空格。
这是网上流传的原因:

FM

Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function:

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.

In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.

Track back

解决办法:
select trim(to_char(12,’00’)) from dual
或者
select to_char(12,’fm00′) from dual

总结:
select to_char(12,’00000′) from dual 结果为:
00012(1个空格)
select to_char(12,’99999′) from dual 结果为:
12(4个空格)
select to_char(-12,’00000′) from dual 结果为:
-00012(无个空格)
select to_char(-12,’99999′) from dual 结果为:
-12(3个空格)

from:url

2009-07-22

linux下sqlplus去除^H

Filed under: 未分类 — 标签:, — hellyguo @ 17:16

在环境变量中加入

stty erase ^H

2009-05-17

在debian 5下安装oracle xe 10g的经过

Filed under: 未分类 — 标签:, , , , , — hellyguo @ 23:56

下载oracle xe版本
强制安装
安装libaio
换32版本,强制安装
安装gtko2
设置oracle环境
启动oracle
报错ora-00205,创建spfile
报错ora-00205,查看init.ora
发现控制文件没有出现在oradata
在bin发现createdb.sh
执行createdb.sh

2009-04-17

oracle10g无法shutdown

Filed under: 未分类 — 标签:, , — hellyguo @ 12:06

在测试环境下,咱的oracle10g需要重启。

登录进去shutdown immediate,结果半天没反应。

不得已,在网上搜索,说是有可能是10g低版本的bug,查到可以以sysdba登录后,shutdown abort来强制停止。

果然,可以。

为了防止出现问题。就再startup后用shutdown immediate停止了一次再正常启动。

2008-11-06

系统运行中报:Resource temporarily unavailable

Filed under: 未分类 — 标签:, , , — hellyguo @ 16:43

2008-11-06 12:49:50 [ERROR] [org.springframework.transaction.CannotCreateTransactionException][Could not open JDBC Connection for
transaction; nested exception is java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153094144)(ERR=12500)
(ERROR_STACK=(ERROR=(CODE=12500)(EMFI=4))(ERROR=(CODE=12540)(EMFI=4))(ERROR=(CODE=12560)(EMFI=4))(ERROR=(CODE=510)(EMFI=4))(ERROR=(B
UF=’IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable’))))DSRA0010E: SQL 状态 = null,错误代码 = 17,002DSRA0010E:
SQL 状态 = null,错误代码 = 17,002]

初步判断是系统资源不足,查看系统:

#ulimit -a
time(seconds) unlimited
file(blocks) 2000
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

果然,打开文件数有限制
修改之

#ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

2007-11-29

遭遇ora-00600错误

Filed under: 未分类 — 标签:, — hellyguo @ 21:09

死机,就重启了笔记本

结果开机后启动oracle报:

ORA-00600: 内部错误代码,参数: [kcratr1_lostwrt], [], [], [], [], [], [], []

Google了下,按这篇文章解决了

记录如下:

c:\>sqlplus

SQL*Plus: Release 9.2.0.1.0 – Production on 星期四 11月 29 21:00:00 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

请输入用户名: /as sysdba

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

SQL> startup
ORA-01081: 无法启动已在运行的 ORACLE — 请首先关闭
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
ORA-00600: 内部错误代码,参数: [kcratr1_lostwrt], [], [], [], [], [], [], []

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL> recover database
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production中断开

%d 博主赞过: