plsql基础语句

--PL使用？

--查询job
1、select * from dba_scheduler_jobs where job_action like '%P_CRM_BU_LOGIN_FAIL_LOG%'
2、 select * from dba_jobs where what like '%P_CRM_BU_LOGIN_FAIL_LOG%'

--查询用户执行信息
select status,sql_exec_start as start_time,  b.FINAL_BLOCKING_INSTANCE, b.FINAL_BLOCKING_SESSION, b.FINAL_BLOCKING_SESSION_STATUS,  b.OSUSER,  B.SQl_ID, 'alter system  kill session '''  || b.sid || ','|| b.serial# || ''';',
    d.event, 
    a.sql_fulltext,  
    a.sql_text,   
    a.address,
    a.executions,
    a.disk_reads,                 
    a.buffer_gets ,
    round(decode(a.executions,0, -1,(a.disk_reads + a.buffer_gets )/a.executions)) per_buffer_gets,
     a.first_load_time,
    Round(ELAPSED_TIME/1000000/decode(EXECUTIONS,0,1,EXECUTIONS),3) "per time" ,
    a.cpu_time, 
    b.*                
    from gv$sql a,gv$session b,    gv$session_wait d  
      
    where   a.address =b.sql_address and   
            a.INST_ID =b.INST_ID 
   -- and b.status = 'ACTIVE'
   AND osuser ='ly-wuweize'
    and b.username is not null
     and  b.sid =d.sid 
     and  b.INST_ID =d.INST_ID 
      order by  sql_exec_start asc; 

--1、oracle 系统表：
--查询锁表和进程session
SELECT l.session_id sid,        s.serial#,        l.locked_mode,        l.oracle_username,        l.os_user_name,        s.machine,        s.terminal,        o.object_name,        s.logon_time   FROM v$locked_object l, all_objects o, v$session s  WHERE l.object_id = o.object_id    AND l.session_id = s.sid  ORDER BY sid, s.serial#
查询数据表被锁定的SQL：
select c.OBJECT_NAME,b.* --select 'alter system kill session '''||b.SID||','||b.SERIAL#||''' immediate;' from sys.v_$locked_object a,sys.v_$session b,SYS.all_objects c where a.PROCESS=b.PROCESS AND c.object_id=a.object_id;

--dba_:数据字典里的对象
--all_:当前用户拥有的对象+当前用户能访问的对象
--user_:当前用户拥有的对象
/*
--专业解释
（
1.user_视图显示关于当前用户模式中的信息。
2.all_视图显示关于当前用户模式中的信息以及其他模式中当前用户具有相应权限的信息。
3.dba_视图显示关于整个数据库的信息。
）
*/
--所有对象
select * from sys.dba_objects t where t.owner = 'MDS'; 
select distinct OBJECT_TYPE from sys.dba_objects t ; 
--所有表
select * from sys.dba_tables t where t.owner = 'MDS'; 
select * from sys.dba_objects t where t.owner = 'MDS' and t.OBJECT_TYPE='TABLE'; 

--表注释
select * from sys.dba_tab_comments t where t.comments is not null and t.owner = 'MDS'; 
--表的列定义
select * from sys.dba_tab_columns t where t.owner = 'MDS';
--表的列注释
select * from sys.dba_col_comments t where t.comments is not null and t.owner = 'MDS';
--存储过程代码
SELECT * FROM DBA_SOURCE t WHERE  t.TYPE='PROCEDURE' and t.owner = 'MDS';
SELECT distinct TYPE FROM DBA_SOURCE t ;

--2 insert  select update delete 
--创建测试用表
create table tmp_test ( a varchar(60),b number(12,2));
--常规INSEWRT语句
truncate table tmp_test;
insert into tmp_test values ('a',1.222);
insert into tmp_test values ('a',1.232);
insert into tmp_test values ('abc',111.0);
select * from tmp_test t;
insert into tmp_test values ('abc',11111111111111111.2);

--回滚掉上次提交到现在为止的所有DML操作，DDL不适用（马上提交）
rollback;

--提交，报错的语句不会被提交
commit;

select * from tmp_test t;
--用集合的数据插入表
insert into tmp_test /*value*/ 
                           (select 'd',14 from dual union
                            select 'b',15 from dual union
                            select 'c',16 from dual )
                            ;
--利用某张表的数据插入表中
insert into tmp_test select a||a,b+b from tmp_test;
select * from tmp_test t;
--去重
select distinct a from tmp_test;

--多表查询
select * 
  from tmp_test t1,
       tmp_test t2
 where t1.a=t2.a
   and t1.b<>t2.b;
 
 --连接 inner、 left 、right、 full join ()      
select * 
  from tmp_test t1
 inner /*outer*/ join tmp_test t2
    on t1.a=t2.a
   and t1.b<>t2.b;
   
select * 
  from tmp_test t1
 inner /*outer*/ join tmp_test t2
    on t1.a=t2.a
   and t1.b<>t2.b;
   
select * 
  from tmp_test t1
  left /*outer*/ join tmp_test t2
    on t1.a=t2.a
   and t1.b<>t2.b;
   
select * 
  from tmp_test t1
 right /*outer*/ join tmp_test t2
    on t1.a=t2.a
   and t1.b<>t2.b;

select * 
  from tmp_test t1
   full /*outer*/ join tmp_test t2
    on t1.a=t2.a
   and t1.b<>t2.b;
          
--更新
update tmp_test t set t.a='bc' where t.a='a';

select * from tmp_test t for update;
select rowid,t.* from  tmp_test t;

--删除
delete from tmp_test t where t.a='a';

--3、子查询 分组 having  like子句 union min..inters..
select * from tmp_test t where t.a in (select a from tmp_test b where b.b<2);
select * from tmp_test t where b<2;
select * from tmp_test t where exists (select 1 from tmp_test b where b.b<2 and b.a=t.a);
--并集
select * from tmp_test t where t.a='a' union /*all*/
select * from tmp_test t where t.a='abc';
--差集
select * from tmp_test t where t.b>3 minus
select * from tmp_test t where t.a like 'b%';
--交集
select * from tmp_test t where t.b>3 intersect
select * from tmp_test t where t.a like 'b%';

--去重

--4、内置函数 
--substr instr ...to_  trunc(sysdate,'dd') nextday()/*'*/ replace
select substr('abcde',3,2) from dual;
select instr('abcde','cd',1) from dual;

select to_date('2015','yyyy') from dual;
select to_date('201512','yyyymm') from dual;
select to_date('20151202','yyyymmdd') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

--格式化
select to_char(5678.90123,'$99999.99') from dual;
select to_char(5678.90123,'$00000.00') from dual;

--系统时间
select sysdate  from dual;
--截取到年
select trunc(sysdate,'yyyy') from dual;
--截取到月
select trunc(sysdate,'mm') from dual;
--截取到星期(周)
select trunc(sysdate,'d') from dual;
--截取到天
select trunc(sysdate,'dd') from dual;
--截取到小时
select trunc(sysdate,'hh24') from dual;
--截取到分钟
select trunc(sysdate,'mi') from dual;

--下星期几
select next_day(sysdate,'星期三') from dual;
select next_day(sysdate,4) from dual;

--今天是星期几
select trunc(sysdate,'dd')-trunc(sysdate,'d') from dual;

--月末最后一天
select last_day(sysdate) from dual;
select add_months(trunc(sysdate,'mm'),1)-1 from dual;

--下月第一天
select last_day(sysdate)+1 from dual;
select add_months(trunc(sysdate,'mm'),1) from dual;

--查找替换
select replace('abcde','cde','CDE') from dual;

--5、动态SQL
declare
  v_sql varchar(4000);
begin
  v_sql:='insert into tmp_test values (''abc'',123456.78)';
  execute immediate v_sql;
end;

select * from tmp_test where b=123456.78;

--6、proc、FUNCtion、触发器 
--介绍

--6.5 数据库管理类
--数据文件
select * from dba_data_files   ;

select * from dba_temp_files ;

--查询数据库表空间 
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB " 表空间大小 (M) ",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " 已使用空间 (M) ",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 , 2 ), '990.99' ) " 使用比 ",
F.TOTAL_BYTES " 空闲空间 (M) ",
F.MAX_BYTES " 最大块 (M) "
FROM       
(SELECT TABLESPACE_NAME,
ROUND(SUM (BYTES)/( 1024 * 1024 ), 2 ) TOTAL_BYTES,
ROUND(MAX (BYTES)/( 1024 * 1024 ), 2 ) MAX_BYTES
FROM SYS .DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME, ROUND(SUM (DD.BYTES)/( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB
FROM     SYS .DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME  
ORDER BY 4 DESC ;

--查询临时表空间
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

--查锁表
select c.OBJECT_NAME,b.* from sys.v_$locked_object a,sys.v_$session b,SYS.all_objects c where a.PROCESS=b.PROCESS AND c.object_id=a.object_id;
--杀进程 
alter system kill session '1367,3369';
--（7、优化） 

--，删除临时表
drop table tmp_test;
--select * from v$version;

① 查看用户锁表

select sess.sid, 
 sess.serial#, 
 lo.oracle_username, 
 lo.os_user_name, 
 ao.object_name, 
 lo.locked_mode 
 from v$locked_object lo, 
 dba_objects ao, 
 v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid

② 解锁语句

alter system kill session 'sid,serial#'