oracle数据库对象管理笔记Word文件下载.docx

1、Oracle为列提供了很多的类型 char（n）:字符长度为n，不足部分以空格补齐 varchar2（n）:变化长度的字符串，根据实际占用分配空间 number（n,m）:n表示数据的总长度，m表示小数位数 date:日期类型，包含日期和时分秒 等等，上面是常用的 这个表对与DML语句是否启用日志 表的初始extent的大小，默认值就是表所在的表空间上所设定的extent大小 数据块在剩余空间10%的时候，可以继续insert，当小于10%的时候，不能继续insert，剩余空间留给update 因为表空间启用了ASSM，因此没有参数表属于哪个buffer pctused cache Pctus

2、ed表示当数据块的剩余。40%的时候，该表的数据块头部ITL槽的个数和最大个数 数据块重新可以insert Keep:适于小的表，当内存空间不够时也不被替换出来 Recycle:在需要时可以被替换出来 数据行在数据块里的结构 数据行头部 列长度 列值 1、数据行的头部 每一个数据行都有一个行头部，在这里存放了该行数据所包含的列的数量、锁定标记，ITL槽号 当某个事务更新某条记录的时候，会在该数据行的头部记录所使用的ITL槽号以及锁定标记 2、列的长度和列的值，每个列之间没有空隙，都是紧密排列 3、不同的数据行之间也没有空隙，紧密排列 数据块的头部有一个结构:行目录，在行目录中，为每条数据行都记

3、录了一个条目，每个条目就指向该记录的行头部，所以oracle能够区分不同的行 Oracle使用了行级锁 每一条记录都有一个ROWID列，这是一个伪列。该列的值并没有实际的保存在数据块里面，但是可以显示和查询。Oracle 10g中，rowdid列的格式是 OOOOOOFFFBBBBBBRRR OOOOOO表示该数据行所在的对象名，一般都是表名 FFF表示该数据行所在的相对文件号 BBBBBB表示该数据行所在的数据块号 RRR表示该行在数据块中的行号 对象名（表名）+文件号+数据块号+行号 18位 6 3 6 3 ROWID采用的是64进制 A-Z 0-25 a-z 26-51 0-9 52-6

4、1 / 62 + 63 Rowid就是行的物理地址，块中的位置是伪列，不是实际存在的 Insert into test15 values（1,a）;Insert into test15 values（2,a）;Insert into test15 values（3,a）;SQL select rowid,id from test15;ROWID ID - - AAAM08AAEAAAAGkAAA 1 AAAM08AAEAAAAGkAAB 2 AAAM08AAEAAAAGkAAC 3 对象ID select dbms_rowid.rowid_relative_fno（AAAM08AAEAAAAG

5、KAAA） as file_id , dbms_rowid.rowid_block_number（AAAM08AAEAAAAGKAAC） as block_id, dbms_rowid.rowid_row_number（） as row_id FROM dual;FILE_ID BLOCK_ID ROW_ID - - - 4 394 0 管理表的主要工作 扩展表 1、主动地扩展一个表所占用的空间（就是给表分配一个extent） 2、将一个表分配到多个数据文件上，实现IO均衡 查询有哪些表空间 Select tablespace_name from dba_tablespaces 查询有哪些数据

6、文件 select file_name from dba_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_

7、system_5nfjwcjh_.dbf /u01/app/oracle/oradata/ORA10G/datafile/o1_mf_example_5nfjzdrq_.dbf 为表空间增加空间 Alter tablespace users add datafile /u01/app/oracle/oradata/ORA10G/datafile/user02.dbf size 10m Alter table test15 allocate extent 将表放入指定表空间中，实现负载均衡 Create table test16（id int） tablespace users;Alter ta

8、ble test16 allocate extent （size 1m datafile /u01/app/oracle/oradata/ORA10G/datafile/user02.dbf） 以sys用户创建的表，在不指定表空间的情况下默认使用system表空间，以非sys用户创建的表使用users表空间.并且两个schema下的表名可以相同 创建新的表空间 Create tablespace user2 默认是100M 重整表 将一个表从目前的表空间转移到另外一个表空间中去，或者消除表的数据块级别的碎片 数据块级别的碎片指的是每个数据块里含有的数据行太少了，例如100条记录分布在100个数

9、据块中，这种表称为稀疏表 稀疏表产生的原因是该表上存在很多的insert、delete操作 表的segment header里记录了一个值，叫高水位标记（HWM high water mark） HWM表示当前segment里使用的最后一个数据块的位置，当发生insert时，extent不断的分配，HWM不断的增长 HWM最典型的好处就是select count（*）的时候，引起表扫描，服务器进程在扫描数据块的时候，只扫描到HWM为止，因为HWM后面不会有segment的数据块 当delete发生的时候，HWM不会下降的，即使表里面的数据全部删除，HWM也不会下降，这势必会影响select c

10、ount（*）的性能 HWM 这个表里面的数据排列不够紧密，我们需要重新整理一下数据行在数据块里的分布，使其分布的紧密一些。即节省了空间，又提高了select的速度 10g以前，我们只能使用move或者导入导出的方式对表进行重整，达到降低HWM的目的，10g以后可以使用shrink对表进行收缩 执行了删除操作，删除尾部20000多行 总行数已经缩小，但是占用的块数没有变化 将test18表由users表空间移到了example表空间后，占用块数减少了 表占用的数据块明显减少。注意:表进行move以后，表上的所有索引失效，需要重建。将表导入导出，可以去除碎片，但表空间和索引都会失效，因为索引记录

11、的是记录的指针 可以为了碎片整理，减低HWM，在表空间内部进行move。对于消除数据块级别的碎片来说，Oracle10g之前采用了move、exp/imp的方式进行解决。当时都会停止应用。Oracle 10g开始采用了shrink技术 Shrink是通过事务的方式将数据行从一个数据块转移到另一个数据块。收缩过程中，表仍然可以进行DML操作 当然，事务要能够进行DML操作，还是需要等待收缩引起的事务锁释放。收缩虽然是事务，但是数据并没有发生变化，因此不会引起触发器的触发。使用shrink的前提条件 1、表所在的表空间必须使用ASSM（自动段空间管理） 2、在收缩表上必须启用row movemen

12、t选项 Shrink通过事务的方式，对索引没有影响 Row movement允许更改rowid，行移动 insert into test18 select object_id,object_name from dba_objects;50328 rows created. delete from test18 where object_id20000;40774 rows deleted. exec dbms_stats.gather_table_stats（user,test18）;PL/SQL procedure successfully completed. select table_n

13、ame,blocks,num_rows from user_tables where table_name=TEST18;TABLE_NAME BLOCKS NUM_ROWS - - - TEST18 444 39108 Alter table test18 enable row movement 收缩操作分为两个阶段 1、压缩 Alter table test18 shrink space compact 2、降低HWM Alter table test18 shrink space 压缩阶段，将数据行紧密的插入到数HWM 据块里面。在这个阶段，其他用户可以对收缩的表进行DML操作。HWM

14、HWM 释放阶段，降低HWM，这个阶段会对表进行排他锁。用户不能对表进行DML操作。可以分开两个阶段进行执行，业务运行期间执行第一个阶段，业务不繁忙或者停止的时候，进行第二个阶段。- - - TEST18 163 39108 如果表上有相关的对象，例如索引，该如何收缩, Create index test18idx on test18（object_id） Alter table test18 shrink space cascade compact 失败,换成users表空间成功 截断表 删除记录可以采用delete，delete是DML事务，对表的记录加锁，产生重做日志，消耗undo空间，

15、消耗资源较多，执行时间较长 对于大表数据，我们建议采用truncate Truncate是一个DDL语句，只更新数据字典 将数据字典里面该表所占用空间记录全部删除 将表所占用的数据块全部释放 将HWM下降到最低 因为是DDL，执行完毕就不能回滚 问题 如果需要截断的表达到几十个GB，那么释放数据块的工作可能会花费很长的时间（可能超过10个小时），在这个过程中，被截断的表是不能被使用的。截断并不意味着drop，因此我们还是需要使用这些表 如何解决，10g中给出了解决方案 1、更新完成数据字典以后，不立即释放全部数据块（这个操作花费时间多） 2、数据块没有释放，仍然被表所拥有，但是表上的HWM已经

16、下降到最低了 3、系统空闲的时候，分多次释放数据块，每次释放部分空间 这样truncate就能将对系统的影响降到最低 我们先使用delete删除数据 select extent_id,block_id,blocks from dba_extents where segment_name=EXTENT_ID BLOCK_ID BLOCKS - - - 0 1009 8 1 1017 8 2 1025 8 3 1033 8 4 1041 8 5 1049 8 6 1057 8 7 8793 8 8 8801 8 9 8809 8 10 8817 8 11 8825 8 12 8833 8 13 8

17、841 8 14 8849 8 15 8857 8 16 8969 48 17 9097 128 18 9225 40 19 9353 128 20 9481 104 21 9609 128 22 9737 128 23 rows selected. delete from test18;167668 rows deleted. alter table test18 move;Table altered. 0 8865 8 使用delete删除数据以后，并没有降低高水位线 我们使用truncate测试 50345 rows created. truncate table test18 ;Tab

18、le truncated. 0 1017 8 我们还是使用truncate，此次我们使用分步释放空间方法 select count（*） from test18;COUNT（*） - 50345 truncate table test18 reuse storage;1 1009 8 2 1017 8 3 1025 8 4 1033 8 5 1041 8 6 1049 8 7 1057 8 8 8793 8 9 8801 8 10 8809 8 11 8817 8 12 8825 8 13 8833 8 14 8841 8 15 8849 8 16 8969 128 17 rows selec

19、ted. alter table test18 deallocate unused keep 0m;0 Trancate table 通过更新数据字典，立即清空表内容，同时降低高水位线 Trancate table reuse storage立即更新数据字典，立即清空数据，但是没有减低高水位线，表中保留了大量未使用但不能分配的空间，这个结果不会影响到新的记录插入 Alter table test18 deallocate unuse keep 1m 将不可用空间降低到1m，也可以降低到0m，即降低了高水位线 这种方法相当于resue storage然后降低高水位线（比如移动表）结果 默认tru

20、ncate 执行降低高水位线功能，即truncate 。drop storage Truncate执行后，是无法回退数据的 删除表的操作 1、删除表的操作属于DDL命令，也就是只是更新数据字典信息，数据字典信息存在system表空间中，因此即使表属于只读表空间中，也能删除表 2、如果这个表是其他表的父表，或者说其他表上有外键引用了这个表，删除这个表的时候，需要加上参数cascade constraint，删除主表以后，引用这个表的外键也被删除。（外表并没有被删除，删除的只是外键） 3.如果删除表的一个列，并且此列是其他表的参考键，需要加上参数cascade constraint，删除此表以后，

21、引用这个表的外键也被删除。（删除的只是外键关系，另一个表列值不变） SQL create table aa（a int primary key, b int）;Table created. create table bb（a int , b int,constraint aa_fnk foreign key （a） references aa（a）; alter table aa drop column a; alter table aa drop column a * ERROR at line 1:ORA-12992: cannot drop parent key column alter table aa drop column a cascade constraint; desc aa;Name Nu