ORACLE期末复习整理笔记详细版.docx
《ORACLE期末复习整理笔记详细版.docx》由会员分享，可在线阅读，更多相关《ORACLE期末复习整理笔记详细版.docx（50页珍藏版）》请在冰点文库上搜索。
ORACLE期末复习整理笔记详细版
Oracle性能优化
存储结构
SQL>selectTABLESPACE_NAME,EXTENT_MANAGEMENTfromdba_tablespaces;
TABLESPACE_NAMEEXTENT_MAN
----------------------------------------
SYSTEMLOCAL
UNDOTBS1LOCAL
SYSAUXLOCAL
TEMPLOCAL
USERSLOCAL
EXAMPLELOCAL
已选择6行。
本地管理的好处
减少了数据字典的冲突，因为区的分配不需要记录UET$,FET$
当执行事务时（如INSERT大量数据）一旦申请了区，即使执行回滚操作
也不需要把区释放回去，因为空间分配和释放没有UNDO生成
不需要SMON整理区碎片。
减少空间递归管理，这是本地管理最重要的出发点。
递归SQL就是我们执行一条SQL,会使oracle在后台执行多条SQL,这个牵扯的多条SQL就是递归SQL
创建表空间时区管理方式语法
本地管理
uniform就是区的大小都是一样的
CREATETABLESPACE"MYTBS2"DATAFILE
'/u01/oracle/oradata/ora10g/mytbs02.dbf'SIZE104857600
LOGGINGONLINEPERMANENTBLOCKSIZE8192
EXTENTMANAGEMENTLOCALuniformsize64KSEGMENTSPACEMANAGEMENTAUTO;
autoallocate是自动分配的
CREATETABLESPACE"MYTBS"DATAFILE
'D:
/test/mytbs01.dbf'SIZE104857600
LOGGINGONLINEPERMANENTBLOCKSIZE8192
EXTENTMANAGEMENTLOCALAUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO
SQL>droptablespacemytbsincludingcontentsanddatafiles;
SQL>createtablespacemytbsdatafile'/u01/oracle/oradata/ora10g/mytbs01.dbf'size100M;
createtablet2tablespacemytbs2asselect*fromscott.emp;
selectsegment_name,extent_id,file_id,block_id,blocksfromdba_extentswhereowner='SYS'andSEGMENT_NAME='T2';
空间回收（整理碎片）
10G以前回收的方法
1.在表空间中移动表
altertableTABLE_NAMEmovetablespaceTABLESPACE_NAME;
2.将数据导出删除表再将数据导入
10G中提供的新方法
altertableTABLE_NAMEshrinkspace[compact|cascate]
altertableTABLE_NAMEshrinkspace;整理碎片并回收空间
altertableTABLE_NAMEshrinkspacecompact;只整理碎片不回收空间
altertableTABLE_NAMEshrinkspacecascate;整理碎片回收空间
并连同表的级联对象一起整理（比如索引）
使用条件
自动段管理模式
打开行移动
使用步骤
1.altertablet1enableROWMOVEMENT;
2.shrink操作
3.altertablet1disableROWMOVEMENT;
ASSM是自动段管理AUTOSEGMENTSPACEMANAGEMENT
MSSM是手工段管理
FREELIST（空闲列表）管理
FREEextent可以分配给不同的段
extent的使用和空闲由数据文件位图或者数据字典UET$和FET$来管理
而在属于段内的块如何来管理呢？
在字典管理及本地管理非自动段管理时将由FREELIST和FREELIST组来管理
在本地管理表空间的自动段管理采用位图来管理
FREELIST管理
FREELIST作为一个Oracle存储管理的核心参数
其行为方式由Oracle内部控制
但对这种机制不了解时,会遇到很多问题
当插入一条记录，会插入到哪个块中?
是使用新块，还是插入有数据的老块?
段是什么时候扩展的，如何扩展的?
表中只有一条记录，但是做一次select时，代价为何却是上千个块?
带着这些问题来了解FREELIST
块的内部结构
header中包含:
块属性信息,事务信息,表目录,行目录
属性信息:
块的类型,块的格式,块地址,SCN,块的序列号,块的标志
事务信息:
事务相关的基本信息,和ITL（事务槽）
表目录:
当前表的信息
行目录:
记录块内存储行的地址信息
freespace:
空闲空间
dataspace:
使用空间
在生成段的时候，会同时分配初始区（initialextents），
初始区的第一个块就格式化为segmentheader，
并被用来记录freelist描述信息、extents信息，HWM信息等。
段头就是初始区的第一个块
freelist是一种单向链表用于定位可以接收数据的块，
在字典管理方式的表空间中或MSSM，Oracle使用freelist来管理未分配的存储块。
Oracle记录了有空闲空间的块用于insert或Update。
空闲空间来源于两种方式：
1.段中所有超过高水位（HWM）的块，这些块已经分配给段了，但是还未被使用。
2.段中所有在HWM下的且链入了freelist的块，可以被重用。
HWM：
HIGHWATERMARK代表一个表使用的最大的（toplimit）块。
简单点说HWM就是一口井曾经达到的最高水位,也就是历史最高水位点
比如一个杯子能装100升水装到80,倒出20.高水位就是80;
空闲的块怎么来的？
1.段中所有超过HWM的块，这些块已经分配给段了，但是还未被使用。
注意限定词：
这些块已经分配给段了
2.段中所有在HWM下的且链入了freelist的块，可以被重用。
FREELIST就是一个链入了空闲块的单向链表
高水位上的肯定是空闲的,想用高水位以上的空闲之前,必须将高水位延伸.
表空间信息
表空间数据字典视图:
DBA_TABLESPACES
表空间动态性能视图:
V$TABLESPACE
数据文件信息
数据字典视图:
DBA_DATA_FILES
动态性能视图:
V$DATAFILE
临时文件信息
数据字典视图:
DBA_TEMP_FILES
动态性能视图:
V$TEMPFILE
空间分配信息
被段分配的区:
DBA_EXTENTS
没有被段分配的区:
DBA_FREE_SPACE
系统默认
database_properties;
查询FREElist
dba_tables
自动段管理ASSM
引入了一种新的段空间管理方式，称为ASSM，自动段管理
自动段管理，采用位图方式管理段空间。
******回退表空间和数据文件的管理******
作用:
1.交易的回退:
没有提交的交易可以rollback
2.交易的恢复:
数据库崩溃时,将磁盘的不正确数据恢复到交易前
3.读一致性:
被查询的记录有事务占用,转向回滚段找改前镜像
4.闪回数据:
从回滚段中构造历史数据
回滚段创建的时候是空闲的，只有事务产生时候才会产生数据。
创建UNDO表空间：
SQL>createundotablespaceundo2datafile'/u01/oracle/oradata/ora10g/undo02.dbf'size10M;
管理风格：
自动或手动
SQL>showparameterundo
NAMETYPEVALUE
-----------------------------------------------------------------------------
undo_managementstringAUTO
undo_retentioninteger900
undo_tablespacestringUNDOTBS1
undo_management=AUTO回滚表空间段的段管理模式，
自动管理
管理员只需要备足够的表空间容量，oracle会自动管理扩展回滚段的数量。
只能使用一个UNDO表空间
undo_tablespace:
只有在自动管理模式下才可以使用。
指明使用哪个UNDO表空间
切换回滚表空间的时候不会影响回滚数据:
SQL>altersystemsetundo_tablespace=undo2;
Systemaltered.
手工管理UNDO
修改方法
SYS@beijing>showparameterrollback
NAMETYPEVALUE
-----------------------------------------------------------------------------
fast_start_parallel_rollbackstringLOW
rollback_segmentsstring
transactions_per_rollback_segmentinteger5
SYS@beijing>
默认一个回滚段可以维持5个事务不要放太多事务会产生回滚段头争用
因为段头中记录段中的块的位置找段里的数据就要访问段头
改成手动
SQL>altersystemsetundo_management=manualscope=spfile;
***************临时表空间*****************
存放排序的中间结果或临时表
selecttablespace_name,contentsfromdba_tablespaceswhereCONTENTS='TEMPORARY';
SQL>改成手动
SQL>altersystemsetundo_management=manualscope=spfile;
追加新的临时文件:
SQL>altertablespacetempaddtempfile'/u01/oracle/oradata/ora10g/temp02.dbf'size50M;
selecttablespace_name,file_namefromdba_temp_files;查询一下临时文件的存储位置。
10G的新语法临时文件的删除
SQL>altertablespacetempdroptempfile'/u01/oracle/oradata/ora10g/temp01.dbf';
删除临时表空间
SQL>droptablespacetemp2includingcontentsanddatafiles;
删除临时表空间的文件
SQL>altertablespacetemp2addtempfile'/u01/oracle/oradata/ora10g/temp02a.db'size10M;
SQL>altertablespacetemp2droptempfile'/u01/oracle/oradata/ora10g/temp02a.db';
扩容手段和永久的一致
SQL>alterdatabasetempfile'/u01/oracle/oradata/ora10g/temp03.dbf'resize10M;
清除临时表空间组
altertablespacetemp01tablespacegroup'';为空即可
******************永久表空间管理***********************
永久表空间管理
又分为:
小文件表空间（一个表空间可以存放1023个文件每个文件4M*block_size）
基本应用都是这类默认创建也是它
大文件表空间（一个表空间可以存放1个文件文件管理4G*block_size）
很少使用,不利于IO分散,容易产生争用,容易被FS限制
小文件表空间
查看
SQL>selecttablespace_name,file_id,file_name,ceil（bytes/1048567）||'M'MB
fromdba_data_files;
TABLESPACE_NAMEFILE_IDFILE_NAMEMB
------------------------------------------------------------------------------------------
USERS4/u01/oracle/oradata/ora10g/users01.dbf26M
SYSAUX3/u01/oracle/oradata/ora10g/sysaux01.dbf241M
UNDOTBS12/u01/oracle/oradata/ora10g/undotbs01.dbf26M
SYSTEM1/u01/oracle/oradata/ora10g/system01.dbf481M
创建大文件表空间
SYS@beijing>createbigfiletablespacebigtbsdatafile'D:
/test/bigtbs01.dbf'size5M;
selecttablespace_name,bigfilefromdba_tablespaces;
TABLESPACE_NAMEBIG
------------------
SYSTEMNO
UNDOTBS1NO
SYSAUXNO
TEMPNO
USERSNO
MYTBSNO
BIGTBSYES
修改表空间状态（onlinereadonlyoffline）
只读表空间只能select查询和drop删除对象不能修改（INTSERTupdatedeletetruncate）
systemundotbstemp包含活动事务的表空间都不能只读
selecttablespace_name,statusfromdba_tablespaces;
TABLESPACE_NAMESTATUS
-------------------------
SYSTEMONLINE
UNDOTBS1ONLINE
SYSAUXONLINE
TEMPONLINE
USERSONLINE
MYTBSONLINE
altertablespacemytbsreadonly;
恢复成online（读写）状态
SYS@beijing>altertablespacemytbsreadwrite;
altertablespacemytbsoffline;
数据文件的移动改名（两种命令方法）
1.使用altertablespaceTABLESPACE_NAMErenamedatafile'OLD_file'to'NEW_file';命令
前提条件
a.表空间要offline状态altertablespaceusersoffline
b.目标文件要存在就是先做mv操作
altertablespaceusersoffline;
selectnamefromv$datafile;
移动文件
altertablespaceusersrenamedatafile'/u01/oracle/oradata/ora10g/users01.dbf'to'/home/oracle/users01.dbf';
2.使用alterdatabaserenamefile'OLD_file'to'NEW_file';命令
前提条件
a.数据库mount或表空间离线shutimmediate==>startupmount
b.目标文件存在mvsrc_filedst_file
shutimmediate
startupmount
selectnamefromv$datafile;
移动文件
alterdatabaserenamefile'/home/oracle/users01.dbf'to'/u01/oracle/oradata/ora10g/users01.dbf';
alterdatabaseopen;
selectnamefromv$datafile;
练习
将表空间设置只读然后备份控制文件创建脚本
一致停库启动到nomount重新创建控制文件
启动数据库根据控制文件备份脚本中的提示将只读表空间恢复正常
（直到可以在这个表空间内创建表）
createtablespacetestdatafile'/u01/oracle/oradata/ora10g/mytest01.dbf'size10M;
altertablespacetestadddatafile'/u01/oracle/oradata/ora10g/mytest02.dbf'size10M;
alterdatabasebackupcontrolfiletotraceas'/u01/oracle/ctl_rw.sql';
altertablespacetestreadonly;
alterdatabasebackupcontrolfiletotraceas'/u01/oracle/ctl_ro.sql';
alterdatabaserenamefile'/u01/oracle/product/10.2.0/dbs/MISSING00005'to'/u01/oracle/oradata/ora10g/mytest01.dbf';
altertablespacetestonline;
altertablespacetestreadwrite;
表空间扩容
使文件自动增长
增加新文件
修改现有文件的大小
SQL>selecttablespace_name,sum（bytes/1048576）curr_MB,sum（MAXBYTES/1048576）MAX_MBfromdba_data_filesgroupbyTABLESPACE_NAME;
TABLESPACE_NAMECURR_MBMAX_MB
-----------------------------------
SYSAUX24032767.9844
UNDOTBS12532767.9844
USERS2532767.9844
SYSTEM48032767.9844
MYTBS1010
SQL>
最大大小为空说明不能增长
查询当前分配大小中的剩余尺寸
SQL>selectTABLESPACE_NAME,sum（bytes/1048576）free_mbfromdba_free_spacegroupbyTABLESPACE_NAME;
TABLESPACE_NAMEFREE_MB
-------------------------
UNDOTBS14.0625
SYSAUX.875
USERS24.5625
SYSTEM6.875
MYTBS10.875
扩容的方法:
1.使文件自动增长
alterdatabasedatafile''autoextendonnext10Mmaxsize4G;
alterdatabasedatafile''autoextendoff;
2.增加新文件
altertablespaceusesadddatafile''size5M;
altertablespaceusesdropdatafile'';/*10G才能删有数据不能删第一个文件不能删*/
3.修改现有文件的大小
alterdatabasedatafile''resize15M;
alterdatabasedatafile''resize10M;
删除表空间和数据文件
删除表空间及其内容一起删除
删除表空间及其文件一起删除
createtablet1tablespacemytbsasselect*fromscott.emp;
droptablespacemytbsINCLUDINGCONTENTS;
createtablespacemytbsdatafile'/u01/oracle/oradata/db10/mytbs01.dbf'size10M;
createtablet1tablespacemytbsasselect*fromscott.emp;
droptablespacemytbsINCLUDINGCONTENTSanddatafiles;
*************表空间******************************
表空间是数据文件的容器
表空间是数据文件在数据库内逻辑的组织形式
数据文件存在于操作系统之上,也可以是裸设备
常见表空间
system系统表空间
数据库创建时就创建必须包含
存放数据库基本组件信息比如基表
sysaux辅助表空间
10G引入system太大了.就将部分功能组件取出放到sysaux里
属于SYSTEM表空间的辅助表空间
独立出来的好处
SYSTEM表空间的负荷得以减轻.
反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免
查看sysaux表空间内的组件信息和大小
selectOCCUPANT_NAME,SPACE_USAGE_KBYTESfrom
V$SYSAUX_OCCUPANTS;
undotbs回退表空间
用于事务回退,保存改前镜像块
temp临时表空间
存放临时数据磁盘排序或临时表
升级会员 