EnterpriseDB: Alter Partitioned Table Oracle Tips by Burleson Consulting

The ALTER syntax is the same as for a regular table as described above.   When you alter the base table, your alterations will automatically be applied to the partitioned table.  You should not modify the partitioned table directly.  Always apply changes to the base table instead.

Here I am altering the table to add a new char column:

ALTER TABLE base_table ADD new_char_column VARCHAR2(10);

edb=# ALTER TABLE base_table ADD new_char_column VARCHAR2(10);

ALTER TABLE

edb=# desc base_table;

                  Table "public.base_table"

     Column      |              Type              | Modifiers
-----------------+--------------------------------+-----------
 pk_field        | numeric                        | not null
 dt_field        | timestamp(0) without time zone |
 char_field      | character varying(100)         |
 new_char_column | character varying(10)          |

Indexes:

    "base_table_pkey" PRIMARY KEY, btree (pk_field)

Triggers:

    base_table_handler
    BEFORE INSERT OR DELETE OR UPDATE
    ON base_table
    FOR EACH ROW
    EXECUTE PROCEDURE base_table_handler_base_table()

edb=# desc base_table_part_200601;

            Table "public.base_table_part_200601"

     Column      |              Type              | Modifiers
-----------------+--------------------------------+-----------
 pk_field        | numeric                        | not null
 dt_field        | timestamp(0) without time zone |
 char_field      | character varying(100)         |
 new_char_column | character varying(10)          |

Indexes:

    "partition1_part_key" btree (dt_field)

Check constraints:

    "base_table_part_200601_dt_field_check" CHECK (|
        to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
              >= 20060101::numeric AND
        to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
              <= 20060131::numeric)

Inherits: base_table

GRANT PARTITIONED TABLE

Grants permissions as you would on a regular table.  Grant to the base table instead of the partitioned tables.

REVOKE PARTITIONED TABLE

You revoke permissions as you would a regular table.

DROP PARTITIONED TABLE

We drop the table as we would a regular table.  We drop the base table with the CASCADE keyword.  When we drop the table, it will also drop the child partitions, the indexes and the trigger.

DROP TABLE base_table CASCADE;

edb=# DROP TABLE base_table CASCADE;

NOTICE:  drop cascades to table base_table_part_200603

NOTICE:  drop cascades to

            constraint base_table_part_200603_dt_field_check

            on table base_table_part_200603

NOTICE:  drop cascades to table base_table_part_200602

NOTICE:  drop cascades to

            constraint base_table_part_200602_dt_field_check

            on table base_table_part_200602

NOTICE:  drop cascades to table base_table_part_200601

NOTICE:  drop cascades to

            constraint base_table_part_200601_dt_field_check

            on table base_table_part_200601

DROP TABLE

edb=#

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.