ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."MERCONFINFO4"
merge操作,如果在where条件里有的字段,则update时不能对该字段赋值
分类目录归档:ORACLE
oracle如何修改索引的字段
比如一个唯一索引,原来是两个字段,现在要增加一个字段。没有直接修改索引的sql,先删除原索引,然后重新建立drop index pbcs_sit.MER_CONFINFO_INDEX;
create unique index MER_CONFINFO_INDEX on pbcs_sit.TCHL_MER_REPORT_CONFINFO (CHANNEL_CODE,MER_CODE,MERCONFINFO4);
sql语法参考:
http://dcx.sap.com/1101/zh/dbreference_zh11/rf-statements.html
Oracle归档日志满了的问题
Oracle归档日志满了,狂打警告导致磁盘满了的问题
现象是oracle连接报错:ORA-00257: archiver error. Connect internal only, until freed.
登录服务器查看磁盘满了,原因是归档日志满了
1、登录su - oracle
sqlplus /nolog
connect /as sysdba
2、检查flash recovery area的使用情况select * from V$FLASH_RECOVERY_AREA_USAGE;
已经占用了99.62%
3、查询日志目录位置show parameter recover;
db_recovery_file_dest /oracle/app/oracle/fast_recovery_area
4、备份(测试环境略过)
5、在删除归档日志后,必须用RMAN维护控制文件,否则空间显示仍然不释放rman target sys/pass
检查一些无用的archivelogRMAN> crosscheck archivelog all;
删除截止到前一天的所有archivelogRMAN> delete archivelog until time 'sysdate-1';
6、删除alert日志
/oracle/app/oracle/diag/rdbms/posp/airtrip/alert下
/oracle/app/oracle/diag/tnslsnr/HKDB01/listener/alert下
的log_xxx.xml文件rm -rf log_*.log
参考资料:
https://blog.csdn.net/cw370008359/article/details/51023794
Oracle缩小表空间大小
1、碎片整理
合并表空间的碎片增加表空间的连续性alter tablespace POSP_DATA coalesce;
2、缩小表空间大小
大小不能小于数据文件中的所处的最大位置alter database datafile '/oradata/posp/posp_data16.dbf' RESIZE 2048M;
3、查询表空间使用率select a.tablespace_name as tablespace, total, free,(total-free) as usage from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
TABLESPACE TOTAL FREE USAGE
--------------------
SYSAUX 980 48.6875 931.3125
UNDOTBS1 250 218.75 31.25
POSP_DATA 32768 31643.875 1124.125
USERS 5 3.6875 1.3125
SYSTEM 1730 7.1875 1722.8125
POSP_IDX 4096 3999.8125 96.1875
Oracle重建控制文件
数据库启动报错:ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
原因为控制文件损坏,之前清理文件的时候误删了,无奈网上找了个文章硬着头皮上,重建控制文件
1、查询数据库版本SQL> select * from v$version;
BANNER
--------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
2、查询控制文件位置SQL> show parameter control_files
NAME TYPE VALUE
--------------------
control_files string /oradata/posp/control01.ctl, /oracle/app/oracle/fast_recovery_area/posp/control02.ctl
3、删除控制文件rm /oradata/posp/control01.ctl
rm /oracle/app/oracle/fast_recovery_area/posp/control02.ctl
4、启动SQL> shutdown abort;
SQL> startup nomount;
5、查询语言SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------
AMERICAN_AMERICA.US7ASCII
oracle bitand函数
语法:
BITAND(nExpression1, nExpression2)
说明:
指定按位进行AND运算的两个数值。如果nExpression1和nExpression2的位都是1,相应的结果位就是1;否则相应的结果位是0
例子:
select bitand(5, 7) from dual;
———-
5
说明:
5的二进制格式:0101
7的二进制格式:0111
计算结果:0101,转换为十进制为5
参考资料:
https://blog.csdn.net/qq_25662627/article/details/81096605
oracle数据库对象-schdeule_job
dbms_scheduler包提供的计划任务,是比dbms_job更强大,更好管理。数据库为oracle 11g。
1、创建测试表
create table SCHEDULE_JOB_TEST(a date);
2、用dbms_scheduler创建job
begin
dbms_scheduler.create_job(job_name => 'test_schedule_job',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into JOB_TEST values(sysdate);',
number_of_arguments => 0,
start_date => to_date('10-01-2020 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Minutely;Interval=5',
end_date => null,
job_class => 'DEFAULT_JOB_CLASS',
enabled => FALSE,
auto_drop => TRUE,
comments => '测试scheduler');
end;
/
3、说明
1) job_name:任务名称
2)job_type:oracle 11g支持4种,取值
‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’, ‘EXECUTABLE’, or ‘CHAIN’
(plsql语句块、存储过程、外部程序、作业链条)
3) job_action:根据job_type的不同,有不同的含义
如果job_type指定的是PL/SQL块,就需要输入完整的PL/SQL代码;
如果job_type指定的是存储过程,就需要指定存储过程的名字;
如果job_type指定的外部程序,就需要输入script的名称或者操作系统的指令名;
如果job_type指定的作业链条,需要输入chain的名称;
4)number_of_arguments:job的参数
5)start_date:开始时间
6)repeat_interval:运行间隔时间
7)end_date:结束时间
8)job_class:指定任务关联的CLASS,默认值为DEFAULT_JOB_CLASS
9)enabled:指定任务是否启用
10)auto_drop:当该标志被置为TRUE时,ORACLE会在满足条件时自动删除创建的任务
任务已过期;
任务最大运行次数已达MAX_RUNS的设置值;
任务未指定REPEAT_INTERVAL参数,仅运行一次;
11)comments:注释信息
4、查询job
SELECT * FROM DBA_SCHEDULER_JOBS;
5、启用job
begin
dbms_scheduler.enable('test_schedule_job');
end;
/
6、执行job
begin
dbms_scheduler.run_job('test_schedule_job');
end;
/
7、停止job
begin
dbms_scheduler.disable('test_schedule_job');
end;
/
8、删除job
begin
dbms_scheduler.drop_job('test_schedule_job');
end;
/
参考资料:
https://blog.csdn.net/cymm_liu/article/details/22318865
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235
https://blog.csdn.net/xlp789/article/details/98586077
oracle计算hash值
oracle计算hash值
1、dbms_utility.get_hash_value(
name VARCHAR2,
base NUMBER,
hash_size NUMBER
)
函数说明
name:输入值
base:返回hash value的起始值(hash bucket最小值)
hash_size:返回hash表的期望大小(hash bucket最大值)
这个函数用于计算并返回落在给定范围内的hash值
2、ora_hash(
expr VARCHAR2,
max_bucket NUMBER,
seed_value NUMBER
)
函数说明
expr:输入值
max_bucket:函数返回的最大值(范围是0到4294967295)
seed_value:随机数(默认为0),可以对同一个输入值返回不同的结果
这个函数用于计算带随机数的hash值
3、例子
1)ora_hash和dbms_utility.get_hash_value返回值不同
select ora_hash(‘CHL20190710T25180748988’, 99999) as “hash1”, dbms_utility.get_hash_value(‘CHL20190710T25180748988’, 0, 99999) as “hash2” from dual;
hash1 hash2
78481 15674
2)seed_value不同,ora_hash返回值也不同
select ora_hash(‘CHL20190710T25180748988’, 99999, 0) from dual;
结果:78481
select ora_hash(‘CHL20190710T25180748988’, 99999, 1) from dual;
结果:57583
select ora_hash(‘CHL20190710T25180748988’, 99999, 2) from dual;
结果:97214
参考资料:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions124.htm#SQLRF06313
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73195
https://blog.csdn.net/zutsoft/article/details/44203027
oracle使用goto实现continue
oracle 10g没有continue功能,在11g才加入。如果要在10g实现continue,可以用goto代替。
--/ begin for i in 1 .. 20 loop if i < 10 then GOTO print_now; end if; dbms_output.put_line(i); <<print_now>> NULL; end loop; dbms_output.put_line('111'); end; /
oracle的ASCII、ASCIISTR函数
oracle的ASCII、ASCIISTR函数
1、ASCII函数
作用是将字符转换为ascii码值,中文没有ascii码值,应该是转成某一编码再取编码的ascii值。
2、ASCIISTR函数
ASCIISTR函数,参数是一个字符串,如果这个字符在ASCII码表中有,则转成ASCII表中的字符。
如果没有,则转成\xxxx格式,xxxx是UTF-16的编码。
如果表中只有中文和英文、数字等字符,则可以用\来判断是否带有中文。
举例:
select * from bacs.tcim_per_account where ASCIISTR(USER_ID) LIKE ‘%\%’;
3、对比区别
select ASCIISTR('A'), ASCIISTR('b'), ASCIISTR('才'), ASCII('A'), ASCII('b'), ASCII('才') from dual; ASCIISTR('A') ASCIISTR('B') ASCIISTR('才') ASCII('A') ASCII('B') ASCII('才') ------------- ------------- ------------- ---------- ---------- ---------- A b \624D 65 98 15108493