实验二：表的管理（设计型，2课时）
(1)Create table class(cno number(2) primary key,cname varchar2(20),num number(3));
Create table student(sno number(4) primary key,sname varchar2(10) unique,sage number,sex char(2),cno number(2));
(2)Alter table student add constraint s_fk foreign key(cno) references class(cno);
(3)Alter table student add constraint s_ck1 check(sage between 0 and 100);
(4)Alter table student add constraint s_ck2 check(sex in (‘M’,’F’));
(5)Create unique index class_cname on class(cname);
(6)Create globle temporary table student_tmp1(sno number(4) primary key,sname varchar2(10) unique,sage number,sex char(2),cno number(2))on commit delete rows;
Create globle temporary table student_tmp2(sno number(4) primary key,sname varchar2(10) unique,sage number,sex char(2),cno number(2))on commit preserve rows;
(7)SQL> create table student_range as select * from student;
表已创建。
SQL> select table_name from user_tables;
TABLE_NAME
CLASS
STUDENT
STUDENT_RANGE
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1
已选择6行。
SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1,partition p3 values less than (maxvalue) tablespace orcltbs2);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1,partition p3 values less than (maxvalue) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项
SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项
SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项
SQL> CREATE table student_range2 AS SELECT * FROM student partition by range(sag
e)(partition p1 values less than (20) tablespace example,partition p2 values LES
S THAN (30) tablespace orcltbs1,partition p3 values less than (MAXVALUE) tablesp
ace orcltbs2);
CREATE table student_range2 AS SELECT * FROM student partition by range(sage)(pa
rtition p1 values less than (20) tablespace example,partition p2 values LESS THA
N (30) tablespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace o
rcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号
SQL> edit
已写入 file afiedt.buf
1* CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
SQL>
SQL> CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
2 /
CREATE table student_range2(sno number(4) primary key,sname varchar2(10) unique,
sage number,sex char(2),cno number(2)) partition by range(sage)(partition p1 val
ues less than (20) tablespace example,partition p2 values LESS THAN (30) tablesp
ace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00959: 表空间 'ORCLTBS1' 不存在
SQL> create tablespace orcltbs1 datafile 'D:\oracle\product\10.2.0\oradata\orcl
orcltbsd1_1.dbf'size 50M;
表空间已创建。
SQL> create tablespace orcltbs2 datafile 'D:\oracle\product\10.2.0\oradata\orcl
orcltbsd2_1.dbf'size 50M;
表空间已创建。
SQL> CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
2 /
表已创建。
SQL> select table_name from user_tables;
TABLE_NAME
CLASS
STUDENT
STUDENT_RANGE
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1
已选择7行。
SQL> desc student_range2;
名称 是否为空? 类型
SNO NOT NULL NUMBER(4)
SNAME VARCHAR2(10)
SAGE NUMBER
SEX CHAR(2)
CNO NUMBER(2)
SQL> CREATE table student_list(sno number(4) primary key,sname varchar2(10) uniq
ue,sage number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM
tablespace orcltbs1,partition p2sF tablespace orcltbs2);
CREATE table student_list(sno number(4) primary key,sname varchar2(10) unique,sa
ge number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM tabl
espace orcltbs1,partition p2sF tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00926: 缺失 VALUES 关键字
（8）SQL> CREATE table student_list(sno number(4) primary key,sname varchar2(10) uniq
ue,sage number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM
values('M') tablespace orcltbs1,partition p2sF values('F') tablespace orcltbs2);
表已创建。
SQL> select table_name from user_tables;
TABLE_NAME
CLASS
STUDENT
STUDENT_RANGE
STUDENT_LIST
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1
已选择8行。
（9）SQL> create sequence wy0411 increment by 2 start with 10000 maxvalue 100000 nocy
cle;
序列已创建。
SQL> select table_name from user_tables;
TABLE_NAME
CLASS
STUDENT
STUDENT_RANGE
STUDENT_LIST
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1
已选择8行。
SQL> spool off;
当前未假脱机
SQL> save c:a.txt
已创建 file c:\a.txt
SQL> list
1* select table_name from user_tables
SQL>
网友评论