impdp – OracleBlog

同字符集impdp报错ORA-01401

2016-10-20 2条评论

客户有个数据库从HP平台迁移到linux平台，字符集相同。impdp的时候报错ORA-01401了。

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" failed to create with error:
ORA-01401: inserted value too large for column
Failing sql is:
CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE"
VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 M
ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN" failed to create with error:
ORA-01401: inserted value too large for column
Failing sql is:
CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "MATRIX_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "B
ENCHMARK_ID" VARCHAR2(16 BYTE), "GROUP_TYPE" VARCHAR2(2 BYTE), "STATUS" VARCHAR2(1 BYTE), "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate, "U
ORA-39083: Object type TABLE:"MYORDATA"."ASS_QUITTK_GREEN" failed to create with error:
ORA-01401: inserted value too large for column
Failing sql is:
CREATE TABLE "MYORDATA"."ASS_QUITTK_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "CREATE_BY" VARCHAR2(100 BYTE), "CREATE_DATE" VARC
HAR2(16 BYTE) DEFAULT sysdate, "UPDATE_BY" VARCHAR2(100 BYTE), "UPDATE_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN_MEMORY" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE"

VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 M

ORA-39083: Object type TABLE:"MYORDATA"."ASS_ACCHSHT_GREEN" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_ACCHSHT_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "MATRIX_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "B

ENCHMARK_ID" VARCHAR2(16 BYTE), "GROUP_TYPE" VARCHAR2(2 BYTE), "STATUS" VARCHAR2(1 BYTE), "ADDED_BY" VARCHAR2(100 BYTE), "ADDED_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate, "U

ORA-39083: Object type TABLE:"MYORDATA"."ASS_QUITTK_GREEN" failed to create with error:

ORA-01401: inserted value too large for column

Failing sql is:

CREATE TABLE "MYORDATA"."ASS_QUITTK_GREEN" ("GROUP_ID" VARCHAR2(16 BYTE) NOT NULL ENABLE, "GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, "CREATE_BY" VARCHAR2(100 BYTE), "CREATE_DATE" VARC

HAR2(16 BYTE) DEFAULT sysdate, "UPDATE_BY" VARCHAR2(100 BYTE), "UPDATE_DATE" VARCHAR2(16 BYTE) DEFAULT sysdate) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

不仅仅是在导表结构+数据的时候报错，单独导metadata的时候，也报上面的错。正常情况下，ORA-01401是因为做insert […]

Study note

一窥12.2的新特性

2016-08-03 没有评论

9月的OOW快要到了，估计12.2的版本届时也会发布，今天我们来看提前一下相比于12.1，12.2多了那些新特性。（注：这些新特性，大家仅当rumor看看就好，真正的12.2新特性，以实际发布为准。） (1)PDB and CDB相关：

(1.1) PDB level snapshot
(1.2) cdb级别设置sga_target,pdb级别也可以（才可以）设置sga_target,
(1.2.1)总计各个pdb的sga_target可以大于instance级别的sga_target,但是单个pdb的sga_target不能大于instance级别的sga_target
(1.2.2)sga_min_size参数，可以在pdb级别设置。
(1.3) pdb级别设置 PGA_AGGREGATE_LIMIT 和 PGA_AGGREGATE_TARGET （默认情况下PGA_AGGREGATE_TARGET×2=PGA_AGGREGATE_LIMIT）
(1.4)控制PDB的IO使用，在12.2引入2个初始化参数MAX_IOPS和MAX_MBPS（注：这2个参数对non-cdb不适用，对exadata不适用。因为exadata有IO resource manager在cell上）
(1.5)12.2支持cdb的ADO
(1.6)PDB hot clone( nearly hot clone)

1

2

3

4

5

6

7

8

(1.1) PDB level snapshot

(1.2) cdb级别设置sga_target,pdb级别也可以（才可以）设置sga_target,

(1.2.1)总计各个pdb的sga_target可以大于instance级别的sga_target,但是单个pdb的sga_target不能大于instance级别的sga_target

(1.2.2)sga_min_size参数，可以在pdb级别设置。

(1.3) pdb级别设置 PGA_AGGREGATE_LIMIT 和 PGA_AGGREGATE_TARGET （默认情况下PGA_AGGREGATE_TARGET×2=PGA_AGGREGATE_LIMIT）

(1.4)控制PDB的IO使用，在12.2引入2个初始化参数MAX_IOPS和MAX_MBPS（注：这2个参数对non-cdb不适用，对exadata不适用。因为exadata有IO resource manager在cell上）

(1.5)12.2支持cdb的ADO

(1.6)PDB hot clone( nearly hot clone)

Study note

升级到12c，你应该了解的一些东西

2016-07-14 7条评论

Mike Dietrich有一篇600多页的ppt，叫『Upgrade, Migrate & Consolidate to Oracle Database 12c』。在这个ppt里面，有一些是标记了“NEW”了的表示12c的新特性、新行为。（其实升级到11g的时候，也有一篇差不多类似的4 […]

..experience, Working case

12c数据泵导入报错KUP-11014

2016-05-21 没有评论

将10.2.0.5的一个大表导入到12.1.0.2的时候，导出参数是： [oracle10g@testdb tmp]$ cat expdp.par userid=’/ as sysdba’ DIRECTORY=DUMPDIR dumpfile=mytable_%U.dmp tables=schem […]

..experience, Working case

impdp时报错Ora-28031

2013-08-14 没有评论

今天一客户说在做impdp导入时，报错Ora-28031。我们看看ora-28031的报错是什么意思：

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded

1

2

3

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-31625: Schema SYS is needed to import this object, but is unaccessible

ORA-28031: maximum of 148 enabled roles exceeded

从报错信息看，是SYS用户的role超过148个，一开始，我们用

select count(*) from dba_role_privs where grantee = 'SYS';

1	select count(*) from dba_role_privs where grantee = 'SYS';

[…]