ORACLE常用脚本

--批量授予权限,授予用户chenmh拥有zhang下所有表的增删改查权限。生产批量执行sql
SELECT 'GRANT SELECT,DELETE,UPDATE,INSERT ON '||OWNER||'.'||TABLE_NAME||' TO CHENMH;' FROM dba_tables WHERE OWNER='ZHANG';

--批量收回系统权限，收回用户chenmh的所有系统权限
SELECT 'REVOKE '||PRIVILEGE||' FROM CHENMH;'  FROM DBA_SYS_PRIVS WHERE GRANTEE='CHENMH';

SELECT * from user_source a
 where upper(text) like '%TT_SCHEDULE_STANDARD%';

---含有xx表的存储过程
 select * from dba_source 
where text like '%TT_OUTSTOCK_SOURCE%' 
and type='PROCEDURE'
;

----查找含有xxx语句的存储过程，函数
select OWNER
from all_source
where OWNER = 'PLVSDB'
and TEXT like '%insert into%d values(%'

--查询当前时间创建的表
select * from dba_objects where OBJECT_TYPE = 'TABLE' AND OWNER = 'WMS_GA' AND TO_CHAR(created,'YYYY-MM-DD') ='2023-03-01'

------------------一行拆分为多行 按，号拆分

SELECT ID,
       substr(test1 /*替换拆分的列*/,
              instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl) + 1,
              instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl + 1) -
              (instr(test1 /*替换拆分的列*/, ',', 1, levels.lvl) + 1)) as test1 /*替换拆分的列*/
  FROM (SELECT id,
               ',' || test1 /*替换拆分的列*/
               || ',' AS test1 /*替换拆分的列*/,
               length(test1 /*替换拆分的列*/) -
               nvl(length(REPLACE(test1 /*替换拆分的列*/, ',')), 0) + 1 AS cnt
          FROM table /*替换表*/
        ) a,
       (SELECT rownum AS lvl
          FROM (SELECT MAX(length(test1 /*替换拆分的列*/
                                  || ',') -
                           nvl(length(REPLACE(test1 /*替换拆分的列*/, ',')),
                               0)) max_len
                  FROM table /*替换表*/
                )
        CONNECT BY LEVEL <= max_len) levels
 WHERE levels.lvl <= a.cnt
 order by ID
 

 select distinct name from USER_SOURCE where type = 'PROCEDURE' and upper(text) like '%TT_BALEPACK_DATA%';

SELECT T.OBJECT_NAME,(SELECT BYTES FROM USER_SEGMENTS TS WHERE TS.SEGMENT_TYPE = 'TABLE' AND TS.segment_name = T.OBJECT_NAME ) BYTES
FROM USER_OBJECTS T WHERE OBJECT_TYPE = 'TABLE' ORDER BY CREATED DESC;

SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 FROM USER_EXTENTS T WHERE T.segment_type = 'TABLE' GROUP BY SEGMENT_NAME;

-----oracle 密码设置永不过期（默认180天）

select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED ;

------------------检索死锁语句
select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id 
--and c.sql_id = ''
AND SQL_TEXT LIKE '%TT_BALEPACK_DATA%'
order by b.logon_time; 

alter system kill session '761,3990';

-------------------查出是哪些进程锁住了指定的对象
Select b.SID,b.SERIAL# From dba_ddl_locks a, v$session b Where a.session_id = b.SID And a.name = ' RE_ANALYSISMODEIMAGE_PKG ';