表的管理分为两个部分,字段管理和表管理。
字段管理包括增加或者删除表中的字段,改变表的存储参数以及对表进行增加、删减和重命名等操作。
普通用户只能对自己模式中的表进行修改,如果想要对任何模式中的表进行修改操作,则用户必须具有alter any table 的系统权限。
1. 管理字段
通过执行alter语句与不同子句组合,可以使用的子句有:add、drop、modify、rename。
增加字段语法:
alter table table_name
add(column_name_1 datatype
[[,column_name_n datatype]……]);
例1. 为student_1表增加新字段:手机号(stelephone char(12))、邮箱(semail varchar2(20))和通信地址(saddress varchar2(50))。
alter table student_1
add (stelephone char(12),
semail varchar2(20),
saddress varchar2(50));
修改字段名称语法:
alter table table_name
RENAME COLUMN column_name to new_column_name;
例2. 将学生表student_1中的字段所在系的名称由sdept改为sdepartment。
alter table student_1
rename column sdept to sdepartment;
修改字段的数据类型语法格式:
alter table table_name
modify column_name new_datatype;
例3. 将学生表student_1中的邮箱semail的数据类型由varchar2(20)修改为varchar(30)。
alter table student_1
modify semail varchar(30);
删除字段语法格式:
//删除一个字段
alter table table_name
drop COLUMN column_name;
还可以删除多个字段
alter table table_name
drop (column_name1,……);
例4. 删除学生表student_1中字段通信地址saddress。
alter table student_1
drop COLUMN saddress ;
例5. 删除掉学生表student_1中的字段手机号stelephone和邮箱semail。
alter table student_1
drop (stelephone,semail);
说明:删除一个表中的字段时,这个字段将从表的结构中消失,而且这个字段的所有数据也将从表中消失,oracle会释放该字段所占用的存储空间。若该字段为表中主键且与其他表有关联,则无法删除。
在一个大型数据表中,如何在数据库使用高峰期删除某个字段,而不会造成过多占用系统资源?
使用unused关键字:
alter table table_name
set unused(column_name1,column_name2,……);
设置unused的作用是为了在cpu、内存等资源不充足的时候,先做上unused标记再等数据库资源空闲的时候用drop set unused删除 。
例6. 查看student_1表中还剩几个字段,将剩下的其中之一标记为unused状态。
desc student_1
alter table student_1
set unused(sname);
例7.彻底删除unused状态字段。
alter table table_name
drop unused column;
SET UNUSED列能否恢复?
设置UNUSED 列之后,并不是将该列数据立即删除,而是被隐藏起来,物理上还是存在的,因此可以恢复,
但是恢复过程需要修改底层的数据字典并重启数据库,因此在执行SET UNUSED操作时务必慎重!
2. 管理表
主要包括:重命名表、移动表、截断表和删除表。
(1)重命名操作
例8.将学生表student_1名改为stu。
rename student_1 to stu
//操作必须在sqlplus中操作
例9.另一种表的重命名方法,将stu表改名为student_2
alter table stu rename to student_2;
(2)移动表操作
移动表,主要指将表从一个表空间移动到另外一个表空间。
语法格式:
alter table table_name move tablespace tablespacename;
在进行移动表操作时,先确定待移动表所在的表空间,使用数据字典user_tables查询。
例10. 移动学生表前的准备工作,查看表所在表空间。
select table_name,tablespace_name from user_tables;
例11. 将student表从当前表空间,移动到指定表空间中。
create tablespace zzxy
datafile '/home/shiyanlou/oracle/zzxy.dbf'
size 50M
extent management local
//先创建自己设定的表空间
alter table student_2 move tablespace zzxy;
//移动到新建的表空间中
select table_name,tablespace_name from user_tables where table_name = 'STUDENT_2'