Fastest way to copy a table in Oracle 9

Hi Everyone,

I have been informed by my DBAs that one of the production tables has got a high water mark which is excessive and needs to be brought down (average size of table 35GB but high water mark currently at 256GB).
I proposed the following:
1. Copy all contents of table to a temporary location
     
CREATE TABLE ITEMS_COPY TABLESPACE TMP_ITEMS NOLOGGING AS SELECT * FROM ITEMS;

Open in new window


2. Disable all foreign keys on items table
3.
TRUNCTATE TABLE ITEMS DROP STORAGE;

Open in new window

4. Insert data from temp table:
     
INSERT INTO ITEMS SELECT * FROM ITEMS_COPY NOLOGGING;

Open in new window

5. Re-enable foreign keys
6. Rebuild indexes on table
     
ALTER INDEX ITEM_IDX REBUILD ONLINE TABLESPACE INDEX_TS NOLOGGING;

Open in new window


This requires application downtime and after the first attempt, the procedure had to be aborted because step one on its own ran over the allotted maintenance window.

Step one took 4.5 hours to complete, where maintenance window was 4 hours.

Is there any way of getting the desired end result quicker than with my queries?

I already considered simply copying table once to "items_copy" then renaming to "items" to save on one copy, but the "items" table has to reside in the allotted tablespace, and due to the huge high water mark of the original table, (i am told by the DBA) if the tablespace would be increased yet further, it would cause problems for the DB hence I have been forced to create the items_copy table in the tmp_items tablespace.

Any pointers would be gladly accepted.

Kind Regards,

Andreas
acrxxAsked:
Who is Participating?
 
bonjourjolieCommented:
Yeah DB exp/imp should be faster if that table is 99.9% of all ur database size.
also use command line to export & import.
0
 
bonjourjolieCommented:
1: CREATE TABLE ITEMS_COPY TABLESPACE TMP_ITEMS NOLOGGING AS SELECT * FROM ITEMS;
this might take a while.
all i thinking is create table structure items _copy 1st  commit; . then use
SELECT *
INTO items_copy
FROM items

it may have give you a bit difference. use commit before you start copy data.
it might every action that not committed will be use resource. but its not sure.
u just have to try it to see result.
0
 
MilleniumaireCommented:
Have you considered using the ALTER TABLE MOVE STORAGE statement?

If you do use this you will need to ALTER INDEX REBUILD after the storage has been moved.

Another possibility would be to use export and import along with TRUNCATE.  If the DIRECT option was used this would improve performance, but it may still be a long process.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
acrxxAuthor Commented:
Hi guys,

I will have to try out all your suggestions on our test DB.
I forgot to mention before that the items table accounts for 99.9% of all data within the DB, hence a colleague of mine suggested a full DB exp/imp which could well be faster. What do you think of this suggestion?

Cheers,

Andy
0
 
acrxxAuthor Commented:
Milleniumaire, just re-read your comment, and realised that may have been what you were suggesting. Or were you suggesting a table exp/imp?
0
 
MilleniumaireCommented:
Yes, although I was only considering using it for the one large table, not the database.  If the other tables in the database aren't causing a problem I would ignore them and concentrate on the big one.
0
 
ram_0218Commented:
what exactly are you trying to do? don't understand what you're trying to do from the steps outlined.

0
 
acrxxAuthor Commented:
ram, I'm lowering the high water mark of the items table from 256GB to something closer to the average size of 40GB
0
 
sventhanCommented:
Is your size of the table is 40GB? What would you get when you query the DBA_segments for the table/index sizes?

Can you take a downtime? If yes, please go with imp/exp option.

Or I would strongly recommend CTAS  as the comment 1 stated. I would take around 30 mins to create the 40GB table and another 20mins to rebuild the index etc. The only downtime when you rename the temp table to the original table and that would be in seconds.

HTH.
0
 
sridharv9Commented:
1. Enough downtime avail = If you have enough downtime go with export and import.
2. Not enough Down time avail = Go with streams. setup streams for big table and keep replicating big table data until your MW. And exp and import small tables during the MW.
0
 
acrxxAuthor Commented:
hi sridharv9, the streams option seems like a really good idea. Is there any way to add streams to an existing table (ITEMS) which will copy to (TMP_ITEMS) within the same DB? I had a look around online and there is a lot of chat about copying to a a table in a different DB, but never in the same DB.

Any help would be appreciated.
/* SQL of ITEMS table */

CREATE TABLE ITEMS
(
    ITEM_ID     NUMBER (22) NOT NULL,
    CONTENT         BLOB
)
LOB (CONTENT) store as (
  TABLESPACE ITEMS_BLOB
  enable storage in row
  nocache logging chunk 8192
) TABLESPACE ITEMS_DATA

Open in new window

0
 
sridharv9Commented:
I worked on db to db but not with in same db. However, I don't see any reason why you cannot setup within same db.  Check the following link http://www.orafaq.com/forum/t/118934/2/
0
 
MilleniumaireCommented:
Streams is about replicating tables, usually across databases.

I confess I'm bemused as to why it is being suggested as a means of efficiently copying a large table!

If you want to copy ITEMS to TMP_ITEMS in the same DB, why do you need streams to do this?
0
 
acrxxAuthor Commented:
to minimise downtime
0
 
esmail1349Commented:
use COPY command :
COPY FROM inv/Picsor12@DS2 TO inv/truelnd@DS3 CREATE temp_tbl_name USING SELECT * FROM tbl_name;
and use drop command insted of delete (delete command generates more redo entries & undo data)

also u can use
alter table <tbl_name> move tablespace <temp_tbs_name>;
alter table <tbl_name> move tablespace <original_tbs_name>;
0
 
acrxxAuthor Commented:
still getting hung up on having to have so much downtime.
I saw I can use the dbms_redefinition package on http://www.dba-oracle.com/t_alter_table_move_shrink_space.htm
I guess it implies that I won't need downtime. What's your views on this?
0
 
esmail1349Commented:
i think
u can use alter table shrink space command in 10g and above.
0
 
acrxxAuthor Commented:
unfortunately it's Oracle 9
0
 
esmail1349Commented:
all solutions in 9i needs down time

1- export/import
2- tablespace map(GUI)
3-alter table move....
 
there is not any other solution in 9i.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
If you are willing to create another table using CTAS, then do the following first in order to get good performance.
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=0;
and
USE PARALLEL hint in select query. ie
Create table tmp as select /*+ parallel(T 4) */ * from table_name T;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.