DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Creation of larger table

  1. #1
    Join Date
    Sep 2006
    Location
    London
    Posts
    58

    Creation of larger table

    I have one large table with size of 18GB. I wanted to create a duplicate table in another schema using this large table. But, It does not create this table. Moreover, it does not show any error messages. I am very much confused about this creation of large tables. How to investigate about the wrong thing. What would be the causes for not creation of tables.

    I used the below statements but remain fail to create this large table.

    CREATE TABLE ARADMARCH.j95 nologging
    AS
    SELECT *
    FROM j95
    WHERE integerdate1 ( c3 ) < add_months( sysdate, -36 )

    integerdat1 is a function name.

    CREATE TABLE ARADMARCH.j95 nologging
    AS
    SELECT *
    FROM j95
    WHERE c3 < 86400*(add_months(sysdate,-36)-date '1970-01-01')


    What is the best way to create thsi big table quckly?

    Your help would be highly appreciated.

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Does it still give error ifyou modify thewhere clause as

    CREATE TABLE ARADMARCH.j95 nologging
    AS
    SELECT *
    FROM j95
    WHERE 1 = 0
    ?

    If yes, can you describe the table structure?

    Thanks,
    -- Dilip

  3. #3
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    I would like to clarify that I have missed to incorporate the ; at the end. It seems that it is not any syntatical issue but it does not give any response from SQL prompt or UNIX console. Moreover, it takes a lot of I/O.

    I have check the provided statemtn is working fine and created the structre onlly. This tables is having arounf 100 coulmns using Oracle 8.1.7 and one of the column is having CLOB datatype and rest of the columns are number,varchar2.

    Hope this infor would help you further to provide recommendation.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Did you say you have a CLOB column in there?... that's why.

    You might want to exp/imp your table or much better resort to sqlcopy...

    1- Pre create your table.
    2- copy from schema/password@source_db to schema/password@target_db -
    append target_table -
    using select * from source_table (your-where-clause);
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    Can you please set one example for copying tables so that I will perform accordingly.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    What part of...

    Quote Originally Posted by PAVB
    copy from schema/password@source_db to schema/password@target_db -
    append target_table -
    using select * from source_table (your-where-clause);
    ... you didn't get?

    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width