oracle - 随笔分类 - 琅琊小王

oracle--查询oracle比较慢的session和sql

摘要：1)查询最慢的sql select * from ( select parsing_user_id,executions,sorts command_type,disk_reads,sql_text from v$sqlarea order by disk_reads desc )where row 阅读全文

posted @ 2025-03-21 11:14 琅琊小王阅读(53) 评论(0) 推荐(0)

oracle--查询次数最多的SQL

摘要：select * from (select s.SQL_TEXT, s.EXECUTIONS "执行次数", s.PARSING_USER_ID "用户名", rank() over(order by EXECUTIONS desc) EXEC_RANK from v$sql s left join 阅读全文

posted @ 2025-03-21 11:11 琅琊小王阅读(31) 评论(0) 推荐(0)

oracle-查询执行最慢的SQL

摘要：select * from (select sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.EXECUTIONS "执行次数", round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间", round(sa.ELAPSED_TIME / 1000000 阅读全文

posted @ 2025-03-21 11:09 琅琊小王阅读(79) 评论(0) 推荐(0)

oracle-查询最占资源的SQL

摘要：select sql_text from v$sql where disk_reads > 1000 or (executions > 0 and buffer_gets / executions > 30000); 阅读全文

posted @ 2025-03-21 11:05 琅琊小王阅读(23) 评论(0) 推荐(0)

oracle 查询某个对象被引用得位置

摘要：select * from dba_dependencies where referenced_name='被引用的对象名' 阅读全文

posted @ 2025-03-21 11:01 琅琊小王阅读(9) 评论(0) 推荐(0)

PLSQL查询结果集中文乱码问题

摘要：问题描述：在plsql中执行查询语句时，查询出的结果中中文为乱码，如图：解决办法：查看服务器端编码 select userenv('language') from dual; 查询结果为: 查看本地字符集 select * from V$NLS_PARAMETERS; --查看本地字符集设置阅读全文

posted @ 2024-11-18 17:40 琅琊小王阅读(807) 评论(0) 推荐(0)

ORACLE存储过程

摘要：CREATE [OR REPLACE] PROCEDURE procedure_name ([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1] 阅读全文

posted @ 2024-10-29 11:37 琅琊小王阅读(32) 评论(0) 推荐(0)

ORACLE自定义函数

摘要：CREATE [OR REPLACE] FUNCTION 函数名称(arg1 [{IN | OUT | IN OUT}] TYPE1 [DEFAULT VALUE1], [arg2 [{IN | OUT |IN OUT }] TYPE2 [DEFAULT VALUE1]],……[argn [ { I 阅读全文

posted @ 2024-10-29 11:35 琅琊小王阅读(266) 评论(0) 推荐(0)

ORACLE创建定时任务

摘要：declare job binary_integer; begin dbms_job.submit(job => job, what => 'DATA_MIGRATION_TOOL2.INSERT_INV_SNAP;', next_date => sysdate ); -- 记得哦 commit; 阅读全文

posted @ 2024-10-29 10:30 琅琊小王阅读(24) 评论(0) 推荐(0)

数据库查询数据前20-30行的数据

摘要：oracle写法： select * from (select fu.display_name, row_number() over(order by fu.id) pageId from fnd_user fu) t where t.pageId between 20 and 30 mysql写法阅读全文

posted @ 2024-10-29 10:22 琅琊小王阅读(47) 评论(0) 推荐(0)

判断字段值是否为数字

摘要：SELECT * FROM tableName t where NOT REGEXP_LIKE(t.source_transaction_id, '^(+ [ 0 - 9 ] +) $') 备注说明：oracle 判断字段为非数字阅读全文

posted @ 2024-10-29 09:30 琅琊小王阅读(11) 评论(0) 推荐(0)

查看一个package是否在执行

摘要：select a.type, a.owner, b.SID, b.SERIAL#, b.OSUSER, b.MACHINE, b.PROGRAM, b.MODULE, b.ACTION From dba_ddl_locks a, v$session b where a.session_id = b. 阅读全文

posted @ 2024-10-29 09:26 琅琊小王阅读(31) 评论(0) 推荐(0)

oracle查询是否锁表以及解锁语句

摘要：--锁表语句 select t2.username, t2.sid, t2.serial#, t3.object_name, t2.OSUSER, t2.MACHINE, t2.PROGRAM, t2.LOGON_TIME, t2.COMMAND, t2.LOCKWAIT, t2.SADDR, t2 阅读全文

posted @ 2024-10-28 17:07 琅琊小王阅读(1479) 评论(0) 推荐(0)

嘻哈小王

公告

随笔分类 - oracle