How to change properties/attributes of partition table?

At times one would want to change properties/attributes of a table so new partitions are affected by change not the partitions already created, like example compression, change PCTFREE, default tablespace. -- enable compression for all operations and change PCTFREE SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5; -- Change default tablespace for new partitions SQL> ALTER TABLE scott.part_table MODIFY DEFAULT … Continue reading How to change properties/attributes of partition table?

How to add a new not null column in a table without providing a default value?

The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value. SQL> create table scott.testn ( x number not null); Table created. SQL> insert into scott.testn values ( 11 ); 1 row created. SQL> commit; Commit complete. -- First step add new column without the NULL constraint SQL> alter … Continue reading How to add a new not null column in a table without providing a default value?

How to add or drop online-redo logs?

Below are the steps on how to add a new online redo-group. It could be of the same size as other groups or different size. 1) List log groups, their archive status, activity status and size SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG; GROUP# ARC STATUS BYTES ———- — —————- ———– 1 NO ACTIVE 104857600 2 NO CURRENT 104857600 3 NO INACTIVE 104857600 2) … Continue reading How to add or drop online-redo logs?