ORACLE学习记录(重点).doc

学习oracle资料总结(刘德龙)1.表空间概念、数据文件概念、用户概要，表空间与数据文件之间的关系。用户默认表空间表示什么意思？答:用户概要: Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。语法结构：创建用户 CREATE USER 用户名 IDENTIFIED BY 口令 ACCOUNT LOCK|UNLOCK 语法解析： LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。代码演示:CREATE USER ZHANGHAO IDENTIFIED BY MIMA ACCOUNT UNLOCK;还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合:CONNECT,RESOURCE,DBA.语法结构：授权 GRANT角色|权限 TO 用户（角色） 代码演示：授权 SQL GRANT CONNECT TO jerry; 授权成功。SQL GRANT RESOURCE TO jerry; 授权成功。 语法结构：其他操作 /回收权限 REVOKE 角色|权限 FROM 用户（角色） /修改用户的密码 ALTER USER 用户名 IDENTIFIED BY 新密码 /修改用户处于锁定（非锁定）状态 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK 表空间（tablespace）:表空间是数据库中最大的逻辑单位，每一个表空间由一个或多个数据文件组成，一个数据文件只能与一个表空间相联系。每一个数据库都有一个SYSTEM表空 间，该表空间是在数据库创建或数据库安装时自动创建的，用于存储系统的数据字典表，程序系统单元，过程函数，包和触发器等，也可用于存储用户数据表，索引 对象。表空间具有在线（online）和离线（offline）属性，可以将除SYSTME以外的其他任何表空间置为离线。数据文件(dbf,ora):数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行.三者的关系:一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系，表空间是一个用来管理数据存储逻辑概念，表空间只是和数据文件发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。用户默认表空间是:oracle中表不可能单独存在，一定附属于某个用户，而某一个用户的数据就必须存在于某一个表空间。你创建用户时候也可以使用系统的默认表空间,也可以指定表空间.练习代码-创建用户并解锁create user lioudelong identified by 83859346 account unlock;-给用户授权grant connect to lioudelong;-连接权限:拥有connect角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。grant resource to lioudelong;-利用资源权限:以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。grant dba to lioudelong;-DBA-解除用户权限revoke connect from lioudelong;-更改用户密码alter user lioudelong identified by 666666;alter user lioudelong identified by 83859346;-更改用户锁alter user lioudelong account lock;-上锁alter user lioudelong account unlock;-解锁-查询所有用户信息select * from dba_users;-创建表空间create tablespace TEST.TABLESPACE -临时表空间是temporary tablespace还原表空间是 undo tablespace表空间tablespacelogging -要不要日志文件 nologgingloggingdatafile D:oracleTEST.TABLESPACE.dbf size 5m -数据文件存储位置和大小autoextend on next 2m -自动增长extent management local-存储区域本地管理UNIFORM SEGMENT SPACE MANAGEMENT-指定区大小，也可使用默认值 (1 MB)auto; -自动段空间管理-创建临时表空间CREATE TEMPORARY TABLESPACE TEST.TEMPTABLESPACE tempFILE D:ORACLETEST.TEMPTABLESPACE.dbf SIZE 5M AUTOEXTEND ON next 2M EXTENT MANAGEMENT LOCAL;-删除表空间drop tablespace TEST.TABLESPACE including contents and datafiles;drop tablespace TEST.TEMPTABLESPACE including contents and datafiles;-修改用户默认表空间和临时表空间alter user lioudelong default tablespace TEST.TABLESPACE;alter user lioudelong temporary tablespace TEST.TEMPTABLESPACE;-查询表空间select * from dba_data_files;2.同义词的基本概念？同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型： 私有同义词：拥有CREATE SYNONYM权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。 公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。练习代码grant create synonym to lioudelong;-创建同义词的权限create synonym MyPerson for system.Common.Person; -在lioudelong用户下，为system.Common.Person创建私有同义词MyPerson， -同义词MyPerson只能在lioudelong用户下使用。 -访问MyPerson就是访问system.Common.Person对象。select * from MyPerson;-现在反问不了,因为system用户还没把system.Common.Person 对象的权限没授予lioudelong-切换到SYSTEM用户grant all on Common.Person to lioudelong;-把Common.Person表对象的所有权限(增删改查)给lioudelong用户-切换回lioudelong用户select * from MyPerson;-这次可以反问啦!-删除同义词drop synonym MyPerson;-登陆有dba角色的用户创建公共同义词create public synonym MyPerson for system.Common.Person;-穿件公共同义词,所有用户都可以访问-用lioudelong用户反问公共同义词select * from MyPerson;-不用system授权也能访问-用dba用户删除公共同义词drop public synonym MyPerson;3.序列概念，作用是什么？序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：语法结构：创建序列 CREATE SEQUENCE sequence_name START WITH num INCREMENT BY increment MAXVALUE num|NOMAXVALUE MINVALUE num|NOMINVALUE CYCLE|NOCYCLE CACHE num|NOCACHE 语法解析： START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。 INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。 MAXVALUE：指最大值。 NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。 MINVALUE：指最小值。 NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。 CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。 NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。 CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。 NOCACHE：不预先在内存中生成序列号。 练习代码-创建序列create sequence MySeqminvalue 1- 最小值1start with 1-从1开始maxvalue 9999-最大值9999increment by 1-递增1cache 30-内存中缓存30个序列号cycle;-循环-创建触发器实现递增主键列 CREATE OR REPLACE TRIGGER MyPerson_PersonId_Tig BEFORE INSERT ON MyPerson FOR EACH ROW BEGIN SELECT MySeq.NEXTVAL INTO :NEW.PersonId FROM DUAL; END;-验证是否成功insert into MyPerson (PersonNo,Name,Partition) values(11,多啦1,0);select * from myperson; -删除序列drop sequence MyPerson;4.View 概念？视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECTFROM即可。 视图具有以下优点：1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。语法结构：创建视图 CREATE OR REPLACE FORCE|NOFORCE VIEW view_name AS SELECT查询 WITH READ ONLY CONSTRAINT 语法解析： 1. OR REPLACE：如果视图已经存在，则替换旧视图。 2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。 3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。 4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。 练习代码-创建视图create or replace view MyViewasselect PersonNo as 编号,Name as 姓名 from MyPersonwith read only;-只读-利用视图select * from myview;-删除视图drop view myview;5.索引知识：常用索引有哪几种？复合索引一般用在什么场景？索引分类:逻辑上：Single column 单列索引(单一索引)Concatenated 多列索引(组合索引,复合索引)Unique 唯一索引NonUnique 非唯一索引Function-based函数索引Domain 域索引物理上：Partitioned 分区索引NonPartitioned 非分区索引B-tree(Normal 正常型B树,Rever Key 反转型B树)Bitmap 位图索引常用的索引:单列索引,复合索引,唯一索引,非唯一索引.复合索引利用场合:当常用的查询条件为多列条件时使用;练习代码-单列索引创建create index index_person_id on myperson (PersonId);-符合索引create index index_person_name_sex on myperson (Name,Sex);6.怎么样才知道查询语句用到了索引？用PL/SQL Developer 按F5 执行计划分析你只要看出来的对话框中间节点的表述，是full 表示你走的是全表遍历;如果是hash或其他的 则表示走的是索引;看看cost也能知道时间消耗情况 .7.Inner join /left join /right join 三种表连接查询之间的区别。left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录;right join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录;inner join(等值连接):只返回两个表中联结字段相等的行.练习代码(有结果理解)-创建测试表1create table T1( Id number not null, Name varchar2(10) not null)tablespace TEST.TABLESPACE;create trigger T1_tigid-测试表1实现Id递增(练习标识列创建,跟本测试无关)before insert on T1for each rowbegin select MySeq.nestval into :new.Id from dual;end;drop trigger T1_tigid;-删除触发器,以免影响插入测试数据alter table T1 add primary key(Id);-添加Id为主键-创建测试表2create table T2( Id number not null, Name varchar2(10) not null)tablespace TEST.TABLESPACE;create trigger T2_tigid-测试表2实现Id递增(练习标识列创建,跟本测试无关)before insert on T2for each rowbegin select MySeq.nestval into :new.Id from dual;end;drop trigger T2_tigid;-删除触发器,以免影响插入测试数据alter table T2 add primary key(Id);-添加Id为主键-插入测试数据insert into T1 values(1,路飞);insert into T1 values(2,山治);insert into T1 values(3,索罗);insert into T1 values(4,娜美);insert into T2 values(1,孙悟空);insert into T2 values(2,猪八戒);insert into T2 values(3,沙和尚);insert into T2 values(5,唐三藏);-查询数据select * from T1;select * from T2;(T1数据) (T2数据)(内连接: 结果只会出现2者Id相同的数据)(左连接:T1的数据全部会显示出来,而T2只会显示与T1有相同Id的数据)(右连接:T2的数据全部显示,T1只会显示与T1有相同Id的数据)8.表分区概念，有哪几种常用的表分区，表分区有什么作用？表分区概念:当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。表分区作用:Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。表分区分类:（1）范围分区（range）:Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。（2）哈希分区（hash）:对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。（3）列表分区（list）；List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。 在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。（4）范围哈希复合分区（range-hash）；（5）范围列表复合分区（range-list）。练习代码-创建3个表空间来存分割的表create tablespace TS1loggingdatafile D:oracleTS1.dbf size 5mautoextend on next 1mextent management local;create tablespace TS2loggingdatafile D:oracleTS2.dbf size 5mautoextend on next 1mextent management local;create tablespace TS3loggingdatafile D:oracleTS3.dbf size 5mautoextend on next 1mextent management local;-range分区(范围分区)create table RangePartition( Id number primary key not null, Name varchar2(10)partition by range (Id)( partition p1 values less than (100) tablespace TS1 , partition p2 values less than (200) tablespace TS2 , partition p3 values less than (maxvalue) tablespace TS3);-hash分区create table HashPartition( Id number primary key not null, Name varchar2(10)partition by hash (Id)( partition p1 tablespace TS1 , partition p2 tablespace TS2 , partition p3 tablespace TS3);-List分区create table ListPartition( Id number primary key not null, Name varchar2(10)partition by list (Id)( partition p1 values(1,2) tablespace TS1 , partition p2 values(3,4) tablespace TS2 , partition p3 values(default) tablespace TS3);-range_hashcreate table Range_HashPartition( Id number primary key not null, Name varchar2(10)partition by range (Id) subpartition by hash(name)subpartition template( subpartition p1 tablespace TS1 , subpartition p2 tablespace TS2 , subpartition p3 tablespace TS3)( partition p1 values less than (100) tablespace TS1 , partition p2 values less than (200) tablespace TS2 , partition p3 values less than (maxvalue) tablespace TS3);9.数据库逻辑备份EXP 命令， 导入命令 IMP， 全备份和指定用户数据的备份还原。Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命 令可以把dmp文件从本地导入到远处的数据库服务器中。利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。数据导出：1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中expsystem/managerTESTfile=d:daochu.dmpfull=y2 将数据库中system用户与sys用户的表导出expsystem/managerTESTfile=d:daochu.dmpowner=(system,sys)3 将数据库中的表inner_notify、notify_staff_relat导出expaichannel/aichannelTESTDB2file=d:datanewsmgnt.dmptables=(inner_notify,notify_staff_relat)4 将数据库中的表table1中的字段filed1以00打头的数据导出expsystem/managerTESTfile=d:daochu.dmptables=(table1)query=wherefiled1like00%上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面 加上 compress=y 来实现。 数据的导入1 将D:daochu.dmp 中的数据导入 TEST数据库中。impsystem/managerTESTfile=d:daochu.dmp impaichannel/aichannelHUSTfull=yfile=d:datanewsmgnt.dmpignore=y上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在后面加上 ignore=y 就可以了。2 将d:daochu.dmp中的表table1 导入impsystem/managerTESTfile=d:daochu.dmptables=(table1)10.包头、包体基本概念？包的慨念:包是由存储在一起的相关对象组成的PL/SQL存储程序包有两个独立的部分，包头和包体，需要分别定义包头包含了包中过程、函数的声明包体是真正的过程、函数的执行部分定义包的作用 : 包可以将任何出现在块声明的语句 ( 过程 , 函数 , 游标 , 游标 , 类型 , 变量 ) 放于包中 , 相当于一个容器 . 将声明语句放入包中的好处是 : 用户可以从其他 PL/SQL 块中对其进行引用 , 因此包为 PL/SQL 提供了全程变量 .包的好处: 可以把相关的过程、函数组织在一起，便于管理 提高了代码性能 能够将私有的过程、函数隐藏起来，只在包体内部看得到，提高了安全性 包中可以调用的变量的值，在一个会话中能够一直保持，提供了会话全局变量的作用包分为两部分 : 包头和包体 .练习代码-创建包头create or replace package MyPackageis function Addnum(num1 in number,num2 in number) return number;end MyPackage;-创建包体create or replace package body MyPackageis function Addnum(num1 in number,num2 in number) return number as a number; begin return num1+num2 ; end Addnum;end MyPackage;-执行包select MyPackage.Addnum(2,3) from dual;11.Database links 概念及作用？物理上存放于网络的多个ORACLE数据库，逻辑上可以看成一个单一的大型数据库，用户可以通过网络对异地数据库中的数据进行存取，而服务器之间的协同处理对于工作站用户及应用程序而言是完全透明的，开发人员无需关心网络的链接细节、数据在网络节点中的具体分布情况和服务器间的协调工作过程。Link类型说明Connected user link看这个类型的名称就可以判断出，用的是当前连接的用户的帐户及其密码来访问远程数据库的。所以这个类型就要求需要在远程数据库上有一个和本地帐户一样的username和password.Fixed user link这个类型是说使用指定的帐户和密码来访问远程数据库。所以只需指定的帐户能够有权限访问远程数据库就可以。Current user link需要用户连接到本地数据库的时候是全局用户的身份连接的。这样这个全局用户就可以使用指定帐户的上下文，而无需存储密码信息到link中。上面是用来告诉Database Link，我们用用什么身份连接。 Database Link有以下类型：类型说明Private来类型的Database Lin只能由这个Database Link的Owner才能使用它。Public在这个数据库里的所有用户均可使用该Database LinkGlobal网络中的数据库中的用户均可使用该database link. 因为当Oracle 使用目录服务器的时候，它会在该目录下的所有数据库里都创建这个 global database link,并且统一控制。因此所有在目录下的数据库里的用户均可使用。好了，让我们看看应该如何创建一个Database Link: 语法：create type database link 语句说明访问方式类型连接类型Create database link dblinkName using NSName;创建一个database link ，通过使用网络服务名称创建。其中Database Link的名称为dbLinkName. 网络服务的名称为NSName.Connected user linkPrivateCreate public database link dblinkName connect to CURRENT_USER using NSName;创建一个database link ，通过使用网络服务名称创建。其中Database Link的名称为dbLinkName. 网络服务的名称为NSName.Current user linkpublicCreate database link dblinkName dblinkName connect to username identified by password using NSName;创建一个database link ，通过使用网络服务名称创建。其中Database Link的名称为dbLinkName. 网络服务的名称为NSName. 通过用户帐户username及其密码password来访问远程数据库。Fixed user linkprivate练习代码-已经配置本地服务create public database link trafficlinkconnect to traffic identified by citmsusing orcl244; -本地配置好了的服务select Name from Common.Persontrafficlink;-利用database link查询-未配置本地服务drop public database link LinkWYY;create public database link LinkWYYconnect to oracle identified by 123using (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 90)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) );select * from employeeLinkWYY;12.PGA、SGA 概念？内存分配（