装oracle报temp无权限,华为云rds-PostgreSQL增强版替换Oracle常见问题以及解决方案

华为云rds-PostgreSQL增强版提供兼容oracle功能，目前已经做到兼容oracle 90%的语法，在使用PostgreSQL增强版替换oracle的过程中，可能遇到一些问题，这里总结一下，并给出解决方案。

1.1JDBC驱动

基于oracle数据库的应用本身使用oracle的JDBC驱动，换成postgresql增强版需要将JDBC驱动换成postgresql的驱动

1.2事务处理

Oracle数据库，在同一个事务中的多个语句，如某个语句执行出错，该语句不影响其他语句的执行，如事务提交，则执行成功语句会持久化到DB中。PG数据库，在同一个事务中的多个语句，如某个SQL语句执行出错，则就算在其后执行commit，事务也会回滚。如在该出错语句之后执行其他DML语句，则会报错。因此Oracle捕获异常后是重做异常sql，而PG是先回滚事务，然后重做整个事务。

1.3查询结果预期有序一定要加order by

oracle数据库在数据量少，查询结果集小，并且数据没有更新时，结果集顺序相对稳定，但是不能绝对保证顺序，当结果集、数据量增大、数据频繁更新时，结果集一定会紊乱，PostgreSQL同理，在一定情况下PG这个情况更容易出现，因此查询结果有顺序的预期一定要加order by

1.4数据库对象大小写

oracle数据字典默认大写，PG数据字典默认小写，因此创建和引用对象时不加引号，如果涉及特殊字符，PG创建数据库对象时要小写，这样才不区分SQL的大小写

例如：oracle

SQL>   drop table test;

create   table "TEST"(ID varchar(20));

insert   into test(id) values('a');

insert   into "TEST"(id) values('b');

Table   dropped.

例如：postgresqltest=>   drop table test;

DROP TABLE

test=> create   table "TEST"(ID varchar(20));

CREATE   TABLE

test=>   insert into test(id) values('a');

ERROR:  relation "test" does not exist

LINE 1:   insert into test(id) values('a');

^

test=>   insert into "TEST"(id) values('b');

INSERT 0 1

1.5列(别)名为关键字

Oracle和PG的数据字典关键字集合是有差异、有交集的。Oracle中比如text这样的关键字可以直接作为列的别名，比如：select xx text from t，目前我们已经做了很多关键字直接作为别名的兼容，但是目前还是没有完全支持，因此建议写sql或者改问题过程中逐步加as，比如select xx as text from t

1.6sequence范围不一致

修改sequence maxvalue的值

PG范围为：1到9223372036854775807

oracle范围为：1到9.99...9 x 10125

1.7同义词

PG中没有同义词，自动创建为视图，转换过来的视图名称与存在的表名相同，需要修改视图名称。

1.8float类型精度

oracle支持float(126)，PG最大支持到float(53)，单在一般情况下PG的精度已经够用了，开发中遇到此类问题可以直接修改。

1.9primary等关键字不能用做列名

oracle支持primary作为列名，PG中要用primary作为列名的话需要加上双引号，例如：create   table t1(“primary” int);

建议最好不用用primary等关键字做为列名，可以修改为primary_key就没有问题了。

1.10不能modify带有view的列

Oracle可以直接modify带有view的列，PG中必须删除view才可以modify表，建议用户先删除view再modify表，最后重建view

1.11在view上创建primary key

Oracle可以在view上直接创建primary key等约束，PG中不能在view上创建约束，建议用户直接在表上创建约束。

1.12Data类型差值不一致问题

Oracle执行以下语句返回结果SQL>   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

TO_DATE('2019-06-0611:41:07','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2018-06-0611:06:08

--------------------------------------------------------------------------------

365.024294

PG执行以下语句返回结果：postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

?column?

-------------------

365 days 00:34:59

(1 row)

PG解决方案，在结果上加上一个0，让数据不做转换postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') + 0from   dual;

?column?

-------------------

365.024293981481

(1 row)

1.13使用pg_dump导出的线下库，在导入到PG线上库时报错“must member of role eas850pgnew”

原因分析：用户线下导出库是用用户eas850pgnew，查看了导出dump出的脚本，每个对象在创建完后都会有alter table xxx owner to eas850pgnew。正是这句报的错，因为用户使用了root账户创建了database，并导入到owner为root的database中，所有表默认owner都是root，而root不是超级权限，在执行alter table xxx owner to eas850pgnew时报错。

解决方法：

a.使用root创建数据库test

create database test;

b.连接上test库创建新用户eas850pgnew

create role eas850pgnew createdb createrole login password 'Perftest1!'

c.使用新用户eas850pgnew创建目标数据库eas850pgnew_ora

crete database eas850pgnew_ora;

d.使用pg_admin通过用户eas850pgnew导入库到目标库eas850pgnew_ora中

1.14时间格式不一致问题

可以按照用户使用的时间格式来设置nls_timestamp_format或则nls_date_format参数

1.15PG增强版不能在postgres库中创建role问题

a.连接数据库postgres，创建一个test库

b.连接test库，在test库中执行创建用户操作

1.16更改数据库中所有对象的owner

从oracle中迁移过来的数据库默认owner是root，需要将owner修改为用户自己新建的用户，修改方法如下：假设要将root下的test库中所有对象owner从root改为用户post

c.用post账户登录test库执行：grant post to root;

d.用root账户登录test库执行：reassign owned by root to post;

1.17PG增强版中定长字符串长度不够会补充空格

例如：char(10)类型的列，在插入数据后，长度不够会补充空格，再如，以下存储过程中用到了定长字符串，在增强版上不能返回正确的值

CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

RETURNS timestamp without time zone AS

$BODY$

DECLARE

YEAR_CONST Char(15) := 'year';

MONTH_CONST Char(15) := 'month';

WEEK_CONST Char(15) := 'week';

DAY_CONST Char(15) := 'day';

HOUR_CONST Char(15) := 'hour';

MIN_CONST Char(15) :='minute';

SECOND_CONST Char(15) :='second';

dateTemp timestamp;

intervals interval;

BEGIN

if inputdate is null then return null ;   end if;

IF lower($1) = lower(YEAR_CONST) THEN

select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;

ELSEIF lower($1) = lower(MONTH_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals;

ELSEIF lower($1) = lower(WEEK_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals;

ELSEIF lower($1) = lower(DAY_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals;

ELSEIF lower($1) = lower(HOUR_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals;

ELSEIF lower($1) = lower(MIN_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals;

ELSEIF lower($1) = lower(SECOND_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;

END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;

END;

$BODY$

LANGUAGE   PLPGSQL;

在PG增强版上执行会返回NULL，原因是char(10)=’year’其实会在year后面补充6个空格，直到长度为10，所以在PG增强版上可以使用变长字符串来解决这个问题，如下：CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

RETURNS timestamp without time zone AS

$BODY$

DECLARE

YEAR_CONST varchar(15) := 'year';

MONTH_CONST varchar(15) := 'month';

WEEK_CONST varchar(15) := 'week';

DAY_CONST varchar(15) := 'day';

HOUR_CONST varchar(15) := 'hour';

MIN_CONST varchar(15) :='minute';

SECOND_CONST varchar(15) :='second';

dateTemp timestamp;

intervals interval;

BEGIN

if inputdate is null then return null ;   end if;

IF lower($1) = lower(YEAR_CONST) THEN

select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;

ELSEIF lower($1) = lower(MONTH_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals;

ELSEIF lower($1) = lower(WEEK_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals;

ELSEIF lower($1) = lower(DAY_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals;

ELSEIF lower($1) = lower(HOUR_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals;

ELSEIF lower($1) = lower(MIN_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals;

ELSEIF lower($1) = lower(SECOND_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;

END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;

END;

$BODY$

LANGUAGE   PLPGSQL;

1.18Date类型相减返回值为interval类型的问题

例如：PG增强版下执行以下语句：glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

?column?

----------

1 day

(1 row)

可以看到，执行结果是一个interval类型，而在PG社区版上执行结果是一个数字glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

?column?

----------

1

(1 row)

如果想要在PG增强版上得到和PG社区版一样的结果，可以做如下修改：

方案1：+ 0glow=>   select cast('2019-08-08' as date) - cast('2019-08-01' as date) +0;

?column?

----------

7

(1 row)

方案2：date加上双引号glow=>   select cast('2019-08-08' as "date") - cast('2019-08-01' as   "date");

?column?

----------

7

(1 row)

1.19加密方式password_encryption=scram-sha-256导致用户无法连接

目前默认的加密方式为scram-sha-256，这种加密方式会导致部分用户客户端无法连接到服务器的问题(客户端不支持这种加密方式)，一般会报如下错误：An error   occurred while establishing the connection:

Long Message:

不支援10验证类型。请核对您已经组态pg_hba.conf文件包含客户端的IP位址或网路区段，以及驱动程序所支援的验证架构模式已被支援。

Details:

Type: org.postgresql.util.PSQLException

解决方法：后台修改password_encryption=md5，然后在控制台上修改rds的root密码，密码设置可以和原来一样。

为什么要修改密码呢？

答：因为加密方式从scram-sha-256变为md5后，原来的密码用md5方式无法解开，所以需要修改密码，修改密码的过程即重新对密码进行md5加密过程。

1.20PG增强版安装插件的方式

为了安全考虑，目前不允许roor以外的用户安装插件，并且在安装插件的时候不能使用社区版的create extension ‘插件名字’的方式，我们提供了插件安装接口

Root无权限直接用create extension安装插件glow=> create   extension postgis;

ERROR:  permission denied to create extension   "postgis"

HINT:  Must be superuser to create this extension.

使用control_extension()来安装卸载插件glow=> select   control_extension('create','postgis');

control_extension

------------------------------

create postgis successfully.

(1 row)

glow=> select control_extension('drop','postgis');

control_extension

----------------------------

drop postgis successfully.

(1 row)