{"id":56,"date":"2020-02-02T12:16:02","date_gmt":"2020-02-02T12:16:02","guid":{"rendered":"http:\/\/ictbank.ir\/blog\/?p=56"},"modified":"2022-12-03T11:36:11","modified_gmt":"2022-12-03T11:36:11","slug":"impdp-errors-ora-02374-ora-12899","status":"publish","type":"post","link":"https:\/\/ictbank.ir\/blog\/impdp-errors-ora-02374-ora-12899\/","title":{"rendered":"IMPDP Errors: ORA-02374, ORA-12899"},"content":{"rendered":"\n<p>If the Character set of source and destination database are not equal, you will see below errors in the impdp log file: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nORA-02374: conversion error loading table &quot;SCHEMA1&quot;.&quot;TABLE1&quot; ORA-12899: value too large for column COLUMN1 (actual: 101, maximum: 100)\n<\/pre><\/div>\n\n\n<p> to solve this error, you have to restore the <strong>structure of tables<\/strong> first <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nimpdp schemas=schema1 dumpfile=dump.dmp logfile=log.log directory=dumps content=&amp;lt;b&gt;METADATA_ONLY&amp;lt;\/b&gt;\n<\/pre><\/div>\n\n\n<p>after that, you have to create a table and fill it with information about columns with have CHAR or VARCHAR datatype:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nCREATE TABLE SEMANTICS$\n(S_OWNER VARCHAR2(40),\nS_TABLE_NAME VARCHAR2(40),\nS_COLUMN_NAME VARCHAR2(40),\nS_DATA_TYPE VARCHAR2(40),\nS_CHAR_LENGTH NUMBER);\n\nINSERT INTO SEMANTICS$\n SELECT C.OWNER, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHAR_LENGTH\n   FROM ALL_TAB_COLUMNS C, ALL_TABLES T\n  WHERE C.OWNER = T.OWNER\n    AND T.OWNER IN (&#039;&#x5B;b]SCHEMA1&#x5B;\/b]&#039;)\n       -- ALL ORACLE PROVIDED USERS \n    AND C.TABLE_NAME = T.TABLE_NAME\n    AND C.CHAR_USED = &#039;B&#039; -- ONLY NEED TO LOOK FOR TABLES WHO ARE NOT YET CHAR SEMANTICS. \n    AND T.PARTITIONED != &#039;YES&#039; -- EXCLUDE PARTITIONED TABLES\n    AND C.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)\n    AND C.DATA_TYPE IN (&#039;VARCHAR2&#039;, &#039;CHAR&#039;);\n  COMMIT;\n<\/pre><\/div>\n\n\n<p>then you have to modify these fields with alter table command as below: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nDECLARE\n  CURSOR C1 IS\n    SELECT * FROM SEMANTICS$;\n  V_STATEMENT VARCHAR2(255);\n  V_NC        NUMBER(10);\n  V_NT        NUMBER(10);\nBEGIN\n  EXECUTE IMMEDIATE &#039;SELECT COUNT(*) FROM SEMANTICS$&#039;\n    INTO V_NC;\n  EXECUTE IMMEDIATE &#039;SELECT COUNT(DISTINCT S_TABLE_NAME) FROM SEMANTICS$&#039;\n    INTO V_NT;\n  FOR R1 IN C1 LOOP\n    V_STATEMENT := &#039;ALTER TABLE &#039; || R1.S_OWNER || &#039;.&#039; || R1.S_TABLE_NAME;\n    V_STATEMENT := V_STATEMENT || &#039; MODIFY (&#039; || R1.S_COLUMN_NAME || &#039; &#039;;\n    V_STATEMENT := V_STATEMENT || R1.S_DATA_TYPE || &#039;(&#039; || R1.S_CHAR_LENGTH;\n    V_STATEMENT := V_STATEMENT || &#039; CHAR));&#039;;\n    EXECUTE IMMEDIATE V_STATEMENT;\n  END LOOP;\n  DBMS_OUTPUT.PUT_LINE(&#039;DONE&#039;);\nEND;\n<\/pre><\/div>\n\n\n<p>finally, we you have to import data to these tables: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nimpdp schemas=schema1 dumpfile=dump.dmp logfile=log.log directory=dumps content=&amp;lt;b&gt;DATA_ONLY&amp;lt;\/b&gt;\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>If the Character set of source and destination database are not equal, you will see below errors in the impdp log file: to solve this error, you have to restore the structure of tables first after that, you have to create a table and fill it with information about columns with have CHAR or VARCHAR [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,10,9],"tags":[18,6,15,12,13,11,14,16,17,2],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-database","category-expdp-impdp","category-oracle","tag-characterset","tag-database","tag-error","tag-expdp","tag-export","tag-impdp","tag-import","tag-ora-02374","tag-ora-12899","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":4,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":624,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions\/624"}],"wp:attachment":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}