oracle知识点总结.docx
《oracle知识点总结.docx》由会员分享，可在线阅读，更多相关《oracle知识点总结.docx（76页珍藏版）》请在冰豆网上搜索。
oracle知识点总结
逻辑数据库结构：
数据块（datablock）:
Oracle的数据块是数据库存储层次的基础，也是oracle中所有数据库存储的基础，一个数据块由操作系统的存储系统中磁盘空间上的若干字节组成。
区段（extent）:
一个区段是两个或多个相邻的数据块，它是空间分配的单元。
段（segment）：
一个段是分配给一个逻辑结构（如一个表或索引或其它对象）的一组区段。
表空间（tablespace）:
表空间是一组数据文件（一个或多个文件），通常由相关的段组成。
数据文件包含表空间中的所有逻辑结构的数据，如表和索引的数据。
所以数据库必备的5个表空间：
1.system2.sysaux3.撤销（undo）4.临时（temporary）5.默认参数
数据库创建阶段，必须拥有SYSTEM,SYSAUX表空间。
system表空间：
包含ORACLE的数据字典。
sysaux表空间:
是system表空间的补充，包含各种ORACLE产品和功能部件说使用的数据。
不能删除和重命名上述两个表空间。
撤销表空间:
包含撤销记录，即ｏｒａｃｌｅ用来回退或撤销对数据库的更改。
临时表空间:
只包含用户会话期间的数据，通常使用这些表空间完成用户的数据排序和类似的活动。
物理数据库结构：
数据文件（datafile）:
存储表和索引的数据。
构成了数据库最大的物理存储部分。
控制文件（controlfile）:
记录所有数据库结构的变化内容。
重做日志文件（redologfile）:
包含对表数据的修改内容。
查看所有的数据库实例：
select*fromv$instance；
创建用户：
CREATEUSER"FOXKUN"PROFILE"DEFAULT"IDENTIFIEDBY"*******"DEFAULTTABLESPACE"EXAMPLE"TEMPORARYTABLESPACE"TEMP"ACCOUNTUNLOCK
GRANT"CONNECT"TO"FOXKUN"
GRANT"DBA"TO"FOXKUN"
创建DB时，如未显示指定SYS,SYSTEM设定密码，默认值为change_on_install和manager。
创建用户：
createuseruser_nameidentified
{bypassword|externally|globallyas'exctnm'}
profileprofile_name;
修改密码
管理员用：
1.alteruseruser_nameidentifiedbyXXX;
2.passworduser_name
自己用：
password
分配登录的系统权限：
grantcreatesessiontouser_name;
分配表空间限额：
1.alteruseru5quota50monUSERS_3;
如果想让用户在所有表空间上具有无限的空间使用权力，则需要授予UNLIMITED
TABLESPACE权限。
createuserfoxkunidentifiedbyfoxkun
profileLIMITED_PROFILE;
grantcreatesessiontofoxkun;
默认概要文件
selectprofilefromdba_usersuwhereu.username='foxkun'
创建概要文件
createprofilehaha
limit
connect_time120
failed_login_attempts3
idle_time60
sessions_per_user2
概要文件属性：
●资源参数
CPU_PER_CALL：
限制事务内每个调用使用CPU时间
CPU_PER_SESSION:
限制会话中使用的总CPU时间
SESSIONS_PER_USER：
指定用户可打开的并发会话的最大数。
IDLE_TIME：
限制一个会话空闲的时间量。
CONNECT_TIME:
指定一个会话能保持连接到数据库的总时间（秒）
LOGICAL_READS_PER_SESSION：
限制数据块读取（从SGA内存区和磁盘读取）的总时间
LOGICAL_READS_PER_CALL限制每个会话调用（分析，执行，取数据）的总的逻辑读取时间。
●密码参数
FAILED_LOGIN_ATTEMPTS指定一个用户在被锁定之前可尝试的登录次数
PASSWORD_LIFE_TIME设置使用一个特定密码的时间限制。
如果在这个指定时间内不更改密码，则密码过期。
PASSWORD_GRACE_TIME设置一个时间段，在此时间段内将发生密码已经过期的警告，该时间段用尽后，用该密码不能连接数据库。
PASSWORD_LOCK_TIME达到不能成功登录的最大次数后，用户被锁定的天数
PASSWORD_REUSE_TIME指定可重新使用相同密码前要经过的天数。
PASSWORD_REUSE_MAX确定在可以重新使用某个特定密码前可更改该密码的次数。
selectdistinctprofiles.resource_name,profiles.limitfromdba_profilesprofileswhereprofiles.profile='DEFAULT';
为用户指定概要文件
alteruserfoxkunprofilehaha
更改概要文件
1.确保将初始化参数RESOURCE_LIMIT设置为TRUE
altersystemsetresource_limit=true;
alterprofilehaha
limit
....
删除概要文件
dropprofilehahacascade
解锁（DBA账户）：
alteruseruser_nameaccountunlock;
手工锁定（DBA账户）：
alteruseruser_nameaccountlock;
怎样查看哪些用户拥有SYSDBA、SYSOPER权限:
SQL>select*fromV_$PWFILE_USERS;
设置密码过期：
alteruseruser_namepasswordexpire;
查询账户信息可查询DBA_USERS数据字典视图。
表空间
创建表空间：
createtablespaceXXX
datafile'/../../xxx.pdf'
size100M;
非DBA用户必须具有CREATETABLESPACE系统权限。
表空间相关信息：
（区段管理，区段分配类型和段空间管理）
selectextent_management,
allocation_type,
segment_space_management
fromdba_tablespaces;
删除表空间：
droptablespaceXXX;
如果XXX表空间中包含表或索引，将会出错，可以先将对象移植到另一个表空间中
，或删除表空间和包含在表空间中的所有对象：
droptablespaceXXXincludingcontents;
删除表空间和包含在表空间中的所有对象+属于表空间的数据文件
droptablespaceXXXincludingcontentsanddatafiles;
如果其他表中存在指向要删除的表空间中表的引用完整性约束，使用以下命令：
droptablespaceXXXcascadeconstraints;
增加表空间的大小：
当表空间逐渐被表和索引数据填满时，必须扩充表空间的尺度。
1.增加更多的物理文件空间：
altertablespaceXXX
adddatafile'/xx/xx.dbf'
size100M
2.调整数据文件尺寸：
alterdatabasedatafile'/oracle/oradata/tran/USERS_3_02.DBF'
resize100M;
3创建一个表空间或在给一个表空间增加数据文件时，可使用autoextend或maxsize参数。
createtablespaceuser_4
datafile'/oracle/oradata/tran/USERS_4_01.DBF'
size50M
autoextendon
next10M
maxsize200M;
重命名表空间：
altertablespaceuser_4renametouser_4Beta;
system,sysaux表空间不能被重新命名。
表空间脱机：
altertablespaceuser_4Betaofflinenormal;
表空间脱机后导致数据文件也脱机。
表空间联机：
altertablespaceuser_4Betaonline;
重命名数据文件：
1.数据文件脱机。
2.altertablespaceuser_4Beta
renamedatafile
'/oracle/oradata/tran/USERS_4_01.DBF'
to
'/oracle2/oradata/tran/USERS_4_01.DBF';
只读表空间：
altertablespaceuser_4Betareadonly;
读写表空间：
altertablespaceuser_4Betareadwrite;
查找表空间
selecta.TS#,a.NAME,b.NAMEfromv$tablespacea,v$datafileb
wherea.TS#=b.TS#;
查找数据库当前表空间：
select*fromdatabase_propertiespwherep.property_name=
'DEFAULT_PERMANENT_TABLESPACE';
临时表空间：
作为用户排序操作和索引创建过程中的排序这些任务的工作区，不能再临时表空间中
创建要永久使用的对象。
临时表空间只保存用户会话期间的数据，且该数据可由所有用户共享。
createtemporarytablespacetemp_1
tempfile'/oracle/oradata/tran/TEMP_1.DBF'
size50M
autoextendon
next10M;
由于ORACLE以64KM的块尺寸向程序全局区段（PGA）中写数据，因此，建议创建有
64KB倍数的区段尺寸的临时表空间。
对于需要大量使用临时空间的大型数据仓库和
决策支持系统数据库，建议的区段尺寸为1MB。
ORACLE建议使用1MB统一区段尺寸的
本地管理的临时表空间作为默认临时表空间。
默认临时表空间：
altertablespacedefaulttemporarytablespacetemptbs02;
不能对临时表空间使用AUTOALLOCATE（分配类型）子句，默认时，所有临时表空间
都是用统一尺寸的本地管理的区段来创建，至于其他表空间，默认的区段尺寸为1MB，
如果在创建临时表空间是需要，可以使用不同的区段尺寸。
临时表空间组：
大的事务有时会使临时表空间出超，大的排序作业，尤其是那些带有许多分区的表，会导致临时表空间的大量使用，导致潜在的性能损坏。
临时表空间组允许用户在不同的会话中同时利用多个临时表空间。
临时表空间组特征：
1.一个临时表空间组必须由至少一个表空间组成，并且无明确的最大数量限制。
2.如果删除一个临时表空间组中的所有成员，该组也自动被删除。
3.一个临时表空间组具有相同的有名空间作为临时表空间，这些临时空间是组的一部分。
4.临时表空间的名字不能与表空间组的名字相同。
5.在给用户分配一个临时表空间时，可以使用临时表空间组的名字代替实际的临时表空间名，在给数据库分配默认临时表空间时还可以使用临时表空间组的名字。
创建语句：
1.createtemporarytablespacetemp_2
tempfile'/oracle/oradata/tran/TEMP_2.DBF'
size50M
tablespacegroupempgrp1;
2.altertablespacetemp_2
tablespacegroupempgrp2;
创建和更改用户时使用临时表空间组：
CREATEUSER"FOXKUN"
PROFILE"DEFAULT"
IDENTIFIEDBY"*******"
DEFAULTTABLESPACE"EXAMPLE"
TEMPORARYTABLESPACE"empgrp2"
一旦创建了用户，还可以使用如下语句更改用户的临时表空间组：
alteruserfoxkuntemporarytablespacetempgrp1;
修改用户表空间
alteruserrosedefaulttablespacetsrose
赋予表空间访问权限
alteruserrosequota46m[unlimited]ontsrose
orgrant
浏览临时表空间组的信息：
可以使用dba_tablespace_groups数据字典视图管理临时表空间组。
可以使用dba_users视图查找分配给每个用户的临时表空间或临时表空间组。
用户有权访问的表空间相关信息：
user_tablespaces
空间限额：
user_ts_quotas
管理表空间的数据字典视图：
1.DBA_DATA_FILES
包含了确定数据文件尺寸的有用信息，从该视图中可以得到数据文件名，文件所属的表空间，以字节为单位的文件尺寸以及数据文件的状态。
2.DBA_TABLESPACE
可以查找有关表空间的各种信息，如是否联机，是否为撤销，永久或临时表空间，区段管理类型，分配类型和段空间管理类型是什么，以及是否由一组小文件或一个大文件组成。
3.DBA_FREE_SPACE
该视图所关心的主要列是每个表空间中的表空间名和可用空间的字节数，例如，可以使用以下查询查看每个表空间中有多少可用空间：
selecttablespace_name,sum（bytes）
fromdba_free_space
groupbytablespace_name;
4.DBA_SEGMENTS
显示段的名称和类型以及该段所属的表空间。
5.DBA_TS_QUOTAS
分配给一个用户的各表空间限额。
完整例子：
--创建用户
createuserroseidentifiedbyrose
--赋予权限
GRANTRESOURCE,CREATESESSIONTOrose
--创建表空间
createtablespacetsrose
datafile'D:
\oracle\product\10.2.0\oradata\orcl\tsrose.DBF'
size50M
autoextendon
next10M
maxsize200M;
--赋予表空间
alteruserrosedefaulttablespacetsrose
--授权表空间
alteruserrosequotaunlimitedontsrose
数据字典的完整清单：
select*fromdict;
表
组织表的四种基本方式：
●堆组织表：
普通的oracle表，数据的存储没有特定的顺序。
●索引组织表：
存储B-树索引结构中排序的数据。
●集群表：
集群表是共享相同数据块的一组表，因为集群表的列经常是一起被请求的。
分区表：
分区表允许你将大量的数据根据不同的标准划分成称为分区的子表，分区在数据仓库环境中特别有用。
创建索引编排表：
索引编排表（index-organiedtable）能根据表的主键列值对数据进行排序。
索引编排表存储数据时就像整个表存储在一个索引中那样。
前提：
1.主键由表中大部分列构成时。
2.主键常作为WHERE子句中的查询条件。
语法：
可使用createtable命令的organizationindex子句,（该表必须包含主键约束）
CREATETABLETROUBLE（
CityVARCHAR2（13）,
SampleDateDATE,
NoonNUMBER（4,1）,
ConstraintTROUBLE_PKPRIMARYKEY（City,SampleDate）
）organizationindex;
如果经常通过City，SampleDate列访问该表数据（在查询的WHERE子句中），创建一个索引编排表是很合适的。
建表注意事项：
表名和列名：
字母开头后，后可包含字母，数字，下划线。
长度1～30个字符，不能用
Oracle保留字。
字符宽度：
char最大为2000个字符，varchar2最大为4000个字符。
添加表列：
altertabletable_nameadd（col_nametype）;
删除表列：
altertabletable_namedrop（col_name）;
隐藏表列：
altertabletable_namesetunused（col1,col2..）;
重命名表列：
altertabletable_name
renamecolumnorginal_nametonew_name;
重命名表：
altertableorginal_namerenametonew_name;
删表：
10g中，删除表时仍保留了表的空间，它们只是被暂时存放在“回收站”中，清除回收中
表使用purge子句
DroptableXXXpurge;
如表已经被删除，可从“回收站”中清除所占空间。
PurgetableXXX
使用以下命令可清除“回收站”中内容：
Purgerecyclebin;
视图
视图是一个虚表-它是一个或一组表的特殊表现，使用SELECT语句进行定义。
视图并不像普通表那样需要实际的表空间。
使用理由：
●增加方便性；
●改善安全性；
●保护数据的完整性；
创建视图必须有CREATEVIEW系统权限，要想在任何模式中创建视图，需要CREATEANYVIEW系统权限。
改变其数据需要有SELECTANYTABLE,INSERTANYTABLE,UPDATEANYTABLE或DELETEANYTABLE系统权限。
例：
createorreplaceviewemp_viewas
selecte.empid,
e.firtname||e.lastnameasempname,
e2.firtname||e2.lastnameasmanagername,
m.codenameasdempartment
fromemployeee
leftjoinemployeee2one.managerid=e2.empid
leftjoinempmatermone.depart=m.code2;
可以在视图上执行ＤＭＬ操作，但该操作不能有任何的GROUPBY,STARTWITHorCONNECT
BY子句。
删除视图
dropviewemp_view;
同义词
创建公共同义词
createpublicsynonymtest4_owner_foxkunforfoxkun.test4_owner_foxkun;
创建私有同义词
createsynonymtest4_owner_foxkunforfoxkun.test4_owner_foxkun;
删除同义词
droppublicsynonymtest4_owner_foxkun（如为私有的，去掉PUBLIC）
同义词信息
select*fromdba_synonyms
切换到另一个模式
select*fromscott.emp；
等价于：
altersessionsetcurrent_schema=scott;
select*fromemp；
权限
基本权限：
系统权限（systemprivileg）和对象权限（objectprivileg）
●系统权限
可在GRANT语句中给出ALLPRIVILEGES将所有系统权限授予某个用户（除selectany
Dictionary权限外），ALLPRIVILEGES本身不是一个系统权限。
作为一个DBA，可将某个系统权限授予PUBLIC,在这种情况下，数据库中所有用户都可以执行该权限授权的活动。
grantcreatesessiontopublic
ACCESS_ANY_WORKSPACE
ADMINISTERANYSQLTUNINGSET
ADMINISTERDATABASETRIGGER
ADMINISTERRESOURCEMANAGER
ADMINISTERSQLTUNINGSET
ADVISOR
ALTERANYCLUSTER
ALTERANYDIMENSION
ALTERANYEVALUATIONCONTEXT
ALTERANYINDEX
ALTERANYINDEXTYPE
ALTERANYLIBRARY
ALTERANYMATERIALIZEDVIEW
ALTERANYOUTLINE
ALTERANYPROCEDURE
ALTERANYROLE
ALTERANYRULE
ALTERANYRULESET
ALTERANYSEQUENCE
ALTERANYSQLPROFILE
ALTERANYTABLE
ALTERANYTRIGGER
ALTERANYTYPE
ALTERDATABASE
ALTERPROFILE
系统权限的传递
1.授予你该系统权限时带有ADMINOPTION子句。
grantcreatesessiontofoxkunwithadminoption;
2.被授予了GRANTANYPRIVIL
升级会员 