-
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,
-- Dilip
-
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);
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.
-
Can you please set one example for copying tables so that I will perform accordingly.
-
What part of...
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|