oracle杂记

无效字符串

info_user中device_number为varcharvar2(64)类型。
select * from info_user where device_number = 17010411580;
报错：无效的字符。
select * from info_user where device_number = ‘17010411580’;
这样就没问题。
之前使用过类似的查询，就没有报错查原因：
1. oracle存在隐式转换：大类型向小类型转换（把char通过to_number转换为number）
2. to_number转换时如果字符串中出现非数字则会报错：无效字符。
3. 需要确定是否是数据存在问题（device_number中插入了非数字）。
4. select device_number, trim(translate(device_number, ‘0123456789’, ’ ‘)) from info_user where trim(translate(device_number, ‘0123456789’, ’ ‘)) is not null;查询获得device_number中存在非数字字符，修改数据，解决问题。

5. translate用法：translate(string, from_str, to_str)；replace的超集，对device_number中每个在from_str字符比较替换为to_str中对应位的字符，不足位的默认为null。

sqlplus连接Oracle

tns配置文件位置：$ORACLE_HOME/network/admin下

---

Oralce正则表达式

select * from crm_cust.info_auth_mobile where regexp_like(user_id, ‘[[:alpha:]]’);
sql正则表达式，匹配字段存在字符类型

---

查看表中重复数据

select serialno
  from cdr_cdr200_test a
 where exists (select 1
          from cdr_cdr200_test b
         where b.serialno = a.serialno
         group by a.serialno
        having count(serialno) > 1);

---

表空间查看

SELECT A.TABLESPACE_NAME,
       A.BYTES / 1024 / 1024 "TOTAL(M)",
       nvl(B.BYTES / 1024 / 1024, 0) "USED(M)",
       C.BYTES / 1024 / 1024 "FREE(M)",
       nvl(round((B.BYTES * 100) / A.BYTES, 2), 0) "% USED",
       round((C.BYTES * 100) / A.BYTES, 2) "% FREE"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)

select b.file_name 物理文件名,
       b.tablespace_name 表空间,
       b.bytes / 1024 / 1024 大小M,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name, b.file_name, b.bytes
 order by b.tablespace_name

---

导入导出

create user newdawntest identified by newdawntest;
grant dba to newdawntest;
exp BILLING/billing@NEWBILLING file=/home/billing_dev/lijy/db/db.dump full=y > daobiao.log &
nohup imp newdawntest/newdawntest@newbilling fromuser=billing touser=newdawntest file=db.dump > daoru.log &

---

oracle的jdbc连接方式: oci和thin

oci和thin是Oracle提供的两套Java访问Oracle数据库方式。
- thin是一种瘦客户端的连接方式，即采用这种连接方式不需要安装oracle客户端,只要求classpath中包含jdbc驱动的jar包就行。thin就是纯粹用Java写的ORACLE数据库访问接口。

- oci是一种胖客户端的连接方式，即采用这种连接方式需要安装oracle客户端。oci是Oracle Call Interface的首字母缩写，是ORACLE公司提供了访问接口，就是使用Java来调用本机的Oracle客户端，然后再访问数据库，优点是速度 快，但是需要安装和配置数据库。

连接数

• oracle连接数

  select count(1) from v$process; select * from v$process;

• 最大连接数

select * from v$parameter where name = ‘processes’;–value

Oracle死锁

死锁的原理

当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提
交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，
此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。

死锁的定位方法

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1. 用dba用户执行以下语句

select username, lockwait, status, machine, program
  from v$session
 where sid in (select session_id from v$locked_object)

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。
字段说明：
- Username：死锁语句所用的数据库用户；
- Lockwait：死锁的状态，如果有内容表示被死锁。
- Status： 状态，active表示被死锁。
- Machine： 死锁语句所在的机器。
- Program： 产生死锁的语句主要来自哪个应用程序。

1. 用dba用户执行以下语句，可以查看到被死锁的语句

   select sql_text
   from v$sql where hash_value in (select sql_hash_value from v$session
   where sid in (select session_id from v$locked_object))

死锁的解决方法

一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

1. 查找死锁的进程

   sqlplus “/as sysdba” (sys/change_on_install)
   SELECT s.username,
   l.OBJECT_ID,
   l.SESSION_ID,
   s.SERIAL#,
   l.ORACLE_USERNAME,
   l.OS_USER_NAME,
   l.PROCESS
   FROM V$LOCKED_OBJECT l, V$SESSION S
   WHERE l.SESSION_ID = S.SID;

2. 查看死锁表

   select p.spid,
   a.serial#,
   c.object_name,
   b.session_id,
   b.oracle_username,
   b.os_user_name
   from v$process p, v$session a, v$locked_object b, all_objects c
   where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;

3. kill掉这个死锁的进程

   alter system kill session ‘sid,serial#’;

其中sid=l.session_id
4. 如果还不能解决

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

其中sid用死锁的sid替换: exit

ps -ef|grep spid

其中spid是这个进程的进程号，kill掉这个Oracle进程

select A.SQL_TEXT,
       B.USERNAME,
       C.OBJECT_ID,
       C.SESSION_ID,
       B.SERIAL#,
       C.ORACLE_USERNAME,
       C.OS_USER_NAME,
       C.Process,
       '''' || C.Session_ID || ',' || B.SERIAL# || ''''
  from v$sql A, v$session B, v$locked_object C
 where A.HASH_VALUE = B.SQL_HASH_VALUE
   and B.SID = C.Session_ID

---

oracle列表横表转换

列表转横表

select *
  from router.code_meta
 where bill_class = 9
   and meta_name like 'GPRS_VPDN';

select meta_name,
       max(decode(attr_name, 'file_type', attr_value)) file_type,
       max(decode(attr_name, 'self_region_code', attr_value)) self_region_code,
       max(decode(attr_name, 'service_code', attr_value)) service_code
  from router.code_meta
 where bill_class = 9
   and meta_name like 'GPRS_VPDN'
 group by meta_name;

横表转列表

select name, sum(case kecheng when 'yuwen' then chengji end) yuwen,  
         sum(case kecheng  when 'shuxue' then chengji  end) shuxue  
from fzq  
group by name;  

---