Menu
I need to add two DATE columns to an already existing table. But it takes very long and I have to kill the process. What is strange is I was able to add those columns to the other tables in the same database momentarily. Most of those tables are larger than the one I'm having trouble with both data and column-count-wise. What do I have to do to be able to add those new columns?
295
We have done migration of over 4 billion rows with this, though it can take upto 10 days, with less than a minute of downtime.
Note that you cannot add a column that already exists in the table; trying to do so will cause an error. In addition, the ALTER TABLE ADD column statement adds the new column at the end of the table.
SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command or use the Enterprise Manager interface.
The most common causes of slow performance are as follows: Excessive round-trips from the application server to the database. Ideally, each UI operation should require exactly one round-trip to the database. Sometimes, the framework will require additional round-trips to retrieve and make session data persistent.
here's how I solved the problem. Before, I was specifying a default value for the columns right at the moment of adding them. But then I first added the columns with no default values. After columns got added I specified the default value and it executed immediately with no more waiting. Thank you very much @Justin Cave for your hint about Default value. That was the key point.
I have no doubt that it's related with the fact that when specifying default value at the time of adding column that default value is written to all the records inserted earlier.So if there are 5 million records in the table, that table will be updated to set default value for the newly added column for all the rows. Updating 5 million records is expensive as one might guess. But if default value is set after adding a column then the value of that new column in the rows inserted earlier will be NULL, so no update will take place.
87
I was also facing this problem when adding a varchar2(4000) column with no default value one a 61K row table in Oracle 11g. After 30 minutes the column was still not added.
I tried to abort the operation, and retried it, but it still didn't finish.
What worked:
I tried adding a number column instead, that took a split second. Dropped it.
Then i tried adding a varchar2(1) column, that took a split second. Dropped it.
Then i tried adding a varchar2(4000) column again, and it only took a split second.
Silly "workaround", but maybe this also works for anyone else facing this problem.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
