Oracle备忘录

Oracle备忘录，在坑中前行

Oracle #

倒序查询某表的最后100条记录，并正序排列 #

select * from
(select * from FAC_HEAT_CONSUMPTION t order by id desc)
where rownum < 100 order by id


select * from
(select * from HS_CHEMICAL_IRON t order by id desc OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY)
order by id

从0开始，获取100个，OFFSET 0 ROWS可以省略掉。

删除sys.dbms_scheduler.create_job创建的日志 #

begin
  sys.dbms_scheduler.create_job(job_name            => 'ARIMSGTEST.UPDATE_PL_JOB',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'arimsgtest.update_pl_job',
                                start_date          => to_date(null),
                                repeat_interval     => 'Freq=Hourly;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/

SELECT * FROM User_Scheduler_Jobs; // 查看作业
SELECT * from All_scheduler_job_log where owner= 'ARIMSGTEST' and job_name= 'UPDATE_PL_JOB2' 
select * from All_scheduler_job_run_details where owner= 'ARIMSGTEST' and job_name= 'UPDATE_PL_JOB2'
Delete from all_scheduler_job_run_details where owner= 'ARIMSGTEST' and job_name= 'UPDATE_PL_JOB2'

logging_level——日志记录级别（DBMS_SCHEDULER.LOGGING_OFF，DBMS_SCHEDULER.LOGGING_RUNS，DBMS_SCHEDULER.LOGGING_FULL）https://blog.csdn.net/Seabiscuit_92/article/details/51038281 restartable max_runs max_failures

to_date(‘2004-05-07 13:23:44’,‘yyyy-mm-dd hh24:mi:ss’)

分组后只取第一条记录 #

SELECT *       
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,       
          test1.*       
          FROM test1)       
   WHERE rn = 1  ;

ora-00936:缺失表达式 #

当whereExpression为空时，会出现这个问题。基本可以判断是生成的sql语句不标准。在使用sugarsql时注意看下where语句使用。

修改oracle的密码永不过期 #

以dba方式登录：

12c开始可以自增id了 #

CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );

select * from v$resource_limit
select * from v$process
select * from v$bgprocess
select * from v$session
select * from v$session t where t.STATUS='ACTIVE'

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

alter system kill session '113,25455'; 

select * from v$process  p where not exists (select spid from v$session where paddr=p.addr)

exec DBMS_SCHEDULER.STOP_JOB(job_name =>'JOB_INSERT_L2_CCM',force => TRUE);
select name from v$database;
select * from dba_scheduler_running_jobs;

— Find the job

select * from dba_scheduler_running_jobs;

— Kill the job:

exec DBMS_SCHEDULER.STOP_JOB(job_name => 'JOB_INSERT_L2_CCM',force => TRUE);

Session Process与Connection的关系 #

select count(*) from v$session
select count(*) from v$process
select * from v$process where addr not in (select paddr from v$session)
select sid, serial#,username,program,machine,status from v$session where status = 'INACTIVE' order by machine;
select * from dba_profiles

Connection并不是直接建立在用户进程和数据库实例之间的。而是在用户进程和Server Process（服务器进程）之间的，因此有一个Connection就一定会有一个用户进程和一个服务器进程。但不一定会存在Session。比如，如果需要将东西从A运到B，Connection可以看成是一座“桥”，而卡车把东西从A运到B后并返回A，这就是Session。所以，只要不断开连接，随时都可以在这个连接上创建出会话。

Session占用问题 #

ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效：首先检查是哪个sid，和serial。

select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

然后准备kill掉这个session，假设sid=111，serial=30547

alter system kill session '111,30547';

可能出现错误： ORA-00031：标记要终止的会话解决方法则首先执行，查看killed状态的spid，sid和serial：

select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';

准备使用oracle自带的命令删掉这个进程，需要在oracle服务器上执行cmd命令：

orakill  _db  _spid

其中，_db是通过以下命令获得：

select name from v$database;

_spid即为上述的spid值。

创建Sequence和插入时自增Trigger #

-- Create sequence 
create sequence HS_ADDITIVE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

创建触发器 HS_ADDITIVE_SEQ

create or replace trigger 
  HS_ADDITIVE_TRG BEFORE INSERT ON
 HS_ADDITIVE for each row
 begin if inserting then if :NEW."ID" is null
 then
   select HS_ADDITIVE_SEQ.nextval into :NEW."ID" from dual;
  end if;end if;end;

创建只读用户和某些表的同义词 #

!注意这是在一个管理员账号下给另一个用户赋权 创建视图，并将视图赋予一个用户只读权限，操作步骤如下： OLDUSER用户具有创建视图的权限。 1）创建用户 zhangsan/zhangsan

CREATE USER ems IDENTIFIED BY ems;

2）给zhangsan赋予权限

GRANT CONNECT TO zhangsan;
GRANT SELECT ON OLDUSER.V_TEST TO zhangsan;

3)使用zhangsan用户登录系统，访问此视图

SELECT * FROM OLDUSER.V_TEST

4)如果不使用用户名访问，那么需要创建同义词，如下：

CREATE SYNONYM  zhangsan.V_TEST for OLDUSER.V_TEST;

创建同义词之后，在zhangsan登录之后就可以使用如下查询语句了

SELECT * FROM V_TEST

orcale 要减小精度修改的列必须为空 #

/*修改原字段名name为name_tmp*/
alter table HS_LADLE_HISTORY rename column IRON_WEIGHT to name_tmp;
/*增加一个和原字段名同名的字段name*/
alter table HS_LADLE_HISTORY add IRON_WEIGHT number(13,3);
/*将原字段name_tmp数据更新到增加的字段name*/
update HS_LADLE_HISTORY set IRON_WEIGHT=name_tmp;
/*更新完，删除原字段name_tmp*/
alter table HS_LADLE_HISTORY drop column name_tmp;

恢复已经删除的记录 #

ALTER TABLE tableName ENABLE row movement ; flashback table tableName to timestamp to_timestamp(‘‘2011-02-28 10:40:00’’,‘‘yyyy-mm-dd hh24:mi:ss’’); 后面的参数为要还原的时间点 https://www.cnblogs.com/chaizp/p/5192522.html

TableSpace表空间 #

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。有了数据库，就可以创建表空间。表空间(tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间（称作SYSTEM表空间）。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。 https://zyjustin9.iteye.com/blog/2193804

select name from v$database;    //查询当前数据库名
select instance_name from v$instance;    //查询当前数据库实例名

Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并为用户指定表空间。有了数据库，表空间和用户，就可以用自定义的用户在自己的表空间创建表了。数据库实例 -> 表空间 -> 创建用户 -> 创建表

 create tablespace DATA_CONFIG
  /*表空间物理文件名称*/
  datafile 'data_config.dbf' 
  size 500M autoextend on next 5M maxsize unlimited; 
  
create user arim3 
  IDENTIFIED BY arimyfzx --用户密码
  default tablespace DATA_CONFIG-- 表空间是上面创建的
  temporary tablespace TEMP -- 临时表空间默认 TEMP
  profile DEFAULT;
  --password expire;
   /*密码过期需要重设密码,意思是当你用这个新建立的密码过期用户首次登录后，系统会提示你重新输入新密码，不然会拒绝你登陆，重设新密码后就可以登录，该用户的密码就是你新设的密码，相当于首次登录修改密码这样的选项。*/
    
   /* 用户授权_1*/
  grant connect,resource,dba,create SEQUENCE,create trigger  to arim3;

create tablespace lgdd datafile 'lgdd.dbf'size 500M autoextend on next 5M maxsize unlimited;
create user arim IDENTIFIED BY arimyfzx default tablespace lgdd temporary tablespace TEMP profile DEFAULT;
grant connect,resource,dba,SEQUENCE,trigger to arim;
grant connect to arim with admin option;
grant dba to arim with admin option;
grant resource to arim with admin option;

 create user arim 
  IDENTIFIED BY arimyfzx --用户密码
  default tablespace lgdd-- 表空间是上面创建的
  temporary tablespace TEMP -- 临时表空间默认 TEMP
  profile DEFAULT;
  --password expire;
   /*密码过期需要重设密码,意思是当你用这个新建立的密码过期用户首次登录后，系统会提示你重新输入新密码，不然会拒绝你登陆，重设新密码后就可以登录，该用户的密码就是你新设的密码，相当于首次登录修改密码这样的选项。*/
    
   /* 用户授权_1*/
  grant connect,resource,dba,create SEQUENCE, create trigger  to arim;
  
   /* 用户授权_2*/
  grant connect to arim with admin option;
  grant dba to arim with admin option;
  grant resource to arim with admin option;

授予用户create sequence和trigger权限 #

GRANT CREATE SEQUENCE TO ARIM;
grant create trigger to arim;

Oracle执行外部文件 #

c:>sqlplus user/pwd@db sql>@new.sql

批量创建Sequence #

CREATE OR REPLACE PROCEDURE "ADD_SEQ_TRIGGER" AS
  ctr number := 0;
  ctr2 number := 0;
  addcount number := 0;
  totcount number := 0;
  var_name varchar(50) := 'ID';
  var_seq varchar(50);
  Var_Trg Varchar(50);
  var_nex varchar(50);
begin
 FOR t IN (select table_name from user_tables)
  LOOP
      select count(*) into ctr from user_tab_columns where table_name like t.table_name and column_name like var_name;
      IF (ctr > 0) THEN
        var_seq :=t.table_name||'_SEQ';
        var_trg :=t.table_name||'_TRG';
        var_nex :=t.table_name||'_SEQ'||'.nextval';
        select count(*) into ctr2 from USER_SEQUENCES where sequence_name = var_seq;
        if(ctr2> 0) THEN
           dbms_output.put_line('sequence exist for table: '||t.table_name||'!');
        ELSE
          EXECUTE IMMEDIATE 'CREATE SEQUENCE '||var_seq||' START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';
          addcount := addcount + 1;
        END IF;
         dbms_output.put_line('create or replace trigger for table: '||t.table_name||'!');
        EXECUTE IMMEDIATE 'create or replace trigger '||var_trg||' BEFORE INSERT ON '||t.table_name||' for each row begin '||
                          'if inserting then if :NEW."ID" is null then '||
                          'select '||var_nex||' into :NEW."ID" from dual; end if;end if;end;';
      ELSE
        dbms_output.put_line('ID does NOT exist for table: '||t.table_name||'!');
      END IF;
         totcount :=  totcount + 1;
  END LOOP;
   DBMS_OUTPUT.PUT_LINE('total tables count: '||totcount);
   DBMS_OUTPUT.PUT_LINE('total add count: '||addcount);
END ADD_SEQ_TRIGGER;