我的Oracle问题集锦

问题一 ORA-01036: illegal variable name/number

当使用 controlparamter 时, sql 所使用的　为 “@parameter” , 但套用到 Oracle 则会出現 “ORA-01036: illegal variable name/number” 错误, 正确使用方法为":paramter"参数不对应。所插入的值与数据库里的数据类型不对应。

问题二ORA-00923: FROM keyword not found where expected

要么 form 漏了 要么前面 select 的地方少个逗号

问题三ORA-00911: invalid character错误

sql语句后面有分号”;”由于客户端oracle字符集和服务器端oracle字符集不匹配,在sql语句中存在中文表或中文字段，例如 select 1 as 测试 from dual 时，也会出现这个错误。

问题四 彻底删除表空间和用户（包括用户创建的所有东西）

drop tablespace xxx including contents and datafiles;
drop user xxx cascade;

问题五 程序查询中文没有数据

在连接字符串加一个Unicode=True;如：
"<add name=“CONN_STRING” connectionString=“data source=127.0.0.1/orcl;User Id=test;Password=test;Unicode=True;” ">

问题六 尝试加载 Oracle 客户端库时引发 BadImageFormatException。

http://www.cnblogs.com/joey0210/archive/2012/08/15/2640248.html
公司电脑是win7 64位旗舰版，刚来时配的新电脑，4G+i5这个配置不装64位那算是浪费了。谁知道一切装完毕后，打开项目却连不上oracle数据了…
首先是pl/sql连不上，我在本地装了个oracle 11g r2 64位的。装了oracle服务器，应该是不用再装客户端，pl/sql应该就能连上的，应为oracle服务器版本自带了客户端的那些管理工具和功能。但是pl/sql一直报个错啊！大概意思是说找不到32位的oci.dll文件之类的错误，网上一搜，说pl/sql装在64位系统上，要连64位的数据库，还得装个32位的客户端。而我又不想再去装个客户端了，感觉太大了，再说装了oracle数据库，应该是不用再装客户端了的。无奈只好下了个IntantClient（轻量级客户端包）。下面说说这个IntantClient（轻量级客户端包）与传统的Oracle Client 的区别：
instanct client 分为几个包，其中basic基本只有oci库文件
其它常见的包还有sqlplus，jdbc等，都运行于basic包之上。
而
Oracle Client不光包括instant client，还包括很多工具。
从10G开始,Oracle提供了一个较为轻量级的客户包，叫做Instant Client. 将它安装好后，就不用再安装庞大的Oracle Client了.
这样一来，只要客户端下载Instant Client，直接解压，设置Path就ok了。
具体设置如下：
1、下载32位Oracle InstantClient
2、将Oracle InstantClient解压到某目录
3、打开PL/SQL，在perference里面设置OCI Library和Oracle_home，例子如下：
OCI Library=D:\instantclient_11_2\oci.dll （刚刚InstantClient的解压目录的OCI.DLL文件）
Oracle_home=D:\instantclient_11_2 （刚刚InstantClient的解压目录）
4、设置环境变量(修改NLS_LANG和TNS_ADMIN环境变量)
对于NLS_LANG环境变量, 最好设置成和数据库端一致, 首先从数据库端查询字符集信息:
SQL> select userenv(‘language’) nls_lang from dual;
NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
右击"我的电脑" - “属性” - “高级” - “环境变量” - “系统环境变量”:
1>.（此项可以不用）点击"新建", 变量名设置为"TNS_ADMIN", 变量值设置为"D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN", 点击"确定"; （64位ORACLE的TNSNAME.ORA文件的目录）
2>.点击"新建", 变量名设置为"NLS_LANG", 变量值设置为"AMERICAN_AMERICA.ZHS16GBK", 点击"确定"; （数据库字段集）
最后点击"确定"退出.
至此，重新登陆PLSQL就可以用了
好。pl/sql 是可以登录了，但是咱asp.net程序还是连不上啊！到connection.open()的时候，就报：“尝试加载 Oracle 客户端库时引发 BadImageFormatException。如果在安装 32 位 Oracle 客户端组件的情况下以 64 位模式运行，将出现此问题。“。这个大概是说我asp.net 程序不应该是用32的客户端去访问64位的数据库，我是这样理解的。但是我装了64的oracle 不就相当宇装了64位的客户端吗？装的那个轻量级的32位客户端只是方便pl/sql来访问oralce数据库的。这个问题网上给出了很多回答。大多是，改项目的属性里的生成选项里的目标平台：改为x86.意识是把项目当成32位运行。每个项目都照着改了，还是不管用。网上还有说，把网站项目属性里的web项里的服务器改成iis不使用vs自带的web deleloper ment 服务，应为它的进程为webDe.webServer.exe 是一个32位的进程，反正说了很多。这个方法还是可以。你改完后，项目确实可以运行，并访问oracle64位数据库了。但是我个人总觉得这种方法很麻烦。而且这样你的本地iis里在你每建一个访问oracle数据的项目时，iis里就会多一个项目，而且这样也不稳定时常会报一些奇怪的错误。最后本来打算装回32位系统的，后边在网上逛逛，自己静下心来想想…
最后我发现是不是那个轻量级的包不行啊！最后还是把那个32位的oracle client安装包执行安装了下，安装时选的管理员。而且以前遇到过asp.net连不上oracle，是oracle安装文件夹的权限问题，所以我又对oracle所在的文件夹加netservices用户完全控制的权限。再重启， OK!搞定，程序居然能够连上了。。。。其实我自己也不知道是哪里的问题，到底是那个轻量级的包不行呢！还是那个权限问题…总之这样就好了。就此记录一下
2013-07-07 Note:今天还是同样的问题，经过再次的实验，证明是那个 轻量级的客户端包不行，一定要是那个安装版的，安装版的安装后的目录里面比那个轻量包解压后的目录里面要多出很多文件，由此可见，那个轻量包里面的oci.dll文件解决 pl/sql 连64位oracle的问题可以，但是咱C#程序要连64位oracle，还是得装那个安装版的客户端，可以不用装Administrator选项，默认客户端选项也就一百多兆。
客户端下载点这里。

问题七 oracle查询时，判断是否为空，给为空的默认值

使用NVL(sum(ID),0)

问题八 oracle连接不上 报ORA-12514

ORA-12514：TNS：监听程序当前无法识别连接描述符中请求的服务。
改了好几个参数都没用，后来找到了解决方法，请先把与之相关的服务全部停掉，如sde、arcims、server等，具体操作步骤说明如下：
1、删除以前的oracle实例
c:>oradim -delete -sid wxgis
2、重建oracle实例
c:>oradim -new -sid wxgis -startmode auto -pfile d:\oracle\product\10.2.0\db_1\database\initwxgis.ora
3、登陆sqlplus
c:>sqlplus /nolog
sql>conn /as sysdba
sql>startup upgrade
4、运行catupgrd.slq
sql>spool upgrade.log
sql>@d:\oracle\product\10.2.0\db_1\rdbms\admin\catupgrd.sql
5、这个执行时间很长，在我的服务器上大约执行了2小时
执行完成会有提示输入字符串的最大数，不关，直接回车
sql>spool off
6、重新启动oracle
sql>shutdown immediate
sql>startup pfile=d:\oracle\product\10.2.0\db_1\database\initwxgis.ora
7、至此数据库重新启动，请重启监听服务
检查是否有无效对象
sql>@d:\oracle\product\10.2.0\db_1\rdbms\admin\ultrp.sql
检查是否有无效对象
select count(*) from dba_objects where status=‘INVALID’
如果值大于0则说明有错误，就要重新执行
sql>@d:\oracle\product\10.2.0\db_1\rdbms\admin\catupgrd.sql，知道没有错误

问题九 oracle重置密码

一、普通用户
1、打开运行，输入
sqlplus /nolog
2、打开了Oracle的控制台，输入
conn /as sysdba; 会提示已连接
已连接
3、然后就像修改普通用户那样修改密码了

alter user system identified by password; 

//password是自己要设置的密码,最好以字母开头
二、系统用户
运行，cmd
sqlplus /nolog;（也可能不要分号）

connect  /  as  sysdba   
alter   user  sys identified  by  新密码;   
alter   user  system identified  by  新密码;  

如果提示
ORA-01031: insufficient privileges
检查sqlnet.ora文件里
SQLNET.AUTHENTICATION_SERVICES= (NTS)
括号里的如果不是NTS，就改成NTS

查询Oracle密码有效期和当前用户信息

select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
SELECT username,PROFILE FROM dba_users;
select * from user_users;

问题十 创建oracle数据库时提示权限访问被拒绝

1、windows有可能是登录用户不是管理员，已管理员用户运行（有可能管理员用户被禁用了）
2、windows有可能打开的程序不是Database Configuration Assistant

问题十一 oracle查询数据保留几位小数

1、取四舍五入的几位小数

select round(1.2345, 3) from dual;

结果:1.235
2、保留两位小数，只舍

select trunc(1.2345, 2) from dual;

结果:1.23

select trunc(1.2399, 2) from dual;

结果:1.23
3、取整数
返回大于或等于x的最大整数:

 select   ceil(23.33)   from  dual;  

结果: 24

问题十二 oracle查询时格式化数字

1、The following are number examples for the to_char function.
　　to_char(1210.73, ‘9999.9’) would return ‘1210.7’
　　to_char(1210.73, ‘9,999.99’) would return ‘1,210.73’
　　to_char(1210.73, ‘$9,999.00’) would return ‘$1,210.73’
　　to_char(21, ‘000099’) would return ‘000021’
2、to_char函数特殊用法
　　to_char(sysdate,‘d’) 每周第几天
　　to_char(sysdate,‘dd’) 每月第几天
　　to_char(sysdate,‘ddd’) 每年第几天
　　to_char(sysdate,‘ww’) 每年第几周
　　to_char(sysdate,‘mm’) 每年第几月
　　to_char(sysdate,‘q’) 每年第几季
　　to_char(sysdate,‘yyyy’) 年
　　比如要找某个时间为每周第几天就可以

SQL> select to_char(to_date('20070101','yyyymmdd'),'d') from dual;

问题十三返回要截取的字符串在源字符串中的位置。

1、使用instr函数
在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。
语法如下：instr( string1, string2 [, start_position [, nth_appearance ] ] )
string1 源字符串，要在此字符串中查找。
string2 要在string1中查找的字符串.
start_position 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
注意：
如果String2在String1中没有找到，instr函数返回0.
应用于：
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
举例说明：

select instr('abc','a') from dual;    -- 返回 1 
select instr('abc','bc') from dual; -- 返回 2
select instr('abc abc','a',1,2) from dual; -- 返回 5
select instr('abc','bc',-1,1) from dual; -- 返回 2
select instr('abc','d') from dual; -- 返回 0

问题十四 bat文件调用sqlplus执行sql脚本不自动退出

在sql脚本后面增加quit命令或者exit命令

问题十五 oracle dmp文件导入，不是有效的导出文件, 头部验证失败

将oracle 11g导出的dmp文件导入到oracle10g
1、进行导入表操作，提示：
IMP-00010: 不是有效的导出文件, 头部验证失败
IMP-00000: 未成功终止导入
2、从网上查阅，oracle数据11g到10g是有问题的，一般由oracle10g客户端去连oracle11g进行导出数据操作然后导入到oracle10g，如果手中只有oracle11g的dmp文件，怎么办
3、从网上查阅，头部验证失败是由于版本号不同所致，经试验可以通过如下方法进行修改：
用notepad++工具打开dmp文件， 可以看到头部信息 －－TEXPORT:V11.01.00，即为源数据库的版本号，将其修改为目的数据库的版本号，如本机为10.02.01
4、再次进行导入操作，导入成功

问题十六 去除用户的DBA权限

1、Sqlplus  “/as sysdba”;
2、revoke dba from <user>;
3、exit; 

问题十七 oracle设置自动增长

create sequence SEQ_TESTTABLE_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache
order;
CREATE OR REPLACE TRIGGER trg_TESTTABLE_id
BEFORE INSERT ON TESTTABLE
FOR EACH ROW
BEGIN
SELECT  seq_TESTTABLE_id.nextval INTO :new.id FROM dual;
END;

问题十八 Oracle创建查询一张表数据（带参）的package

create or replace package pkg_SelectData as
     -- 定义ref cursor类型
     type sData is ref cursor;
     --函数申明
     function getData(
        link1 in varchar2,
        link2 in varchar2,
        link3 in varchar2,
        link4 in varchar2,
        link5 in varchar2,
        date1 in varchar2,
        date2 in varchar2
        ) return sData;
   end pkg_SelectData;
   
CREATE OR REPLACE package body pkg_SelectData as
     --此处函数头跟上面函数申明一致
      function getData(
        link1 in varchar2,
        link2 in varchar2,
        link3 in varchar2,
        link4 in varchar2,
        link5 in varchar2,
        date1 in varchar2,
        date2 in varchar2
        ) return sData
      IS
        return_cursor  sData;
      BEGIN
        OPEN return_cursor FOR 
             select tag_code,trendvalue,trendtime from data_store where tag_code IN (link1,link2,link3,link4,link5) 
             and trendtime > to_date(date1,'yyyy-mm-dd hh24:mi:ss')  and trendtime < to_date(date2,'yyyy-mm-dd hh24:mi:ss');
       return return_cursor;
     END getData;
   end pkg_SelectData;
SELECT pkg_SelectData.getData('LINK1001','','LINK1002','LINK1003','LINK1001','2015-01-01 00:00:00','2015-02-01 00:00:00') FROM dual;

问题十九 Oracle更改登录不区分大小写

请输入用户名: SYSTEM
输入口令:
连接到:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> show 　parameter sec_case_sensitive_logon
NAME TYPE VALUE
sec_case_sensitive_logon boolean TRUE
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
系统已更改。
SQL> show 　parameter sec_case_sensitive_logon
NAME TYPE VALUE
sec_case_sensitive_logon boolean FALSE
SQL>

问题二十 提示msvcr71.dll找不到

拷贝msvcr71.dll文件到Oracle客户端目录instantclient_10_2下

问题二十一 密码过期及设置密码永不过期

1、查看用户的proifle是哪个，一般是default：

SELECT username,PROFILE FROM dba_users;

2、查看指定概要文件（如default）的密码有效期设置：

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

3、将密码有效期由默认的180天修改成“无限制”：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

修改之后不需要重启动数据库，会立即生效。
4、修改后，还没有被提示ORA-28000警告的用户不会再碰到同样的提示；
已经被锁定的帐户必须解除锁定，举例如下：
$sqlplus / as sysdba

 alter user smsc identified by oracle account unlock;

5、修改后default profile应该如下：

select * from dba_profiles WHERE dba_profiles.profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

修改之后不需要重启动数据库，会立即生效。
如果出现ORA-28000 the account is locked.错误

alter user 用户名 account unlock;

查询用户密码到期时间

select expiry_date from dba_users
where username='XXX'

问题二十二提示System.Data.OracleClient 需要Oracle 客户端软件 8.1.7 或更高版本

1、 在客户端目录下增加Oracle.DataAccess.dll
2、 在客户端目录上增加network service和Authenticated Users权限
3、 以上不行 IIS重启 IISRESET
4、 IIS需要改为32位

问题二十三Win10 安装Oracle 19c 点击安装没有反应，安装窗口一闪而过

原因：Oracle19c的存放路径有空格或者中文
解决方法：去掉空格或者中文，就可以正常安装了。

问题二十四c#连接oracle中文乱码(insert和update会乱码,select不会)需要在连接字符串中加入”Unicode=True”，但是在执行存储过程时,有”Unicode=True”会报错,但是去掉这句的话，在插入数据时又会乱码或查不到数据。

原因：字符集问题
解决方法：去掉” Unicode=True”，在代码中增加环境变量语言设置：
中文：
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
AMERICAN_AMERICA.WE8ISO8859P1

System.Environment.SetEnvironmentVariable("NLS_LANG, "SIMPLIFIED CHINESE_CHINA.ZHS16GBK",EnvironmentVariableTarget.Process);

System.Environment.SetEnvironmentVariable(“NLS_LANG”, "SIMPLIFIED CHINESE_CHINA.ZHS16GBK");

问题二十五远程连接oralce失败，网络无限制，Telnet失败。

可能原因：监听设置问题
解决方法： 找到监听目录（如：D:\oracle_db_home\network\admin），将监听目录中的IP地址换为主机名，如下：将127.0.0.1改为WIN-ORACLE(根据实际主机名来设置)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

还可以在Oracle服务器进入CMD 输入netstat -an 查看1521端口
一般可以远程连接的是0.0.0.0:1521

问题二十六查询oralce所有用户密码有效期及修改密码有效期。

oracle查询数据库密码哪一天过期，其中expiry_date即是过期的时间

select username,account_status,expiry_date,profile from dba_users;

查询数据库密码有效期

select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

修改数据库密码无限期

alter profile default limit password_life_time unlimited;

修改数据库密码180天

alter profile default limit password_life_time 180;