ORACLE
老码伏枥驿站
这个作者很懒,什么都没留下…
展开
-
ORACLE将表中逗号分隔的字段内容拆分成多行的方法(列变行)
ORACLE将表中逗号分隔的字段内容拆分成多行的方法(列变行)拆分前的效果:拆分后的 效果:SQL语句:select * from ( select enbid, REGEXP_SUBSTR(v_list, '[^,]+', 1, lv) v_value from (select 736021 as enbid, '5,51' as v_list from dual union all select 186168 as enbid, ...原创 2020-10-28 17:37:02 · 5273 阅读 · 0 评论 -
oracle 把一张表中的某字段的多条记录用逗号连接,特别适用于记录个数不固定的表
方法一:有如下一张表。表table id c1 c2 1 1 小明 2 1 老张 3 2 小王 4 2 张三 5 2 李四 i 执行sql语句如下:SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ',')), 2) NAME FROM (SELECT c1, c2, rn, LEAD (rn) OVER (P...原创 2018-11-05 10:21:40 · 735 阅读 · 0 评论 -
Oracle取某字段最大的整行记录内容
select * from (select t.*, row_number() over (order by worklist_id desc) as rnum from table_name t where t.wf_serial_no='1342121') where rnum = 1;原创 2018-11-05 10:26:40 · 19399 阅读 · 0 评论 -
Oracle行列转换方法汇总
函数名:wmsys.wm_concat作用:以逗号分隔连接列的值oracle 10g引入。使用时直接用wm_concat也可以。以下介绍一些实际使用的状况。介绍实际Case前先建立一个测试table和一些测试数据。两张表: employee,prject;属性如下1)employee: 员工ID号,员工名,部门测试数据0001 user1 IT0002...原创 2018-11-05 10:29:55 · 529 阅读 · 0 评论 -
Oracle查询表空间使用情况
--查询表空间使用情况SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100...转载 2018-11-05 10:32:25 · 978 阅读 · 0 评论 -
Oracle获取本月第一天和最后一天及trunc()函数的用法
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') "本月第一天", to_char(last_day(sysdate), 'yyyy-mm-dd') "本月最后一天" --Oracle trunc()函数的用法 /**************日期********************/ ...原创 2018-11-05 10:34:10 · 1988 阅读 · 1 评论 -
Oracle取两个标识字符之间字符串的方法
select substr('10-10-20-11:bs_sweb16-cpu', instr('10-10-20-11:bs_sweb16-cpu', ':') + 1, instr('10-10-20-11:bs_sweb16-cpu', '-', -1) - instr('10-10-20-11:bs_sweb16-cpu', ':') - 1) from dual; selec...原创 2018-11-05 10:36:29 · 4000 阅读 · 0 评论 -
Oracle分割字符串转成表的方法
--分割字符串转成表(1)定义嵌套表类型CREATE OR REPLACE TYPE "MYTABLE" AS TABLE OF varchar2(100)(2)创建函数CREATE OR REPLACE FUNCTION split ...原创 2018-11-05 10:40:29 · 4245 阅读 · 0 评论 -
Oracle 回车换行符增加、删除方法
1、回车换行符chr(10)是换行符,chr(13)是回车,增加换行符 select ' update ' || table_name || ' set VALID_STATE =''0A'';'||chr(13)||' commit;' from user_tables 删除换行符select id,replace(content,to_char(chr(13))...原创 2018-11-05 10:43:13 · 2205 阅读 · 0 评论 -
Oracle查看数据库表空间大小(空闲、已使用),是否要增加表空间的数据文件
在数据库管理中,磁盘空间不足是DBA都会遇到的问题,问题比较常见。--1查看表空间已经使用的百分比Sql代码 select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-...原创 2018-11-05 10:46:52 · 1045 阅读 · 0 评论 -
ORACLE 使用在线重定义对非分区表进行分区
什么是分区表,分区表详解:http://langgufu.iteye.com/blog/1552042在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线...转载 2018-11-08 11:28:11 · 785 阅读 · 1 评论 -
Oracle 字符型时间相减取小时
select Round(To_Number(To_Date(replace(substr('2010-05-01 02:44:17', -8),':',''), 'HH24MISS') - To_Date(replace(substr('2010-05-01 00:07:00', -8),':',''), ...原创 2018-11-08 16:01:25 · 2599 阅读 · 0 评论 -
Oracle 判断日期是否合法自定义函数
CREATE OR REPLACE FUNCTION IS_DATE(str VARCHAR2) RETURN NUMBERIS tmpDT DATE;BEGIN tmpDT := TO_DATE(NVL(str, 'kk'), 'YYYY-MM-DD HH24:MI:SS'); RETURN 1; EXCEPTION ...原创 2018-11-08 16:04:58 · 4257 阅读 · 1 评论 -
Oracle 取随机37位字符串函数
Oracle中DBMS_RANDOM.STRING(var1,var2)这个函数有两个参数var1的参数值情况有:u,l,a,x,pu-->uppercase 返回全是大写的字符串l-->lowercase 返回全是小写的字符串a-->mixed case 返回大小写结合的字符串x-->uppercase,alpha&numeric 返回全是...原创 2018-11-08 16:06:43 · 822 阅读 · 0 评论 -
Oracle dbms_redefinition在线重定义表结构
刚接手一套系统应用数据库,因为项目建设期间种种原因,库是非归档模式也没有备份,更让我无语的是有个表增长的比较快,将近90G大小,每隔一段时间都要删除前三个月以前的数据,然后再用shrink收缩空间,因为是非分区表,shrink很是浪费时间,而且很多时间无法正常完成shrink操作,想将它改成分区表,同时业务部门又允许停数据库,只能利用ORACLE 10g的一个新特性,DBMS_REDEFINIT...转载 2018-11-08 20:45:06 · 706 阅读 · 0 评论 -
Oracle判断字段内容是否包含中文字符的方法
--方法一:使用asciistr函数得出是否字段里面包含“\”,但是需要注意一个特殊字符“\”,当它出现的时候转换后的码为“\005C”select t.* from work_events t where asciistr(t.descr) like '%\%'--方法二:使用length和lengthb,如果中文的话length是一个字符,但是lengthb是两个字节。但是这...原创 2018-11-07 13:45:10 · 6815 阅读 · 0 评论 -
Oracle在使用like中匹配下划线的方法
select * from tb_staff a where a.istaffid !='0'and a.istaffid not like '\_%' escape '\';原创 2018-11-07 13:42:03 · 3015 阅读 · 0 评论 -
Oracle自定义解析字符串函数split
create or replace function basedba.ac_get_splitstr(I_str IN VARCHAR2,I_num IN number) return varchar2 is str1 VARCHAR2(100); i integer; i1 integer;begin str1:=' ';beginif I_num=1 then ...原创 2018-11-04 15:05:36 · 809 阅读 · 1 评论 -
Oracle计算单个字符在字符串中出现的次数
计算单个字符在字符串中出现的次数:select length('HELLO WORLD!')-length(replace('HELLO WORLD!','L','')) from dual;--计算单个字符在字符串中出现的次数:select length('10-11-30-22:dbsaleinst1_138-FRA')-length(replace('10-11-30-22:dbs...原创 2018-11-04 15:09:13 · 5351 阅读 · 0 评论 -
oracle系统表大全
oracle系统表大全数据字典dict总是属于Oracle用户sys的。 1、用户: select username from dba_users; 改口令 alter user spgroup identified by spgtest; 2、表空间: select * from dba_data_files; select * from dba_tabl...原创 2018-11-04 15:13:59 · 156 阅读 · 0 评论 -
Oracle关于数据库锁处理的一些个人见解
/*用户锁,数据库的锁有的时候是比较耗费资源的,特别是发生锁等待的时候,我们必须找到发生等待的锁,有可能的话,杀掉该进程。这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。可以通过alter system kill session ‘sid,serial#’来杀掉会话*/SELECT /*+ rule */ s.usernam...原创 2018-11-04 15:20:12 · 201 阅读 · 0 评论 -
Oracle 获取当前周的星期一方法
SELECT CASE to_number(to_char(to_date('2010-01-11','yyyy-MM-dd'),'D')) WHEN 7 THEN to_date('2010-01-11','yyyy-MM-dd')-5 WHEN 6 THEN to_date('2010-01-11','yyyy-MM-dd')-4 ...原创 2018-11-04 15:23:00 · 5889 阅读 · 0 评论 -
Oracle日期函数大全
trunc(sysdate,'Q') --本季度第一天trunc(sysdate,'D') --本周的第一天(周日)查询Oracle日期格式 ----------------------------------select * from nls_database_parameters; 得到结果如下表: 表中NLS_DATE_FORMAT表示日期格式.PARAMETER V...原创 2018-11-04 15:25:56 · 246 阅读 · 0 评论 -
oracle sql 按某个字段分组然后从每组取出最大的一条纪录
oracle sql(按某个字段分组然后从每组取出最大的一条纪录)? 比如表 mo_partprg字段有:listid recid 1 1 1 2 2 3 2 4 2 5想得到的数据是 按照 listid 分组 然后取出每组...原创 2018-11-04 15:28:04 · 2200 阅读 · 0 评论 -
Oracle分割字符串返回数组函数
create type char_table is table of varchar2(2000);--创建自定义类型脚本create or replace function split_string(pi_str in varchar2, pi_separator in varchar2) --创建函数return char_table is v_char_table char_ta...原创 2018-11-04 15:30:39 · 2210 阅读 · 0 评论 -
Oracle中drop和delete误操作导致数据被删除的数据恢复方法
--删除了CUST表中的记录,用下面的语句恢复alter table cust enable row movementflashback table cust to timestamp to_timestamp('2008-03-1912:00:00','yyyy-mm-dd hh24:mi:ss')--删除了cust表,用下面的语句恢复flashback table cust to ...原创 2018-11-04 15:38:07 · 4309 阅读 · 1 评论 -
Oracle 合计统计sql语句
select decode(grouping(DATA_ID), 1, '合计', DATA_ID) as DATA_ID, sum(H1) as H1, sum(H2) as H2 from (select DATA_ID, H1, H2 from TB_REPORT_f302050700 where DATA_ID betw...原创 2018-11-05 10:16:54 · 5142 阅读 · 0 评论 -
Oracle中exp,imp的使用详解
基本语法和实例: 1、EXP: 有三种主要的方式(完全、用户、表) 1、完全: EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y 如果要执行完全导出,必须具有特殊的权限 2、用户模式: EXP SONIC/SONIC ...原创 2018-11-07 13:22:27 · 266 阅读 · 0 评论 -
Oracle中函数返回数据集的方法(拆分表字符串函数,返回数据集)
--1、创建表结构对象/*创建表结构类型 */CREATE OR REPLACE TYPE TY_TABLE_TYPE AS OBJECT ( key_column VARCHAR2 (1000), split_column VARCHAR2 (1000));--2、定义嵌套表类型/*创建嵌套表类型*/CREATE OR REPLACE TYPE TY_TABLE_LIST A...原创 2018-11-07 13:27:39 · 1578 阅读 · 0 评论 -
Oracle查看数据库是否归档和修改归档模式
Oracle分为非归档模式(NOARCHIVELOG) 和归档模式(ARCHIVELOG)。非归档模式不产生归档日志,虽然节省了硬盘空间,但是备份方案选择很有限,通常只能选择冷备份。还原也只能还原到备份那一时刻的数据,通常也仅在开发时使用(据说在数据仓库中也使用),Oracle安装默认就是非归档模式。在生产环境中我们因该使用归档模式,它会产生归档日志,可以使用多种备份和还原方案,对与Oracle管...原创 2018-11-07 13:32:51 · 2333 阅读 · 1 评论 -
Oracle取月份-不带前面的0
今天碰到只要取月份和天数,如果月份前面有0要去掉0.比如说2010-01-08 ,需要的结果是1-8. 引出了一系列的sql语句 第一: 利用to_number的函数转换自动截0 select to_number(to_char(sysdate,'mm'))||'-'||to_number(to_char(sysdate,'dd')) from dual; 第二: 利用ltrim函数加固定参数...原创 2018-11-07 13:38:08 · 734 阅读 · 0 评论 -
ORACLE树查询学习
昨天CSDN里处理问题的时候,发现了一个语法connect_by_root,眼前一亮,好像没有见过,经常用的只是connect by ,于是翻开文档重新学习了下先看自带的SCHEMA的SCOTT的数据:SQL>SELECT e.empno,e.ename,e.mgr,e.deptno FROM emp e;EMPNO ENAME MGR DEPTNO7369 SMITH...原创 2018-11-04 15:02:25 · 209 阅读 · 0 评论