Solved

Fastest way to copy a table in Oracle 9

Posted on 2010-08-25
20
1,226 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:acrxx
  • 7
  • 3
  • 3
  • +5
20 Comments
 
LVL 1

Expert Comment

by:bonjourjolie
ID: 33519387
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33519399
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
 

Author Comment

by:acrxx
ID: 33519852
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
 

Author Comment

by:acrxx
ID: 33519868
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33519884
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
 
LVL 1

Accepted Solution

by:
bonjourjolie earned 167 total points
ID: 33519933
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
 
LVL 17

Expert Comment

by:ram_0218
ID: 33520124
what exactly are you trying to do? don't understand what you're trying to do from the steps outlined.

0
 

Author Comment

by:acrxx
ID: 33520284
ram, I'm lowering the high water mark of the items table from 256GB to something closer to the average size of 40GB
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 167 total points
ID: 33521315
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
 
LVL 6

Expert Comment

by:sridharv9
ID: 33548486
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:acrxx
ID: 33584973
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
 
LVL 6

Expert Comment

by:sridharv9
ID: 33593982
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33596075
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
 

Author Comment

by:acrxx
ID: 33611994
to minimise downtime
0
 
LVL 1

Expert Comment

by:esmail1349
ID: 33616390
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
 

Author Comment

by:acrxx
ID: 33616745
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
 
LVL 1

Expert Comment

by:esmail1349
ID: 33616967
i think
u can use alter table shrink space command in 10g and above.
0
 

Author Comment

by:acrxx
ID: 33617122
unfortunately it's Oracle 9
0
 
LVL 1

Assisted Solution

by:esmail1349
esmail1349 earned 166 total points
ID: 33617341
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 33640255
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now