oracle实用sql

技术源于积累,整理我在开发中常用的sql实用小技巧,方便平时开发查阅使用

查询表格基础信息

查询指定用户所管理的表格以及表格注释

select tab.table_name, comm.comments
  from dba_tables tab, user_tab_comments comm
 where tab.table_name = comm.table_name
   and tab.owner = upper('OWNER')

查询列信息

查询指定表格的列名、数据类型以及列注释

select a.column_name, b.data_type, comments
  from user_tab_columns b, user_col_comments a
 where a.column_name = b.column_name
   and a.table_name = b.table_name
   and a.table_name = upper('TABLE_NAME')

查询锁定进程

SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE, C.SID, C.SERIAL#
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B, V$SESSION C
 WHERE B.OBJECT_ID = A.OBJECT_ID
   AND A.SESSION_ID = C.SID;

解除锁定进程

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
--SID,SERIAL# 来源于查询锁定进程钟的值

根据内容查询存储过程名

SELECT distinct name
  FROM all_source
 where upper(text) like upper('%o_sfmx%zxzt%')

Oracle查询密码过期时间和修改密码过期为永久的语句

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

解决表空间不足的问题

select REGEXP_REPLACE(FILE_NAME,
                      '([0-9]+)(\.dbf)',
                      case
                        when n > 9 then
                         TO_CHAR(n)
                        else
                         '0' || TO_CHAR(n)
                      end || '.dbf') file1
  from (SELECT t.FILE_NAME,
               TO_NUMBER(REGEXP_SUBSTR(t.FILE_NAME,
                                       '([0-9]+)(\.dbf)',
                                       1,
                                       1,
                                       NULL,
                                       1)) + 1 n
          FROM dba_data_files t
         WHERE t.TABLESPACE_NAME = '表空间名');
ALTER tablespace TBS_O_INDEX ADD datafile '查询结果' SIZE 100m autoextend ON NEXT 100m;