1:查V$DB_OBJECT_CACHE SELECT * FROM V$DB_OBJECT_CACHE WHERE name='CUX_OE_ORDER_RPT_PKG' AND LOCKS!='0'; 注意:CUX_OE_ORDER_RPT_PKG 为存储过程的名称. 发现 locks＝2 2:按对象查出sid的值 select /*+ rule*/ SID from V$ACCESS WHERE object='CUX_OE_ORDER_RPT_PKG'; 注意:CUX_OE_ORDER
查锁表 select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as OBJ_NAME, -- 对象名称(已经被锁住) LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME, -- 子对象名称(已经被锁住) SESS_INFO.MACHINE as MACHINE, -- 机器名称 LOCK_INFO.SESSION_ID as SESSION_ID, -- 会话SESSION_ID SESS_INFO.SERIAL# as S
创建变长数组类型 ) ); 这个变长数组最多可以容纳两个数据,数据的类型为 varchar2(50) 更改元素类型的大小或精度 可以更改变长数组类型和嵌套表类型 元素的大小. ALTER TYPE varray_type MODIFY ELEMENT TYPE ) CASCADE; CASCADE选项吧更改传播到数据库中的以来对象.也可以用 INVALIDATE 选项使依赖对象无效 增加变长数组的元素数目 ALTER TYPE vrray_name MODIFY LIMIT CASCADE;
查每张表中有多少条记录 SELECT table_name,num_rows FROM user_tables order by num_rows desc; https://www.cnblogs.com/xzhg/p/6473011.html 查一张表中不同时间段分别有多少条记录: select ttime, count(ttime) from VOLTE_GT_CELL_ANA_BASEDAY group by ttime; select ttime,count(ttime) from V
-- 查找重复记录select names,num from test where rowid != (select max(rowid) from test b where b.names = test.names and b.num = test.num) 或者使用 select names,num from test where rownum!= (select max(rownum
1.查年度周末日期sql SELECT distinct TRUNC(TO_DATE('2019-01-01','yyyy-mm-dd')+ rownum,'iw')+ 5 AS sat, TRUNC(TO_DATE('2019-01-01','yyyy-mm-dd')+ rownum,'iw')+ 6 AS sunFROM dual where instr(to_char(sat),'2020')=0 AND instr(to_char(sun),'2020')=0CONNECT BY ro
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name,s.machine, s.terminal,a.sql_text, a.action FROM v$sqlarea a,v$session s, v$locked_object l WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address ORDER
--查询行锁语句 select sql_text from v$sql a,v$session b where a.sql_id=b.sql_id and b.event='enq: TX - row lock contention'; --找到被锁定的表 SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id = o.
reference:https://www.cnblogs.com/XQiu/p/5212787.html--以下几个为相关表SELECT * FROM v$lock;SELECT * FROM v$sqlarea;SELECT * FROM v$session;SELECT * FROM v$process ;SELECT * FROM v$locked_object;SELECT * FROM all_objects;SELECT * FROM v$session_wait;--查看被锁的表
一.查询表大小,块多少语句 Select SEGMENT_Name,BYTES,BLOCKS,Extents From dba_segments Where segment_name In('BAI_NEW_SESSION_HISTORY2') order by segment_name; (2)查表空间 Select Tablespace_Name, Sum(bytes)/1024/1024 From Dba_Segments group By Tablespace_Name (3)可以用输入
新入oracle数据库,把目前学到的知识点记录下来,可能都比较基础,但还是比较全的,里面的示例都是自己在PL/SQL中跑过的,如果有错误,还望各位大侠指出哈. 创建用户 1.创建用户(使用管理员身份创建,即用system进行登录) create user rick identified by ric account unlock; --创建一个用户名是rick的用户,密码是ric,用户状态未锁定 2.上诉创建后,如果直接用设置的用户名和密码进行登录,则会提示下面的错误 没有权限.png 3.可以