Forum OpenACS Q&A: Oracle - loading a table minus one column

Collapse
1: Oracle - loading a table minus one column
Posted by Janine Ohmer on 09/16/02 08:48 PM
I'm setting up a staging site for an existing site, and am trying to load some of the tables without their LOB columns. The LOBs take up about 8 Gb which I don't need on the staging site.

Just loading the dump and then deleting those columns won't do it; Oracle doens't give you any way to shrink a LOB segment once it has expanded.

I tried to follow the advice given here, in the "dropping a column" example: http://asktom.oracle.com/pls/ask/f?p=4950:8:1289526::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:301416506499,%7Bimp%7D%20and%20%7Bcolumn%7D

But I can't get it to work; even if I only load the integer columns in my test table I still get an "inconsistent datatypes" error. It's even more frustrating than usual because each trial run takes *forever* because the dump is so large.

Has anyone done this? Any tips or tricks I should know about?

Thanks in advance!

Collapse
2: Response to Oracle - loading a table minus one column (response to 1)
Posted by David Walker on 09/16/02 09:06 PM
How about restoring, "select (non-LOB columns) into tmptable", "drop
tablename", "alter table tmptable rename to tablename"?  (Or
something similar, my syntax might be off or might be wrong for
Oracle, I have experience with Postgres and MS SQL)
Collapse
3: Response to Oracle - loading a table minus one column (response to 1)
Posted by Janine Ohmer on 09/16/02 09:11 PM
Unfortunately, dropping the table won't get rid of the space used by the LOBs. The only way to do this is to not restore them at all.

The example from the Ask Tom site goes like this:

  • create the new table (minus the column you don't want)
  • rename to table_TEMP
  • create a view with the same name as the table, and with all the columns in the table as it exists in the dump
  • write a trigger that will fire instead of doing an insert into the table, and use it to insert the columns you do want into table_TEMP
It's quite ingenious, but I can't get it to work!
Collapse
4: Response to Oracle - loading a table minus one column (response to 1)
Posted by David Walker on 09/16/02 09:35 PM
Perhaps you could load the dump into a temp database, delete those
columns, create a new dump, load into your new database, and drop
the temp database.
Collapse
5: Response to Oracle - loading a table minus one column (response to 1)
Posted by Sebastiano Pilla on 09/17/02 09:22 AM
It's quite ingenious, but I can't get it to work!
What's the error message you're getting?
Collapse
6: Response to Oracle - loading a table minus one column (response to 1)
Posted by Janine Ohmer on 09/17/02 02:29 PM
The error is "inconsistent datatypes".

I've created my views using data appropriate to each column, but my final test case involved only integer columns (that is, my trigger only inserted integer columns) and I still got this error.  That's when I ran out of other ideas.

David, your last idea should work - I was hoping to figure out how to use the more elegant solution, but you may have the best idea I'm going to come up with.