oracle知识点总结.docx

1、oracle知识点总结逻辑数据库结构： 数据块（data block）: Oracle的数据块是数据库存储层次的基础，也是oracle中所有数据库存 储的基础，一个数据块由操作系统的存储系统中磁盘空间上的若干字节组成。 区段（extent）: 一个区段是两个或多个相邻的数据块，它是空间分配的单元。 段（segment）： 一个段是分配给一个逻辑结构(如一个表或索引或其它对象)的一组区段。 表空间（tablespace）: 表空间是一组数据文件（一个或多个文件），通常由相关的段组成。数据文件包含表空间中的所有逻辑结构的数据，如表和索引的数据。 所以数据库必备的5个表空间： 1.system 2.

2、sysaux 3.撤销(undo) 4.临时（temporary）5.默认参数 数据库创建阶段，必须拥有SYSTEM,SYSAUX表空间。 system 表空间： 包含ORACLE的数据字典。 sysaux 表空间: 是system 表空间的补充，包含各种ORACLE产品和功能部件说使用的数据。 不能删除和重命名上述两个表空间。 撤销表空间: 包含撤销记录，即用来回退或撤销对数据库的更改。 临时表空间: 只包含用户会话期间的数据，通常使用这些表空间完成用户的数据排序和类似的活动。物理数据库结构： 数据文件（data file）: 存储表和索引的数据。构成了数据库最大的物理存储部分。 控制文件(

3、control file) : 记录所有数据库结构的变化内容。 重做日志文件(redo log file): 包含对表数据的修改内容。 查看所有的数据库实例：select * from v$instance；创建用户：CREATE USER FOXKUN PROFILE DEFAULT IDENTIFIED BY * DEFAULT TABLESPACE EXAMPLE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCKGRANT CONNECT TO FOXKUNGRANT DBA TO FOXKUN创建DB时，如未显示指定SYS,SYSTEM设定密码， 默认值

4、为change_on_install和manager。创建用户：create user user_name identified by password | externally | globally as exctnmprofile profile_name;修改密码管理员用：1. alter user user_name identified by XXX;2. password user_name自己用： password分配登录的系统权限：grant create session to user_name;分配表空间限额：1. alter user u5 quota 50m on US

5、ERS_3;如果想让用户在所有表空间上具有无限的空间使用权力，则需要授予UNLIMITED TABLESPACE权限。create user foxkun identified by foxkunprofile LIMITED_PROFILE;grant create session to foxkun;默认概要文件select profile from dba_users u where u.username = foxkun 创建概要文件create profile haha limit connect_time 120 failed_login_attempts 3 idle_time

6、60 sessions_per_user 2概要文件属性： 资源参数CPU_PER_CALL ：限制事务内每个调用使用CPU时间CPU_ PER_SESSION: 限制会话中使用的总CPU时间SESSIONS_PER_USER：指定用户可打开的并发会话的最大数。IDLE_TIME：限制一个会话空闲的时间量。CONNECT_TIME : 指定一个会话能保持连接到数据库的总时间（秒）LOGICAL_READS_PER_SESSION： 限制数据块读取（从SGA内存区和磁盘读取）的总时间LOGICAL_READS_PER_CALL 限制每个会话调用（分析，执行，取数据）的总的逻辑读取时间。 密码参数

7、FAILED_LOGIN_ATTEMPTS 指定一个用户在被锁定之前可尝试的登录次数PASSWORD_LIFE_TIME 设置使用一个特定密码的时间限制。如果在这个指定时间内不更改密码，则密码过期。PASSWORD_GRACE_TIME 设置一个时间段，在此时间段内将发生密码已经过期的警告，该时间段用尽后，用该密码不能连接数据库。PASSWORD_LOCK_TIME 达到不能成功登录的最大次数后，用户被锁定的天数PASSWORD_REUSE_TIME 指定可重新使用相同密码前要经过的天数。PASSWORD_REUSE_MAX 确定在可以重新使用某个特定密码前可更改该密码的次数。select d

8、istinct profiles.resource_name, profiles.limit from dba_profiles profiles where profiles.profile = DEFAULT;为用户指定概要文件alter user foxkun profile haha更改概要文件1. 确保将初始化参数RESOURCE_LIMIT设置为TRUEalter system set resource_limit = true;alter profile haha limit .删除概要文件drop profile haha cascade解锁（DBA账户）：alter user

9、 user_name account unlock;手工锁定（DBA账户）：alter user user_name account lock;怎样查看哪些用户拥有SYSDBA、SYSOPER权限:SQLselect * from V_$PWFILE_USERS;设置密码过期：alter user user_name password expire;查询账户信息可查询DBA_USERS数据字典视图。 表空间 创建表空间：create tablespace XXXdatafile /././xxx.pdfsize 100M;非DBA用户必须具有CREATE TABLESPACE系统权限。表空间相

10、关信息：（区段管理，区段分配类型和段空间管理） select extent_management, allocation_type, segment_space_management from dba_tablespaces;删除表空间： drop tablespace XXX; 如果XXX表空间中包含表或索引，将会出错，可以先将对象移植到另一个表空间中，或删除表空间和包含在表空间中的所有对象：drop tablespace XXX including contents;删除表空间和包含在表空间中的所有对象+属于表空间的数据文件drop tablespace XXX including con

11、tents and datafiles;如果其他表中存在指向要删除的表空间中表的引用完整性约束，使用以下命令：drop tablespace XXX cascade constraints;增加表空间的大小： 当表空间逐渐被表和索引数据填满时，必须扩充表空间的尺度。1. 增加更多的物理文件空间：alter tablespace XXXadd datafile /xx/xx.dbfsize 100M2. 调整数据文件尺寸：alter database datafile /oracle/oradata/tran/USERS_3_02.DBFresize 100M;3创建一个表空间或在给一个表空间增

12、加数据文件时，可使用autoextend或maxsize参数。create tablespace user_4 datafile /oracle/oradata/tran/USERS_4_01.DBFsize 50Mautoextend onnext 10Mmaxsize 200M;重命名表空间：alter tablespace user_4 rename to user_4Beta;system,sysaux表空间不能被重新命名。表空间脱机： alter tablespace user_4Beta offline normal; 表空间脱机后导致数据文件也脱机。表空间联机： alter ta

13、blespace user_4Beta online;重命名数据文件：1. 数据文件脱机。2. alter tablespace user_4Beta rename datafile /oracle/oradata/tran/USERS_4_01.DBF to /oracle2/oradata/tran/USERS_4_01.DBF;只读表空间： alter tablespace user_4Beta read only;读写表空间： alter tablespace user_4Beta read write; 查找表空间select a.TS# ,a.NAME , b.NAME from

14、v$tablespace a,v$datafile b where a.TS# = b.TS#;查找数据库当前表空间： select * from database_properties p where p.property_name = DEFAULT_PERMANENT_TABLESPACE;临时表空间： 作为用户排序操作和索引创建过程中的排序这些任务的工作区，不能再临时表空间中创建要永久使用的对象。临时表空间只保存用户会话期间的数据，且该数据可由所有用户共享。create temporary tablespace temp_1 tempfile /oracle/oradata/tran/

15、TEMP_1.DBF size 50M autoextend on next 10M; 由于ORACLE以64KM的块尺寸向程序全局区段（PGA）中写数据，因此，建议创建有 64KB倍数的区段尺寸的临时表空间。对于需要大量使用临时空间的大型数据仓库和决策支持系统数据库，建议的区段尺寸为1MB。ORACLE建议使用1MB统一区段尺寸的本地管理的临时表空间作为默认临时表空间。默认临时表空间：alter tablespace default temporary tablespace temptbs02;不能对临时表空间使用AUTOALLOCATE（分配类型）子句，默认时，所有临时表空间都是用统一尺寸

16、的本地管理的区段来创建，至于其他表空间，默认的区段尺寸为1MB，如果在创建临时表空间是需要，可以使用不同的区段尺寸。临时表空间组：大的事务有时会使临时表空间出超，大的排序作业，尤其是那些带有许多分区的表，会导致临时表空间的大量使用，导致潜在的性能损坏。临时表空间组允许用户在不同的会话中同时利用多个临时表空间。 临时表空间组特征：1. 一个临时表空间组必须由至少一个表空间组成，并且无明确的最大数量限制。2. 如果删除一个临时表空间组中的所有成员，该组也自动被删除。3. 一个临时表空间组具有相同的有名空间作为临时表空间，这些临时空间是组的一部分。4. 临时表空间的名字不能与表空间组的名字相同。5.

17、 在给用户分配一个临时表空间时，可以使用临时表空间组的名字代替实际的临时表空间名，在给数据库分配默认临时表空间时还可以使用临时表空间组的名字。创建语句：1. create temporary tablespace temp_2 tempfile /oracle/oradata/tran/TEMP_2.DBF size 50M tablespace group empgrp1;2. alter tablespace temp_2 tablespace group empgrp2;创建和更改用户时使用临时表空间组： CREATE USER FOXKUN PROFILE DEFAULT IDENTI

18、FIED BY * DEFAULT TABLESPACE EXAMPLE TEMPORARY TABLESPACE empgrp2一旦创建了用户，还可以使用如下语句更改用户的临时表空间组：alter user foxkun temporary tablespace tempgrp1;修改用户表空间alter user rose default tablespace tsrose 赋予表空间访问权限 alter user rose quota 46munlimited on tsrose or grant 浏览临时表空间组的信息： 可以使用dba_tablespace_groups数据字典视图管

19、理临时表空间组。 可以使用dba_users视图查找分配给每个用户的临时表空间或临时表空间组。用户有权访问的表空间相关信息：user_tablespaces空间限额： user_ts_quotas管理表空间的数据字典视图：1. DBA_DATA_FILES包含了确定数据文件尺寸的有用信息，从该视图中可以得到数据文件名， 文件所属的表空间，以字节为单位的文件尺寸以及数据文件的状态。2. DBA_TABLESPACE可以查找有关表空间的各种信息，如是否联机，是否为撤销，永久或临时表空间， 区段管理类型，分配类型和段空间管理类型是什么，以及是否由一组小文件或一个大文件组成。3. DBA_FREE_S

20、PACE该视图所关心的主要列是每个表空间中的表空间名和可用空间的字节数，例如，可以使用以下查询查看每个表空间中有多少可用空间：select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;4. DBA_SEGMENTS显示段的名称和类型以及该段所属的表空间。5. DBA_TS_QUOTAS分配给一个用户的各表空间限额。完整例子：-创建用户create user rose identified by rose -赋予权限GRANT RESOURCE,CREATE SESSION TO rose-创建

21、表空间create tablespace tsrosedatafile D:oracleproduct10.2.0oradataorcltsrose.DBFsize 50Mautoextend onnext 10Mmaxsize 200M;-赋予表空间alter user rose default tablespace tsrose-授权表空间alter user rose quota unlimited on tsrose数据字典的完整清单： select * from dict; 表组织表的四种基本方式： 堆组织表： 普通的oracle表，数据的存储没有特定的顺序。 索引组织表：存储B-树

22、索引结构中排序的数据。 集群表：集群表是共享相同数据块的一组表，因为集群表的列经常是一起被请求的。分区表：分区表允许你将大量的数据根据不同的标准划分成称为分区的子表，分区在数据仓库环境中特别有用。创建索引编排表： 索引编排表（index-organied table）能根据表的主键列值对数据进行排序。索引编排表存储数据时就像整个表存储在一个索引中那样。 前提：1. 主键由表中大部分列构成时。2. 主键常作为WHERE子句中的查询条件。 语法：可使用create table命令的organization index子句,(该表必须包含主键约束) CREATE TABLE TROUBLE( Cit

23、y VARCHAR2(13), SampleDate DATE, Noon NUMBER(4,1), Constraint TROUBLE_PK PRIMARY KEY(City, SampleDate) )organization index;如果经常通过City，SampleDate列访问该表数据（在查询的WHERE子句中），创建一个索引编排表是很合适的。建表注意事项：表名和列名： 字母开头后，后可包含字母，数字，下划线。 长度130个字符，不能用Oracle保留字。 字符宽度 ： char最大为2000个字符， varchar2最大为4000个字符。添加表列： alter table t

24、able_name add(col_name type);删除表列： alter table table_name drop(col_name);隐藏表列： alter table table_name set unused(col1,col2.);重命名表列： alter table table_name rename column orginal_name to new_name;重命名表： alter table orginal_name rename to new_name;删表： 10g中，删除表时仍保留了表的空间，它们只是被暂时存放在“回收站”中，清除回收中表使用purge子句Dr

25、op table XXX purge;如表已经被删除，可从“回收站”中清除所占空间。Purge table XXX使用以下命令可清除“回收站”中内容：Purge recyclebin; 视图视图是一个虚表- 它是一个或一组表的特殊表现，使用SELECT语句进行定义。视图并不像普通表那样需要实际的表空间。使用理由： 增加方便性； 改善安全性； 保护数据的完整性；创建视图必须有CREATE VIEW系统权限，要想在任何模式中创建视图，需要CREATE ANY VIEW系统权限。改变其数据需要有SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE或DE

26、LETE ANY TABLE系统权限。例：create or replace view emp_view as select e.empid, e.firtname | e.lastname as empname, e2.firtname | e2.lastname as managername, m.codename as dempartment from employee e left join employee e2 on e.managerid = e2.empid left join empmater m on e.depart = m.code2;可以在视图上执行操作，但该操作不能

27、有任何的GROUP BY, START WITH or CONNECT BY子句。删除视图drop view emp_view; 同义词创建公共同义词 create public synonym test4_owner_foxkun for foxkun.test4_owner_foxkun;创建私有同义词 create synonym test4_owner_foxkun for foxkun.test4_owner_foxkun;删除同义词 drop public synonym test4_owner_foxkun （如为私有的，去掉PUBLIC）同义词信息 select * from

28、dba_synonyms切换到另一个模式 select * from scott.emp； 等价于： alter session set current_schema = scott; select * from emp；权限基本权限： 系统权限（system privileg）和对象权限（object privileg） 系统权限 可在GRANT语句中给出ALL PRIVILEGES将所有系统权限授予某个用户（除select any Dictionary权限外），ALL PRIVILEGES本身不是一个系统权限。作为一个DBA，可将某个系统权限授予PUBLIC,在这种情况下，数据库中所有用户

29、都可以执行该权限授权的活动。grant create session to publicACCESS_ANY_WORKSPACEADMINISTER ANY SQL TUNING SETADMINISTER DATABASE TRIGGERADMINISTER RESOURCE MANAGERADMINISTER SQL TUNING SETADVISORALTER ANY CLUSTERALTER ANY DIMENSIONALTER ANY EVALUATION CONTEXTALTER ANY INDEXALTER ANY INDEXTYPEALTER ANY LIBRARYALTER

30、ANY MATERIALIZED VIEWALTER ANY OUTLINEALTER ANY PROCEDUREALTER ANY ROLEALTER ANY RULEALTER ANY RULE SETALTER ANY SEQUENCEALTER ANY SQL PROFILEALTER ANY TABLEALTER ANY TRIGGERALTER ANY TYPEALTER DATABASEALTER PROFILE系统权限的传递1. 授予你该系统权限时带有ADMIN OPTION子句。grant create session to foxkun with admin option;2. 被授予了GRANT ANY PRIVIL