第二章Orcle数据库应用(上机操作).docxVIP

Orcle数据库应用——技能训练上机练习1：1．创建tp_orders表空间、创建tp_hr表空间CREATE TABLESPACE tp_orders DATAFILE d:\data\tp_order01.dbf SIZE 10MAUTOEXTEND ON;CREATE TABLESPACE tp_hr DATAFILE d:\data\tp_hr01.dbf SIZE 10MAUTOEXTEND ON;2．创建A_oe用户、A_hr用户-- Create the user create user A_oe identified by bdqn default tablespace TP_ORDERS temporary tablespace TEMP;-- Grant/Revoke role privileges grant connect to A_oe;grant resource to A_oe;-- Create the user create user A_hr identified by bdqn default tablespace TP_HR temporary tablespace TEMP;-- Grant/Revoke role privileges grant connect to A_hr;grant resource to A_hr;3．用A_hr用户登录orcl，创建employee、dept两张表CREATE TABLE employee /*-创建员工信息表-*/(EMPNO NUMBER(4), --员工编号ENAME VARCHAR2(10), --员工姓名JOB VARCHAR2(9), --员工工种MGR NUMBER(4), --上级经理编号HIREDATE DATE, --受雇日期SAL NUMBER(7,2), --员工薪水COMM NUMBER(7,2), --福利DEPTNO NUMBER(2) --部门编号);CREATE TABLE DEPT( DEPTNO NUMBER(2) PRIMARY KEY, --部门编号 DNAME VARCHAR2(14) , --部门名称 LOC VARCHAR2(13) --地址) ; 4．将A_hr.employee表的访问权限授予A_oe用户 用system用户登录Orcl，将A_hr.employee表的访问权限授予A_oe用户grant SELECT ON A_hr.employee to A_oe; 5. 使用A_oe登录orcl，访问A_hr.employee表上机练习21.创建A_hr模式的序列dept_seq-- Create sequence create sequence dept_seqminvalue 60maxvalue 10000start with 60increment by 10;2. 从序列中取值插入到部门表dept，至少两条记录INSERT INTO DEPT VALUES (dept_seq.nextval,ACCOUNTING,NEW YORK);INSERT INTO DEPT VALUES (dept_seq.nextval,RESEARCH,DALLAS);INSERT INTO DEPT VALUES (dept_seq.nextval,SALES,CHICAGO);INSERT INTO DEPT VALUES (dept_seq.nextval,OPERATIONS,BOSTON);3. 数据库迁移创建新表deptBakCREATE TABLE deptBak AS SELECT * FROM dept;重新创建序列dept_seq-- Create sequence create sequence dept_seqminvalue 60maxvalue 10000start with 180increment by 10;为新表插入新的记录INSERT INTO DEPTBak VALUES (dept_seq.nextval,OPERATIONS,BOSTON);上机练习31.在system用户中为A_hr用户授权私有同义词权限 GRANT CREATE SYNONYM TO A_hr;用A_hr用户登录，创建dept表的私有同义词sy_deptCREATE SYNONYM sy_dept FOR Dept;2.在system用户中为A_oe用户授权共有同义词权限 GRANT CREATE public SYNONYM TO A_oe;用A_oe用户登录，创建Employee表的共有同义词p_sy_deptCREATE public SYNONYM p_sy_de