oracle自己工作中的整理

--sysdba用户登陆
--1.建立用户：
/*
10.163.201.245
root hwmtv
su - oracle
sqlplus / as sysdba
*/
create user iassess identified by iassess default tablespace imp;
--2赋权
---2.1.基本权限
grant connect to iassess;
grant resource to iassess;
grant execute on dbms_lock to iassess;
---2.2.扩展权限
----2.2.1对象权限
grant select on dba_kgllock     to iassess;
grant select on v_$lock         to iassess;
grant select on v_$mystat       to iassess;
grant select on v_$parameter    to iassess;
grant select on v_$process      to iassess;
grant select on v_$session_wait to iassess;
grant select on v_$sqltext      to iassess;
grant select on v_$session      to iassess;
grant select on v_$statname     to iassess;
grant select on v_$sesstat      to iassess;
----2.2.2系统权限
grant alter any trigger to iassess;
grant alter any type to iassess;
grant alter system to iassess;
grant create any directory to iassess;
grant create any index to iassess;
grant create any table to iassess;
grant create any trigger to iassess;
grant create any type to iassess;
grant create any view to iassess;
grant drop any index to iassess;
grant drop any table to iassess;
grant drop any trigger to iassess;
grant drop any type to iassess;
grant drop any view to iassess;
grant query rewrite to iassess;
grant select any table to iassess;
grant create sequence to  iassess;
grant create database link to iassess;
-----收回权限
revoke drop any index from iassess;
-----------删除用户
--需要sysdba用户登陆操作
drop user iassess cascade;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--定时任务
select sysdate from dual;
--创建job（如果为过程，则过程不要有入参和出参）
begin
  sys.dbms_scheduler.create_job(job_name        => 'P_CMDINFO_TRANS_JOB',
                                job_type        => 'STORED_PROCEDURE',
                                job_action      => 'p_dispose_queue_add',
                                start_date      => to_date('10-07-2013 14:08:20',
                                                           'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval => 'Freq=Minutely;Interval=1',--Monthly
                                end_date        => to_date(null),
                                job_class       => 'DEFAULT_JOB_CLASS',
                                enabled         => true,
                                auto_drop       => false,
                                comments        => '定时把任务移到待处理队列');
end;
--查看
select * from user_scheduler_jobs;
--查看job状态：dba权限
SELECT JOB_NAME, STATE
  FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'P_CMDINFO_TRANS_JOB';
--查看job的运行详情
select log_id, job_name, status, to_char(log_date, 'DD-MON-YYYY HH24:MI')
log_date from dba_scheduler_job_run_details  where job_name = 'P_CMDINFO_TRANS_JOB';
--删除job:删除多个job，job之间用,隔开
begin 
dbms_scheduler.drop_job (         
   job_name   =>  'P_CMDINFO_TRANS_JOB',
   force      =>  true);
end;

--修改job:例如修改job的执行频率：frequency
begin 
dbms_scheduler.set_attribute ( 
   name           =>   'P_CMDINFO_TRANS_JOB', 
   attribute      =>   'repeat_interval', 
   value          =>   'FREQ=DAILY');    --每天执行一次
end;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--创建触发器
create or replace trigger task_idc_stuts_oper before  update
  on t_js_idc_CPStatus
  for each row
  declare
    v_status Integer;
    v_flag varchar2(100);
   
begin
  v_status := /:new.status;
      v_flag := case v_status when 0 then '未结算' when 1 then '确认中' when 2 then '已结算'   else '驳回' end;
       update t_js_data_temp t set t.value = v_flag where t.processid = /:old.processid
           and t.rownumber in (select t.rownumber from t_js_data_temp t
           where t.processid = /:old.processid
           and t.value = /:old.cpname
           and t.sheetnumber=1)
           and t.colnumber = 9
           and t.sheetnumber =1;
end  task_idc_stuts_oper;
--删除触发器
drop trigger task_idc_stuts_oper;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--获取当前时间
select to_char(sysdate,'yyyymm')from dual;
--获取2个月前的时间
select to_char(sysdate-interval'2'month,'yyyymm') from dual;
select to_char(add_months(trunc(sysdate),-2),'yyyymm')from dual;
select to_char(add_months(trunc(last_day(sysdate)+1),-3),'yyyymmdd')from dual;
select to_char(add_months(last_day(sysdate)+1,-3),'yyyymmdd')from dual;
-----
select to_char(last_day(to_date('201305','yyyymm'))+1,'yyyymmdd') from dual;
select to_char(add_months(last_day(to_date('201305','yyyymm'))+1,-1),'yyyymmdd')from dual;
--获取每月第一天
select last_day(sysdate)+1 from dual;
-------------------
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
alter session enable parallel dml;
-- 记录任务开始时
select trunc(sysdate, 'mm') from dual;

-- 给起止时间赋
select add_months(trunc(sysdate, 'mm'), -2) from dual;
select to_char(add_months(trunc(sysdate, 'mm'), -2), 'yyyy-mm') from dual;
-------------------------------------------------
execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''';
execute immediate 'alter session enable parallel dml';
-- 记录业务时间
v_static_month := to_char(add_months(trunc(sysdate, 'mm'), -2), 'yyyy-mm');

----------------------------------------------------------------------------
----------------------------------------------------------------------------
--1.闪回表到某个时间点的状态（以t_document_model_info为例）
alter table t_document_model_info enable row movement;
select sysdate from dual;
flashback table t_document_model_info to timestamp to_timestamp('20130403154000','yyyymmddhh24miss');
--2.闪回对象到某个时间点的状态（要以DB的角色登陆）
select * from dba_source AS OF TIMESTAMP TO_TIMESTAMP('2013-4-3 15:00:32', 'YYYY-MM-DD HH24:MI:SS') where  name like upper('%P_JS_CONFIG_QUERY_SHEET%') and owner ='ASSESS_BASIC2'order by line;

--3.
创建表，只需要表结构
create table t_js_parameter_all_temp as select * from t_js_parameter_all where 1=2;
创建表，并且数据也相同
create table t_js_parameter_all_temp as select * from t_js_parameter_all;
--4.查询去重后的数据
  select statis_month,
         product_id,
         creator_id,
         playmode,
         user_count,
         time_count,
         duration,
         volume,
         fee,
         msisdntype
    from (select t.*,
                 row_number() over(partition by t.product_id, t.creator_id order by t.product_id, t.creator_id) rown
            from T_JS_PARAMETER_ALL_temp t) tt
   where tt.rown = 1;
  
--5.设置时间显示格式
  execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''';
  execute immediate 'alter session enable parallel dml';
 
--导入bmp文件
imp assess/assess@impsh file=D:\t_fz_hisbilldlstotal_filter.dmp tables=t_fz_hisbilldlstotal_filter full=y  buffer=1024 ignore=y

--求两个时间之间的月份差
select months_between(to_date( '2013-10 ', 'yyyy-mm '),to_date( '2013-01-01 ', 'yyyy-mm-dd ')) from dual;

--自定义数组类型
  type operatorcode is table of t_boss_booschargeinfo.operatorcode %type index by binary_integer;
  v_operatorcode    operatorcode;
--循环数据
for i in v_objectid.first .. v_objectid.last loop
      update t_data_microadjust t1
         set t1.objectid = v_objectid(i)
       where t1.operatorcode = v_operatorcode(i)
         and t1.task_processid = i_task_processID
         and t1.type = i_type;
      commit;
    end loop;
------------case  when 函数与decode函数
case when v.quarter_type=0 and v.templateid=13 then 0 else sum(v.award_pool) * 0.5 end
decode(v.quarter_type,0,'',sum(v.award_pool) * 0.5)
decode函数的运用（类似于Java三目运算）
update T_JS_PARA_RULE_TEMP t
   set t.row_count = decode(t.mark, 2, 1, -1)
where t.static_month = '2013-02';

--获取分区
select to_char(to_date('2013-05','yyyy-mm'),'MONyyyy','nls_date_language=american') from dual;
--查看主机的进程ID
select t.PADDR from v$session t where t.sid = '428';
select t.SPID from v$process t where t.ADDR = '000000009C3C6D78';
--把一个字段分割成多个字段
with  t as
  (select  name, descr  from deal_t x)
    select REPLACE(name,'del','') name,
           REPLACE(REPLACE(REPLACE(substr(descr,1,instr(descr,';')-1) ,'Cpid=',''),'cpid=',''),'del','') Cpid,
           REPLACE(REPLACE(substr(descr,instr(descr,';')+1,instr(descr,';',1,2)-instr(descr,';')-1) ,'serviceid=',''),'del','') serviceid,
           REPLACE(substr(descr,instr(descr,';',1,2)+1,instr(descr,';',1,3) -instr(descr,';',1,2)-1) ,'producttype=','') producttype,
           REPLACE(substr(descr,instr(descr,';',1,3)+1,instr(descr,';',1,4) -instr(descr,';',1,3)-1),'productname=','') productname,
           REPLACE(substr(descr,instr(descr,';',1,4)+1,instr(descr,';',1,5) -instr(descr,';',1,4)-1) ,'cpname=','') cpname
             from t ;
---表分区
删除一个表分区（drop会把分区都删除，而truncate不会删除分区，只会删除分区中的数据）
alter table 表名 truncate partition 分区名;
alter table 表名称 drop partition 分区名称

增加分区
rang分区:alter table 表名称 add partition 分区名称 values less than (to_date('',''));
list分区:alter table XXXXX add partition 分区名 values('值');
--查分区
select to_char(to_date('2013-05','yyyy-mm'),'MONyyyy','nls_date_language=american') from dual;
----代码块中执行过个过程
declare
begin
过程名（入参）;
end;
----group 中包含函数
select  /*+parallel(t,16)*/count(to_char(t.billtime,'yyyymmdd')),to_char(t.billtime,'yyyymmdd')
from t_fz_hisbilldlstotal_filter partition (JAN2014 ) t group by to_char(t.billtime,'yyyymmdd') ;
------------
还有过程的创建，函数的创建，包的创建