学步园

第五次
实验目的：
    掌握创建表的不同方式，理解数据在不同需求下采取的存储方式。
    掌握创建索引的方法，理解索引在oracle数据库中的使用方式。
    掌握创建各类约束的方法，理解约束是如何实现数据的完整性。
实验内容：
1、以SYSTEM用户登录，创建课程表，存储结构为分区表，表名为C21????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   按学分(分3及以下，4，5及以上)做范围分区，分别存储在TEST2,USERS2,EXAMPLE2三个表空间中。

create table c217026
(
Cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
partition by range(Credit)
(
partition par_3 values less than(3) tablespace TEST2,
partition par_4 values less than(5) tablespace USERS2,
partition par_other values less than(maxvalue) tablespace EXAMPLE2
);

2、以SYSTEM用户登录，创建课程表，存储结构为分区表，表名为C22????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   按课程类别（01<必修课>，02<自由选修课>，03<限选课>）做列表分区，分别存储在TEST2,USERS2,EXAMPLE2三个表空间中。

执行脚本 c22.sql

create table c227026
(
cno char(8),
name varchar2(100),
kind char(2),
Credit number(1)
)
partition by list(Kind)
(
partition par_01 values (01) tablespace TEST2,
partition par_02 values (02) tablespace USERS2,
partition par_03 values (03) tablespace Example2
);

3、以SYSTEM用户登录，创建课程表，存储结构为分区表，表名为C23????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   按课程名称做哈希分区，分别存储在TEST2,USERS2,EXAMPLE2三个表空间中。

create table c237026
(
cno char(8),
Name varchar2(100),
Kind char(2),
credit number(1)
)
partition by hash(Name)
(
partition par_01 tablespace TEST2,
partition par_02 tablespace USERS2,
partition par_03 tablespace EXAMPLE2
);

4、以SYSTEM用户登录，创建课程表，存储结构为索引组织的表，表名为C24????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   课号做主键，存储在TEST2表空间中，其中溢出部分存储在tb2_2k表空间中。

 执行脚本 c24.sql

create table c247026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1),
constraint pk_c247026_cno primary key(cno)
)
organization index tablespace TEST2
mapping table
overflow tablespace tb2_2k;

5、以SYSTEM用户登录，创建簇clu_c2????(????为学号后四位）,共享字段为课号，存储在tb2_8k表空间中，然后建立课程表，表名为C25????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   建立选课表SC2????(????为学号后四位），表结构为学号（8字节），课号（8字节），成绩（3位整数）。将这两个表加入簇clu_c2????(????为学号后四位）。

create cluster CLU_c27026
(
cno char(8)
)
tablespace tb2_8k;

create table c257026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
cluster CLU_c27026(cno)
;

create table SC27026
(
sno char(8),
cno char(8),
grade number(3)
)
cluster CLU_c27026(cno)
;

6、以SYSTEM用户登录，创建课程表，表名为C26????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数），简介（超过2000个汉字），
   基本信息存储在tb2_2k表空间中，其中简介字段的值以段名S2LOB存储在test2表空间中。

create table c267026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1),
contents clob
)
tablespace tb2_2k
lob(contents) store as S2LOB (tablespace test2)
;

 

7、以SYSTEM用户登录，创建课程表，存储结构为全局临时表，表名为C27????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   记录在事务结束后自动删除。

create global temporary table c277026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
on commit delete rows
;

8、以SYSTEM用户登录，创建课程表，存储结构为全局临时表，表名为C28????(????为学号后四位），
   表结构为课号(8字节），课程名称（50汉字），课程类别（2字节），学分（1位整数）
   记录在会话结束后自动删除。

create global temporary table c287026
(
cno char(8),
Name varchar2(100),
kind char(2),
Credit number(1)
)
on commit preserve rows
;

9、以SYSTEM用户登录，使用OMF机制建立表空间ind2_????(????为自己学号后四位)，称为“索引表空间”，
   块大小为8KB，数据文件初始40M，自动扩展，每次10M，最大不限，存放在“数据文件夹”。

alter system set db_create_file_dest='c:/DA917026';//
alter system set db_8K_cache_size=8K;
create tablespace ind2_7026 datafile size 40M autoextend on next 10M maxsize unlimited blocksize 8K;

10、以SYSTEM用户登录目标数据库，为前面创建的所有课程表（不包括临时表）在字段课程名称、课程类别上创建最合适的索引，
   要求索引名为ind2_xxxxxxx_y(xxxxxxx为表名，y：课程名称索引为N，课程类别索引为K)。
   这些索引存储在索引表空间中。

///////////////////////////////
create bitmap index ind2_c217026_k on c217026(kind) tablespace ind2_7026;
create index ind2_c217026_n on c217026(name) tablespace ind2_7026;
create bitmap index ind2_c227026_k on c227026(kind) tablespace ind2_7026;
create index ind2_c227026_n on c227026(name) tablespace ind2_7026;
create bitmap index ind2_c237026_k on c237026(kind) tablespace ind2_7026;
create index ind2_c237026_n on c237026(name) tablespace ind2_7026;
alter table c247026 move mapping table;

create bitmap index ind2_c247026_k on c247026(kind) tablespace ind2_7026;
create index ind2_c247026_n on c247026(name) tablespace ind2_7026;
create bitmap index ind2_c257026_k on c257026(kind) tablespace ind2_7026;
create index ind2_c257026_n on c257026(name) tablespace ind2_7026;
create bitmap index ind2_c267026_k on c267026(kind) tablespace ind2_7026;
create index ind2_c267026_n on c267026(name) tablespace ind2_7026;

11、开启对第1个表C21????(????为学号后四位）的所有索引的监控并进行测试。

alter index ind2_c217026_k monitoring usage;
alter index ind2_c217026_n monitoring usage;

12、以SYSTEM用户登录目标数据库，为前面创建的表
C21????,C22????,C23????,C25????,C26????,C27????,C28????在课程号字段上创建主键，
   要求约束名为pk_xxxxxxx_cno(xxxxxxx为表名)，修改表C24????的主键约束名为pk_xxxxxxx_cno(xxxxxxx为表名)。

alter table c217026 add constraint pK_c217026_cno primary key(cno);
alter table c227026 add constraint pK_c227026_cno primary key(cno);
alter table c237026 add constraint pK_c237026_cno primary key(cno);
alter table c257026 add constraint pK_c257026_cno primary key(cno);
alter table c267026 add constraint pK_c267026_cno primary key(cno);
alter table c277026 add constraint pK_c277026_cno primary key(cno);
alter table c287026 add constraint pK_c287026_cno primary key(cno);

13、以SYSTEM用户登录目标数据库，对表c21????(????为学号后四位）增加字段先修课，定义外键，
   参照本表的课程号字段，要求约束名为fk_xxxxxxx_cpno(xxxxxxx为表名)。

////////////////////////////
alter table c217026 add cpno char(8);
alter table c217026 add constraint fk_c217026_cpno foreign key(cpno) references c217026(cno);

14、以SYSTEM用户登录目标数据库，建立学生表student????(????为学号后四位），存储在test2表空间里，
   约束名为xx_student????_zzz(xx为约束类别，zzz为涉及的字段名，????为学号后四位），
   约束类别：（pk：主键,fk：外键,ck：其它,uk：唯一键,nk:非空）
   包含的字段有学号（8字节,主键），姓名(10个汉字，不能重复，不能为空），性别（1字节，不能为空），入学成绩（3位整数，值范围为500~750，非空），
   班长学号（参照学号）。

create table student7026 (
                            sno char(8) constraint pk_student7026_sno primary key,
                            sname varchar(10) constraint nk_student7026_sname   unique not NULL ,
                            sex  char(1)  constraint nk_student7026_sex  not NULL,
                            score number(3) constraint  nk_student7026_score not NULL check(score >=500 and score<=750),
                            monitor   char(8) constraint  fk_student7026_monitor references student7026(sno)
                            ) tablespace test2;

15、以SYSTEM用户登录目标数据库，为第14步建立的表增加约束：学号第四、五位编号为13的学生，入学成绩必须大于等于550，
   约束名为ck_student_score。
   此约束为延迟检查，并实现真正的延迟检查。

//////////////////////////////
alter table student7026 add constraint ck_student_score check(substr(sno,4,2)='13' and score>=550) deferrable initially deferred;
16、成功做完前15条后，执行语句（如前面步骤有修改，修改完后需再次执行）：

  drop table part2_mes;
  create table part2_mes (tname varchar2(40),mes1 varchar2(100),mes2 clob);
  insert into part2_mes
    select a.table_name||'('||b.partitioning_type||' partition)',a.partition_name ||'('||a.tablespace_name||')',to_lob(a.high_value)
    from user_tab_partitions a,user_part_tables b
    where a.table_name like 'C2%' and a.table_name=b.table_name;
    
  drop table con2_mes;
  create table con2_mes (tname varchar2(40),mes1 varchar2(100),mes2 clob);
  insert into con2_mes
    select a.constraint_name||'('||a.constraint_type||')',A.table_name||'('||b.column_NAME||')',to_lob(search_condition)
    from user_constraints a,user_cons_columns b
    where a.constraint_name=b.constraint_name and a.constraint_name like '_K%' and a.constraint_type='C';
  insert into con2_mes
    select a.constraint_name||'('||a.constraint_type||')',A.table_name||'('||b.column_NAME||')',r_constraint_name
    from user_constraints a,user_cons_columns b
    where a.constraint_name=b.constraint_name and a.constraint_name like '_K%' and a.constraint_type<>'C';
  commit;

//建立映射表语句
alter table xxxxxx move mapping table;

insert into student7052 values('1','2','e','800','1');

 

create table c217026
(
Cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
partition by range(Credit)
(
partition par_3 values less than(3) tablespace TEST2,
partition par_4 values less than(4) tablespace USERS2,
partition par_other values less than(maxvalue) tablespace EXAMPLE2
);

create table c227026
(
cno char(8),
name varchar2(100),
kind char(2),
Credit number(1)
)
partition by list(Kind)
(
partition par_01 values (01) tablespace TEST2,
partition par_02 values (02) tablespace USERS2,
partition par_03 values (03) tablespace Example2
);

create table c237026
(
cno char(8),
Name varchar2(100),
Kind char(2),
credit number(1)
)
partition by hash(Name)
(
partition par_01 tablespace TEST2,
partition par_02 tablespace USERS2,
partition par_03 tablespace EXAMPLE2
);


create table c247026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1),
constraint pk_c247026_cno primary key(cno)
)
organization index tablespace TEST2
mapping table
overflow tablespace tb2_2k;

create cluster CLU_c27026
(
cno char(8)
)
tablespace tb2_8k;

create table c257026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
cluster CLU_c27026(cno)
;

create table SC27026
(
sno char(8),
cno char(8),
grade number(3)
)
cluster CLU_c27026(cno)
;

create table c267026
(
cno char(8),
Name varchar2(100),
K2 char(2),
Credit number(1),
contents clob
)
tablespace tb2_2k 
lob(contents) store as S2LOB (tablespace test2)
;

 
create global temporary table c277026
(
cno char(8),
Name varchar2(100),
Kind char(2),
Credit number(1)
)
on commit delete rows
;

create global temporary table c287026
(
cno char(8),
Name varchar2(100),
kind char(2),
Credit number(1)
)
on commit preserve rows
;
alter system set db_create_file_dest='c:/DB947026';
alter system set db_8K_cache_size=8K;
create tablespace ind2_7026 datafile size 40M autoextend on next 10M maxsize unlimited blocksize 8K;

create bitmap index ind2_c217026_k on c217026(kind) tablespace ind2_7026 local;
create index ind2_c217026_n on c217026(name) tablespace ind2_7026;
create bitmap index ind2_c227026_k on c227026(kind) tablespace ind2_7026 local;
create index ind2_c227026_n on c227026(name) tablespace ind2_7026;
create bitmap index ind2_c237026_k on c237026(kind) tablespace ind2_7026 local;
create index ind2_c237026_n on c237026(name) tablespace ind2_7026;
alter table c247026 move mapping table;

create bitmap index ind2_c247026_k on c247026(kind) tablespace ind2_7026;
create index ind2_c247026_n on c247026(name) tablespace ind2_7026;
create bitmap index ind2_c257026_k on c257026(kind) tablespace ind2_7026;
create index ind2_c257026_n on c257026(name) tablespace ind2_7026;
create bitmap index ind2_c267026_k on c267026(kind) tablespace ind2_7026;
create index ind2_c267026_n on c267026(name) tablespace ind2_7026;

alter index ind2_c217026_k monitoring usage;
alter index ind2_c217026_n monitoring usage;

alter table c217026 add constraint pK_c217026_cno primary key(cno);
alter table c227026 add constraint pK_c227026_cno primary key(cno);
alter table c237026 add constraint pK_c237026_cno primary key(cno);
alter table c257026 add constraint pK_c257026_cno primary key(cno);
alter table c267026 add constraint pK_c267026_cno primary key(cno);
alter table c277026 add constraint pK_c277026_cno primary key(cno);
alter table c287026 add constraint pK_c287026_cno primary key(cno);

alter table c217026 add cpno char(8);
alter table c217026 add constraint fk_c217026_cpno foreign key(cpno) references c217026(cno);

create table student7026 (
                            sno char(8) constraint pk_student7026_sno primary key,
                            sname varchar(10) constraint uk_student7026_sname   unique  constraint nk_student7026_sname not NULL ,
                            sex  char(1)  constraint nk_student7026_sex  not NULL,
                            score number(3) constraint  nk_student7026_score not NULL constraint  ck_student7026_score check(score >=500 and score<=750),
                            monitor   char(8) constraint  fk_student7026_monitor references student7026(sno)
                            ) tablespace test2;

alter table student7026 add constraint ck_student_score check(substr(sno,4,2)='13' and score>=550) deferrable initially deferred;


  drop table part2_mes;
  create table part2_mes (tname varchar2(40),mes1 varchar2(100),mes2 clob);
  insert into part2_mes
    select a.table_name||'('||b.partitioning_type||' partition)',a.partition_name ||'('||a.tablespace_name||')',to_lob(a.high_value)
    from user_tab_partitions a,user_part_tables b 
    where a.table_name like 'C2%' and a.table_name=b.table_name;
                
  drop table con2_mes;
  create table con2_mes (tname varchar2(40),mes1 varchar2(100),mes2 clob);
  insert into con2_mes
    select a.constraint_name||'('||a.constraint_type||')',A.table_name||'('||b.column_NAME||')',to_lob(search_condition)
    from user_constraints a,user_cons_columns b
    where a.constraint_name=b.constraint_name and a.constraint_name like '_K%' and a.constraint_type='C';
  insert into con2_mes
    select a.constraint_name||'('||a.constraint_type||')',A.table_name||'('||b.column_NAME||')',r_constraint_name
    from user_constraints a,user_cons_columns b
    where a.constraint_name=b.constraint_name and a.constraint_name like '_K%' and a.constraint_type<>'C';
  commit;