实验四数据库表对象管理

1、实验四 数据库表对象管理实验目的熟悉Oracle数据库对象的管理，包括表、分区表的管理。实验内容表空间的简单管理表对象的管理分区表的管理实验步骤：准备工作创建表空间语法：CREATE TABLESPACE tablespace_name DATAFILE filename SIZE sizeK|M AUTOEXTEND OFF | ON NEXT numberK|M MAXSIZE UNLIMITED | numberK|M ,创建一个表空间APP，表空间相关参数学生自行设定。如：CREATE TABLESPACE APP DATAFILE D:ATESTAPP.DBF SIZE 50M;创建

2、一个用户TEST，密码TEST，设置用户默认表空间为APP。分配CONNECT、RESOURCE、DBA角色给用户TEST。创建用户TEST：CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE APP;分配权限语句：GRANT CONNECT,RESOURCE TO TEST;GRANT SELECT ON SCOTT.EMP TO TEST;GRANT SELECT ON SCOTT.DEPT TO TEST;以TEST用户登录SQL*Plus，使用SQL命令完成以下相关操作。CONNECT TEST/TEST表对象的管理 按下面的要

3、求创建 DEPT 表，使用SQL语句来创建表，确认表已被创建。create table dept( id number(7), name varchar2(25) )tablespace app ; 从SCOTT模式下的DEPT表取数据填充到当前模式下的DEPT 表中，只包括需要的列。insert into deptselect deptno,dname from scott.dept; 根据下面的表创建 EMP 表，使用SQL语句创建表，确认表已被创建。create table emp( id number(7), last_name varchar2(25), first_name var

4、char2(25), dept_id number(7) )tablespace app ; 修改EMP表允许更长的雇员名字 (last_name)，确认你的修改。 alter table emp modify last_name varchar2(50); 确认DEPT表和EMP表被存储在数据字典中。(提示：USER_TABLES) select table_name from user_tables; 基于SCOTT模式下的EMP表的结构创建EMPLOYEES表，只包括EMPNO, ENAME, JOB,COMM,SAL和DEPTNO列，分别将新表中的列命名为ID, EMP_NAME, J

5、OB,SALARY,COMM和DEPT_ID。 create table employees(id,emp_name,job,salary,comm,dept_id)asselect empno,ename,job,comm,sal,deptno from scott.emp where 1=2; 删除EMP表。 drop table emp; 重命名EMPLOYEES表为EMP。alter table employees rename to emp; 从EMP表中删除JOB列，检查表的定义来确认你的修改。 alter table emp drop column job;desc emp; 在

6、EMP表中，将COMM列标记为UNUSED，检查表定义以确认你的修改。 alter table emp set unused(comm);desc emp; 从EMP表中删除所有UNUSED列，检查表的定义以确认你的修改。 alter table emp drop unused column;desc emp; 添加一个表级PRIMARY KEY约束到EMP表的ID上，约束在创建时被命名，将该约束命名为my_emp_id_pk。 提示：ALTER TABLE命令。 alter table emp add constraint my_emp_id_pk primary key(id); 创建一个

7、PRIMARY KEY约束到DEPT表的ID列上，约束在创建时应该被命名，将该约束命名为my_dept_id_pk。 alter table dept add constraint my_dept_id_pk primary key(id); 添加一个外键到EMP表的DEPT_ID列上，确保雇员不被指定到一个不存在的部门，将该约束命名为my_emp_dept_id_fk。 alter table emp add constraint my_emp_dept_id_fkforeign key (dept_id) references dept(id); 查询USER_CONSTRAINTS视图，

8、确认约束已被添加，注意约束的类型和名字。 select constraint_name,constraint_type from user_constraints; 修改EMP表，添加NUMBER数据类型的列COMMISSION，精度2，小数位数为2。添加一个约束到COMMISSION列，确保佣金值大于零。 alter table emp add commission number(2,2);alter table emp add check(commission 0);分区表的管理通过下面两个分区表的练习掌握分区表的管理。 创建一个名为employee_details的分区表，要求该表以da

9、teofjoining列进行范围分区。Dateofjoining列取值在2001年4月1日前的记录保存在名为doj1的分区内，2001年4月1日到2003年7月1日前的保存在doj2分区内，2003年7月1日到2004年9月1日前的保存在doj3分区内，2004年9月1日到2005年10月1日前的保存在doj4分区内。employee_details表结构如下所示。emp_id VARCHAR2(5)/主键ename VARCHAR2(25)job VARCHAR2(20)dateofjoining DATEcreate table employee_details( emp_id VARCH

10、AR2(5), ename VARCHAR2(25), job VARCHAR2(20), dateofjoining DATE )partition by range(dateofjoining)(partition doj1 values less than(01-4月-2001) tablespace APP,partition doj2 values less than(01-7月-2003) tablespace APP,partition doj3 values less than(01-9月-2004) tablespace APP,partition doj4 values l

11、ess than(01-10月-2005) tablespace APP); 为employee_details分区表插入一些测试记录，观察数据分区保存的情况。比如，查询一下doj3分区内的数据。insert into employee_details values(1,张三，工程师，02-2月-04);select * from employee_details partition(doj3); 创建一个名为Employee_Data的分区表，要求该表针对Department列进行列表分区。表中Department列取值为Accounts的数据记录保存在D1分区内，Management的记录

12、保存在D2分区内，Human Resource的记录保存在D3分区内。Employee_Data表结构如下所示。Employee_ID varchar2 (5)Employee_Name varchar2 (20)Department varchar2 (15)create table Employee_Data( Employee_ID varchar2 (5), Employee_Name varchar2 (20), Department varchar2 (15) )partition by list(Department)(partition D1 values (Accounts) tablespace APP,partition D2 values (Management) tablespace APP,partition D3 values