sqloracle-database

ALTER TABLE SQL Oracle - 12c

I am unsure about the following answers. So our answer is 3, 5.

Which three actions can you perform by using the ALTER TABLE command?

  1. Lock a set of rows in a table.

  2. Drop pseudocolumns from a table.

  3. Rename a table.

  4. Drop all columns simultaneously from a table.

  5. Enable or disable constraints on a table.

  6. Restrict all DML statements on a table.

Solution

  • 3, 5 and 6

    1. Lock a set of rows in a table.

      Oracle locks rows it is performing DML on or when you use SELECT ... FOR UPDATE; you cannot lock rows with ALTER TABLE.

    2. Drop pseudocolumns from a table.

      The common pseudocolumns of a table are ROWID, ROWNUM and ORA_ROWSCN; you cannot drop these.

    3. Rename a table.

      CREATE TABLE table_name ( a NUMBER, b NUMBER );
ALTER TABLE table_name RENAME TO other_name;

      Works.

    4. Drop all columns simultaneously from a table.

      CREATE TABLE table_name ( a NUMBER, b NUMBER );
ALTER TABLE table_name DROP ( a, b );

      Outputs:

    ORA-12983: cannot drop all columns in a table

    1. Enable or disable constraints on a table.

      CREATE TABLE table_name ( a NUMBER CONSTRAINT qu5__pk PRIMARY KEY, b NUMBER );
ALTER TABLE table_name MODIFY PRIMARY KEY DISABLE;

      Will disable the constraint.

    2. Restrict all DML statements on a table.

      CREATE TABLE table_name ( a NUMBER, b NUMBER );
ALTER TABLE table_name READ ONLY;

      Then

      INSERT INTO table_name ( a, b ) VALUES ( 1, 1 );
UPDATE table_name SET b = 2;
DELETE FROM table_name;

      all fail with the exception:

    ORA-12081: update operation not allowed on table "SCHEMA_NAME"."TABLE_NAME"

    db<>fiddle here