第十章 表管理与维护
<p>[TOC]</p>
<h2>Oracle内置数据类型</h2>
<ul>
<li>标量数据类型：常见的那些</li>
<li>集合数据类型：能不用就不用，必须要用就少用</li>
<li>关系数据类型：能不用就不用，必须要用就少用</li>
</ul>
<h2>ROWID</h2>
<ul>
<li>扩展ROWID</li>
<li>限制ROWID</li>
</ul>
<h2>Oracle行结构</h2>
<ul>
<li>一行存放的内容</li>
<li>字符长度不要超过250</li>
</ul>
<h2>创建普通表</h2>
<ul>
<li>创建表时指定表空间
<code>create table pdm (cell_name varchar2(10)) tablespace users ;</code></li>
</ul>
<h2>创建临时表</h2>
<ul>
<li>语法
<pre><code>--创建临时表，有会话级的事务级，临时处理数据效率高
create global temporary table
pdm
--存在周期是会话/事务
on commit preserve rows --on commit delete rows
as
select *
from TMP_NVT_PROCESS_STATUS;
--其他会话无法看到该表的数据
SQL> select * from pdm where rownum=1 ;
no rows selected</code></pre></li>
</ul>
<h2>非分区表的重组</h2>
<ul>
<li>
<p>可以移动非分区表，但是索引会变得无效，要重建索引</p>
</li>
<li>示范表：</li>
</ul>
<table>
<thead>
<tr>
<th>索引</th>
<th>表</th>
</tr>
</thead>
<tbody>
<tr>
<td>A_PK</td>
<td>A</td>
</tr>
<tr>
<td>JHIST_JOB_IX</td>
<td>JOB_HISTORY</td>
</tr>
<tr>
<td>JHIST_DEPARTMENT_IX</td>
<td>JOB_HISTORY</td>
</tr>
<tr>
<td>JHIST_EMPLOYEE_IX</td>
<td>JOB_HISTORY</td>
</tr>
<tr>
<td>JHIST_EMP_ID_ST_DATE_PK</td>
<td>JOB_HISTORY</td>
</tr>
</tbody>
</table>
<pre><code>--查询用户数据段存放的表空间
select segment_name,tablespace_name,extents,blocks from dba_segments where owner='HR';
--查询用户数据段对象类型
select object_id,object_name,object_type,status,created from dba_objects where owner='HR' ;
--查询索引对应的表
select index_name,table_name,tablespace_name,status from dba_indexes where owner='HR';
--查看可用的表空间
select * from dba_tablespaces ;
--将以上表移到tbs01表空间
alter table hr.a move tablespace tbs01;
select segment_name,tablespace_name,extents,blocks from dba_segments where owner='HR' and segment_name in('A','A_PK');
--status=valid 可用状态
select object_id,object_name,object_type,status,created from dba_objects where owner='HR' and object_name in('A','A_PK');
--JOB_PDM PROCEDURE类型无效了
--查看表的索引，发现索引无效了，要重建索引
select index_name,table_name,tablespace_name,status from dba_indexes where owner='HR' and table_name='A';
--创建一个index表空间用于存放索引
--重建索引放至INDX表空间
alter index hr.a_pk rebuild tablespace INDX;
--索引可用了
select index_name,table_name,tablespace_name,status from dba_indexes where owner='HR' and table_name='A';
--移动另一张表，同样要检查每步的正确执行
alter table hr.JOB_HISTORY move tablespace tbs01 ;
alter index hr.JHIST_JOB_IX rebuild tablespace INDX;
alter index hr.JHIST_DEPARTMENT_IX rebuild tablespace INDX;
alter index hr.JHIST_EMPLOYEE_IX rebuild tablespace INDX;
alter index hr.JHIST_EMP_ID_ST_DATE_PK rebuild tablespace INDX;</code></pre>
<h2>列的维护</h2>
<ul>
<li>系统出现意外终止后继续删除</li>
</ul>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/8356521743124d6ec280e3ad36315be4" alt="" /></p>
<ul>
<li>直接删除列，设置列不可用再删除</li>
</ul>
<pre><code>select * from hr.a ;--C1是主键，有索引是无法修改列名的(书上这么说，但是不是)
--修改列名
alter table hr.a rename column C2 to join_time ;
alter table hr.a rename column C3 to emp_age ;
alter table hr.a rename column C1 to user_id ;
--删除列
select * from hr.tmp_nvt_process_status ;
--删除列
alter table hr.tmp_nvt_process_status drop column jig_no cascade constraints checkpoint 100;
--删除列，使用checkpoint 100，执行100行产生检查点
--数据库意外中断可以继续删除列，如果不完全删除列则表不可用
alter table hr.tmp_nvt_process_status drop column pn cascade constraints checkpoint 100;
SQL> shutdown abort
alter table hr.tmp_nvt_process_status drop columns continue;
--设置列不可用
alter table hr.tmp_nvt_process_status set unused (remark_4,remark_6,remark_6) CASCADE CONSTRAINTS ;
--数据库空闲时再执行删除列
alter table hr.tmp_nvt_process_status drop unused columns checkpoint 100;
--如果意外失败，重启后执行如下继续删除
alter table hr.tmp_nvt_process_status drop columns continue 100;</code></pre>
<h2>表的截断和删除</h2>
<ul>
<li>如题</li>
</ul>
<pre><code>select * from dba_tables where owner='HR';
--删除表结构及数据
drop table hr.lly ;
--仅删除表数据
truncate table hr.boxes;
--删除表数据可回滚
delete from hr.emp_history;</code></pre>
<h2>高水线及直接装入数据</h2>
<ul>
<li>先备份试验的数据</li>
</ul>
<pre><code>select count(*) from hr.tmp_nvt_process_status;
--查看用户使用的默认表空间
select * from dba_users where username='PJINLIAN';
--设置整个库使用的默认表空间
alter database default tablespace TBS01;
--设置用户的默认表空间
alter user PJINLIAN default tablespace TS_DATA;
--为PJINLIAN创建表报错：
--erro:on privileges on tablespace "TS_DATA"
create table pjinlian.pdm as select * from hr.tmp_nvt_process_status ;
select * from pjinlian.pdm;
--需要对用户授予表空间权限
GRANT UNLIMITED TABLESPACE TO PJINLIAN;
--查看创建的表列及块数量
select num_rows,blocks,empty_blocks from dba_tables where owner='PJINLIAN' and table_name ='PDM';</code></pre>
<h3>直接装入的串行直接装入 /<em>+APPEND </em>/</h3>
<ul>
<li>
<p>直接装入数据，数据将放置在高水位线(数据块)之上，速度快。</p>
<blockquote>
<p>insert /<em>+APPEND </em>/ into [用户名.]表名
[[NO]LOGGING] --写不写重做日志，写有可能实现恢复
子查询</p>
</blockquote>
</li>
<li>查看当前高水位线</li>
</ul>
<pre><code>--blocks是占用的块，实际上就是高水位线HWM：6656
select blocks from dba_segments where owner='HR' and segment_name='TMP_NVT_PROCESS_STATUS';
--查询是空，实际上是没有统计分析，blocks+empty_blocks也是HWM
select num_rows,blocks,empty_blocks from dba_tables where owner='HR' and table_name ='TMP_NVT_PROCESS_STATUS';
--做统计分析
analyze table hr.tmp_nvt_process_status compute statistics;
--统计分析后dba_table的那三列就有数据了
select num_rows,blocks,empty_blocks from dba_tables where owner='HR' and table_name ='TMP_NVT_PROCESS_STATUS';</code></pre>
<ul>
<li>直接装入数据，观察高水位给的变化
delete 不会改变高水位线
truncate 会改变高水位线</li>
</ul>
<pre><code>--删除HR.TMP_NVT_PROCESS_STATUS 表，高水位线不会下降
delete hr.tmp_nvt_process_status;
commit;
--查看高水位线HWM：6656
select blocks from dba_segments where owner='HR' and segment_name='TMP_NVT_PROCESS_STATUS';
--做统计分析
analyze table hr.tmp_nvt_process_status compute statistics;
--高水位线HWM：6656，数据行num_rows:0
select num_rows,blocks,empty_blocks from dba_tables where owner='HR' and table_name ='TMP_NVT_PROCESS_STATUS';
--装入数据，提前将HR.TMP_NVT_PROCESS_STATUS表的数据放到PJINLIAN.PDM表中了
insert /*+ APPEND */ into hr.tmp_nvt_process_status nologging select * from pjinlian.pdm ;
commit;</code></pre>
<ul>
<li>观察高水位线的变化</li>
</ul>
<pre><code>--HWM：11779
select blocks from dba_segments where owner='HR' and segment_name='TMP_NVT_PROCESS_STATUS';
--做统计分析
analyze table hr.tmp_nvt_process_status compute statistics;
--统计分析后列数和数据块更新
select num_rows,blocks,empty_blocks from dba_tables where owner='HR' and table_name ='TMP_NVT_PROCESS_STATUS';</code></pre>
<ul>
<li>截断表 truncate 高水线置位</li>
</ul>
<pre><code>--截断后重复观看高水线
truncate table hr.tmp_nvt_process_status;</code></pre>
<h3>直接装入的并行直接装入/<em>+PARALLEL </em>/</h3>
<ul>
<li>删除表的主键得以insert 重复数据</li>
</ul>
<pre><code>alter table hr.tmp_nvt_process_status drop constraint tmp_nvt_process_status_pk;</code></pre>
<ul>
<li>并行直接装入，insert 使用 PARALLEL</li>
</ul>
<pre><code>--先要执行这个
alter session enable parallel dml;
insert /*+ PARALLEL(hr.tmp_nvt_process_status,2) */
into hr.tmp_nvt_process_status nologging
select * from pjinlian.pdm ;
commit;
--观察高水线</code></pre>
<ul>
<li>并行直接装入的另一种方法，创建表或者alter修改表的属性为PARALLEL</li>
</ul>
<h2>收缩段</h2>
<ul>
<li>分两个阶段</li>
</ul>
<pre><code>--数据向左移
alter table hr.tmp_nvt_process_status shrink space compact;
--调整高水线
alter table hr.tmp_nvt_process_status shrink space;</code></pre>
<ul>
<li>收缩表操作需要行移动功能</li>
</ul>
<pre><code>select num_rows,blocks,empty_blocks,row_movement from dba_tables where owner='HR' and table_name='TMP_NVT_PROCESS_STATUS';</code></pre>
<table>
<thead>
<tr>
<th>序号</th>
<th>num_rows</th>
<th>blocks</th>
<th>empty_blocks</th>
<th>row_movement</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1111768</td>
<td>11117</td>
<td>147</td>
<td>DISABLED</td>
</tr>
</tbody>
</table>
<ul>
<li>修改表行移动功能</li>
</ul>
<pre><code>alter table hr.tmp_nvt_process_status enable row movement;
select num_rows,blocks,empty_blocks,row_movement from dba_tables where owner='HR' and table_name='TMP_NVT_PROCESS_STATUS';</code></pre>
<table>
<thead>
<tr>
<th>序号</th>
<th>num_rows</th>
<th>blocks</th>
<th>empty_blocks</th>
<th>row_movement</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1111768</td>
<td>11117</td>
<td>147</td>
<td>ENABLED</td>
</tr>
</tbody>
</table>
<ul>
<li>数据左移不改变高水线</li>
</ul>
<pre><code>--收缩段的第一阶段：数据向左移
alter table hr.tmp_nvt_process_status shrink space compact;
--统计分析
analyze table hr.tmp_nvt_process_status compute statistics;
--高水线没有发生任何改变
select blocks from dba_segments where owner='HR' and segment_name='TMP_NVT_PROCESS_STATUS';
select num_rows,blocks,empty_blocks,row_movement from dba_tables where owner='HR' and table_name='TMP_NVT_PROCESS_STATUS';</code></pre>
<table>
<thead>
<tr>
<th>序号</th>
<th>blocks</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>11264</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>序号</th>
<th>num_rows</th>
<th>blocks</th>
<th>empty_blocks</th>
<th>row_movement</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1111768</td>
<td>11117</td>
<td>147</td>
<td>ENABLED</td>
</tr>
</tbody>
</table>
<ul>
<li>收缩段的第二阶段：：调整高水线</li>
</ul>
<pre><code>alter table hr.tmp_nvt_process_status shrink space;
select blocks from dba_segments where owner='HR' and segment_name='TMP_NVT_PROCESS_STATUS';
select num_rows,blocks,empty_blocks,row_movement from dba_tables where owner='HR' and table_name='TMP_NVT_PROCESS_STATUS';
analyze table hr.tmp_nvt_process_status compute statistics;</code></pre>
<table>
<thead>
<tr>
<th>序号</th>
<th>blocks</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>10480</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>序号</th>
<th>num_rows</th>
<th>blocks</th>
<th>empty_blocks</th>
<th>row_movement</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1111768</td>
<td>11117</td>
<td>147</td>
<td>ENABLED</td>
</tr>
<tr>
<td>2</td>
<td>1111768</td>
<td>10331</td>
<td>147</td>
<td>ENABLED</td>
</tr>
</tbody>
</table>
<h2>表压段</h2>
<ul>
<li>
<p>不同版本参数不同P202</p>
</li>
<li>如下一个示例</li>
</ul>
<pre><code>--创建一张支持OLTP操作和直接装入数据操作时都压缩的数据表
select * from hr.pdm ;
create table hr.pdm row store compress advanced as select * from pjinlian.pdm;
--装备原始数据
create table hr.nvt_process_status as select * from pjinlian.pdm;
--统计分析
analyze table hr.pdm compute statistics;
--查看段信息
select num_rows,blocks,empty_blocks,compression,compress_for from dba_tables where owner='HR' and table_name='PDM'
union
select num_rows,blocks,empty_blocks,compression,compress_for from dba_tables where owner='PJINLIAN' and table_name='PDM'
union
select num_rows,blocks,empty_blocks,compression,compress_for from dba_tables where owner='HR' and table_name='NVT_PROCESS_STATUS';
--计算压缩比：44%
select ((5280+0) -(2824+120))/(5280+0) from dual;
--使用alter 修改已经存在的表支持压缩
alter table pjinlian.pdm row store compress advanced;
--统计分析，再对比压缩前后，并没减少，所以对已经存在的数据不进行压缩
analyze table pjinlian.pdm compute statistics;
--再次装入数据
insert /*+ APPEND */ into pjinlian.pdm nologging select * from hr.nvt_process_status ;
--分析统计再查询，数据翻倍数据块却没有翻倍，被压缩了
analyze table pjinlian.pdm compute statistics;</code></pre>
<h2>创建表的应用实例</h2>
<ul>
<li>就创建三张表</li>
</ul>