Thread: alter table

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    76
    Rep Power
    12

    alter table


    hi..i'm using the oracle db. I have some sql query problems here.
    i'd like to drop the ACPC_DTTMISS column here. I've try the sql but failed. Can someone give me the solution?
    SQL> desc ac_paramcontrol;
    Name Null? Type
    ------------------------- --------------- -----------
    ACPC_PARAMID NOT NULL VARCHAR2(2)
    ACPC_ISSNO NOT NULL NUMBER(5)
    ACPC_DTTMISS DATE
    ACPC_RECCOUNT NUMBER(5)
    ACPC_LOCKBY NUMBER(5)
    ACPC_DTTMSEND DATE
    ACPC_SENDBY NUMBER(5)

    SQL> alter table ac_paramcontrol drop column acpc_dttmiss;
    alter table ac_paramcontrol drop column acpc_dttmiss
    *
    ERROR at line 1:
    ORA-00406: COMPATIBLE parameter needs to be 8.1.0.0.0 or greater
    And, I also would like to remove the primary key of ACPC_ISSNO. How to do that?
    Thank you!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Change the Compatible parameter in init.ora to 8.1.0x
    and restart the database
    ...
    Last edited by victorpendleton; January 13th, 2004 at 09:46 AM.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    Dropping of primary key is very simple, if you know the constraint name of primary key then use the follwoing example:

    ALTER TABLE dept
    DROP CONSTRAINT pk_dept CASCADE
    /

    If you are not sure about constraint name then use the follwoing example:

    ALTER TABLE dept
    DROP PRIMARY KEY CASCADE
    /

    If column dropping functionality is not available in your installed version of oracle then you can drop it by simple method:

    For example i want to drop the location column of DEPT table:

    CREATE TABLE dept2
    AS SELECT deptno,dname from dept
    /
    now drop the DEPT table:

    DROP TABLE dept
    /

    create table DEPT again:

    CREATE TABLE dept
    AS SELECT * from dept2
    /
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    india
    Posts
    1
    Rep Power
    0

    Thumbs up Re: alter table


    Originally posted by weebeng
    hi..i'm using the oracle db. I have some sql query problems here.
    i'd like to drop the ACPC_DTTMISS column here. I've try the sql but failed. Can someone give me the solution?


    And, I also would like to remove the primary key of ACPC_ISSNO. How to do that?
    Thank you!
    to drop a column from table use the command ' alter table [tablename] drop column [column_name']. this should work. all the best.

IMN logo majestic logo threadwatch logo seochat tools logo