using file system to rename tables/indices

Post Reply
tboyer
Posts: 68
Joined: Mon Aug 28, 2006 4:43 pm

using file system to rename tables/indices

Post by tboyer »

I have to do some maintenance on some large tables and I'm wondering if the following procedure would work. I'm thinking it could be a way to replace a table and indices with the least disruption to our system.

Original table is log1.

1) create table log2 as select * from log1 where ... (it will create table and blob files)
2) create the same indices on log2 that I have on log.
3) use Linux mv to physically move log table and indices out of the folder (not changing the database)
4) Linux mv to rename the log2 table, blob and indices to the identical names that the log1 table had.

Would this work? Are there hazards? Like for example if the original log1 table had a blob but the select creates log2 column as a varblobi, is that going to cause a problem?

Thanks! Tom
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

using file system to rename tables/indices

Post by John »

The biggest hazard to avoid is making sure that you don't try and access either table during the switch, and ideally even the database monitor is quieted so it doesn't try to open or use the files.

Create table as select * from ... should work.
John Turnbull
Thunderstone Software
Post Reply