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

Orcle数据库应用——技能训练上机练习1：1．创建tp_orders表空间、创建tp_hr表空间CREATETABLESPACEtp_ordersDATAFILE'd:\data\tp_order01.dbf'SIZE10MAUTOEXTENDON;CREATETABLESPACEtp_hrDATAFILE'd:\data\tp_hr01.dbf'SIZE10MAUTOEXTENDON;2．创建A_oe用户、A_hr用户--CreatetheusercreateuserA_oeidentifiedbybdqndefaulttablespaceTP_ORDERStemporarytablespaceTEMP;--Grant/RevokeroleprivilegesgrantconnecttoA_oe;grantresourcetoA_oe;--CreatetheusercreateuserA_hridentifiedbybdqndefaulttablespaceTP_HRtemporarytablespaceTEMP;--Grant/RevokeroleprivilegesgrantconnecttoA_hr;grantresourcetoA_hr;3．用A_hr用户登录orcl，创建employee、dept两张表CREATETABLEemployee/*-创建员工信息表-*/(EMPNONUMBER(4),--员工编号ENAMEVARCHAR2(10),--员工姓名JOBVARCHAR2(9),--员工工种MGRNUMBER(4),--上级经理编号HIREDATEDATE,--受雇日期SALNUMBER(7,2),--员工薪水COMMNUMBER(7,2),--福利DEPTNONUMBER(2)--部门编号);CREATETABLEDEPT(DEPTNONUMBER(2)PRIMARYKEY,--部门编号DNAMEVARCHAR2(14),--部门名称LOCVARCHAR2(13)--地址);4．将A_hr.employee表的访问权限授予A_oe用户用system用户登录Orcl，将A_hr.employee表的访问权限授予A_oe用户grantSELECTONA_hr.employeetoA_oe;5.使用A_oe登录orcl，访问A_hr.employee表上机练习21.创建A_hr模式的序列dept_seq--Createsequencecreatesequencedept_seqminvalue60maxvalue10000startwith60incrementby10;2.从序列中取值插入到部门表dept，至少两条记录INSERTINTODEPTVALUES(dept_seq.nextval,'ACCOUNTING','NEWYORK');INSERTINTODEPTVALUES(dept_seq.nextval,'RESEARCH','DALLAS');INSERTINTODEPTVALUES(dept_seq.nextval,'SALES','CHICAGO');INSERTINTODEPTVALUES(dept_seq.nextval,'OPERATIONS','BOSTON');3.数据库迁移创建新表deptBakCREATETABLEdeptBakASSELECT*FROMdept;重新创建序列dept_seq--Createsequencecreatesequencedept_seqminvalue60maxvalue10000startwith180incrementby10;为新表插入新的记录INSERTINTODEPTBakVALUES(dept_seq.nextval,'OPERATIONS','BOSTON');上机练习31.在system用户中为A_hr用户授权私有同义词权限GRANTCREATESYNONYMTOA_hr;用A_hr用户登录，创建dept表的私有同义词sy_deptCREATESYNONYMsy_deptFORDept;2.在system用户中为A_oe用户授权共有同义词权限GRANTCREATEpublicSYNONYMTOA_oe;用A_oe用户登录，创建Employee表的共有同义词p_sy_deptCREATEpublicSYNONYMp_sy_deptFORA_hr.Employee;用jbit用户登录，用同义词访问dept和Employee表共有的同义词，可在所有的用户下访问私有的同义词只能在该用户模式下访问，不可在所有的用户下访问上机练习4用A_oe用户登录，建立customers表（1）建立索引唯一或者反向索引createuniqueindexindex_unique_customer_idonCUSTOMERS(customer_id);或createuniqueindexindex_unique_customer_idonCUSTOMERS(customer_id)reverse;（2）给地域创建位图索引createbitmapindexindex_bit_nls_territoryonCUSTOMERS(nls_territory);（3）建立组合索引createindexindex_enameonCUSTOMERS(cust_first_name,cust_last_name);上机练习5范围分区表1.创建order表2.建立范围分区CREATETABLErangeOrdersPARTITIONBYRANGE(order_date)(PARTITIONP1VALUESLESSTHAN(to_date('2005-01-01','yyyy-mm-dd')),PARTITIONP2VALUESLESSTHAN(to_date('2006-01-01','yyyy-mm-dd')),PARTITIONP3VALUESLESSTHAN(to_date('2007-01-01','yyyy-mm-dd')),PARTITIONP4VALUESLESSTHAN(to_date('2008-01-01','yyyy-mm-dd')),PARTITIONP5VALUESLESSTHAN(to_date('2009-01-01','yyyy-mm-dd')),PARTITIONP6VALUESLESSTHAN(maxvalue))asselect*fromorders;3.查询每个分区4.插入’2013/01/01’数据INSERTINTOrangeordersVALUES(2557,TO_TIMESTAMP('01-Jan-1310.22.16.162632PM','DD-MON-RRHH.MI.SS.FFAM','NLS_DATE_LANGUAGE=American'),'direct',118,5,21586.2,159,NULL);5.删除第三个分区deletefromrangeorderspartition(p3)上机练习61.创建间隔分区表CREATETABLEintervalOrdersPARTITIONBYRANGE(order_date)INTERVAL(NUMTOYMINTERVAL(1,'year'))(PARTITIONP1VALUESLESSTHAN(to_date('2005-01-01','yyyy/mm/dd')))asselect*fromorders2.获得分区情况SELECTtable_name,partition_nameFROMuser_tab_partitionsWHEREtable_name=UPPER('intervalorders');3.查询每个分区的数据4.插入数据并查看INSERTINTOintervalordersVALUES(2557,TO_TIMESTAMP('01-Jan-0310.22.16.162632PM','DD-MON-RRHH.MI.SS.FFAM','NLS_DATE_LANGUAGE=American'),'direct',118,5,21586.2,159,NULL);5.删除任意一个分区的数据deletefromintervalorderspartition(sys_p24)