![](https://img-blog.csdnimg.cn/20201014180756919.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
Oracle
早起的菜鸟wzt
这个作者很懒,什么都没留下…
展开
-
Oracle raw类型数据转成varchar2
将raw类型数据转成varchar2的函数create or replace function func_conversion(src_val in varchar2) return VARCHAR2 IS rtn_utf_val VARCHAR2(2000 CHAR); rtn_big5_val VARCHAR2(2000 CHAR); err_num NUMBER; err_msg VARCHAR2(1000);BEGIN BEGIN SEL原创 2021-07-16 11:18:31 · 723 阅读 · 0 评论 -
Oracle自定义聚合函数 拼接超长字符串
Oracle拼接字符串是由长度限制的,可以自定义聚合函数来解决这个问题create or replace TYPE T_LINK AS OBJECT ( STR VARCHAR2(32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARC原创 2021-07-16 11:12:11 · 1120 阅读 · 0 评论 -
Oracle查找乱码的数据
SELECT COL_NAMEFROM table_name WHERE ASCIISTR(COL_NAME) LIKE '%\FFFD%' OR COL_NAME IS NOT NULL AND ASCIISTR(COL_NAME) IS NULLOR ASCII(SUBSTR(ASCIISTR(COL_NAME),1,1)) BETWEEN 2 AND 31OR LENGTHB(COL_NAME)>LENGTHB(ASCIISTR(COL_NAME));原创 2021-07-15 11:15:42 · 2353 阅读 · 2 评论 -
Oracle生成data patching脚本
生成data patching脚本DECLARE V_SQL CLOB; V_SUB_STR VARCHAR2(32767); V_OWNER VARCHAR2(30) := 'schema'; --' V_TABLE_NAME VARCHAR2(30) := 'table_name'; --' V_COUNT INTEGER := 0; V_TEMP_CNT INTEGER; V_PK_NUM原创 2021-07-15 10:57:06 · 174 阅读 · 0 评论 -
Oracle查看表中是否有中文字符
--查看表中是否有中文字符DECLARE V_SQL CLOB; TYPE T_TABLE_ARRAY IS VARRAY(50) OF VARCHAR(50); TABLES T_TABLE_ARRAY := T_TABLE_ARRAY('table_name'); V_OWNER VARCHAR2(30) := 'schema'; -- V_RESULT INTEGER; BEGIN FOR I IN 1 .. TABLES.COUNT LOOP原创 2021-07-15 10:56:46 · 483 阅读 · 0 评论 -
Oracle aes128和aes256加解密
–aes128加密DECLARE l_src_data VARCHAR2(20) := 'Source Data'; l_type pls_integer := dbms_crypto.encrypt_aes128 + dbms_crypto.pad_pkcs5 + dbms_crypto.chain_cbc; l_key VARCHAR2(16) := '0123456789123456'; l_encval raw(2000);BEGIN l_encval :原创 2021-07-15 10:56:15 · 1331 阅读 · 0 评论 -
Oracle查看正在执行的sql及锁住的表
–查看正在执行的sqlSELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名FROM Gv$process a, Gv$session b, Gv$sqlarea cWHERE a.addr = b.paddr --and b.username='原创 2021-07-15 10:55:58 · 550 阅读 · 0 评论 -
Oracle批量生成拉链表trigger
DECLARE V_SQL CLOB; V_SUB_STR VARCHAR2(32767); TYPE T_TABLE_ARRAY IS VARRAY(50) OF VARCHAR(50); TABLES T_TABLE_ARRAY := T_TABLE_ARRAY('table_name'); V_OWNER VARCHAR2(30) := ''; --' V_TAB_SPACE VARCHAR2(30) := ''; -- V_ROLE原创 2021-07-15 10:55:43 · 299 阅读 · 0 评论 -
Oracle生成日期列表
SELECT TO_CHAR(TO_DATE('20190401', 'YYYYMMDD') + ROWNUM - 1,'YYYYMMDD') AS DATE_LIST FROM DUALCONNECT BY ROWNUM <= TRUNC(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - TO_DATE('20190401', 'YYYYMMDD')) + 1;原创 2021-07-15 10:57:22 · 1299 阅读 · 0 评论 -
Oracle查看SYS_LOB*文件属于哪个表
查看SYS_LOB*文件属于哪个表SELECT B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME, a.SEGMENT_TYPE, ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G FROM DBA_SEGMENTS A LEFT JOIN DBA_LOBS B ON A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.原创 2021-07-14 16:26:51 · 2320 阅读 · 0 评论 -
Oracle表空间管理
查看表空间剩余空间SELECT SUM(bytes) / (1024 * 1024 * 1024) AS free_space, tablespace_nameFROM dba_free_spacewhere TABLESPACE_NAME = 'tablespace name' GROUP BY tablespace_name;SELECT a.tablespace_name as tablespace_name,to_char(b.total/1024/1024/1024,999999.99原创 2021-07-14 16:26:23 · 114 阅读 · 0 评论 -
Oracle存储过程模板
create or replace procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); 变量2 类型(值范围); Begin Select count(*) into 变量1 from 表A where列名=param1; If (判断条件) then Select 列名 into 变量2 from 表A where列名=param1; Dbms_output。Put_li原创 2021-07-14 16:02:58 · 262 阅读 · 0 评论 -
Oracle查询某些字段的重复记录及删除
查询某些字段的重复记录select a.col1,a.col2 from tablename a, (select col1,col2 from tablename group by col1,col2 having count(*)>1) b where a.col1=b.col1 and a.col2=b.col2–保留最新的一条DELETE FROM TABLE_NAME WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM TABLE_NAME D原创 2021-07-14 16:01:10 · 182 阅读 · 0 评论