数据库
码0扣1
学海无涯,回头是岸!
展开
-
创建dblink
create public database link BASE_NBXYXT connect to BASE_NBXYXT identified by BASE_NBXYXT using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.233)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';原创 2021-07-30 10:24:31 · 178 阅读 · 0 评论 -
一张表的字段关联id更新到林一张表中
---这里t_pre_credit_xycnxx_bm 的cnnr更新到 T_CREDITPROMISE的context中merge into T_CREDITPROMISE Ausing(select id,CNNR from t_pre_credit_xycnxx_bm B) TMPon (A.ID=TMP.ID)when matched thenupdate set A.CONTEXT=TMP.CNNR原创 2021-05-19 20:56:37 · 161 阅读 · 0 评论 -
oracle 联合主键去重,并删除重复数据
---去重select XK_XDR_MC, XK_LYDWDM, XK_XKJGDM, XK_WSH, XK_JDRQ, XK_XKBH, count(1)from T_XZxksgroup by XK_XDR_MC, XK_LYDWDM, XK_XKJGDM, XK_WSH, XK_JDRQ, XK_XKBHhaving count(1) > 1--删除重复delete from T_XZxks where rowid not in( select max(rowid)...原创 2020-12-24 17:06:58 · 1515 阅读 · 1 评论 -
ORACLE更改密码时不能使用最近5次密码的设置方法:PASSWORD_REUSE_MAX值可参考设为5;密码有效周期PASSWORD_LIFE_TIME。
查看不能使用最近5次密码select resource_name, limitfrom dba_profiles a, dba_users uwhere a.profile = u.profileand u.account_status = 'OPEN'and resource_name = 'PASSWORD_REUSE_MAX' AND username='ZSCREDIT';设置alter profile default limit password_reuse_max 5;原创 2020-09-02 10:43:33 · 3959 阅读 · 1 评论 -
增加归档日志的大小
1.登录,sqlplus ZSNBXYXT/ZSNBXYXT@orcl as sysdba2.查看是否启用归档日志,archive log list3.进入rman ,rman target ZSNBXYXT/ZSNBXYXT@ORCL4.修改归档日志最大空间,alter system set db_recovery_file_dest_size=35G原创 2017-12-25 16:17:21 · 6544 阅读 · 0 评论 -
创建用户,表空间,导入新数据库
createtablespaceSTNBXYXTdatafile'D:/tablespace/STNBXYXT.dbf'size500Mautoextendonnext50Mmaxsizeunlimited;--创建用户createuserSTNBXYXTidentifiedbySTNBXYXT;--设置表空间给用户alteruserSTNB原创 2017-05-12 20:50:25 · 359 阅读 · 0 评论 -
Oracle修改字段属性
/*修改原字段名name为name_tmp*/alter table T_XZCF_NEW_DELETE rename column CF_NR_FK to CF_NR_FK_tmp;/*增加一个和原字段名同名的字段name*/alter table T_XZCF_NEW_DELETE add CF_NR_FK NUMBER(13,6);/*将原字段name_tmp数据更新到增加的...原创 2019-03-04 20:15:24 · 3648 阅读 · 0 评论 -
数据库无法更新无反应,锁住了,如何解锁。
用dba权限的用户登陆数据库。select object_name,machine,s.sid,s.serial#from v$locked_object l,dba_objects o ,v$session swhere l.object_id = o.object_id and l.session_id=s.sid;--(其中24,111分别是上面查询出的sid,seria...原创 2016-11-28 17:56:28 · 2073 阅读 · 0 评论