Skip to Main Content
  • Questions

  • Copy huge table without the data on it.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Radoslav.

Asked: August 17, 2016 - 3:47 pm UTC

Last updated: August 20, 2016 - 3:18 am UTC

Version: 11.2.0.3.6

Viewed 1000+ times

You Asked

Hi There,

I have 2.7 TB table that contains lots of old data to be deleted.

Due to the table usage and functionality I am planning to copy this table to a new one and rename after.

My table is IOT partitioned and I want to keep the entire structure together with the indexes and partitions.

Can you help me with the best way to achieve my goal. The old table will be offline after I switch to the new one so I can drop it of later point to save the space allocated.

Thank You in advance.

and Chris said...

Sounds like you want a "create table as select" statement to me.

You can specify the physical properties of the table when you do this. e.g.:

create table new_tab ( x primary key ) 
  organization index 
  partition by range(x) (
    ... partitions ...
  ) as
    select * from old_tab
    where  <clause returning the data to keep>;


You'll need to create the indexes manually afterwards.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CTAS

Rado, August 19, 2016 - 1:08 pm UTC

Thank You for your feedback.

I will have to deal with the index ddl's after.

Will it be the same If use to copy the DDL for the table only and insert some data after.

Regards,
Chris Saxon
August 19, 2016 - 3:54 pm UTC

If you're not copying the data, you can just write your create table statement as normal.

Or do a CTAS that returns no rows (where 1=0) so you get the column list.

CTAS

Rado, August 19, 2016 - 4:23 pm UTC

Hi,

I have used the following scenario.

1. Get DDL + partitions + indexes of The original table.
2. Renamed all indexes related to the original table.
3. Renamed the original table
4. Created the new table using the ddl script collected in point 1 + all indexes and partitions.
5. Inserted 59k rows as select from the original table in parallel 4.
6. Recompiled all dependencies.
7. Collected fresh statistics.



Connor McDonald
August 20, 2016 - 3:18 am UTC

Probably would be faster to populate your table and *then* index it.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.