Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Unanswered: altering a table column's length

    Hi,

    I am trying to extend the length on a column of a table by 3 bytes.
    It's a CHAR field. The table has about 17.5 million rows and 11 GB in size. Even though I have extended the tablespace by 4 GB, I am still getting an error
    " ORA-01653: unable to extend table XXXX by 163840 in the tablespace"
    What exactly goes on when we do this step. Can somebody explain.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    3

    Re: altering a table column's length

    You forgot to spec what version of Oracle...

    Maybe you have row chaining starting. Worst case: adding 3 bytes to each row makes *each* row chain into a second block, the table's size would *double* instead of increasing by the 50Mb or so implied by 17.5mil x 3bytes...

    Are the existing rows prehaps crafted with all fixed size column data type to exactly fit into one block?

  3. #3
    Join Date
    Sep 2003
    Posts
    11
    We are using Oracle 8.1.7.4

    I am not sure about the answer to your question. But I have to correct my self that the field is VARCHAR2 and not CHAR.

    How do you basically monitor the situation while running the job and make sure that it would not complain again?

    Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    3
    varchar2... well then it shouldn't matter. It doesn't actually allocate those three extra bytes when you alter the column. post your ALTER TABLE sql...

    I'm not sure how to monitor it as it runs. There's a way to check the chain count (how many rows are chained...) but I don't know that off the top of my head.

Posting Permissions

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