Oracle异常解决总结

Zephyr丶Syn

已于 2023-07-27 16:09:56 修改

阅读量1.4k

点赞数

文章标签： oracle 数据库

于 2020-08-20 09:15:07 首次发布

本文链接：https://blog.csdn.net/weixin_44624841/article/details/108115972

版权

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor；
ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的SID；

  问题：给程序配置新的数据库的时候，出现了以下错误：ORA-12505, TNS:listener does not currently know of SID given in connect descriptor；
  分析：说明给到的`SID`有误，很有可能就是`服务名`和`SID`的连接串(`url`)的写法有误。数据库url的写法有两种，第一种是监听`SID`，第二种则是监听`服务名`；
  解决：配管人员给的是服务名，我以为是SID，用了监听SID的写法，所以最后把orcl前的 “:” 改为 “/”后，数据库访问正常；
  监听sid服务，表示sid为orcl：database.url=jdbc:oracle:thin:@10.xx.46.xx:1521:orcl
  监听服务名，表示服务名为orcl：database.url=jdbc:oracle:thin:@10.xx.46.xx:1521/orcl

字符串截取函数：

  1、MySQL中按关键字截取字符串：substring_index(str, delim, count)
  	说明：substring_index(被截取字符串, 关键字, 关键字出现的次数)
  	测试：SELECT substring_index ( 'blog.jb51.net', '.', 1 ) AS substr;
  2、Oracle中等效的截取字符串函数：要达到和上面同样的截取结果，需要`SUBSTR(string, start, [length])`和`INSTR(string, child_string, [start], [show_time])`组合使用
  	语法：SUBSTR(string, start, [length])，截取字符串：
  		string：表示源字符串，即要截取的字符串。
  		start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。
  		length：可选项，表示截取字符串长度。
  	语法：INSTR(string，child_string，[start]，[show_time])，返回关键字在源字符串中的位置 ：
  		string：表示源字符串。
  		child_string：关键字，即要查找的字符串。
  		start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
  		show_time：可选项，表示关键字第几次出现在源字符串当中，默认第1次，负数则报错。
  	测试：`SELECT CASE WHEN INSTR( 'blog.jb51.net', '.', 1, 1 )>0 THEN SUBSTR( 'blog.jb51.net', 1, INSTR( 'blog.jb51.net', '.', 1, 1 ) - 1 ) ELSE 'blog.jb51.net' END AS substr FROM dual;`
  上面两条测试语句的运行结果相同。补充：使用CASE WHEN是考虑到源字符串不包含关键字以及关键字在首位的场景，在MySQL`substring_index`函数中，不包含关键字会返回源字符串，关键字在首位会返回空。

数据库常用命令：

  1、给予用户dba权限：
  	grant dba to base;
  2、给予用户链接权限：
  	grant connect to base;
  3、这个是给用户赋予登录的权限：
  	grant create session to base;
  4、给用户赋予表操作的权限：
  	grant create table to  base;
  5、给用户赋予表空间操作的权限：
  	grant unlimited tablespace to base;
  6、给该用户赋予访问任务表的权限：
  	grant select any table to base;
  7、同理可以赋予update和delete的权限：
  	grant update any table to base;
  	grant delete any table to base;
  8、修改表字段名称：
  	alter table 表名 rename column 现列名 to 新列名;

ORA-28040:No matching authentication protocol
navicat连oracle数据库报错（连接设置一切正常）

  错误原因：缺少对应oracle的oci或缺少对应oracle版本的oci
  解决方法：下载相应版本的oci文件

mybatis批量插入数据到oracle报“java.sql.SQLException: ORA-00933: SQL命令未正确结束”错误，批量插入使用的是insert into table(…) (select … from dual) union all (select … from dual)

  错误原因：mybatis批量插入oracle时需要显式指定为 useGeneratedKeys="false" 
  解决方法：可以在xml映射文件中指定，也可以在dao方法上指定(也可以不指定，默认的就是false)

Oracle日期计算方法：

  select sysdate+N from dual;
  sysdate+1 加一天
  sysdate+1/24 加1小时
  sysdate+1/(24*60) 加1分钟
  sysdate+1/(24*60*60) 加1秒钟
  加法：
  select sysdate,add_months(sysdate,12) from dual; --加1年
  select sysdate,add_months(sysdate,1) from dual; --加1月
  select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
  select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
  select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1小时
  select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
  select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
  减法
  select sysdate,add_months(sysdate,-12) from dual; --减1年
  select sysdate,add_months(sysdate,-1) from dual; --减1月
  select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期
  select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天
  select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1小时
  select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟
  select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒

修改表字段名称：

  MySQL：ALTER TABLE 表名 CHANGE 字段名 新字段名 数据类型;
  Oracle：ALTER TABLE 表名 RENAME COLUMN 字段名 TO 新字段名;

修改表字段长度：

  MySQL：alter table 表名 modify 字段 类型(新长度) comment '字段描述';
  Oracle：alter table 表名 modify(字段 类型(新长度));

修改表字段类型(主要针对Oracle的列有数据并不能直接修改类型的场景，例如varchar2转clob)：

  1、添加新字段：ALTER TABLE 表名 ADD 新字段 CLOB;
  2、复制旧字段数据：UPDATE 表名 SET 新字段 = TO_CLOB(旧字段);
  3、删除旧字段：ALTER TABLE 表名 DROP COLUMN 旧字段;
  4、修改字段名称：ALTER TABLE 表名 RENAME COLUMN 新字段 TO 旧字段;

修改数据库字符集

  >sqlplus
  >conn /as sysdba
  >输入口令:
  >shutdown immediate;
  >startup mount;
  >ALTER SESSION SET SQL_TRACE=TRUE;
  >ALTER SYSTEM ENABLE RESTRICTED SESSION;
  >ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
  >ALTER SYSTEM SET AQ_TM_PROCESSES=0;
  >ALTER DATABASE OPEN;
  >alter database character set ZHS16GBK;
  ORA-12712: new character set must be a superset of old character set RROR at line 1：结果报错，提示新字符集必须是老字符集的超集。
  于是强制转换：
  >ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
  >shutdown immediate;
  >STARTUP;
  >select userenv('language') from dual;

Oracle查询没有主键的表

  SELECT table_name FROM User_tables t WHERE NOT EXISTS (SELECT table_name FROM User_constraints c WHERE constraint_type = 'P' AND t.table_name = c.table_name)

ORA-02290: check constraint (NAMIBIAWEB.SYS_C0069520) violated：

  Oracle查询表约束：注意TABELNAME必须是大写
  	SELECT * FROM user_constraints WHERE table_name='TABLE_NAME';
  Oracle删除表约束：
  	ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME;

Oracle：jdbc获取sysdate的时间和plsql获取的时间相差12小时；

  SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;--获取数据库时区和session时区
  发现数据库时区是-4:00，而session时区是+8:00，修改数据库时区为+8:00后正常；
  alter database set time_zone='+8:00';--修改数据库时区，然后重启数据库

修改Oracle主键：

  删除已建好的主键：
  	alter table [表名] drop primary key --有主键无主键约束名的情形
  	alter table [表名] drop constraint [主键约束名] --有主键约束名的情形
  重新新建主键：
  	alter table [表名] add primary key ([字段名1],[字段名2],...)  --重建主键
  	alter table [表名] add constraint [主键约束名]  primary key ([字段名1],[字段名2],...) --重建主键约束
  查询主键约束名：
  	select * from user_cons_columns t where t.table_name = '[表名]'

Oracle锁表解决方法：

  1、锁表查询SQL：“SELECT object_name, machine, s.sid, s.serial# ,o.owner FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id　= o.object_id AND l.session_id = s.sid”;
  2、强制关闭进程：alter system kill session '1434, 2425'; -- 此处1434 和 2425 分别为上步查询的sid和serial；
  3、当出现'ORA-00030: 用户会话ID不存在'时，查询进程号：select distinct s.sid,s.serial#,p.spid as 系统进程号 from v$locked_object l , dba_objects o , v$session s , v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
  4、使用Xshell工具登录数据库服务器，执行如下命令：kill -9 系统进程号，系统进程号为上步查询的spid；

[INS-35075] The specified SID is already in use提示：

  原因：之前安装过Oracle，但是后来卸载不干净，下次重新安装时出现：[INS-35075] The specified SID is already in use。即指定的 Oracle 系统标识符 (SID) 已在使用；
  解决方法：
  	1、停止服务：
  	（1）win+r 打开运行窗口；
  	（2）输入 services.msc；
  	（3）停用所有Oracle开头的服务；
  	2、运行regedit，选择HKEY_LOCAL_MACHINE >SOFTWARE >ORACLE，按del键删除这个入口；
  	3、运行regedit，选择HKEY_LOCAL_MACHINE>SYSTEM>CurrentControlSet>Services，删除该路径下的所有以oracle开始的服务名称；

ORA-01861: literal does not match format string

  原因：插入日期数据未使用to_date转换格式；
  两种解决方法：
  	1、插入时使用to_date转换格式；
  	2、修改数据库nls_date_format='yyyy-mm-dd hh24:mi:ss'：
  	（1）在文件$ORACLE_HOME/sqlplus/admin/glogin.sql中加入：“alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';”，这样每个会话都会起作用；
  	（2）在会话级别运行命令：“alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';”，只在当前session会话起作用；
  	（3）修改数据库参数，需要重启数据库后生效：alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

查询重复的记录：

查找全部重复记录：
Select * From tableName Where (重复字段1,重复字段2,...) In (Select 重复字段1,重复字段2,... From tableName Group By 重复字段1,重复字段2,... Having Count(*)>1)

遇见问题继续补充…

Zephyr丶Syn

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Oracle异常解决总结

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor；ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的SID；问题：给程序配置新的数据库的时候，出现了以下错误：ORA-12505, TNS:listener does not currently know of SID given in connect descriptor分析：说明给到的SID有误，很有可能就是.
复制链接

扫一扫