Oracle 19C入门到精通之维护数据表

在创建表后，如果发现对表的定义有不满意的地方，还可以对表进行修改，这些修改操作包括增加或删除表中的字段、改变表的存储参数设置以及对表进行增加、删除和重命名等操作。普通用户只能对自己模式中的表进行修改，如果要对任何模式中的表进行修改操作，则用户必须具有ALTER ANY TABLE系统权限。

1. 增加和删除字段

在创建表后，可能会需要根据应用需求的变化向表中增加或删除列，可以使用ALTER TABLE…ADD语句向表中添加新的字段。

--在students_6表中增加一个province（省份）新字段
alter table students_6  add(province varchar2(10));

通过ALTER TABLE…DROP语句来删除数据表中指定的字段，但是不能删除表中所有的字段，也不能删除sys模式中任何表的字段。如果仅需要删除一个字段，则必须在字段名前指定COLUMN关键字。

--在students_6表中删除province（省份）字段
alter table students_6 drop column province;

如果要在一条语句中删除多个字段，则需要将删除的字段名放在括号中，各个字段之间用逗号隔开，并且不能使用关键字COLUMN。

--在students_6表中同时删除sex和age字段
alter table students_6 drop (sex,age);

在删除字段时，系统将删除表中每条记录对应的字段值，同时释放所有占用的存储空间，并且不会影响到表中其他列的数据。如果要删除一个大型表中的字段，则需要对每条记录进行处理，因此删除操作可能会执行很长时间。

2. 修改字段

修改字段通常使用ALTER TABLE…MODIFY语句，可以修改字段的数据类型的长度、数字列的精度、列的数据类型和列的默认值等，其语法格式如下：

ALTER TABLE table_name MODIFY column_name column_property;

• table_name：表示要修改的列所在的表名称。

• column_name：要修改列的名称。

• column_property：要修改列的属性，包括数据类型的长度、数字列的精度、列的数据类型和列的默认值等。

需要注意的是，在修改字段时，不可以随意修改。通常情况下，把某种数据类型改变为兼容的数据类型时，只能把数据的长度从低向高改变，不能从高向低改变，否则会出现数据溢出的情况，影响原有数据的精度；如果表中没有数据，既可以把数据的长度从高向低改变，也可以把某种数据类型改变为另一种数据类型。

--将students_6表中的departno字段的长度由2更改为4
alter table students_6 modify departno varchar2(4);

注意：修改某个字段的默认值只对今后的插入操作起作用，对于先前已经插入的数据不起作用。

3. 重命名表

在创建表后，用户可以修改指定表的名称，但用户只能对自己模式中的表进行重命名。重命名表通常使用ALTER TABLE…RENAME语句，其语法格式如下：

ALTER TABLE table_old_name RENAME TO table_new_name

• table_old_name：表示原表名称。

• table_new_name：表示新表名称。

对表进行重命名非常容易，但是影响却非常大，所以在对表的名称进行修改时，要格外谨慎。虽然Oracle可以自动更新数据字典中的外键、约束定义以及表关系，但是它不能更新数据库中的存储过程、客户应用，以及依赖该对象的其他对象。

--将students_6表重命名为students_7
alter table students_6 rename to students_7;

4. 改变表空间和存储参数

在创建表时，可以通过一些参数指定表空间、存储参数等，当然也可以不指定参数而使用默认值。在创建表之后，如果发现这些参数被设置得不合适，管理员可以对其进行修改。在维护数据表时经常遇到的两种修改数据表参数的情况：修改表空间和存储参数。

4.1. 修改表空间

若要将一个“非分区”表移动到一个新的表空间，可以使用ALTER TABLE…MOVE TABLESPACE语句；

--将students_7表由tbsp_1表空间移动到TBS_TEST_2表空间
alter table students_7 move tablespace TBS_TEST_2;

由于表空间对应的数据文件不同，因此在移动表空间时会将数据在物理上移动到另一个数据文件中。

4.2. 修改存储参数

修改存储参数主要是指修改数据块参数PCTFREE和PCTUSED，若改变了这两个参数值，则表中所有的数据块都将受到影响，而不论数据块是否已经使用。修改存储参数一般使用ALTER TABLE语句。

使用ALTER TABLE语句重新设置students_7表中的PCTFREE参数，代码如下：

alter table students_7 pctfree 25;

5. 删除表

在创建完数据表之后，根据实际需求情况，用户还可以将其删除。但需要注意的是，一般情况下用户只能删除自己模式中的表，如果要删除其他模式中的表，则用户必须具有DROP ANY TABLE系统权限。删除表通常使用DROP TABLE语句，其语法格式如下：

DROP TABLE table_name [CASCADE CONSTRAINTS];

参数table_name表示要删除表的名称。如果该表存在约束、关联的视图和触发器等，则必须使用CASCADE CONSTRAINTS可选子句才能将其删除。

删除表与删除表中的所有数据不同，当使用DELETE语句删除操作时，删除的仅是表中的数据，该表的数据结构仍然存在于子数据库中；当使用DROP TABLE语句删除表的定义时，不仅表中的数据将被删除，而且该表的定义信息（数据结构）也将从数据库中被删除，用户将无法再向该表中添加数据，因为该表对象在数据库中已经不存在。

在删除一个表的结构时，通常Oracle会执行以下操作：

• 删除表中所有的数据。

• 删除与该表相关的所有索引和触发器。

• 如果有视图或PL/SQL过程依赖于该表，这些视图或PL/SQL过程将被置于不可用状态。

• 从数据字典中删除该表的定义。

• 回收为该表分配的存储空间。

DROP TABLE语句有一个可选子句CASCADE CONSTRAINTS。当使用该参数时，DROP TABLE不仅删除该表，而且所有引用这张表的视图、约束或触发器等也都被删除；

--删除表students_5以及所有引用这张表的视图、约束或触发器等
drop table students_5 cascade constraints;

一般情况下，当某张表被删除之后，实际上它并没有被彻底删除（仅仅是在数据字典中被除名），而是把该表放到了回收站（即它依然占用存储空间）。这样当用户需要还原该表时，就可以使用FLASHBACK TABLE语句（这是一种闪回技术）进行还原。

利用Oracle的闪回功能快速恢复被删除的表students_5，具体操作步骤如下：

①首先确认students_5表是否已经被删除，代码如下：

select * from students_5;

②通过查询结果可以看出，该表已经被删除，通过查询数据字典视图RECYCLEBIN来了解该表是否在回收站中，代码如下：

select object_name,original_name from recyclebin where original_name = 'STUDENTS_5';

③使用FLASHBACK TABLE语句恢复被删除的students_5表，代码如下：

flashback table students_5 to before drop;

④这时，通过SELECT语句查询表students_5，发现该表被恢复了。

如果用户想在删除表时立即释放空间，并且不希望将其放到回收站中，则可以在DROP TABLE语句中使用PURGE选项，这样该表就被彻底删除了。

6. 修改表的状态

在Oracle中，用户可以将表置于READ ONLY（只读）状态，处于该状态的表不能执行DML和某些DDL操作。

--将表students_5设置为只读的READ ONLY状态
alter table students_5 read only;

尝试把students_5表中学号大于5000的学生性别都修改为“男”，代码如下：

update students_5 set sex = '男' where stuno > 5000;

从上述运行结果中可以看出，处于只读状态的表不能执行DML操作。但可以将处于只读状态的表从一个表空间移动到另一个表空间；

--把表students_5从TBS_TEST_1表空间移动到TBS_TEST_2表空间
alter table students_5 move tablespace TBS_TEST_2;

对于READ ONLY状态的表，用户还可以将其重新置于可读写的READ WRITE状态；

--把表students_5从READ ONLY状态更改为READ WRITE状态
alter table students_5 read write;