Oracle10g上机实验汇总.doc

数据库课程设计实验报告专业 计算机科学与技术 姓名 许静晨 学号 1027401097 指导老师 季怡 实验室 使用日期 苏州大学计算机科学与技术学院统一印制二零零九年十二月试验1关系模式如下：教师（教师号，姓名，性别，出生日期，系，职称，课程号）课程（课程号，课程名，学分）学生（学号，姓名，性别，年龄，班级，系）学习（学号，课程号，成绩）试验21利用DBCA创建一个名为STUDENT的数据库。2启动SQL*Plus工具，用SYSTEM用户连接到ORCL数据库。3.为当前数据库用户配置数据库证明。试验3为EXAMPLE表空间添加一个数据文件，文件名为example02.dbf，大小为20M。用PL/SQL实现试验41使用SQL命令创建一个本地管理方式下自动分区管理的表空间USERTBS1，其对应的数据文件大小为20MB。SQL> CREATE TABLESPACE USERTBS1 DATAFILE 2 'C:ORACLEPRODUCT10.2.0ORADATAstudentUSERTBS1.DBF'SIZE 20M 3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;2.使用SQL命令创建一个本地管理方式下的表空间USERTBS2，要求每个分区大小为512KB。SQL> CREATE TABLESPACE USERTBS2 DATAFILE 2 'C:ORACLEPRODUCT10.2.0ORADATAstudentUSERTBS2.DBF'SIZE 20M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;3.修改USERTBS1表空间的大小，将该表空间的数据文件改为自动扩展方式，最大值为100MB。SQL> ALTER DATABASE DATAFILE2 'C:ORACLEPRODUCT10.2.0ORADATAstudentUSERTBS1.DBF'RESIZE 40M;SQL> ALTER DATABASE DATAFILE 2 'C:ORACLEPRODUCT10.2.0ORADATAstudentUSERTBS1.DBF' 3 AUTOEXTEND ON MAXSIZE 100M; Database altered4.为USERTBS2表空间添加一个数据文件，以改变该表的空间的大小。SQL> ALTER TABLESPACE USERTBS2 ADD DATAFILE 2 'C:ORACLEPRODUCT10.2.0ORADATAstudentUSERTBS2_1.DBF' SIZE 10M; Tablespace altered5.使用SQL命令创建一个本地管理方式下的临时表空间TEMPTBS，并将该表空间作为单签数据库实例的默认临时表空间。SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE 2 'C:ORACLEPRODUCT10.2.0ORADATAstudentTEMPTBS.DBF' SIZE 20M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; Tablespace createdSQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS; Database altered8.删除表空间USERTBS2，同时删除该表空间的内容以及对应的操作系统文件。SQL> DROP TABLESPACE USERTBS2 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped9.查询当前数据库中所有的表空间及其对应的数据文件信息。SQL> SELECT FROM DBA_DATA_FILES; BLOCKS TABLESPACE_NAME- - -C:ORACLEPRODUCT10.2.0ORADATASTUDENTUSERS01.DBF 640 USERSC:ORACLEPRODUCT10.2.0ORADATASTUDENTSYSAUX01.DBF 30720 SYSAUXC:ORACLEPRODUCT10.2.0ORADATASTUDENTUNDOTBS01.DBF 4480 UNDOTBS1C:ORACLEPRODUCT10.2.0ORADATASTUDENTSYSTEM01.DBF 61440 SYSTEMC:ORACLEPRODUCT10.2.0ORADATASTUDENTEXAMPLE01.DBF 12800 EXAMPLEC:ORACLEPRODUCT10.2.0ORADATASTUDENTORCLTBS6_1.DBF 2560 ORCLTBS6C:ORACLEPRODUCT10.2.0ORADATASTUDENTUSERTBS1.DBF 5120 USERTBS1 7 rows selected试验5（1）按下列表格结构利用SQL语句创建class，student俩个表。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）为student表添加一个可以延迟的外间约束，其CNO列参照class表的CNO列。Alter table student add constraint fk_cno foreign key (cno) references class(cno) deferrable;（3）为student表的SAGE列添加一个检查约束，保证该列取值在0100之间。Alter table student add constraint ck_sage check (sage>0 and sage<=100);（4）为student表的SEX列表添加一个检查约束，保证该列取值为“M”或“F”且默认值为“M”。alter table student add constraint ck_stu check(sex='M' or sex='F')modify sex default 'M'（5）在class表的CNAME列上创建一个唯一性索引。Create unique index ind_cname on class(cname);（6）利用子查询分别创建一个事务级的临时表和会话级的临时表，其结构域student表的结构相同。Create global temporary table stu_transOn commit delete rows asSelect * from student;Create global temporary table stu_sessOn commit preserve rows asSelect * from student;（7）创建一个student_range表（列，类型与student表的列，类型相同），按学生的年龄分为3个区，低于20岁的学生信息放入part1区，存储在EXAMPLE表空间中；2030岁的学生信息放在part2区，存放在ORCLTBS1表空间中；其他数据放在part3区，存放在ORCLTBS2表空间中。create table student_range(sno number(4) primary key,sname varchar2(10),sage number,sex char(2),cno number(2)partition by range(sage)(partition part1 values less than(20) tablespace example,partition part2 values less than(30) tablespace orcltbs1,partition part3 values less than(maxvalue) tablespace orcltbs2)（8）创建一个student_list表（列，类型与student表的列，类型相同），按学生性别分为俩个区。create table student_list(sno number(4) primary key,sname varchar2(10),sage number,sex char(2),cno number(2)partition by list(sex)(partition man values('M') tablespace orcltbs1,partition woman values('F') tablespace orcltbs2)（9）创建一个启始值为10000的序列，步长为2最大值为100000，不可循环。Create sequence sequ1 increment by 2 start with 10000 maxvalue 100000 nocycle;试验61.创建一个口令认证的数据库用户usera_exer,口令为usera，默认表空间为USERS，配额为10MB，初始帐户为锁定状态。create user usera_exer identified by usera default tablespace users quota 10M on users account lock; 3.为usera_exer用户授予CREATE SESSION权限、scott.emp的SELECT权限和UPDATE权限。同时允许该用户将获得的权限授予其他用户。 conn scott/tiger grant select,update on scott.emp to usera_exer with grant option;8.创建角色rolea和roleb,将CREATE TABLE权限、scott.emp的INSERT权限和DELETE权限授予rolea;将CONNECT,RESOURCE角色授予roleb。create role rolea; create role roleb; grant create table to rolea; conn scott/tiger grant insert,delete on scott.emp to rolea; conn system/manager grant connect,resource to roleb; 9.将角色rolea、roleb授予用户usera_exer。grant rolea,roleb to usera_exer; 10.屏蔽用户usera_exer的roleb角色。alter user usera_exer default role all except roleb; 1.使用冷物理备份对数据库进行完全备份select from dba_data_files;select member from v$logfile;select value from v$parameter where name='control_files'SQL> shutdown immediate;SQL> host copy;SQL> startup;2.假定丢失了一个数据文件example01.dbf, 试使用前面做过的完全备份对数据库进行恢复，并验证恢复是否成功。shutdown mount ;alter database datafile 'C:oracleproduct10.2.0oradataSTUDENTEXAMPLE01.DBF' offline;alter database open;recover datafile'C:oracleproduct10.2.0oradataSTUDENTEXAMPLE01.DBF'alter database datafile 'C:oracleproduct10.2.0oradataSTUDENTEXAMPLE01.DBF' online;8.将数据库SCOTT 模式下的EMP,DEPT表导出-expdp scott/tiger directory=dumpdir dump log tables=emp,dept job_name=eemp_dept_job parallel=3;EXPDP scotttiger directory = mypump dumpfile = KXP_1.dmp tables = scott.emp EXPDP scotttiger directory = mypump dumpfile = KXP_2.dmp tables = scott.dept9.将scott模式下的EMP，DEPT 表删除，利用（8）中的到处文件恢复IMPDP scott/tiger directory = mypump dumpfile = KXP_1.dmp tables = scott.empIMPDP scott/tiger directory = mypump dumpfile = KXP_2.dmp tables = scott.dept试验75查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。SQL> select * from scott.emp where (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLEAR'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7782 CLARK MANAGER 7839 1981-6-9 2450.00 1018查询员工的姓名和入职日期，并按入职日期从先到后进行排列。SQL> select ename,hiredate from scott.emp order by hiredate asc ; ENAME HIREDATE- -SMITH 1980-12-17ALLEN 1981-2-20WARD 1981-2-22JONES 1981-4-2BLAKE 1981-5-1CLARK 1981-6-9TURNER 1981-9-8MARTIN 1981-9-28KING 1981-11-17JAMES 1981-12-3FORD 1981-12-3MILLER 1982-1-23SCOTT 1987-4-19ADAMS 1987-5-23 14 rows selected30查询最低工资大于2500的各种工作。SQL> select job from (select min(sal) min_sal,job from scott.emp group by job) where min_sal > 2500 ; JOB-PRESIDENTANALYST试验8（2）编写一个PL/SQL块，输出所有比本部门平均工资高的员工信息。DECLARECURSOR c_emp IS SELECT * FROM emp WHERE sal>(select avg(sal) from emp);v_emp c_emp%ROWTYPE;BEGINOPEN c_emp;LOOP FETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|' '| v_emp.ename|' '|v_emp.sal|' '|v_emp.hiredate|' '|v_emp.deptno);END LOOP;CLOSE c_emp;END;因为所有职工的工资都是3000，所以没有结果。（4）查询姓为“Smith”的员工信息，并输出其员工号，姓名，工资，部门号。如果该员工不存在，则插入一条新的记录，员工号为2012，员工姓为“Smith”,工资为7500元，入职日期为“2002年3月5日”，部门号为50。如果存在多个名为“Smith”的员工，则输出所有名为“Smith”的员工号，姓名，工资，入职日期，部门号L。DECLARE v_emp emp%ROWTYPE;BEGIN SELECT * INTO v_emp FROM EMP WHERE ename='SMITH' DBMS_OUTPUT.PUT_LINE(v_emp.empno|' '| v_emp.ename|' '|v_emp.sal|' '|v_emp.hiredate|' '|v_emp.deptno);EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO EMP(EMPNO,ENAME,SAL,HIREDATE,DEPTNO) VALUES(20012,'SMITH',7500,5-3月-2002,50); WHEN TOO_MANY_ROWS THEN FOR v IN (SELECT * FROM EMP WHERE ENAME='SMITH') LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno|' '| v_emp.ename|' '|v_emp.sal|' '|v_emp.hiredate|' '|v_emp.deptno); END LOOP;END;2012 Smith 7500 2002-3-5 5020 / 20文档可自由编辑打印