Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: The Create table as select (CTAS) Statement creates tables w/ different field lenghts

    I want to copy over tables from one database to the other.

    The source database (ERP system) is Oracle 11.2.0.1.0 (64-bit) running on AIX7.1, the target database (Acts like a backup machine) is Oracle 11.2.0.4.0 (64-bit) running on Windows 2012

    I setup a datalink named aix7 and this link is working, so I can query on the Windows machine something like "select * from table@aix7".
    Also the field lengths seems to be like the table on the source database.

    However, when I want to copy over the table using CTAS:

    create table table1 as select * from table1@aix7

    this statement creates a table with CHAR lengths that are 3x longer than the source table.

    In previous systems (Oracle 8 and 10) I did exactly the same without any problems.
    Of coarse I can create tables first by using data from DBA_TAB_COLUMNS, but filling tables using "insert into" is a much slower process.

    Does anyone know how to solve this problem?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Wim Venema View Post
    I want to copy over tables from one database to the other.
    . . .
    However, when I want to copy over the table using CTAS:
    . . . E t c . . .
    It depends on the characterset of both databases, don't know if it will work, but you could try to export/import using transition characterset.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Can you please tell me how to do this?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you consider using data pump export/import (or even original exp/imp utilities)?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    I got same issue (notice the VARCHAR2 columns):
    Code:
    SQL> --  Production
    SQL> SELECT *
      2    FROM Nls_Database_Parameters
      3*  WHERE Parameter LIKE 'NLS_CHAR%'
    SQL> /
    
    PARAMETER                        VALUE
    -------------------------------- ----------------------------------------------------
    NLS_CHARACTERSET                 WE8MSWIN1252
    
    SQL> desc myschema.code_table@prod
     Name                                       Null?    Type
     ------------------------------------------ -------- --------------------------------------------
     CODE_TABLE_ID                              NOT NULL NUMBER
     CODE_TYPE_ID                                        NUMBER
     CODE                                                VARCHAR2(10)
     DESCRIPT                                            VARCHAR2(60)
     ACTIVE                                              CHAR(1)
     DISPLAY_SEQUENCE                                    NUMBER
     USER_ID                                             NUMBER(38)
     DATE_TIME                                  NOT NULL DATE
     USER_NAME                                           VARCHAR2(60)
    
    SQL> --  Test
    SQL> SELECT *
      2    FROM Nls_Database_Parameters
      3*  WHERE Parameter LIKE 'NLS_CHAR%'
    SQL> /
    
    PARAMETER                        VALUE
    -------------------------------- ----------------------------------------------------
    NLS_CHARACTERSET                 AL32UTF8
    
    SQL> create table code_table1 as select * from myschema.code_table@prod;
    
    Table created.
    
    SQL> desc code_table1
     Name                                                              Null?    Type
     ------------------------------------------ -------- --------------------------------------------
     CODE_TABLE_ID                              NOT NULL NUMBER
     CODE_TYPE_ID                                        NUMBER
     CODE                                                VARCHAR2(30)
     DESCRIPT                                            VARCHAR2(180)
     ACTIVE                                              CHAR(3)
     DISPLAY_SEQUENCE                                    NUMBER
     USER_ID                                             NUMBER(38)
     DATE_TIME                                  NOT NULL DATE
     USER_NAME                                           VARCHAR2(180)

    Datapump will work if you set the NLS_LANG environment variable on the source, to same characterset as the target.
    Last edited by LKBrwn_DBA; 01-16-14 at 11:53.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    I just find the same LKBrwn_DBA!
    And also 3 times larger.

    Is there a way to amend this, or should I drop the old database and create a new one?

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Wim Venema View Post
    I just find the same LKBrwn_DBA!
    And also 3 times larger.

    Is there a way to amend this, or should I drop the old database and create a new one?
    Datapump will work if you set the NLS_LANG environment variable on the source, to same characterset as the target.

    PS: If both databases are somewhat related (and you have the time), re-create with same characterset.
    Last edited by LKBrwn_DBA; 01-16-14 at 15:26.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi,

    I solved the problem quite easily:

    logon in sqlplus with user system as sysdba

    SHUTDOWN IMMEDIATE;
    STARTUP RESTRICT;
    ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    ALTER SYSTEM SET AQ_TM_PROCESSES=0;

    ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
    SHUTDOWN IMMEDIATE;
    STARTUP;

    Takes a couple of minutes and the tables keep untouched, but newly generated tables are in the correct format!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •