restructure table with xmltype column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: restructure table with xmltype column

  1. #1
    Join Date
    Nov 2000
    Posts
    172

    restructure table with xmltype column

    All,

    I have a table with an xmltype column and a BLOB column in it. I need to restructure this table and add an additional column. There is data that we need to keep in the table.

    What is the best method? Also, need to back it up beforehand. Thanks

    Original table: I changed a few column names.

    F_ID_PK NOT NULL NUMBER(11)
    G_ID_FK NOT NULL NUMBER(11)
    FAC_ID NOT NULL NUMBER(11)
    SOME_ID -- Need to add this column (some_id) here NOT NULL NUMBER(11)
    ID_FLAG_NY NOT NULL VARCHAR2(1)
    CREATED_BY NOT NULL VARCHAR2(50)
    CREATED_DATE NOT NULL DATE
    UPDATED_BY VARCHAR2(50)
    UPDATED_DATE DATE
    EXPIRED_BY VARCHAR2(50)
    EXPIRED_DATE DATE
    BLOB_COLUMN_NAME BLOB
    FOLLOW_UP_COMPLETED_DATE DATE
    FOLLOW_UP_COMPLETED_BY VARCHAR2(50)
    PROOF_COMPLETED_DATE DATE
    PROOF_COMPLETED_BY VARCHAR2(50)
    TRACKING_XML SYS.XMLTYPE(XMLSchema "http:
    //name.nn.ggg/schemas/name
    /trackingschema" Element "TR
    ACKING") STORAGE Object-rela
    tional TYPE "TRACKING697_T"

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    alter table xxx add yyy number(11)

    add data

    make it not null

  3. #3
    Join Date
    Nov 2000
    Posts
    172
    Doesn't that just add the column to the last column in the table?

    I need to put it in the 4th column.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why on earth do you want to do that, there is no order in tables

  5. #5
    Join Date
    Nov 2000
    Posts
    172
    Well, I am under the impression. All tables should be in this order
    required (not null) columns followed by nullable and then any large objects should be at the end, if possbile.

    Is this not the case?

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Some people believe in column order in the table make some difference. I personally never worried about the column order in the table. I didn't even find that in any of the Oracle documents so far i have read.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by kburrows View Post
    Well, I am under the impression. All tables should be in this order
    required (not null) columns followed by nullable and then any large objects should be at the end, if possbile.

    Is this not the case?
    utter rubbish

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