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

1、Orcle数据库应用技能训练上机练习1：1创建tp_orders表空间、创建tp_hr表空间CREATE TABLESPACE tp_orders DATAFILE d:datatp_order01.dbf SIZE 10MAUTOEXTEND ON;CREATE TABLESPACE tp_hr DATAFILE d:datatp_hr01.dbf SIZE 10MAUTOEXTEND ON;2创建A_oe用户、A_hr用户- Create the user create user A_oe identified by bdqn default tablespace TP_ORDERS te

2、mporary 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，创建em

3、ployee、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

4、) , -部门名称 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

5、;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 d

6、eptBak 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表的私有同义词s

7、y_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_dept FOR A_hr.Employee;用jbit用户登录，用同义词访问dept和Employee表共有的同义词，可在所有的用户下访问私有的同义词只能在该用户模式下访问，不可在所有的用户下访问上机练习4用A_oe用户登录，建立customers表（1）建立索引唯

8、一或者反向索引create unique index index_unique_customer_id on CUSTOMERS (customer_id);或create unique index index_unique_customer_id on CUSTOMERS (customer_id) reverse;（2）给地域创建位图索引create bitmap index index_bit_nls_territory on CUSTOMERS (nls_territory);（3）建立组合索引create index index_ename on CUSTOMERS (cust_fi

9、rst_name, cust_last_name);上机练习5 范围分区表1.创建order表2. 建立范围分区CREATE TABLE rangeOrdersPARTITION BY RANGE (order_date)( PARTITION P1 VALUES LESS THAN (to_date(2005-01-01, yyyy-mm-dd), PARTITION P2 VALUES LESS THAN (to_date(2006-01-01, yyyy-mm-dd), PARTITION P3 VALUES LESS THAN (to_date(2007-01-01, yyyy-mm-

10、dd), PARTITION P4 VALUES LESS THAN (to_date(2008-01-01, yyyy-mm-dd), PARTITION P5 VALUES LESS THAN (to_date(2009-01-01, yyyy-mm-dd), PARTITION P6 VALUES LESS THAN (maxvalue)as select * from orders;3. 查询每个分区4.插入2013/01/01数据INSERT INTO rangeorders VALUES (2557 ,TO_TIMESTAMP(01-Jan-13 10.22.16. PM ,DD-

11、MON-RR HH.MI.SS.FF AM ,NLS_DATE_LANGUAGE=American) ,direct ,118 ,5 ,21586.2 ,159 ,NULL);5.删除第三个分区delete from rangeorders partition(p3)上机练习61.创建间隔分区表CREATE TABLE intervalOrders PARTITION BY RANGE(order_date) INTERVAL(NUMTOYMINTERVAL(1,year) (PARTITION P1 VALUES LESS THAN (to_date(2005-01-01,yyyy/mm/dd) as select * from orders2.获得分区情况 SELECT table_name,partition_name FROM user_tab_partitions WHERE table_name=UPPER(intervalorders);3.查询每个分区的数据4.插入数据并查看INSERT INTO intervalorders VALUES (2557 ,TO_