常用SQL语句收集

 

--使用connect by将拆分分割的字符串变为行数据
select rownum,level,regexp_substr(str, '[^,]+', 1, rownum) as v, sys_connect_by_path(regexp_substr(str, '[^,]+', 1, rownum),'/') a
  from (select 'sdfsdf,sdf,dsfdsf,sadfs,jkg,43erdsvc' str from dual)
connect by ROWNUM <= length(str) - length(replace(str, ',', '')) + 1;

select substr(t, 1, instr(t, ',', 1) - 1)
  from (select substr(s, instr(s, ',', 1, rownum) + 1) || ',' as t,
               rownum as d,
               instr(s, ',', 1, rownum) + 1
          from (select ',tt,aa,bb' as s from dual)
        connect by instr(s, ',', '1', rownum) > 1);

select substr(t, 1, instr(t, ',', 1) - 1)
  from (select substr(s, instr(s, ',', 1, rownum) + 1) as t,
               rownum as d,
               instr(s, ',', 1, rownum) + 1
          from (select ',tt,aa,bb,' as s from dual)
        connect by instr(s, ',', '1', rownum) > 1)
 where t is not null;

--delete数据恢复
select timestamp_to_scn(to_timestamp('2014-12-18 09:43:20', 'yyyy-mm-dd hh24:mi:ss'))from dual;
select * from SYS_ROLE as of timestamp to_Date('2014-12-18 09:43:20', 'yyyy-mm-dd hh24:mi:ss');
--随机产生100行数据
select level L from dual connect by level<101;

--通过dbms_flashback.get_system_change_number 可以获得系统当前的SCN值：
select dbms_flashback.get_system_change_number scn from dual;
--通过scn_to_timestamp函数可以将SCN转换为时间戳:
select scn_to_timestamp(8908390522972) scn from dual;
--再通过timestamp_to_scn可以将时间戳转换为SCN:
select timestamp_to_scn(scn_to_timestamp(8908390522972)) scn from dual;
--通过这两个函数，最终Oracle将SCN和时间的关系建立起来，在Oracle10g之前，是没有办法通过函数转换得到SCN和时间的对应关系的，一般可以通过logmnr分析日志获得。但是这种转换要依赖于数据库内部的数据记录，对于久远的SCN则不能转换，请看以下举例:
select min(FIRST_CHANGE#) scn,max(FIRST_CHANGE#) scn from v$archived_log;

--删除表对象
begin
  for v_c in(select object_name,object_type from user_objects where object_type in('TYPE','FUNCTION','SEQUENCE','PACKAGE','VIEW','PROCEDURE','TABLE'))loop
    begin
      execute immediate 'drop '|| v_c.object_type ||' '||v_c.object_name;
    exception
      WHEN OTHERS THEN
        dbms_output.put_line('object='||v_c.object_name||',type='||v_c.object_type||',err:'||dbms_utility.format_error_stack);
    end;
  end loop;  
end;

--
SELECT 
  --DBMS_RANDOM
  DBMS_RANDOM.RANDOM As "任意随机数",
  ABS(MOD(DBMS_RANDOM.RANDOM,100)) as "100以内随机数",
  dbms_random.value as "0-1随机数",
  dbms_random.value(10,20) as "10-20随机数",
  TRUNC(100+900*dbms_random.value) as "100-1000随机数",
  dbms_random.normal as "正态分布随机数",--此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间
  dbms_random.string('a',20) as "随机字符串",--[u,U]-大写字母(默认);[l,L]-小写字母;[a,A]-大、小写字母;[x,X]-数字、大写字母;[p,P]-可打印字符
  to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') as "随机日期",
  sys_guid() as "GUID",
  --utl_raw
  utl_raw.compare('a','a') as "字符串比较",--字符串比较,0-相等,1-不相等
  --dbms_obfuscation_toolkit
  utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => 'djk(*&*(23SJLKA')) as "加密" --要使用cast_to_raw进行转换,否则乱码
from dual;

--listagg 按照某列分组，合并某一列的数据
with temp as(   
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all  
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all  
  select 500 population, 'China' nation ,'Beijing' city from dual union all  
  select 1000 population, 'USA' nation ,'New York' city from dual union all  
  select 500 population, 'USA' nation ,'Bostom' city from dual union all  
  select 500 population, 'Japan' nation ,'Tokyo' city from dual    
)
--select * from temp;
select population,   
       nation,   
       city,   
       listagg(city,',') within GROUP (order by city) over (partition by nation) rank   
from temp;
--lag函数,可以访问到向上特定offset偏移行的数据, ignore nulls->如果上条记录为空则取上上条
--lead函数,可以访问到向下特定offset偏移行的数据, ignore nulls->如果下条记录为空则取下下条
with temp as(  
  select '1' month_ ,'1000' amount from dual union all  
  select '2' month_ ,'1001' amount from dual union all  
  select '3' month_ ,'' amount from dual union all  
  select '4' month_ ,'' amount from dual union all  
  select '5' month_ ,'1004' amount from dual union all  
  select '6' month_ ,'1005' amount from dual 
) 
/*select month_,amount,
  lag(amount,1) over(order by month_) as prev_amount,
  lead(amount,1) over(order by month_) as next_amount
from temp*/
select month_,amount,
 lag(amount,1)ignore nulls over (order by month_) as prev_amount,
 lead(amount,1)ignore nulls over (order by month_) as next_amount
from temp
;
SELECT DBMS_METADATA.get_ddl('PACKAGE','PKG_EXCEL_CHECK') FROM DUAL;--获取某个对象的ddl代码
--查询某个对象在数据库中是否未被引用
select * from user_objects t
where not exists(select 1 from user_source where upper(text) like '%'||t.OBJECT_NAME||'%')
  and not exists(select 1 from user_dependencies where referenced_name=t.OBJECT_NAME)
  and not exists(select 1 from user_dependencies where name=t.OBJECT_NAME)
  and object_type not in('INDEX','LOB')；

--================================
select SYS_CONTEXT('USERENV', 'TERMINAL') terminal,
       SYS_CONTEXT('USERENV', 'LANGUAGE') language,
       SYS_CONTEXT('USERENV', 'SESSIONID') sessionid,
       SYS_CONTEXT('USERENV', 'INSTANCE') instance,
       SYS_CONTEXT('USERENV', 'ENTRYID') entryid,
       SYS_CONTEXT('USERENV', 'ISDBA') isdba,
       SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory,
       SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency,
       SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar,
       SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
       SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
       SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort,
       SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user,
       SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid,
       SYS_CONTEXT('USERENV', 'SESSION_USER') session_user,
       SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid,
       SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user,
       SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid,
       SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain,
       SYS_CONTEXT('USERENV', 'DB_NAME') db_name,
       SYS_CONTEXT('USERENV', 'HOST') host,
       SYS_CONTEXT('USERENV', 'OS_USER') os_user,
       SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address,
       SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol,
       SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id,
       SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id,
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data
      ,userenv('CLIENT_INFO')--
      --,userenv('LABLE')--
      ,userenv('ENTRYID')--
      ,userenv('sid')--
      ,userenv('isdba')--查看当前用户是否是DBA如果是则返回true
      ,userenv('sessionid')--SESSION 返回会话标志
      ,userenv('entryid')--ENTRYID 返回会话人口标志
      ,userenv('instance')--INSTANCE 返回当前INSTANCE的标志
      ,userenv('language')--LANGUAGE 返回当前环境变量
      ,userenv('lang')--LANG 返回当前环境的语言的缩写
      ,userenv('terminal')--TERMINAL 返回用户的终端或机器的标志
      ,vsize(user)--VSIZE(X) 返回X的大小(字节)数
      ,user
  from dual;

--重新编译无效对象
begin
  for v_c in (select 'ALTER ' || decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', object_type) || ' ' || object_name || ' COMPILE' AS txt
                from user_objects
               where status = 'INVALID') loop
    begin
      execute immediate v_c.txt;
    exception
      when others then
        dbms_output.put_line(dbms_utility.format_error_stack || '  ' || dbms_utility.format_error_backtrace);
    end;
  end loop;
end;
--查找未被引用的对象,结合代码查找,若还找不到,则可以删除这些对象
select * from user_objects t
where not exists(select 1 from user_source us where upper(us.TEXT) like '%'||t.object_name||'%' and not regexp_like(upper(text),'^(FUNCTION|PACKAGE|PROCEDURE|TYPE|END)\s+'||t.object_name))
  and not exists(select 1 from user_dependencies ud where ud.REFERENCED_NAME = t.OBJECT_NAME)
  and t.OBJECT_TYPE not in('LOB','PACKAGE BODY','INDEX')
;