ORA-12899: value too large for column

Obnova tabulky z RMANa v Oracle 12c SQL*Loader Express (12c)

Tomáš Solař – databázový administrátor Health check zdarma
DBA for reference Nezapomeňte patchovat Monitorování databáze Kontakt

2. 3. 2014
Návody

CREATE TABLE "CRPDTA"."F00165T" ("GDOBNM" CHAR(10 BYTE), "GDTXKY" VARCHAR2(254 BYTE), "GDLNGP" CHAR(2 BYTE), "GDTXPO
" VARCHAR2(254 BYTE), "GDCRTU" CHAR(10 BYTE), "GDDQE" NUMBER(6,0), "GDTENT" NUMBER, "GDMUSE" CHAR(10 BYTE), "GDUPMJ" NUMBER(6,0), "GDTDAY" NUMBER, "GDEFTJ" NUMBER(6,0), "GDEXDJ" NUMBER(6,0), "GDPNTC" CHAR(1 BYTE), "GDISTM" CHAR(1 BYTE), "GDISFL" CHAR(1 BY
TE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table "CRPDTA"."F00165"
ORA-12899: value too large for column GDGTITNM (actual: 52, maximum: 50)

Původní
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
EE8MSWIN1250

Nová
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
AL32UTF8

SQL> drop table CRPDTA.F00165;

Table dropped.

$ impdp system@dbserver directory=DATA_PUMP dumpfile=EXPDAT_F00165_F00165T.DMP remap_tablespace=CRPDTA:CRPDTAT content=metadata_only

Import: Release 11.2.0.3.0 - Production on Sun Mar 2 14:40:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@dbserver directory=DATA_PUMP dumpfile=EXPDAT_F00165_F00165T.DMP remap_tablespace=CRPDTA:CRPDTAT content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:40:46

SQL> select c.owner, c.table_name, c.column_name, c.data_type, c.char_length
 2 from all_tab_columns c, all_tables t
 3 where c.owner = t.owner
 4 and t.owner = 'CRPDTA'
 5 and c.table_name = t.table_name
 6 and c.char_used = 'B'
 7 and t.partitioned='YES'
 8 and c.table_name not in (select table_name from all_external_tables)
 9 and c.data_type in ('VARCHAR2', 'CHAR');

no rows selected

SQL> select INDEX_NAME , INDEX_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, FUNCIDX_STATUS
 2 from ALL_INDEXES
 3 where INDEX_TYPE not in ('NORMAL', 'BITMAP','IOT - TOP')
 4 and TABLE_OWNER = 'CRPDTA'
 5 and TABLE_NAME in
 6 (select unique (table_name) from dba_tab_columns where char_used ='B');

no rows selected

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS =
'INVALID' AND OWNER='CRPDTA'; 2

no rows selected

SQL> CREATE TABLE SEMANTICS$(S_OWNER VARCHAR2(40), S_TABLE_NAME VARCHAR2(40),
S_COLUMN_NAME VARCHAR2(40), S_DATA_TYPE VARCHAR2(40), S_CHAR_LENGTH NUMBER); 2

Table created.

SQL> insert into semantics$
 2 select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
 3 from all_tab_columns C, all_tables T
 4 where C.owner = T.owner
 5 and T.owner = 'CRPDTA'
 6 and C.table_name = T.table_name
 7 and C.char_used = 'B'
 8 and T.partitioned != 'YES'
 9 and C.table_name not in (select table_name from all_external_tables)
 10 and C.data_type in ('VARCHAR2', 'CHAR')
 11 /

20 rows created.

SQL> set serveroutput on
SQL> set termout on
SQL> declare
 2 cursor c1 is select * from semantics$;
 3 v_statement varchar2(255);
 4 v_nc number(10);
 5 v_nt number(10);
 6 begin
 7 execute immediate 'select count(*) from semantics$' into v_nc;
 8 execute immediate 'select count(distinct s_table_name) from semantics$' into v_nt;
 9 dbms_output.put_line ('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
 10 for r1 in c1 loop
 11 v_statement := 'ALTER TABLE "' || r1.s_owner || '"."' || r1.s_table_name;
 12 v_statement := v_statement || '" modify ("' || r1.s_column_name || '" ';
 13 v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
 14 v_statement := v_statement || ' CHAR))';
 15 dbms_output.put_line(v_statement);
 16 execute immediate v_statement;
 17 end loop;
 18 dbms_output.put_line('Done');
 19 end;
 20 /
ALTERing 20 columns in 2 tables
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTFUTS4" CHAR(50 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTFUTS3" CHAR(50 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTFUTS2" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTFUTS1" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTFILENM" CHAR(254 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDQUNAM" CHAR(30 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDGTITNM" CHAR(50 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDUSER" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDLNGP" CHAR(2 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDTXKY" VARCHAR2(254 CHAR));
ALTER TABLE "CRPDTA"."F00165" modify ("GDOBNM" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDISFL" CHAR(1 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDISTM" CHAR(1 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDPNTC" CHAR(1 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDMUSE" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDCRTU" CHAR(10 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDTXPO" VARCHAR2(254 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDLNGP" CHAR(2 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDTXKY" VARCHAR2(254 CHAR));
ALTER TABLE "CRPDTA"."F00165T" modify ("GDOBNM" CHAR(10 CHAR));
Done

PL/SQL procedure successfully completed.

SQL>

SQL> DROP TABLE SEMANTICS$;

Table dropped.

$ impdp system@dbserver directory=DATA_PUMP dumpfile=EXPDAT_F00165_F00165T.DMP remap_tablespace=CRPDTA:CRPDTAT content=data_only

Import: Release 11.2.0.3.0 - Production on Sun Mar 2 14:51:14 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@dbserver directory=DATA_PUMP dumpfile=EXPDAT_F00165_F00165T.DMP remap_tablespace=CRPDTA:CRPDTAT content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CRPDTA"."F00165" 3.657 GB 4191289 rows
. . imported "CRPDTA"."F00165T" 1.311 GB 4114151 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:19:42

SQL> select count(*) from CRPDTA.F00165;

COUNT(*)
----------
 4191289

SQL> select count(*) from CRPDTA.F00165T;

COUNT(*)
----------
 4114151

Komentáře

Kontakt

Zanechte mi telefon nebo e-mail a spojíme se!

databázový administrátor, konzultant a školitel

Tomáš Solař

+420 731 196 647
tom@tomas-solar.com