I am trying to create a generic script which can replicate the oracle source table structure along with data to target db. I am using the below copy command but it is somehow changing the table structure at destination.

Changes line a field from 10 bytes is changing to 40 bytes. How to I make sure this does not happen and as is structure with data gets copied. I am refraining from mentioning each of the columns in the command

copy from USER_XXX/$PASS_YYY@DB_NAMEXXX create $table_name using select * from $table_name where ROWNUM <=100;

I am trying to create a generic script which can replicate the oracle source table structure along with data to target db. I am using the below copy command but it is somehow changing the table structure at destination.

Changes line a field from 10 bytes is changing to 40 bytes. How to I make sure this does not happen and as is structure with data gets copied. I am refraining from mentioning each of the columns in the command

copy from USER_XXX/$PASS_YYY@DB_NAMEXXX create $table_name using select * from $table_name where ROWNUM <=100;

• oracle-database
• oracle19c

• Why don't you use export/import utilities instead? – Littlefoot Commented Mar 11 at 12:58

1 Answer 1

Most likely it is not changing it as you think. If you are seeing 10 become 40, that is very likely the data_length you're looking at (maximum number of bytes required) not the char_length (maximum number of characters allowed) that has changed, and this reflects a move from a single-byte characterset to a multiple-byte characterset such as AL32UTF8 (which can encode up to 4 bytes per char) for varchar2 fields defined using character semantics { varchar2(10 char) vs varchar2(10 byte) }. If you have any foreign characters in your data, the new characterset may require more than 10 bytes to store those 10 characters, so it has no choice but to report a byte maximum of 4x the character length to account for the possibility of every character being a 4-byte character.

I wouldn't worry about it. What matters from a declaratory perspective is the char_length, and that should remain the same. How many bytes Oracle might require internally to store those characters is up to it, not something we should be too concerned with (unless, of course, it overflows the 4KB limit).