orace数据库生产环境操作实录

最新推荐文章于 2022-04-16 19:44:37 发布

warrah

最新推荐文章于 2022-04-16 19:44:37 发布

阅读量659

点赞数

分类专栏：岁月云——安全文章标签： oracle

本文链接：https://blog.csdn.net/warrah/article/details/98933704

版权

岁月云——安全专栏收录该内容

15 篇文章 0 订阅

订阅专栏

最近在做一次大批量数据库同步的工作，因为很久没有弄oracle了，遇到了较大困难，客户又催的比较紧，这里再捡一下。
1 基础
Oracle 数据库、实例、用户、表空间、表之间的关系讲解，实例名也叫sid，一个数据库可以有多个实例

jdbc:oracle:thin:@localhost:1521:orcl(orcl就为数据库实例名)
#windows下查看是否可以访问到数据库
tnsping 192.168.0.1
# 查看oracle安装路径
env |grep ORACLE
# 查看端口占用
netstat -Aan|grep 1588

ORACLE中SID和SERVICE_NAME的区别,SERVICE_NAME是出现在8i之后，支持并发才出现的，提供给对外访问的，在集群环境，让别人看起来只有一个统一的数据库。单机环节应该是没有什么差别。
再看一下下面的语句，oracle中数据库名与实例名又是什么关系呢？参考Oracle数据库名与Oracle实例名的关系

show parameter instance_name --实例名
show parameter db_name --数据库名

1.1 表空间
Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表

# 查看已经创建好的表空间
select default_tablespace, temporary_tablespace,d.username from dba_users d;
# 查看当前用户的缺省表空间
select username,default_tablespace from user_users;
# 查看表空间文件位置
select * FROM DBA_DATA_FILES;

1.2 用户与表空间
Oracle创建表空间、用户、授权，这些都大同小异，用户赋予了一个默认的表空间，那么用户只有这一个默认的表空间吗？
通过一个用户可以管理多个表空间，这个是在创建的时候。
我在执行imp操作的时候，提示表空间不存在，那么意味着当前用户存在两个以上的表空间，跟我之前使用的只有一个默认表空间不一样，这个时候的操作如下：

create tablespace "space1" logging datafile "/oradata/instance_xx/my_space.dbf" size 4096M reuse extent management local segment
 management manual;
 create tablespace "space2" logging datafile "/oradata/instance_xx/my_space2.dbf" size 4096M reuse extent management local segment
 management manual;
 create user xx identified by xxx default tablespace space1;
 grant connect,resource to xx;
 grant dba to xx;
 alter user xx quota unlimited on space2;

drop user xxx cascade;
drop tablespace xxx including contents and datafiles;

在删除用户的时候，如果出现ORA-01940，参考oracle中用户删除不了，ORA-01940提示 “无法删除当前已连接用户”可执行命令

alter user xx account lock;
select username,sid, serial# from v$session where username = xx;
alter system kill session 'xx,xx';
drop user xxx cascade;

删除表空间的时候，有可能会提示ORA-12919，参考 oracle数据库默认永久表空间的查询及删除方法，oracle默认永久表空间是USERS，网上有解决方案,可是问题是为什么我们为什么要删除这个永久表空间呢？如果在删除用户的时候已经将用户关联的信息删除掉了，那么再次创建用户，然后指定用户默认表空间为USERS，是否也可以呢？实测是可行的，也就说无需删除表空间USERS，或者更改USERS为非永久的。

select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
alter database default tablespace USERS;

1.3 rac
执行select instance_number,INSTANCE_NAME from gv$instance;,现在当前数据库有两个rac节点
但通过其中一个instance_sid却连不上，这是为什么呢？这里有介绍rac的链接方式Oracle RAC 环境下的连接管理，也就是每个rac节点都配置上去。
执行SELECT value FROM v$parameter where name = 'cluster_database';也可以看出这个数据库是不是rac集群。
scan与rac节点之间，又是什么关系呢？
进入到$ORACLE_HOME/bin目录，执行./srvctl config scan可查看scan配置信息
再查看/etc/hosts，可以看到配置了public-ip,private-ip,scan-ip,vip-ip的配置，参考RAC中的各种IP-PUBLIC-VIP-Private-SCAN IP
1.4 数据库配置要求
按照下面命令检查，发现与生产环境要求的配置有太大差别。其他空间查询参考Oracle中查询表的大小、表的占用情况和表空间的大小

-- memory_target
show parameter memory_target;
-- 查看TEMP表空间大小
select sum(bytes)/1024/1024/1024 "temp size(G)" from dba_temp_files where tablespace_name='TEMP';
-- 查看UNDO表空间大小
select sum(bytes)/1024/1024/1024 "current undo size(G)" from dba_data_files where tablespace_name='UNDOTBS1';
-- 查看个表空间的使用情况
select
　　a.a1 "表空间名称",
　　c.c2 "类型",
　　c.c3 "区管理",
　　b.b2/1024/1024/1024 "表空间(G)",
　　(b.b2-a.a2)/1024/1024/1024 "已使用(G)",
　　substr((b.b2-a.a2)/b.b2*100,1,5) "利用率"
　　from
　　(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
　　(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
　　(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
　　where a.a1=b.b1 and c.c1=b.b1;

1.5 oracle11g安装

systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
# 关闭selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config

# 安装oracle依赖
yum -y install gcc make binutils gcc-c++ compat-libstdc++-33 elfutils-libelf-devel elfutils-libelf-devel-static elfutils-libelf-devel ksh libaio libaio-devel numactl-devel sysstat unixODBC unixODBC-devel pcre-devel libXp*
# 
chattr -i /etc/group
chattr -i /etc/gshadow 
chattr -i /etc/shadow
chattr -i /etc/passwd
# 创建用户
groupadd oinstall
groupadd dba
useradd oracle
sudo usermod -a -G dba oracle
sudo usermod -a -G wheel oracle
sudo usermod -a -G  oinstall  oracle
passwd oracle
# 查看oracle用户现在拥有的组，确保含有oinstall和wheel
id oracle

[root@bwhs5seg241 applications]# id oracle
uid=1000(oracle) gid=1001(oracle) groups=1001(oracle),10(wheel),1000(oinstall)

参考【体系结构】Oracle的kernel.shmmax和kernel.shmall设置设置大小

vi /etc/sysctl.conf
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.file-max = 6815744 
fs.aio-max-nr = 1048576
#控制共享内存页数 内存大小/4KB=4194304
kernel.shmall = 2097152           
#定义单个共享内存段的最大值:物理内存 -1byte
kernel.shmmax = 4294967295
kernel.shmmni = 4096 
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500 
net.core.rmem_default = 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576

sysctl -p
sysctl -a

修改用户的限制文件

vi /etc/security/limits.conf
oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile         65536
oracle           soft    stack           10240
# 生效
ulimit -a

vi /etc/pam.d/login
session    required     /lib64/security/pam_limits.so
session    required     pam_limits.so

vi /etc/profile
if [ $USER = "oracle" ]; then
    if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
fi
# 生效
source /etc/profile

参考CentOS7.5安装oracle11g设置oracle目录

mkdir -p /application/oracle/product/11.2.0
mkdir -p /application/oracle/oradata
mkdir -p /application/oracle/oraInventory
mkdir -p /application/oracle/flash_recovery_area
chown -R oracle:oinstall /application/oracle
chmod -R 775 /application/oracle

设置oracle环境变量

su - oracle
vi .bash_profile
ORACLE_BASE=/application/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
# 环境变量生效
source .bash_profile

静默安装

# 切换到root用户下
su
cd /application/oracle/database/response
vi db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
# 注：woitumi-197为当前主机名称
ORACLE_HOSTNAME=woitumi-197
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/application/oracle/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/application/oracle/product/11.2.0
ORACLE_BASE=/application/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
DECLINE_SECURITY_UPDATES=true
# 切换到oracle用户下
su - oracle
cd /application/oracle/database
./runInstaller -silent -ignorePrereq -ignoreSysPrereqs -responseFile /application/oracle/database/response/db_install.rsp

看到下图静默安装完成

提示切换root用户运行脚本

su
sh /application/oracle/oraInventory/orainstRoot.sh
sh /application/oracle/product/11.2.0/root.sh

su - oracle
netca -silent -responseFile /application/oracle/database/response/netca.rsp
# 查看监听是否启动
netstat -tnulp | grep 1521

su
vi /application/oracle/database/response/dbca.rsp
GDBNAME = "orcl"
SID = "orcl"
SYSPASSWORD = "oracle"
SYSTEMPASSWORD = "oracle"
SYSMANPASSWORD = "oracle"
DBSNMPPASSWORD = "oracle"
DATAFILEDESTINATION =/application/oracle/oradata
RECOVERYAREADESTINATION=/application/oracle/fash_recovery_area
CHARACTERSET = "ZHS16GBK"
TOTALMEMORY = "1638"
# 
su - oracle
dbca -silent -responseFile /application/oracle/database/response/dbca.rsp
# 实例检查
 ps -ef | grep ora_ | grep -v grep
 # 查看监听状态
 lsnrctl status

1.6 数据库归档

# RAC节点都要关闭掉
# 在101中执行和在102中执行，都要执行
sqlplus / as sysdba
shutdown immediate;

# 在101中执行
startup mount;
alter database noarchivelog;
alter database open;
alter system set cluster_database=true scope=spfile;
shutdown immediate;
startup

# 在111中执行
startup

# 查看归档状态
archive log list;

查看每天归档日志大小

select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024/1024)) as gb 
from v$archived_log
group by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;

1.7 sga_target
原本想更爱一下NLS_CHARACTERSET,停机后执行startup mount，结果提示

SQL> startup mount;
ORA-00821: Specified value of sga_target 1232M is too small, needs to be at least 2832M
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

解决方案参考oracle出现sga导致ORA-00821 ORA-00093报错解决办法,但这个并不能最终解决问题，可以临时性解决

cd /home/oracle
# 更改temp_init.ora中sga_target值
vi temp_init.ora
#启动
shutdown immediate;
STARTUP MOUNT pfile='/home/oracle/temp_init.ora';
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE character set INTERNAL_USE AL32UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;
shutdown immediate;
startup pfile='/home/oracle/temp_init.ora';

2 exp/imp
导入导出原本并不复杂，但是oracle中操作的时候，总有各种各样的问题
先确定导出库的字符集echo $NLS_LANG,然后再目标库执行export NLS_LANG=xxx
2.1 exp

nohup exp $exp_user/$exp_pwd file=/home/test/bak/20190810.dmp log=/home/test/bak/20190810.exp.log buffer=8192000 feedback-10000
tables=table1,table2 query=\" where cjrz \>\=data \'2018-01-01\' \" &

如果想做成动态的，可以调整为

#!/bin/bash
emp_oracle_sid=xx
exp_username=xx
exp_password=xx
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

export oracle_sid=$exp_oracle_sid
exp_root_dir = /oradata/"$oracle_sid"exp
cur_date=$(date +%Y%m%d)
exp_file_path=$exp_root_dir/$cur_date

mkdir -p $exp_file_path/01

cd $exp_file_path/01
nohup exp $exp_username/"$exp_password" file = $exp_file_path/01/"$exp_oracle_sid".dmp log=$exp_file_path/01/"$exp_oracle_sid".exp.log buffer-8192000 feedback=10000 tables=xx,xx,xx &

2.2 imp

nohup imp $exp_user/$exp_pwd file=/home/test/bak/20190810.dmp log=/home/test/bak/20190810.imp.log buffer=8192000 feedback-10000 fromuser=$imp_user touser=$imp_user &

imp做成动态，如下所示：

#!/bin/bash
imp_oracle_sid=xx
imp_username=xx
imp_password=yyy
exp_oracle_sid=xxx
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

export oracle_sid=$imp_oracle_sid
log_root_dir=/oradata/xxx/$exp_oracle_sid
exp_root_dir=/oradata/"$exp_oracle_sid"exp
cur_date=$(date +%Y%m%d)
exo_file_path=$exp_root_dir/$cur_date

mkdir -p $log_root_dir/01
cd $log_root_dir/01
nohup imp $imp_user/"$imp_password"@$imp_oracle_sid file=$exp_file_path/01/"$exp_oracle_sid".dmp log=$log_root_dir/01/"$imp_oracle_sid".imp.log buffer=8192000 feedback=100000 fromuser=$imp_username touser=$imp_username &

2.3 expdp
通过执行show parameter cpu查看cpu的核数，但在实际运用的时候，并不是唯一指标，我们的服务器cpu核数是100，但是实际PARALLEL设置为4，就已经很快了。并发导出，也受制于磁头。dumpfile设置为%U，根据PARALLEL导出多个文件，也比较快。

sqlplus / as sysdba 
create directory skuserexp as '/application/skuserexp';
grant read,write on directory skuserexp to skuser;

#!/bin/ksh
EXP_ORACLE_SID=orcl
EXP_USERNAME=skuser
EXP_PASSWORD=skuser
DIRECTION=skuserexp
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
export LANG=zh_CN.UTF-8

export ORACLE_SID=$EXP_ORACLE_SID
CUR_DATE=$(date +%Y%m%d)

nohup expdp $EXP_USERNAME/"$EXP_PASSWORD" directory=$DIRECTION \
tables=SKUSER.DJ_XT_QYXX,SKUSER.DJ_XT_SCJH \
dumpfile="$EXP_ORACLE_SID"_"$CUR_DATE"_his1.dmp \
job_name="$EXP_ORACLE_SID"_"$CUR_DATE"_his1_exp \
logfile="$EXP_ORACLE_SID"_"$CUR_DATE"_his1_exp.log \
PARALLEL=8 compression=ALL &

2.4 impdp
注意job_name不要重复，查看下面的命令看有哪些任务没有运行

SELECT 'DROP TABLE' || OWNER_NAME ||'.'|| JOB_NAME ||';' FROM DBA_DATAPUMP_JOBS WHERE   STATE='NOT RUNNING';

REMAP_TABLESPACE为表空间的映射

#!/bin/bash
IMP_ORACLE_SID=orcl
IMP_USERNAME=skuser
IMP_PASSWORD=skuser
DIRECTION=skuserexp
EXP_ORACLE_SID=orcl
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

export ORACLE_SID=$IMP_ORACLE_SID
CUR_DATE=$(date +%Y%m%d)

nohup impdp $IMP_USERNAME/"$IMP_PASSWORD"@$IMP_ORACLE_SID directory=$DIRECTION \
REMAP_SCHEMA=$IMP_USERNAME:$IMP_USERNAME \
REMAP_TABLESPACE=SRC_TAB:TARGET_TAB \
dumpfile="$EXP_ORACLE_SID"_"$CUR_DATE"_his1.dmp \
job_name="$EXP_ORACLE_SID"_"$CUR_DATE"_his1_imp \
logfile="$EXP_ORACLE_SID"_"$CUR_DATE"_his1_imp.log TABLE_EXISTS_ACTION=REPLACE parallel=8 &

3 ogg
这里需要注意的是ogg11.2.1.0.1同步数据速度实在无法忍受，后来发现升级补丁后，版本到ogg11.2.1.0.20，同步速度飞快
当两个数据库之间表结构不同，那么就可以通过ogg进行数据同步了。如果源端和目标端在同一台机器上，注意执行export ORACLE_SID=xx切换环境。源端和目标端均采用oracle 11g
3.1 ogg安装
使用oracle创建ogg安装目录：ogg11g,按照下面的命令顺序执行

mkdir -p /ogg/ogg11g
tar -xvf ggs_AIX_ppc_ora11g_64bit.tar
chown –R ogg:oinstall  /ogg11g
# 切换到oracle用户
su – oracle
# linux环境用vi ~/.bash_profile，AIX采用下面的方式
vi ~/.profile
export OGG_HOME=/ogg/ogg11g
export ORACLE_SID=’实例名’
export NLS_LANG=AMERICA_AMERICA.AL32UTF8
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:/usr/sfw/lib

创建管理目录

cd /ogg/ogg11g
ggsci
create subdirs

目录	用途
dirprm	存放OGG参数的配置信息
dirrpt	存放进程报告文件
dirchk	存放检查点文件
dirpcs	存放进程状态文件
dirsql	存放SQL脚本文件
dirdef	存放DEFGEN工具生成的数据定义文件
dirdat	存放Trail文件，也就是Capture进程捕获的日志文件
dirtmp	当事物需要的内存超过已分配内存时，默认存储在这个目录

参考OGG_GoldenGate数据表定义方式DEFGEN（案例），对ogg源端和目标进行参数定义

3.2 数据同步
执行命令status idstaa，状态是RUNNING，但是数据没有同步
执行info idstaa,detail,结果为如下。但是IDSTAA.out这个文件没有生成，而ggserr.log也没发现什么异常。

Report file /ogg/ogg11g/dirrpt/IDSTAA.rpt
Parameter file /ogg/ogg11g/dirprm/idstaa.prm
Checkpoint file /ogg/ogg11g/dirchk/IDSTAA.cpr
Process file /ogg/ogg11g/dirpcs/IDSTAA.pcr
Stdout file /ogg/ogg11g/dirout/IDSTAA.out
Error log /ogg/ogg11g/ggserr.log

我执行stop isrcaa，结果居然这个进程居然停不下来，为何GoldenGate replicat进程显示running而不应用任何记录，这篇文章有操作说明，但是也得不清楚，搞不清楚在做什么，最简单的是将目标端、源端的进程都删掉，然后再重启源端进程

ps -ef|grep isrcaa
ps -ef|grep idstaa

启动后，参考Goldjengate-如何查看OGG进程已无延迟执行lag isrcaa，结果提示ERRO:sending message to EXTRACT ISCRAA(Timeout waiting for message
但是执行查看其它进程的lag，就有数据返回
执行命令

select * from dba_hist_active_sess_history where module like 'OGG-IDSTAA%'

可以看到大量的waiting,这里查不到源端的module。
在view report idstaa中找到每条数据插入在idstaa.dsc文件中。
最终执行tail -fn100 /ogg/ogg11g/dirrpt/idstaa.dsc中找到异常数据，
原因是因为目标库的字符集是 AL32UTF8，中文占3个字节，源端是 ZHS16GBK，中文占2个字节，所以到目标库后，长度就少了。调整长度就ok了。
3.3 ogg字符集
当时我想当然的理解目标端配置为目标端的字符集，没有想到在ogg中目标端要配置与源端一样的字符集，否则会导致乱码，例如源端是 ZHS16GBK，那么目标端也应该设置为ZHS16GBK，与目标端NLS_LANG的AL32UTF8无关，由ogg自动完成。
3.4 ogg同步机制
ogg的同步是基于归档日志的，所以只要表发生insert或者update，都会影响ogg的再次同步，批量操作，可能会到值ogg进程无法关闭。
3.5 mgr日志
ogg同步的数据会存在日志文件中，故需要设置存货周期

GGSCI >stop mgr
GGSCI >edit param mgr
autorestart extract *,retries 5,waitminutes 2
purgeoldextracts /ogg/ogg12c/dirdat/*,usecheckpoints,minkeepdays 7
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

4 数据处理
4.1 spool
下面的命令是一个完整的脚本，将查询到结果数据导入到csv文件中

-- host mkdir 调用操作系统的命令会创建一个文件夹
host mkdir log
--  verify off:设置是否显示替代变量被替代前后的语句 echo off:显示start启动的脚本中的每个sql命令，缺省为on
set verify off echo off
-- 回显本次sql命令处理的记录条数，缺省为on
set feedback off
-- 去除标准输出每行的拖尾空格，缺省为off
set trimout on
-- 去除重定向（spool）输出每行的拖尾空格，缺省为off
set trimspool on 
-- 显示脚本中的命令的执行结果，缺省为on
set termout on
-- 输出每页行数，缺省为24,为了避免分页，可设定为0
set pagesize 0 linesize 3000
set numwidth 30
-- 域输出分隔符
set colsep 
set colsep ','
-- 打印
prompt "问题数据"
spool "./log/问题数据.csv"
set term off
set timing off
select 'name1, name2, name3, name4' from dual;
-- 时间精确到0.01秒。也就是10毫秒。
set timing on
select name1, name2, name3, name4from my_table where length(name1) != lengthb(name1);
spool off
set term on

4.2 ORA-01950
这个问题解决方案一堆，例如下面的命令，但是我遇到的情况是怎么执行都没有用

alter user my_user quota unlimited on your_tablespace;
grant unlimited tablespace to my_user;

执行select * from user_ts_quotas发现表空间有权限
再执行查看表空间使用情况，发现TEMP表空间资源耗尽，原来问题在此。

4.3 one or more UTF8 fields contain non-UTF8 data
NLS_LANG为AMERICAN_AMERICA.AL32UTF8，在plsql中直接查看中文显示正常，
但是在plsql中对某条数据进行for update 的时候，提示下面的警告：

参考Oracle判断字段中是否包含中文(若有，取出该中文的方法)

select * from my_table where asciistr(name) like '%\%';
select * from my_table where length(name)=lengthhb(name);

修正中文字符集,注意下面语句中UTF8是目标字符集，而ZHS16GBK源字符集,

update my_table set name = convert(name,'UTF8','ZHS16GBK');

需要注意的是，如果之前的字段，如果原本就是UTF8,那么执行convert(name,‘UTF8’,‘ZHS16GBK’)就会是乱码,这样带来的问题是，我怎么知道，某个中文当前是何种字符集呢？既然utf8中中文占3个字节，而GBk是2个字节.于是演化为下面的语句，但是这并不能解决全量的，如果name中包含了数字、字母或者特殊字符，那么就会有问题。

select * from my_table where length(name)*3 != lengthb(name)

通过中文正则，我又想到一个解决办法，如下
先创建日志输出目录

sqlplus / as sysdba
create or replace directory prec_log as '/home/oracle/log';
grant read,write on directory prec_log to your_user;

再创建存储过程

create or replace procedure tmp_deal_1
as
  -- 定义中文字段的游标
   cursor cur1 is  select code,name from  teacher where not regexp_like(name,'[' || unistr('\4e00') || '-' || unistr('\9fa5') || ']','');
   v_code  teacher.code%TYPE;
   v_name   teacher.name%TYPE;
   out_log utl_file.file_type;
   begin
     -- 写入文件
     out_log := utl_file.fopen('PREC_LOG','tmp_utf8_11.log','a');
     -- 处理name
     utl_file.put_line(out_log,'-------------------------deal name----------------------- ' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),true);
     for v_item in cur1 loop
       begin
         v_code:=v_item.code;
         v_name:=v_item.name;
         utl_file.put_line(out_log,'in deal code is ' || v_code ,true );
         update teacher set name=convert(v_name,'AL32UTF8','ZHS16GBK') where code=v_code;
         commit;
         exception
             when others then
                  utl_file.put_line(out_log,'exception code is ' || v_code || ' msg is ' || SUBSTR(SQLERRM, 1, 200),true );
                  end;
         end loop;
         utl_file.fclose(out_log);
     end;

另外还发现一种解决办法，即判断是否包含非中文的，就把他转成中文的

update my_table set mc= convert(mc,'UTF8','ZHS16GBK') where regexp_like(mc,'[' || unistr('\4e00') || '-' || unistr('\9fa5') || ']','')

上面的方法，运行了2个月都没有问题，下面的方法是将GBK的特殊汉字转化为UTF8

update my_table t set t.mc= convert(t.mc,'UTF8','ZHS16GBK')
 where regexp_like(t.mc,'[' || unistr('\B0A1') || '-' || unistr('\F7FE') || ']','')

这样还不够,下面的语句又可以处理一部分

update my_table  set mc= convert(mc,'UTF8','ZHS16GBK') where ASCIISTR(mc) LIKE '%\FFFD%';

这样其实还不够，需要增加下面的语句

update my_table set skfwyj = convert(mc,'UTF8','ZHS16GBK') where mc is not null and ASCIISTR(mc) is null;

warrah

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
orace数据库生产环境操作实录

最近在做一次大批量数据库同步的工作，因为很久没有弄oracle了，遇到了较大困难，客户又催的比较紧，这里再捡一下。1 基础Oracle 数据库、实例、用户、表空间、表之间的关系讲解，实例名也叫sid，一个数据库可以有多个实例jdbc:oracle:thin:@localhost:1521:orcl(orcl就为数据库实例名)ORACLE中SID和SERVICE_NAME的区别,SERVI...
复制链接

扫一扫