oracle数据库对象管理笔记Word文件下载.docx
《oracle数据库对象管理笔记Word文件下载.docx》由会员分享，可在线阅读，更多相关《oracle数据库对象管理笔记Word文件下载.docx（52页珍藏版）》请在冰豆网上搜索。
Oracle为列提供了很多的类型
char（n）:
字符长度为n，不足部分以空格补齐
varchar2（n）:
变化长度的字符串，根据实际占用分配空间
number（n,m）:
n表示数据的总长度，m表示小数位数
date:
日期类型，包含日期和时分秒
等等，上面是常用的
这个表对与DML语
句是否启用日志
表的初始extent的大小，默认值就是表所在的表空
间上所设定的extent大小
数据块在剩余空间>
10%的时候，可以继续
insert，当小于10%的时候，不能继续insert，
剩余空间留给update
因为表空间启用了
ASSM，因此没有参数
表属于哪个bufferpctused
cachePctused表示当数据块
的剩余。
40%的时候，该表的数据块头部ITL槽的个数和最大个数
数据块重新可以insertKeep:
适于小的表，当内存空间不够时也不被替换出来
Recycle:
在需要时可以被替换出来
数据行在数据块里的结构
数据行头部列长度列值
1、数据行的头部
每一个数据行都有一个行头部，在这里存放了该行数据所包含的列的数量、锁定标记，ITL槽号
当某个事务更新某条记录的时候，会在该数据行的头部记录所使用的ITL槽号以及锁定标记
2、列的长度和列的值，每个列之间没有空隙，都是紧密排列
3、不同的数据行之间也没有空隙，紧密排列
数据块的头部有一个结构:
行目录，在行目录中，为每条数据行都记录了一个条目，每个条目就指向该记录的行头部，所以oracle能够区分不同的行
Oracle使用了行级锁
每一条记录都有一个ROWID列，这是一个伪列。
该列的值并没有实际的保存在数据块里面，但是可以显示和查询。
Oracle10g中，rowdid列的格式是
OOOOOOFFFBBBBBBRRR
OOOOOO表示该数据行所在的对象名，一般都是表名
FFF表示该数据行所在的相对文件号
BBBBBB表示该数据行所在的数据块号
RRR表示该行在数据块中的行号
对象名（表名）+文件号+数据块号+行号18位
6363
ROWID采用的是64进制
A-Z0-25
a-z26-51
0-952-61
/62
+63
Rowid就是行的物理地址，块中的位置是伪列，不是实际存在的
Insertintotest15values（1,’a’）;
Insertintotest15values（2,’a’）;
Insertintotest15values（3,’a’）;
SQL>
selectrowid,idfromtest15;
ROWIDID
----------------------------AAAM08AAEAAAAGkAAA1
AAAM08AAEAAAAGkAAB2
AAAM08AAEAAAAGkAAC3
对象ID
selectdbms_rowid.rowid_relative_fno（'
AAAM08AAEAAAAGKAAA'
）asfile_id,
dbms_rowid.rowid_block_number（'
AAAM08AAEAAAAGKAAC'
）asblock_id,
dbms_rowid.rowid_row_number（'
）asrow_idFROMdual;
FILE_IDBLOCK_IDROW_ID------------------------------
43940
管理表的主要工作
扩展表
1、主动地扩展一个表所占用的空间（就是给表分配一个extent）
2、将一个表分配到多个数据文件上，实现IO均衡
查询有哪些表空间
Selecttablespace_namefromdba_tablespaces
查询有哪些数据文件
selectfile_namefromdba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA10G/datafile/o1_mf_users_5nfjwd0f_.dbf/u01/app/oracle/oradata/ORA10G/datafile/o1_mf_sysaux_5nfjwck7_.dbf/u01/app/oracle/oradata/ORA10G/datafile/o1_mf_undotbs1_5nfjwcxt_.dbf/u01/app/oracle/oradata/ORA10G/datafile/o1_mf_system_5nfjwcjh_.dbf/u01/app/oracle/oradata/ORA10G/datafile/o1_mf_example_5nfjzdrq_.dbf
为表空间增加空间
Altertablespaceusersadddatafile‘/u01/app/oracle/oradata/ORA10G/datafile/user02.dbf’size
10m
Altertabletest15allocateextent
将表放入指定表空间中，实现负载均衡
Createtabletest16（idint）tablespaceusers;
Altertabletest16allocateextent（size1mdatafile
‘/u01/app/oracle/oradata/ORA10G/datafile/user02.dbf’）
以sys用户创建的表，在不指定表空间的情况下默认使用system表空间，以非sys用户创建
的表使用users表空间.并且两个schema下的表名可以相同
创建新的表空间
Createtablespaceuser2默认是100M
重整表
将一个表从目前的表空间转移到另外一个表空间中去，或者消除表的数据块级别的碎片数据块级别的碎片指的是每个数据块里含有的数据行太少了，例如100条记录分布在100个数据块中，这种表称为稀疏表
稀疏表产生的原因是该表上存在很多的insert、delete操作
表的segmentheader里记录了一个值，叫高水位标记（HWMhighwatermark）HWM表示当前segment里使用的最后一个数据块的位置，当发生insert时，extent不断的分配，HWM不断的增长
HWM最典型的好处就是selectcount（*）的时候，引起表扫描，服务器进程在扫描数据块的时候，只扫描到HWM为止，因为HWM后面不会有segment的数据块
当delete发生的时候，HWM不会下降的，即使表里面的数据全部删除，HWM也不会下降，这势必会影响selectcount（*）的性能
HWM这个表里面的数据排列不够紧密，我们需要重新整理一下数据行在数据块里的分布，使其分布的紧密一些。
即节省了空间，又提高了select的速度
10g以前，我们只能使用move或者导入导出的方式对表进行重整，达到降低HWM的目的，10g以后可以使用shrink对表进行收缩
执行了删除操作，删除尾部20000多行
总行数已经缩小，但是占用的块数没有变化
将test18表由users表空间移到了example表空间后，占用块数减少了
表占用的数据块明显减少。
注意:
表进行move以后，表上的所有索引失效，需要重建。
将表导入导出，可以去除碎片，但表空间和索引都会失效，因为索引记录的是记录的指针
可以为了碎片整理，减低HWM，在表空间内部进行move。
对于消除数据块级别的碎片来说，Oracle10g之前采用了move、exp/imp的方式进行解决。
当时都会停止应用。
Oracle10g开始采用了shrink技术
Shrink是通过事务的方式将数据行从一个数据块转移到另一个数据块。
收缩过程中，表仍然
可以进行DML操作
当然，事务要能够进行DML操作，还是需要等待收缩引起的事务锁释放。
收缩虽然是事务，但是数据并没有发生变化，因此不会引起触发器的触发。
使用shrink的前提条件
1、表所在的表空间必须使用ASSM（自动段空间管理）
2、在收缩表上必须启用rowmovement选项
Shrink通过事务的方式，对索引没有影响
Rowmovement允许更改rowid，行移动
insertintotest18selectobject_id,object_namefromdba_objects;
50328rowscreated.
deletefromtest18whereobject_id>
20000;
40774rowsdeleted.
execdbms_stats.gather_table_stats（user,'
test18'
）;
PL/SQLproceduresuccessfullycompleted.
selecttable_name,blocks,num_rowsfromuser_tableswheretable_name='
TEST18'
;
TABLE_NAMEBLOCKSNUM_ROWS
--------------------------------------------------TEST1844439108
Altertabletest18enablerowmovement
收缩操作分为两个阶段
1、压缩
Altertabletest18shrinkspacecompact
2、降低HWM
Altertabletest18shrinkspace
压缩阶段，将数据行紧密的插入到数HWM据块里面。
在这个阶段，其他用户可
以对收缩的表进行DML操作。
HWM
HWM释放阶段，降低HWM，这个阶段会对表进行
排他锁。
用户不能对表进行DML操作。
可以分开两个阶段进行执行，业务运行期间执行第一个阶段，业务不繁忙或者停止的时候，进行第二个阶段。
--------------------------------------------------TEST1816339108
如果表上有相关的对象，例如索引，该如何收缩,
Createindextest18idxontest18（object_id）
Altertabletest18shrinkspacecascadecompact失败,换成users表空间成功
截断表
删除记录可以采用delete，delete是DML事务，对表的记录加锁，产生重做日志，消耗undo
空间，消耗资源较多，执行时间较长
对于大表数据，我们建议采用truncate
Truncate是一个DDL语句，只更新数据字典
将数据字典里面该表所占用空间记录全部删除
将表所占用的数据块全部释放
将HWM下降到最低
因为是DDL，执行完毕就不能回滚问题
如果需要截断的表达到几十个GB，那么释放数据块的工作可能会花费很长的时间（可能超过10个小时），在这个过程中，被截断的表是不能被使用的。
截断并不意味着drop，因此我们还是需要使用这些表
如何解决，10g中给出了解决方案
1、更新完成数据字典以后，不立即释放全部数据块（这个操作花费时间多）2、数据块没有释放，仍然被表所拥有，但是表上的HWM已经下降到最低了3、系统空闲的时候，分多次释放数据块，每次释放部分空间这样truncate就能将对系统的影响降到最低
我们先使用delete删除数据
selectextent_id,block_id,blocksfromdba_extentswhere
segment_name='
EXTENT_IDBLOCK_IDBLOCKS------------------------------
010098
110178
210258
310338
410418
510498
610578
787938
888018
988098
1088178
1188258
1288338
1388418
1488498
1588578
16896948
179097128
18922540
199353128
209481104
219609128
229737128
23rowsselected.
deletefromtest18;
167668rowsdeleted.
altertabletest18move;
Tablealtered.
088658
使用delete删除数据以后，并没有降低高水位线
我们使用truncate测试
50345rowscreated.
truncatetabletest18;
Tabletruncated.
010178
我们还是使用truncate，此次我们使用分步释放空间方法
selectcount（*）fromtest18;
COUNT（*）
----------
50345
truncatetabletest18reusestorage;
110098
210178
310258
410338
510418
610498
710578
887938
988018
1088098
1188178
1288258
1388338
1488418
1588498
168969128
17rowsselected.
altertabletest18deallocateunusedkeep0m;
0
Trancatetable通过更新数据字典，立即清空表内容，同时降低高水位线
Trancatetablereusestorage立即更新数据字典，立即清空数据，但是没有减低高水位线，表中保留了大量未使用但不能分配的空间，这个结果不会影响到新的记录插入Altertabletest18deallocateunusekeep1m将不可用空间降低到1m，也可以降低到0m，即降低了高水位线
这种方法相当于resuestorage然后降低高水位线（比如移动表）结果
默认truncate执行降低高水位线功能，即truncate。
。
dropstorageTruncate执行后，是无法回退数据的
删除表的操作
1、删除表的操作属于DDL命令，也就是只是更新数据字典信息，数据字典信息存在system表空间中，因此即使表属于只读表空间中，也能删除表
2、如果这个表是其他表的父表，或者说其他表上有外键引用了这个表，删除这个表的时候，需要加上参数cascadeconstraint，删除主表以后，引用这个表的外键也被删除。
（外表并没有被删除，删除的只是外键）
3.如果删除表的一个列，并且此列是其他表的参考键，需要加上参数cascadeconstraint，删除此表以后，引用这个表的外键也被删除。
（删除的只是外键关系，另一个表列值不变）SQL>
createtableaa（aintprimarykey,bint）;
Tablecreated.
createtablebb（aint,bint,constraintaa_fnkforeignkey（a）references
aa（a））;
altertableaadropcolumna;
altertableaadropcolumna
*
ERRORatline1:
ORA-12992:
cannotdropparentkeycolumn
altertableaadropcolumnacascadeconstraint;
descaa;
NameNu
升级会员 