Oracle-表的字段增加修改删除操作~迁移~记录被锁~Date补充

 表结构修改

ALTER TABLE SCOTT.TEST RENAME TO TEST1--修改表名
ALTER TABLE SCOTT.TEST RENAME COLUMN NAME TO NAME1 --修改表列名
ALTER TABLE SCOTT.TEST MODIFY NAME1 NUMBER(20) --修改字段类型
ALTER TABLE SCOTT.TEST ADD ADDRESS VARCHAR2(40)--添加表列
ALTER TABLE SCOTT.TEST DROP NAME CASCADECONSTRAINTS --删除表列 

 

这里要注意几个地方，

      首先，增加和修改列是不需要加关键字COLUMN，否则会报错ora-00905。

　　其次，对删除单列的话，一定要加COLUMN，然后记住，删除是不需要加列类型的

     很奇怪的现象，再单列中要加关键字COLUMN，然而再删除多列的时候，不能加COLUMN关键字。

 

alter table emp4 drop column test;

alter table emp4 drop (test,test2);

 

 

表备注添加

 comment on column TableName.ColumnName is '备注名';
 comment on table TableName is '备注名';

 

从一个表中查询的数据插入到另一张表中

----- 
---27....  系统操作日志表 SYS_OPE_LOGS_BAK
create table SYS_OPE_LOGS_BAK
(
  SYSID       NUMBER(20) not null,
  OPE_USER    VARCHAR2(20),
  OPE_TIME    date,
  OPE_ACTION  VARCHAR2(20),
  OPE_ORG  VARCHAR2(20),
  OPE_CARDNO  VARCHAR2(20),
  OPE_CHANNEL VARCHAR2(3)
);
--
--原来表SYS_OPE_LOGS中的数据备份到表SYS_OPE_LOGS_BAK中
insert into SYS_OPE_LOGS_BAK   
    (SYSID,OPE_USER,OPE_TIME,OPE_ACTION,OPE_ORG,OPE_CARDNO,OPE_CHANNEL)  
select   
    SYSID,   
    OPE_USER,  
    to_date(OPE_TIME,'yyyy-mm-dd hh24:mi:ss') as OPE_TIME,  
    OPE_ACTION,  
    OPE_ORG,
    OPE_CARDNO,
    OPE_CHANNEL
 from SYS_OPE_LOGS 
--**************************************
-- 一定要等成功之后，在删除旧表的数据
--**************************************
--删除旧表
drop table SYS_OPE_LOGS;
-- 重命名表SYS_OPE_LOGS_BAK为SYS_OPE_LOGS
ALTER TABLE SYS_OPE_LOGS_BAK RENAME TO SYS_OPE_LOGS--修改表名
-- 最后加相关备注
comment on table SYS_OPE_LOGS  is '系统操作日志';
comment on column SYS_OPE_LOGS.SYSID  is '主键、自动生成';
comment on column SYS_OPE_LOGS.OPE_USER  is '操作人';
comment on column SYS_OPE_LOGS.OPE_TIME  is '操作时间';
comment on column SYS_OPE_LOGS.OPE_ACTION  is '操作动作';
comment on column SYS_OPE_LOGS.OPE_ORG  is '机构号';
comment on column SYS_OPE_LOGS.OPE_CARDNO  is '卡号';
comment on column SYS_OPE_LOGS.OPE_CHANNEL  is '操作渠道：002终端机 001柜面';
alter table SYS_OPE_LOGS
  add constraint PK_SYS_OPE_LOGS primary key (SYSID);

--

解决oracle记录被锁

1、查看数据库锁,诊断锁的来源及类型：
　　select object_id,session_id,locked_mode from v$locked_object;
或命令：
       select b.owner,b.object_name,l.session_id,l.locked_mode
       from v$locked_object l, dba_objects b
       where b.object_id=l.object_id ;

2、找出数据库的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;

3、杀死该session
　　alter system kill session 'sid,serial#';

时间的妙用

1、表中的字段是时间字段，可以直接用date引号来查询信息：
　　select * from tabe_name t where t.data_date>=date'2023-01-10';

       select * from tabe_name t where t.data_date>=date'2023-01-10' + 10/24 + 30/1440 + 45/86400;

2、表中的字段是 参数是字符串带毫秒信息的时间值，表中的是Date类型字段，查询信息：
　　select * from tabe_name t where t.data_time >= to_timestamp('2023-01-10 16:30:45.0'，'yyyy-mm-dd hh24:mi:ss.ff');

 

 

插入数据库出现主键冲突，改为merge 的方式

insert into tabe_name (cr_id, org_no, cycle_time) values ('1014275983', '61001', to_date('2023-04-12 17:46:30','YYYY-MM-DD hh24:mi:ss') ) ;

主键: cr_id

改造为: 

merge into tabe_name  a
using (select '1014275983' cr_id , '61001' org_no , to_date('2023-04-12 17:46:30','YYYY-MM-DD hh24:mi:ss') cycle_time from dual ) b
on (a.cr_id=b.cr_id )

-- when matched then
-- update set a.to_closedcycle_time = sysdate
when not matched then
insert(cr_id, org_no, cycle_time) values(b.cr_id, b.org_no, b.cycle_time) ;