oracle-dba-管理和维护表

太过爱你忘了你带给我的痛 2022-07-16 16:08 303阅读 0赞

管理和维护表  
数据存储类型  
标量数据类型:其他  
集合数据类型:VARRAY,TABLE  
关系数据类型:REF

标量数据类型  
Varchar2(size)|nvarchar2(size)：变长字符型数据1~1700大小。Nvarchar2支持全球化数据类型，支持定长和变长。每次使用需要不断计算存储的数据长度，再分配数据块  
Char(SIZE)NCHAR(SIZE)定长字符型数据1~2000大小, NCHAR支持全球化数据类型，支持定长和变长  
Date：日期，使用7个定长存储区存储日期型数据，月，年，日，世纪，时,分,秒  
177712年1月1日~公元9999年12月31日  
Number(P,S)：p总长度个数，S小数个数。P范围1~38，s范围-817~1217  
CLOB|LONG：存储大数据对象，定长的字符串数据。如学术论文，个人简历。CLOB列不能直接使用ORACLE数据库指令，需要一个DBMS\_LOB的plsql软件包维护  
BLOB|LONG RAW:存储无结构的大对象，如照片，PPT，二进制图像等。需要一个DBMS\_LOB的plsql软件包维护  
BFILE：在操作系统文件中存储无结构的二进制对象。他是oracle的外部数据类型。Oracle无法直接维护这些数据类型。必须由操作系统维护  
RAW:该数据类型使得数据库可以直接存储2进制数据。在计算机之间传输该类型数据库不做任何转换。所以存储和传输效率较高，最长2000字节  
LONG/LONG RAW数据类型主要用在ORACLE8版本，LONG可以用8以上版本CLOB/BLOB代替

集合数据类型  
VARRY数据类型：有数量限制  
嵌套表:列值包含表，元素数量无限制，不能超过表空间的大小

关系数据类型  
关系类型REF指向一个对象，如游标cursor

创建表  
创建表的规则  
1. 命令简单  
2. 每个表一个表空间(较少维护数据字典的负担)，易于管理和维护。表空间是本地管理，对表空间的维护不影响其他的表  
3. 使用标准extent尺寸减少表空间碎片  
4. 表名长度最大为30字符  
查看scott下的所有表  
Select owner,table\_name,tablespace\_name from dba\_tables where owner=’SCOTT’;  
创建表时，不指定用户名字直接写表名，默认当前用户创建的表。不指定表空间，使用默认表空间创建该表

创建表空间，然后在该表空间下创建一个表  
Create tablespace lin datafile ‘G:\\oracleDataBase\\TEMP\\lin.dbf’ size 30m  
extent management local uniform size 1m;  
create table scott.employees(ecode number(17)) storage (initial 100k next 1  
00 k pctincrease 0 minextents 1 maxextents 8) tablespace lin;  
Storage:initial针对该表，当表的数据量增加时，需要自动分配磁盘空间时第一次分配100K，第二次也是100K，所分配的最大磁盘为8个extents。最小为1个extents。Pctincrease是一个权值参数表示当第三次为该表增加磁盘空间时，需要按规矩计算。  
Next\*(1+pctincrease/100)^(n-2);当n>=3时，如果第三次需要增加磁盘空间时  
分配100\*(1+0/100)^(3-2)=100k。第四次需要增加时分配100\*（1+0/100）^(17-2)=100k。当pctincrease=0时，每次分配的磁盘空间和next参数相同。100K

查看表信息  
Select owner,table\_name,tablespace\_name,initial\_extent,next\_extent from dba\_tables where owner=’SCOTT’ AND table\_name=’EMPLOYEES’;

创建临时表  
只对当前用户的当前会话有效。当前会话只操作自己的数据，没有数据锁的争用。  
临时表默认存储在系统的临时段中，如果临时表空间为空，无法创建成功。  
创建临时表空间  
Create temporary tablespace user\_temp  
Tempfile ‘G:\\oracleDataBase\\temps\\user\_temp.dbf’ size 20m  
创建临时表  
Create global temporary table scott.testTemp on commit preserve rows as se  
lect \* from scott.emp;  
查看表是否存在, TABLESPACE\_NAME为空说明临时表不存在默认表空间和默认临时表空间中，而是存储在临时段，临时段是一个磁盘区，当用户使用sql语句执行查询时如果需要对返回的数据进行排序时，oracle首先需要在内存中完成排序工作，如果内存容量不够就把计算的结果放在临时段中  
Select owner,table\_name,tablespace\_name from dba\_tables where table\_name=’TESTTEMP’;

查看表是否为临时表  
Select table\_name ,tablespace\_name,temporary from dba\_tables where owner=’SCOTT’ and table\_name =’TESTTEMP’;

DROP TABLE scott.TESTTEMP;

段空间管理  
段空间管理：是表段的空间管理，其他段数据都需要自己的段管理方式。段是oracle一个逻辑结构，对数据进行操作都会造成段空间的变化。  
版本9i之后使用自动段空间管理(ASSM)，使用位图管理段空间的使用状况。表空间是assm，段空间则也是assm

高水位线(HWM)  
表段使用高水位线来标记，表示使用过的数据块的边界  
|1|2|3|4|5|…|1000|未用  
此时1000个数据块就是这个表段 的高水位线所在  
新建的表，高水位线在第一块。插入数据，HWM会随之移动。如果删除表中的数据，依旧保留HWM的高度(此时查询数据会扫描删除前的所有数据块，花费时间)。在进行truncate而不是delete时，会调整HWM到第一个位置  
ASSM(Automatic segment space managerment,自动断空间管理)：维护一个低HWM，在低HWM之前的所有数据都是有数据的，可以直接读取。而低HWMHWM之间的所有数据不能保证有。此时需要参考ASSM位图查看数据块使用情况

行迁移  
读取一行数据，数据过于庞大，导致该行无法存储在创建这一行的数据块中，此时oracle会把该行离开原来的块，存储到另一个数据块中，使用rowid关联。  
避免方式，设置合理数据库块大小等  
这样方式访问增加了io，其次还有io相关的latch(缓存多个数据块)，访问速度下降。

创建索引组织表(IOT)  
数据与索引存储在一起，按照索引的结构来组织和存储表中的数据。索引组织表的存储结构不是堆组织表(无序)。表中数据按照某个主键排序后存储，然后在以B树的组织结构存储在数据段中。  
场景：  
 经常使用主键字段来实现查询的事务  
 完全由主键组成的表，如果采用堆组织表，因为所有的数据全部同样存储在索引中，所以表本身就是多余开销。  
 只会通过一个主键访问表。  
 数据以某种特定的顺序物理存储。  
限制：主键不能被删除，延期，禁止。  
优点：节约磁盘空间占用，降低IO，减少访问缓冲区缓存。  
创建IOT表  
CREATE TABLE tiot(x int primary key,y number,z varchar2(20)) organization index;  
提取表定义元数据  
SELECT DBMS\_METADATA.GET\_DDL(‘TABLE’,’TIOT’) FROM DUAL;

索引组织表属性  
 Organization index:表明为索引组织表  
 Overflow：允许创建一个新段，如果IOT的行记录太大，则可以储存在这个新段上，什么时候使用这个新段，涉及2参数。PCTTHRESHOLD/including  
 INCLUDING:行中从第一列直到INCLUDING所指定的列的所有列数据都储存在索引表上，其余列存储在溢出段上。  
 PCTTHRESHOLD：如果行中的数据量超过数据块大小这个百分比，行中其余的数据放入溢出段上  
 COMPRESS(键压缩):此子句可以进行键压缩以消除重复值，普通索引也可以。具体操作为在创建索引表后面添加organization index之后添加COMPRESS n子句。N表示指定压缩的列数，默认无穷大。

列Y的数据溢出到overflow参数指定的表空间USERS。违反PCTTHRESHOLD规则的行记录溢出到表空间USERS  
CREATE table iot\_test  
(x int ,  
Y number,  
Z varchar2(20)  
Constraint iot\_test\_pk primary key(X)  
)  
Organization index  
Pctthreshold 10  
Including y  
Overflow tablespace users;

表参数以及参数维护  
HEAP TABLE:普通表，获取表中的数据是按照命中率来得到的，没有明确先后之分。  
IOT:类似全是索引的表，表中的所有字段都放在索引上，所以等于是约定了数据存放的时候是按照严格规定来的，在数据插入以前就已经确定了其位置。不管插入先后顺序，他在物理上的位置与插入的先后顺序无关。查询时可以少访问很多block。插入比一般表慢。

Intrans：控制对数据块的并行操作的参数  
事务槽：在数据块头中，存储事务的控制信息。每行数据有一个锁位，锁位跟事务槽号相同。数据库服务器就是通过每行的锁位找到数据库头中的事务槽，然后通过事务槽中的事务信息完成对该行数据的操作。每个事务只能使用一个事务槽。  
Intrans定义创建数据块时事务槽的初始值，默认1。说明数据库服务器实现在一个数据块中可以有最多2个并行的事务。每个事务独立并行并且通过自己的事务槽实现对该数据的事务操作

Maxtrans：定义创建数据块时事务槽最大值。默认255。

查看2参数  
select ini\_trans,max\_trans from dba\_tables where table\_name=’EMPLOYEE’;

pctfree:设置每个数据块中预留空间的百分比数。默认10%  
数据块结构，从上到下3部分，1：数据块头，2：空闲区，3：数据区  
数据块头从上往下增长，数据区从下往上增长。2者碰头则空闲区被占满。  
一旦空闲区满，后续操作需要占用空间，数据块无法满足，就占用其他数据块的空间。这种空间置换会带来磁盘io效率低下。  
Oracle针对此情况设置预留空间，满足在数据操作时对数据块空间的要求  
如果修改数据行需要额外的空间，使用此参数指定预留空间大小  
FREELISTS：空闲数据块队列的列表，当用户向表中插入数据时，需要数据块作为存储空间，那么该参数中的数据块就作为候选的数据块。  
PCTUSED：定义数据块中已经使用的空间的百分比数，数据块已经使用的空间低于该参数值时，数据块才放入段中的空闲数据块列表。默认170%。

Select table\_name,tablespace\_name,pct\_free,pct\_used from dba\_tables where table\_name=’EMPLOYEE’;  
修改2参数  
Alter table employee pctused 50 pctfree 30;