【Oracle】实验09：表的管理（647001）

考核内容
     1、连接信息检测,要求如下：
         1）保证第2次实验中服务器端配置的第1个监听端口可以正常工作，
         2）保证使用第2次实验中客户端配置的第5个服务命名可以成功创建会话。
     2、创建分区表,要求如下：
         1）①以system用户登录，创建第1个表：分区表，要求如下：
         2）①--  表名命名为RJPAR671，
         3）①--  表的说明设置为“零件表〈分区表〉”，
         4）①--  同名表的数量为1，
         5）①--  以下为字段信息，根据描述确定合适的类型及宽度：
         6）①--  (1)字段名命名为pno，
         7）①-----  (1)字段说明设置为“件号”，
         8）①-----  (1)字符集使用数据库字符集，
         9）①-----  (1)类型特征为性能优先，
        10）①-----  (1)长度：有非数字字符，字符数为6，
        11）①--  (2)字段名命名为pname，
        12）①-----  (2)字段说明设置为“零件名”，
        13）①-----  (2)字符集使用数据库字符集，
        14）①-----  (2)类型特征为最省空间，
        15）①-----  (2)长度：可存汉字数为7，
        16）①--  (3)字段名命名为pkind，
        17）①-----  (3)字段说明设置为“零件类别”，
        18）①-----  (3)字符集使用国家字符集，
        19）①-----  (3)类型特征为性能优先，
        20）①-----  (3)长度：可存汉字数为6，
        21）①-----  (3)缺省值为“杆状”，
        22）①--  (4)字段名命名为pleave，
        23）①-----  (4)字段说明设置为“出厂日期”，
        24）①-----  (4)类型特征为日期，
        25）①-----  (4)缺省值为当前日期，
        26）①--  (5)字段名命名为pnum，
        27）①-----  (5)字段说明设置为“月生产量”，
        28）①-----  (5)类型特征为数值，
        29）①-----  (5)小数位数为0，
        30）①-----  (5)有效数字个数10，
        31）①-----  (5)缺省值为200，
        32）①--  (6)字段名命名为ppart，
        33）①-----  (6)字段说明设置为“子零件”，
        34）①-----  (6)字符集与“件号”一致，
        35）①-----  (6)类型特征与“件号”一致，
        36）①-----  (6)长度与“件号”一致，
        37）①--  (7)字段名命名为pplant，
        38）①-----  (7)字段说明设置为“生产车间”，
        39）①-----  (7)字符集使用数据库字符集，
        40）①-----  (7)类型特征为性能优先，
        41）①-----  (7)长度：可存汉字数为11，
        42）①-- 以下为附加信息，根据描述确定合适的语句，
        43）①-----  闪回表设置为不支持，
        44）①-- 以下为分区基本信息：
        45）①------- 分区类型设置为哈希，
        46）①------- 分区字段为“零件名”，
        47）①------- 分区数为3，
        48）①------- 分区名命名规则：为P_表名_分区字段名_分区号，
        49）①-- 以下为分区明细信息：
        50）①------ 第1个分区描述如下：
        51）①---------- (1)存储表空间为<第1个表空间>，
        52）①------ 第2个分区描述如下：
        53）①---------- (2)存储表空间为<第2个表空间>，
        54）①------ 第3个分区描述如下：
        55）①---------- (3)存储表空间为<缺省的永久表空间>，
        56）①向此表加6条记录，保证每个分区存2条。
     3、创建索引组织的表,要求如下：
         1）②以system用户登录，创建第2个表：索引组织的表，要求如下：
         2）②--  表名命名为RJEMP882，
         3）②--  表的说明设置为“员工表【索引组织的表】”，
         4）②--  同名表的数量为1，
         5）②--  以下为字段信息，根据描述确定合适的类型及宽度：
         6）②--  (1)字段名命名为e_no，
         7）②-----  (1)字段说明设置为“员工编号”，
         8）②-----  (1)字符集使用国家字符集，
         9）②-----  (1)类型特征为性能优先，
        10）②-----  (1)长度：有非数字字符，字符数为9，
        11）②--  (2)字段名命名为e_fname，
        12）②-----  (2)字段说明设置为“员工名”，
        13）②-----  (2)字符集使用数据库字符集，
        14）②-----  (2)类型特征为最省空间，
        15）②-----  (2)长度：可存汉字数为6，
        16）②--  (3)字段名命名为e_type，
        17）②-----  (3)字段说明设置为“员工工种”，
        18）②-----  (3)字符集使用“国家字符集”，
        19）②-----  (3)类型特征为最省空间，
        20）②-----  (3)长度：可存汉字数为7，
        21）②-----  (3)缺省值为“普通”，
        22）②--  (4)字段名命名为e_hire，
        23）②-----  (4)字段说明设置为“报到时间”，
        24）②-----  (4)类型特征为日期，
        25）②-----  (4)缺省值为当前日期，
        26）②--  (5)字段名命名为e_income，
        27）②-----  (5)字段说明设置为“工资”，
        28）②-----  (5)类型特征为数值，
        29）②-----  (5)小数位数为0，
        30）②-----  (5)有效数字个数为9，
        31）②-----  (5)缺省值为900，
        32）②--  (6)字段名命名为e_manager，
        33）②-----  (6)字段说明设置为“主管”，
        34）②-----  (6)字符集与“员工编号”一致，
        35）②-----  (6)类型特征与“员工编号”一致，
        36）②-----  (6)长度与“员工编号”一致，
        37）②--  (7)字段名命名为e_dept，
        38）②-----  (7)字段说明设置为“部门名称”，
        39）②-----  (7)字符集使用数据库字符集，
        40）②-----  (7)类型特征为最省空间，
        41）②-----  (7)长度：可存汉字数为9，
        42）②-- 以下为附加信息，根据描述确定合适的语句，
        43）②-----  索引键为“员工编号”，
        44）②-----  基本信息初始事务位置数为13，
        45）②-----  基本信息存储位置为<第2个表空间>，
        46）②-----  溢出部分初始事务位置数为8，
        47）②-----  溢出部分存储为<第4个表空间>，
        48）②向此表加的记录数为5。
     4、创建包含大对象字段的表,要求如下：
         1）③以system用户登录，创建第3个表：包含大对象字段的表，要求如下：
         2）③--  表名命名为RJSTU303，
         3）③--  表的说明设置为“学生表【包含大对象字段的表】”，
         4）③--  同名表的数量为1，
         5）③--  以下为字段信息，根据描述确定合适的类型及宽度：
         6）③--  (1)字段名命名为s_id，
         7）③-----  (1)字段说明设置为“学生编号”，
         8）③-----  (1)字符集使用国家字符集，
         9）③-----  (1)类型特征为性能优先，
        10）③-----  (1)长度：有非数字字符，字符数为7，
        11）③--  (2)字段名命名为s_name，
        12）③-----  (2)字段说明设置为“学生姓名”，
        13）③-----  (2)字符集使用数据库字符集，
        14）③-----  (2)类型特征为性能优先，
        15）③-----  (2)长度：可存汉字数为11，
        16）③--  (3)字段名命名为s_city，
        17）③-----  (3)字段说明设置为“毕业地区”，
        18）③-----  (3)字符集使用国家字符集，
        19）③-----  (3)类型特征为最省空间，
        20）③-----  (3)长度：可存汉字数为8，
        21）③-----  (3)缺省值为“广州”，
        22）③--  (4)字段名命名为s_enroll，
        23）③-----  (4)字段说明设置为“报到时间”，
        24）③-----  (4)类型特征为日期，
        25）③-----  (4)缺省值为当前日期，
        26）③-----  (4)缺省值“当前日期”，
        27）③--  (5)字段名命名为s_score，
        28）③-----  (5)字段说明设置为“入学成绩”，
        29）③-----  (5)类型特征为数值，
        30）③-----  (5)小数位数为0，
        31）③-----  (5)有效数字个数为5，
        32）③-----  (5)缺省值为500，
        33）③--  (6)字段名命名为s_monitor，
        34）③-----  (6)字段说明设置为“学习委员”，
        35）③-----  (6)字符集与“学生编号”一致，
        36）③-----  (6)类型特征与“学生编号”一致，
        37）③-----  (6)长度与“学生编号”一致，
        38）③--  (7)字段名命名为s_dept，
        39）③-----  (7)字段说明设置为“系名称”，
        40）③-----  (7)字符集使用数据库字符集，
        41）③-----  (7)类型特征为性能优先，
        42）③-----  (7)长度：可存汉字数为9，
        43）③--  (8)字段名命名为s_skill，
        44）③-----  (8)字段说明设置为“特长信息”，
        45）③-----  (8)字符集使用国家字符集，
        46）③-----  (8)类型特征为存放超过4000字节的文本，
        47）③-----  (8)段名为s_skill_l，
        48）③-----  (8)存储表空间为<第4个表空间>，
        49）③--  (9)字段名命名为s_photo，
        50）③-----  (9)字段说明设置为“照片”，
        51）③-----  (9)类型特征为存储照片，
        52）③-----  (9)段名为s_photo_l，
        53）③-----  (9)存储表空间为<第6个表空间>，
        54）③-- 以下为附加信息，根据描述确定合适的语句，
        55）③-----  基本信息存储表空间为<第2个表空间>，
        56）③-----  闪回表为不支持，
        57）③-----  初始事务位置数为22，
        58）③-----  使用缓存池的特征为“立即淘汰”，
        59）③向此表加的记录数为5。
     5、创建簇,要求如下：
         1）以system用户登录，仅创建1个簇，要求如下：
         2）--- 簇名命名为RJC_AB，
         3）--- 存储表空间为<第4个表空间>，
         4）--- 共同字段描述如下：
         5）------- 字段名命名为cou_prno，
         6）------- 字符集使用数据库字符集，
         7）------- 类型特征为性能优先，
         8）------- 长度：可存英文字符数为10，
         9）------- 初始事务位置数为9，
        10）------- 使用缓存池的特征为“立即淘汰”。
     6、创建第1个簇表,要求如下：
         1）④以system用户登录，创建第4个表：簇表1，要求如下：
         2）④--  表名命名为RJCOU804，
         3）④--  表的说明设置为“课程表-簇表1”，
         4）④--  同名表的数量为1，
         5）④--  以下为字段信息，根据描述确定合适的类型及宽度：
         6）④--  (1)字段名命名为couno，
         7）④-----  (1)字段说明设置为“课程编号”，
         8）④-----  (1)字符集使用国家字符集，
         9）④-----  (1)类型特征为性能优先，
        10）④-----  (1)长度：有非数字字符，字符数为12，
        11）④--  (2)字段名命名为couname，
        12）④-----  (2)字段说明设置为“课程名称”，
        13）④-----  (2)字符集使用国家字符集，
        14）④-----  (2)类型特征为性能优先，
        15）④-----  (2)长度：可存汉字数为9，
        16）④--  (3)字段名命名为coukind，
        17）④-----  (3)字段说明设置为“课程种类”，
        18）④-----  (3)字符集使用数据库字符集，
        19）④-----  (3)类型特征为最省空间，
        20）④-----  (3)长度：可存汉字数为10，
        21）④-----  (3)缺省值为“专业选修”，
        22）④--  (4)字段名命名为coumodify，
        23）④-----  (4)字段说明设置为“修改时间”，
        24）④-----  (4)类型特征为日期，
        25）④-----  (4)缺省值为当前日期，
        26）④--  (5)字段名命名为coulevel，
        27）④-----  (5)字段说明设置为“课程学分”，
        28）④-----  (5)类型特征为数值，
        29）④-----  (5)小数位数为0，
        30）④-----  (5)有效数字个数为2，
        31）④-----  (5)缺省值为4，
        32）④--  (6)字段名命名为couprev，
        33）④-----  (6)字段说明设置为“前序课程”，
        34）④-----  (6)字符集与“课程编号”一致，
        35）④-----  (6)类型特征与“课程编号”一致，
        36）④-----  (6)长度与“课程编号”一致，
        37）④--  (7)字段名命名为couspno，
        38）④-----  (7)字段说明设置为“专业号”，
        39）④-----  (7)字符集与簇中共同字段一致，
        40）④-----  (7)类型特征与簇中共同字段一致，
        41）④-----  (7)长度与簇中共同字段一致，
        42）④-- 以下为附加信息，根据描述确定合适的语句，
        43）④-----  存储簇为RJC_AB，
        44）④-----  闪回表为“支持”，
        45）④向此表加的记录数为5。
     7、创建第2个簇表,要求如下：
         1）⑤以system用户登录，创建第5个表：簇表2，要求如下：
         2）⑤--  表名命名为RJSPEC835，
         3）⑤--  表的说明设置为“专业表-簇表2”，
         4）⑤--  同名表的数量为1，
         5）⑤--  以下为字段信息，根据描述确定合适的类型及宽度：
         6）⑤--  (1)字段名命名为spec_spno，
         7）⑤-----  (1)字段说明设置为“专业序号”，
         8）⑤-----  (1)字符集与簇中共同字段一致，
         9）⑤-----  (1)类型特征与簇中共同字段一致，
        10）⑤-----  (1)长度与簇中共同字段一致，
        11）⑤--  (2)字段名命名为spec_spname，
        12）⑤-----  (2)字段说明设置为“专业名称”，
        13）⑤-----  (2)字符集使用数据库字符集，
        14）⑤-----  (2)类型特征为最省空间，
        15）⑤-----  (2)长度：可存汉字数为10，
        16）⑤-- 以下为附加信息，根据描述确定合适的语句，
        17）⑤-----  存储簇为RJC_AB，
        18）⑤-----  闪回表为不支持，
        19）⑤向此表加的记录数为6。
     8、创建全局临时表,要求如下：
         1）⑥以system用户登录，创建第6个表，要求如下：
         2）⑥--  表名命名为RJPAR816，
         3）⑥--  表的说明设置为“零件表（全局临时表）”，
         4）⑥--  同名表的数量为1，
         5）⑥--  字段信息与“分区表”一致，
         6）⑥--  数据保持时间为事务期间。
实验步骤：

创建第1个表：分区表

conn system/647001

create table RJPAR671
(
  pno char(6),
  pname varchar2(14), 
  pkind nchar(6) default '杆状',
  pleave date default sysdate,
  pnum number(10,0) default 200,
  ppart char(6),
  pplant char(22)
)
partition by hash(pname)
( 
  partition P_RJPAR671_pname_1 tablespace TA_145,
  partition P_RJPAR671_pname_2 tablespace TD_653,
  partition P_RJPAR671_pname_3 tablespace TA_FAD
);

comment on table RJPAR671 is '零件表〈分区表〉';
comment on column RJPAR671.pno is '件号';
comment on column RJPAR671.pname is '零件名';
comment on column RJPAR671.pkind is '零件类别';
comment on column RJPAR671.pleave is '出厂日期';
comment on column RJPAR671.pnum is '月生产量';
comment on column RJPAR671.ppart is '子零件';
comment on column RJPAR671.pplant is '生产车间';

insert into RJPAR671(pno,pname,pleave) values('a1','03',to_date('2010-06-01','yyyy-mm-dd'));
insert into RJPAR671(pno,pname,pleave) values('b2','2',to_date('2010-06-02','yyyy-mm-dd'));
insert into RJPAR671(pno,pname,pnum) values('c3','03','9');
insert into RJPAR671(pno,pname,pnum) values('a4','4','8');
insert into RJPAR671(pno,pname,pkind) values('b5','5','3');
insert into RJPAR671(pno,pname,pkind) values('c6','6','3');

commit;
analyze table RJPAR671 compute statistics;
select table_name,partition_name,num_rows from dba_tab_partitions where table_name='RJPAR671';

//删除错表  drop table RJPAR671;
//查哪条记录在哪个哈希分区  select substr(rowid,7,3),pname from RJPAR671;
//除了上面的，也可以选择插入这6条数据
insert into RJPAR671(pno,pname) values('1','00');
insert into RJPAR671(pno,pname) values('2','00');

insert into RJPAR671(pno,pname) values('3','02');
insert into RJPAR671(pno,pname) values('4','02');

insert into RJPAR671(pno,pname) values('5','03');
insert into RJPAR671(pno,pname) values('6','03');

索引组织的表:

create table RJEMP882
(
  e_no nchar(9),
  e_fname varchar2(12), 
  e_type nvarchar2(7) default '普通',
  e_hire date default sysdate,
  e_income number(9,0) default 900,
  e_manager nchar(9),
  e_dept varchar2(18),
  primary key (e_no)
)
Organization index tablespace TD_653 initrans 13 overflow tablespace TSA_454 initrans 8;

comment on table RJEMP882 is '员工表【索引组织的表】';
comment on column RJEMP882.e_no is '员工编号';
comment on column RJEMP882.e_fname is '员工名';
comment on column RJEMP882.e_type is '员工工种';
comment on column RJEMP882.e_hire is '报到时间';
comment on column RJEMP882.e_income is '工资';
comment on column RJEMP882.e_manager is '主管';
comment on column RJEMP882.e_dept is '部门名称';

insert into RJEMP882(e_no) values('001');
insert into RJEMP882(e_no) values('002');
insert into RJEMP882(e_no) values('003');
insert into RJEMP882(e_no) values('004');
insert into RJEMP882(e_no) values('005');

commit;
analyze table RJEMP882 compute statistics;
select table_name,num_rows from dba_tables where table_name='RJEMP882';

大对象字段的表:
 

create table RJSTU303
(
  s_id nchar(7),
  s_name char(22), 
  s_city nvarchar2(8) default '广州',
  s_enroll date default sysdate,
  s_score number(5,0) default 500,
  s_monitor nchar(7),
  s_dept char(18),
  s_skill nclob,
  s_photo blob
)
tablespace TD_653
Lob(s_skill) store as s_skill_l(tablespace TSA_454),
Lob(s_photo) store as s_photo_l(tablespace TSA_5),
initrans 22 storage (buffer_pool recycle);

comment on table RJSTU303 is '学生表【包含大对象字段的表】';
comment on column RJSTU303.s_id is '学生编号';
comment on column RJSTU303.s_name is '学生姓名';
comment on column RJSTU303.s_city is '毕业地区';
comment on column RJSTU303.s_enroll is '报到时间';
comment on column RJSTU303.s_score is '入学成绩';
comment on column RJSTU303.s_monitor is '学习委员';
comment on column RJSTU303.s_dept is '系名称';
comment on column RJSTU303.s_skill is '特长信息';
comment on column RJSTU303.s_photo is '照片';

insert into RJSTU303(s_id) values('001');
insert into RJSTU303(s_id) values('002');
insert into RJSTU303(s_id) values('003');
insert into RJSTU303(s_id) values('004');
insert into RJSTU303(s_id) values('005');

commit;
analyze table RJSTU303 compute statistics;
select table_name,num_rows from dba_tables where table_name='RJSTU303';

簇:

Create cluster RJC_AB(cou_prno char(10)) tablespace TSA_454 initrans 9 storage (buffer_pool recycle);

第一个簇表：

create table RJCOU804
(
  couno nchar(12),
  couname nchar(9), 
  coukind varchar2(20) default '专业选修',
  coumodify date default sysdate,
  coulevel number(2,0) default 4,
  couprev nchar(12),
  couspno char(10)
) 
cluster RJC_AB(couspno);

comment on table RJCOU804 is '课程表-簇表1';
comment on column RJCOU804.couno is '课程编号';
comment on column RJCOU804.couname is '课程名称';
comment on column RJCOU804.coukind is '课程种类';
comment on column RJCOU804.coumodify is '修改时间';
comment on column RJCOU804.coulevel is '课程学分';
comment on column RJCOU804.couprev is '前序课程';
comment on column RJCOU804.couspno is '专业号';

create index RJCOU804 on cluster RJC_AB;
insert into RJCOU804(couno) values('001');
insert into RJCOU804(couno) values('002');
insert into RJCOU804(couno) values('003');
insert into RJCOU804(couno) values('004');
insert into RJCOU804(couno) values('005');

alter table RJCOU804 enable row movement; 
analyze table RJCOU804 compute statistics;
select table_name,num_rows from dba_tables where table_name='RJCOU804';

第二个簇表：

create table RJSPEC835
(
  spec_spno char(10)，
  spec_spname varchar2(20)
) 
cluster RJC_AB(spec_spno);

comment on table RJSPEC835 is '专业表-簇表2';
comment on column RJSPEC835.spec_spno is '专业序号';
comment on column RJSPEC835.spec_spname is '专业名称';

insert into RJSPEC835(spec_spno,spec_spname) values('19','qq');
insert into RJSPEC835(spec_spno,spec_spname) values('19','qw');
insert into RJSPEC835(spec_spno,spec_spname) values('19','qe');
insert into RJSPEC835(spec_spno,spec_spname) values('19','qr');
insert into RJSPEC835(spec_spno,spec_spname) values('19','qt');
insert into RJSPEC835(spec_spno,spec_spname) values('19','qy');

analyze table RJSPEC835 compute statistics;
select table_name,num_rows from dba_tables where table_name='RJSPEC835';

全局临时表:
 

CREATE GLOBAL TEMPORARY TABLE RJPAR816
(
  pno char(6),
  pname varchar2(14), 
  pkind nchar(6) default '杆状',
  pleave date default sysdate,
  pnum number(10,0) default 200,
  ppart char(6),
  pplant char(22)
)
on commit delete rows;
comment on table RJPAR816 is '零件表（全局临时表）';
comment on column RJPAR816.pno is '件号';
comment on column RJPAR816.pname is '零件名';
comment on column RJPAR816.pkind is '零件类别';
comment on column RJPAR816.pleave is '出厂日期';
comment on column RJPAR816.pnum is '月生产量';
comment on column RJPAR816.ppart is '子零件';
comment on column RJPAR816.pplant is '生产车间';

当drop不能删除全局临时表时：

ORA-14452: 试图创建, 变更或删除正在使用的临时表中的索引

SYS(T647001)>truncate table RJPAR816;
表被截断。

SYS(T647001)>drop table RJPAR816 cascade constraints;
表已删除。