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.

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,

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.

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);

Can you please set one example for copying tables so that I will perform accordingly.

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?

;)