No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7200
3 B. Vroman 6200
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3200
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h7
Act. users last hour0
Registered user hits last week192
Registered user hits last month1051
Go up

impdp
Next thread: RU PATCH ON RAC ENV
Prev thread: CTX indexes

Message Score Author Date
Hi Guys I am busy doing schema import on test d...... Tso P Apr 29, 2019, 11:42
As a followup the indexes were due to the sys as s...... Tso P Apr 29, 2019, 15:41
Never had that before (varchar to numbers) You mi...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Rob Pattyn Apr 30, 2019, 11:34
Thanks Rob The ddl looks like these: -- CONN...... Tso P May 01, 2019, 15:40
From source DB using DBMS_METADATA.get_ddl: CRE...... Tso P May 01, 2019, 20:03
Hi Tso, can you login to the database where you...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek May 01, 2019, 22:48
Thanks Ales <pre> SQL> show parameter compatib...... Tso P May 02, 2019, 08:06
Hi Tso, now you have the answer. Long identifie...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Ales Kavsek May 02, 2019, 10:17
Thanks Ales Much appreciated !!!... Tso P May 02, 2019, 11:14
I have changed compatibility to 12.2.0 then went t...... Tso P May 09, 2019, 12:12
Hi, you hit another bug (or regression of the ...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Ales Kavsek May 09, 2019, 14:38
Thanks Ales... Tso P May 09, 2019, 14:54

Follow up by mail Click here


Subject: impdp
Author: Tso P, South Africa
Date: Apr 29, 2019, 11:42, 23 days ago
Os info: solaris 10
Oracle info: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Message: Hi Guys

I am busy doing schema import on test database and now I have used parallel and excluded the statistics during the export but when I do the import I get the below errors :

W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
W-1 Completed 56 PACKAGE_BODY objects in 9 seconds
W-1 Completed by worker 1 19 PACKAGE_BODY objects in 8 seconds
W-1 Completed by worker 2 15 PACKAGE_BODY objects in 9 seconds
W-1 Completed by worker 3 10 PACKAGE_BODY objects in 9 seconds
W-1 Completed by worker 4 12 PACKAGE_BODY objects in 8 seconds
ORA-39083: Object type TABLE:"SCHEMA_OWNER"."LOG_APPLICATION_EVENTS" failed to create with error:
ORA-12899: value too large for column "VIRTUAL_APPLICATION" (actual: 480, maximum: 120)

Failing sql is:
ALTER TABLE "SCHEMA_OWNER"."LOG_APPLICATION_EVENTS" MODIFY ("VIRTUAL_APPLICATION" VARCHAR2(30 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.
sourceapp' RETURNING VARCHAR2(120 CHAR) NULL ON ERROR)) VIRTUAL )

and also these:

W-4 Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX:"SCHEMA_OWNER"."TABLE_LINE_IDX1" failed to create with error:
ORA-20000: Oracle Text error:
DRG-52204: error while registering index
DRG-10507: duplicate index name: TABLE_LINE_IDX1, owner: SCHEMA_OWNER


Please help !!!

Thanks in advance !!!





Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: Apr 29, 2019, 15:41, 23 days ago
Message: As a followup the indexes were due to the sys as sysdba that I use during the import.

I corrected that by using system and I realized that DBA and IMP_FULL_DATABASE roles lack become user privilege.

Now, I saw something weird on LOG_APPLICATION_EVENTS table:

On Source:


Name Null Type
--------------------------- -------- ---------------------------
ID NOT NULL NUMBER(38)
EVENT_DOC NOT NULL CLOB
PERSISTED_TIMESTAMP NOT NULL TIMESTAMP(6)
VIRTUAL_APPLICATION VARCHAR2(30 CHAR)
VIRTUAL_APPLICATION_VERSION VARCHAR2(60 CHAR)
VIRTUAL_HOSTNAME VARCHAR2(512 CHAR)
VIRTUAL_INSTANCENAME VARCHAR2(100 CHAR)
VIRTUAL_INSTANCETYPE VARCHAR2(100 CHAR)
VIRTUAL_EVENT_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
VIRTUAL_EVENT_DATE DATE
VIRTUAL_EVENT_LEVEL VARCHAR2(20 CHAR)
VIRTUAL_EVENT_MESSAGE VARCHAR2(4000 CHAR)
VIRTUAL_EVENT_LOGGER VARCHAR2(1024 CHAR)
VIRTUAL_EVENT_THREAD VARCHAR2(1024 CHAR)
VIRTUAL_CONTEXT_ITEM_ID VARCHAR2(100 CHAR)
VIRTUAL_CONTEXT_ITEM_TYPE VARCHAR2(100 CHAR)


On the target:


Name Null Type
--------------------------- -------- ------------
ID NOT NULL NUMBER(38)
EVENT_DOC NOT NULL CLOB
PERSISTED_TIMESTAMP NOT NULL TIMESTAMP(6)
VIRTUAL_APPLICATION NUMBER
VIRTUAL_APPLICATION_VERSION NUMBER
VIRTUAL_HOSTNAME NUMBER
VIRTUAL_INSTANCENAME NUMBER
VIRTUAL_INSTANCETYPE NUMBER
VIRTUAL_EVENT_TIMESTAMP NUMBER
VIRTUAL_EVENT_DATE NUMBER
VIRTUAL_EVENT_LEVEL NUMBER
VIRTUAL_EVENT_MESSAGE NUMBER
VIRTUAL_EVENT_LOGGER NUMBER
VIRTUAL_EVENT_THREAD NUMBER
VIRTUAL_CONTEXT_ITEM_ID NUMBER
VIRTUAL_CONTEXT_ITEM_TYPE NUMBER


The data type changes from VARCHAR2 to NUMBER during impdp.

I suspect this might the problem...

On the source the CDB and PDB their nls_length_semantics are on CHAR and on target the CBD is on BYTE while the PDB is on CHAR.

I think this might be the problem.

Thanks



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Rob Pattyn, Belgium
Date: Apr 30, 2019, 11:34, 22 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Never had that before (varchar to numbers)
You might want to create an sql file from the dump using impdp sqlfile =
to verify/correct it.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 01, 2019, 15:40, 21 days ago
Message: Thanks Rob

The ddl looks like these:

-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "APP_AUTHS_OWNER"."LOG_APPLICATION_EVENTS"
( "ID" NUMBER(38,0) NOT NULL ENABLE,
"EVENT_DOC" CLOB NOT NULL ENABLE,
"PERSISTED_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE,
"VIRTUAL_APPLICATION" GENERATED ALWAYS AS (4) VIRTUAL ,
"VIRTUAL_APPLICATION_VERSION" GENERATED ALWAYS AS (5) VIRTUAL ,
"VIRTUAL_HOSTNAME" GENERATED ALWAYS AS (6) VIRTUAL ,
"VIRTUAL_INSTANCENAME" GENERATED ALWAYS AS (7) VIRTUAL ,
"VIRTUAL_INSTANCETYPE" GENERATED ALWAYS AS (8) VIRTUAL ,
"VIRTUAL_EVENT_TIMESTAMP" GENERATED ALWAYS AS (9) VIRTUAL ,
"VIRTUAL_EVENT_DATE" GENERATED ALWAYS AS (10) VIRTUAL ,
"VIRTUAL_EVENT_LEVEL" GENERATED ALWAYS AS (11) VIRTUAL ,
"VIRTUAL_EVENT_MESSAGE" GENERATED ALWAYS AS (12) VIRTUAL ,
"VIRTUAL_EVENT_LOGGER" GENERATED ALWAYS AS (13) VIRTUAL ,
"VIRTUAL_EVENT_THREAD" GENERATED ALWAYS AS (14) VIRTUAL ,
"VIRTUAL_CONTEXT_ITEM_ID" GENERATED ALWAYS AS (15) VIRTUAL ,
"VIRTUAL_CONTEXT_ITEM_TYPE" GENERATED ALWAYS AS (16) VIRTUAL
)
PARTITION BY RANGE ("PERSISTED_TIMESTAMP") INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
(PARTITION "P0" VALUES LESS THAN (TIMESTAMP' 2018-03-01 00:00:00') ) ;

What might be the issue here?

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 01, 2019, 20:03, 20 days ago
Message: From source DB using DBMS_METADATA.get_ddl:

CREATE TABLE "LOG_APPLICATION_EVENTS"
( "ID" NUMBER(38,0) NOT NULL ENABLE,
"EVENT_DOC" CLOB NOT NULL ENABLE,
"PERSISTED_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE,
"VIRTUAL_APPLICATION" VARCHAR2(30 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.sourceapp' RETURNING VARCHAR2(120 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_APPLICATION_VERSION" VARCHAR2(60 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.sourceappversion' RETURNING VARCHAR2(240 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_HOSTNAME" VARCHAR2(512 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.targethost' RETURNING VARCHAR2(2048 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_INSTANCENAME" VARCHAR2(100 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.instancename' RETURNING VARCHAR2(400 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_INSTANCETYPE" VARCHAR2(100 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.instancetype' RETURNING VARCHAR2(400 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_EVENT_TIMESTAMP" TIMESTAMP (6) WITH TIME ZONE GENERATED ALWAYS AS (TO_TIMESTAMP_TZ(JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.timestamp' RETURNING VARCHAR2(4000 CHAR) NULL ON ERROR),'YYYY-MM-DD"T"HH24:MI:SS.ff3TZH:TZM')) VIRTUAL ,
"VIRTUAL_EVENT_DATE" DATE GENERATED ALWAYS AS (TO_DATE(SUBSTR(JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.timestamp' RETURNING VARCHAR2(4000 CHAR) NULL ON ERROR),1,19),'YYYY-MM-DD"T"HH24:MI:SS')) VIRTUAL ,
"VIRTUAL_EVENT_LEVEL" VARCHAR2(20 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.ohiLevel' RETURNING VARCHAR2(80 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_EVENT_MESSAGE" VARCHAR2(4000 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.message' RETURNING VARCHAR2(4000 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_EVENT_LOGGER" VARCHAR2(1024 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.logger_name' RETURNING VARCHAR2(4000 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_EVENT_THREAD" VARCHAR2(1024 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.thread_name' RETURNING VARCHAR2(4000 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_CONTEXT_ITEM_ID" VARCHAR2(100 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.executionid' RETURNING VARCHAR2(400 CHAR) NULL ON ERROR)) VIRTUAL ,
"VIRTUAL_CONTEXT_ITEM_TYPE" VARCHAR2(100 CHAR) GENERATED ALWAYS AS (JSON_VALUE("EVENT_DOC" FORMAT JSON , '$.executiontype' RETURNING VARCHAR2(400 CHAR) NULL ON ERROR)) VIRTUAL ,
CONSTRAINT "LOG_APPLICATION_EVENT_JSON_CK" CHECK (event_doc is json) ENABLE
) PARTITION BY RANGE ("PERSISTED_TIMESTAMP") INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
(PARTITION "P0" VALUES LESS THAN (TIMESTAMP' 2018-03-01 00:00:00') ) ;

I tried to recreate the table using the DDL from DBMS_METADATA.get_ddl on the same database but different schema/user but I get these:

(PARTITION "P0" VALUES LESS THAN (TIMESTAMP' 2018-03-01 00:00:00') ) ; 14 15 16 17 18
"VIRTUAL_EVENT_THREAD" VARCHAR2(1024 CHAR) GENERATLOG_APPLICATION_EVENT_JSON_CK" CHECK (event_doc is json) ENABLE
*
ERROR at line 15:
ORA-00972: identifier is too long

NB I recently upgraded these databases from 12c Release 12.1.0.2.0 to Release 12.2.0.1.0.

Please assist...

Thanks in advance...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Ales Kavsek, Slovenia
Date: May 01, 2019, 22:48, 20 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Tso,

can you login to the database where you received "ORA-00972: identifier is too long" and post the result of:

SQL> show parameter compatible

Regards,
Ales

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 02, 2019, 08:06, 20 days ago
Message: Thanks Ales


SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE


Thanks for the help...

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Ales Kavsek, Slovenia
Date: May 02, 2019, 10:17, 20 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hi Tso,

now you have the answer. Long identifiers were introduced with 12.2 and your're running 12.2 in 12.1 compatible mode.

Regards,
Ales

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 02, 2019, 11:14, 20 days ago
Message: Thanks Ales

Much appreciated !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 09, 2019, 12:12, 13 days ago
Message: I have changed compatibility to 12.2.0 then went to the source get the dump and try the import again then I got another issue, that is Unpublished Bug 23715518. The issue has been described in the Doc ID 2385430.1, I have applied the workaround on the as described in the DOC that fix the problem.

Now, I still get the below errors:

ORA-39083: Object type TABLE:"SCHEMA_OWNER"."LOG_APPLICATION_EVENTS" failed to create with error:
ORA-12899: value too large for column "VIRTUAL_APPLICATION" (actual: 480, maximum: 120)

See below:


SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE


What might be the issue here ?

Thanks for the replies...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Ales Kavsek, Slovenia
Date: May 09, 2019, 14:38, 13 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hi,

you hit another bug (or regression of the bug), this time it's related to importing table with virtual columns. You'll find several references if you search for:

Oracle Virtual Columns error during datapump import ORA-12899

start with document:

1968276.1 "DataPump Import (Impdp) Fails With ORA-39083, ORA-12899 For Table With Virtual Column"

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: impdp
Author: Tso P, South Africa
Date: May 09, 2019, 14:54, 13 days ago
Message: Thanks Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here