DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: VERY URGENT ! Columns set to "Unused" - Can it be set yo "used" ?

  1. #1
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85

    Exclamation

    Hello,
    We have a production database in which some columns in table A have been set to "UNUSED" i.e:
    ALTER TABLE A SET UNUSED (COLx);

    I just realized that a wrong column has been set to unused.
    Is there an command to set this column back to a used state ?
    I've already tried
    ALTER TABLE A SET USED (COLx); it does not work.
    Please help.

    If there isn't such command, my other alternative is to restore this table from backup.

    Thank you

  2. #2
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    The version 8.1.6

  3. #3
    Join Date
    Jun 2000
    Posts
    417
    I checked docs and looked at some other references and couldn't find anything about it yet. Setting columns as unused is a precursor to dropping them so I suppose it follows the rule that DDL can't be 'rolled back', but you would think it's just a flag until the column is actually dropped.

    If you have a plan ready and it's really urgent to get the columns back online, your best bet is probably to restore the table.

    If you can wait a little maybe someone else has a better answer, or you can open up a TAR. You might open a TAR either way just for future reference.


  4. #4
    Join Date
    Jun 2000
    Posts
    417
    I kinda skimmed it the first time I looked through, but here's the definitive answer

    <i>
    Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns per table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED.

    Also, if you mark a column of datatype LONG as UNUSED, you cannot add another LONG column to the table until you actually drop the unused LONG column.

    See Also: "CREATE TABLE" for more information on the 1000 column limit.
    </i>

  5. #5
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    Thanks alot for your help pwoneill,
    I was in a position where I could not wait any longer therefore I restored that table from backup.
    I will go ahead and open a TAR with oracle and update my posting as I hear from them.

    Thanks again for the help.
    Robel

  6. #6
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    pwoneill,
    Sorry, I must have overlooked your second reply.
    I guess there is s documentation about this. And it looks like there is no way to reverse this transaction.
    My advise would be : Only use this command on a column if you are about to drop this column (specialy usefull on very large column). When you set a column to unused before droping it, a redo log and rb segments will not be generated therefor speeding the column drop.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width