2表空间
<p>[TOC]</p>
<h2>创建本地管理表空间</h2>
<ul>
<li>查看所有表空间$数据文件位置
<strong><div id='view'>查询</div></strong></li>
</ul>
<pre><code>select tablespace_name,block_size,extent_management,segment_space_management,status from dba_tablespaces ;
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES,STATUS,AUTOEXTENSIBLE,MAXBYTES,ONLINE_STATUS from dba_data_files where tablespace_name in('PIONEER_DATA','MLDN_TEMP','MLDN_DATE')
-- 临时表空间数据文件
select f.file#,f.ts#,f.enabled,f.name,t.name from v$tempfile f,v$tablespace t where f.ts#=t.ts# ;
-- 查看临时表空间
select * from database_properties where property_name like 'DEFAULT_%';</code></pre>
<ul>
<li>创建表空间，两个数据文件路径如下</li>
</ul>
<pre><code>/home/oracle/backup/disk2/orcl/jinlian01.dbf
/home/oracle/backup/disk2/orcl/jinlian02.dbf</code></pre>
<ul>
<li>创建表空间语句，每个段大小是1M，本地管理</li>
</ul>
<pre><code>create tablespace jinlian
datafile '/home/oracle/backup/disk2/orcl/jinlian01.dbf' size 50M,
'/home/oracle/backup/disk2/orcl/jinlian02.dbf' size 50M
extent management local
uniform size 1M;</code></pre>
<ul>
<li>
<p><a href="#view">查看表空间及其数据文件</a></p>
</li>
<li>MLDN_DATE、MLDN_TEMP、PIONEER_DATA、PIONEER_INDX、JINLIAN学会怎么删除表空间的时候，回来删除掉</li>
</ul>
<h2>创建还原表空间</h2>
<ul>
<li>还原表空间数据文件如下，其大小20M</li>
</ul>
<pre><code>/home/oracle/backup/disk7/orcl/jinlian_undo.dbf</code></pre>
<ul>
<li>创建语句</li>
</ul>
<pre><code>create undo tablespace jinlian_undo
datafile '/home/oracle/backup/disk7/orcl/jinlian_undo.dbf'
size 20M ;</code></pre>
<ul>
<li><a href="#view">查看表空间及其数据文件</a>，另外也要查看物理文件否存在 </li>
</ul>
<h2>创建临时表空间</h2>
<ul>
<li>临时表空间数据文件如下，其大小创建为10M,相关目录要手动创建，不然就会报错,</li>
</ul>
<pre><code>/home/oracle/backup/disk8/orcl/jinlian_temp.dbf</code></pre>
<ul>
<li>
<p><a href="#view">查看临时表空间及其数据文件</a></p>
</li>
<li>创建临时表空间语句,uniform size 2 M是show parameter sort_area_size的整倍数，效率更高</li>
</ul>
<pre><code>create temporary tablespace jinlian_temp
tempfile '/home/oracle/backup/disk8/orcl/jinlian_temp.dbf'
size 10M
extent management local
uniform size 2 M;</code></pre>
<ul>
<li><a href="#view">查看临时表空间及其数据文件</a></li>
</ul>
<h2>默认临时表空间</h2>
<ul>
<li>查看当前默认临时表空间</li>
</ul>
<pre><code>select * from database_properties where property_name like 'DEFAULT_TEMP%';</code></pre>
<ul>
<li>修改默认临时表空间</li>
</ul>
<pre><code>alter database default temporary tablespace jinlian_temp;</code></pre>
<ul>
<li>修改回来原来的默认临时表空间</li>
</ul>
<pre><code>alter database default temporary tablespace temp;</code></pre>
<h2>设置表空间为脱机状态，该变化会写到数据字典、控制文件、报警文件</h2>
<ul>
<li>查看表空间的状态</li>
</ul>
<pre><code>select tablespace_name,status,contents from dba_tablespaces where tablespace_name like 'JINL%';</code></pre>
<ul>
<li>查看数据文件的状态</li>
</ul>
<pre><code>select FILE_ID,FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where tablespace_name like 'JINL%';
select file#,name,status from v$datafile where name like '%jin%';</code></pre>
<ul>
<li>
<p>设置表空间为脱机状态<br />
<code>alter tablespace jinlian offline ;</code></p>
</li>
<li>设置表空间为联机状态<br />
<code>alter tablespace jinlian online;</code></li>
</ul>
<h2>设置表空间为只读模式</h2>
<ul>
<li>查看status=online，联机才能被设置为只读，执行完后status=read only<br />
<code>select tablespace_name,status,contents from dba_tablespaces where tablespace_name like 'JINL%';</code></li>
<li>设置jinlian表空间为read only状态<br />
<code>alter tablespace jinlian read only ;</code></li>
<li>设置回读写状态<br />
<code>alter tablespace jinlian read write ;</code></li>
<li>该记录写到报警文件可，去查看一下报警文件
<pre><code>select value from v$diag_info where name='Diag Trace';
[oracle@oracle trace]$ adrci
adrci> show alert -tail 10 --可以看到以上修改信息</code></pre></li>
</ul>
<h2>重置表空间的大小</h2>
<ul>
<li>数据字典管理的、本地管理的，重置方法不同</li>
<li>本地管理的：改变数据文件的大小，增加数据文件的数量</li>
<li>查看表空间对应的数据文件能否自动扩展</li>
</ul>
<pre><code>select file_id,tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name like 'JINL%';</code></pre>
<ul>
<li>设置数据文件自动扩展</li>
</ul>
<pre><code>alter database datafile
'/home/oracle/backup/disk2/orcl/jinlian01.dbf'
autoextend on
next 1M;</code></pre>
<ul>
<li>手工重置数据文件的大小</li>
<li>查看数据文件的大小<br />
<code>select file_id,file_name,tablespace_name,bytes/(1024*1024) mb from dba_data_files where tablespace_name like 'JIN%';</code></li>
<li>增加数据文件的大小到100M<br />
<code>alter database datafile '/home/oracle/backup/disk2/orcl/jinlian01.dbf' resize 100M;</code></li>
<li>为jinlian表空间增加数据文件 </li>
</ul>
<pre><code>/home/oracle/backup/disk4/orcl/jinlian03.dbf
alter tablespace jinlian add datafile
'/home/oracle/backup/disk4/orcl/jinlian03.dbf'
size 80M;</code></pre>
<h2>移动数据文件</h2>
<ul>
<li>两种方法：表空间可置为脱机状态、表空间不可置为脱机状态</li>
</ul>
<h3>表空间可置为脱机的移动方法</h3>
<ul>
<li>
<p>查看表空间信息<br />
select file_name,tablespace_name,online_status from dba_data_files where tablespace_name like 'JINL%';</p>
</li>
<li>设置表空间为脱机状态<br />
<code>alter tablespace jinlian offline ;</code></li>
<li>
<p>移动数据文件，到源数据文件的目录下移动<br />
<code>[oracle@oracle orcl]$ cp jinlian03.dbf /home/oracle/backup/disk8/orcl/jinlian03.dbf</code> </p>
</li>
<li>执行数据文件重命名命令 </li>
</ul>
<pre><code>alter tablespace jinlian rename datafile
'/home/oracle/backup/disk4/orcl/jinlian03.dbf'
to
'/home/oracle/backup/disk8/orcl/jinlian03.dbf';</code></pre>
<ul>
<li>
<p>将表空间设置为联机状态<br />
<code>alter tablespace jinlian online ;</code></p>
</li>
<li>删除旧的数据文件，整个变化过程都要确认的</li>
</ul>
<h3>表空间不可置为脱机状态的情况</h3>
<ul>
<li>有两个要求：数据库置于mount状态，目标数据文件必须存在，示例移动system表空间，文件：/u01/app/oracle/oradata/orcl/system01.dbf</li>
<li>关闭数据库<code>SQL> shutdown immediate</code></li>
<li>移动源数据文件到目标数据文件</li>
</ul>
<pre><code>[oracle@oracle orcl]$ cp /u01/app/oracle/oradata/orcl/system01.dbf /home/oracle/backup/disk8/orcl/system01.dbf</code></pre>
<ul>
<li>
<p>置数据库于mount状态<code>SQL> startup mount</code></p>
</li>
<li>执行移动语句 </li>
</ul>
<pre><code>alter database ORCL rename file
'/u01/app/oracle/oradata/orcl/system01.dbf'
to
'/home/oracle/backup/disk8/orcl/system01.dbf';</code></pre>
<ul>
<li>
<p>打开数据库<br />
<code>alter database open;</code></p>
</li>
<li>使用数据字典查看移动的情况，不要的文件可以删除，试验完全恢复原始状态</li>
<li>同样的操作移动回来，旧的数据文件不能使用了，要重新移动一次</li>
</ul>
<pre><code>alter database ORCL rename file
'/home/oracle/backup/disk8/orcl/system01.dbf'
to
'/u01/app/oracle/oradata/orcl/system01.dbf';</code></pre>
<h2>删除表空间</h2>
<ul>
<li>查看表空间的情况</li>
</ul>
<pre><code>select tablespace_name,status,contents,logging from dba_tablespaces ;--显示临时表空间
select file_id,file_name,tablespace_name,online_status from dba_data_files ;--不显示临时表空间
MLDN_DATE /u01/app/oracle/product/12.2.0/db_1/dbs/D:tablespacesmldn_data01.dbf
PIONEER_DATA /home/oracle/backup/disk2/moon/pioneer_data.dbf
PIONEER_INDX /home/oracle/backup/disk4/moon/pioneer_indx.dbf
JINLIAN /home/oracle/backup/disk2/orcl/jinlian01.dbf /home/oracle/backup/disk4/orcl/jinlian02.dbf /home/oracle/backup/disk8/orcl/jinlian03.dbf
JINLIAN_UNDO /home/oracle/backup/disk7/orcl/jinlian_undo.dbf</code></pre>
<ul>
<li>无参数删除MLDN_DATE，ERROR：tablespace not empty, use INCLUDING CONTENTS option。</li>
<li>无and datafiles 参数，要手动删除物理文件</li>
</ul>
<pre><code>SQL> drop tablespace mldn_date ;
SQL> drop tablespace mldn_date including contents;</code></pre>
<ul>
<li>
<p>再试试无参数删除jinlian，无活动段可删除，手动删除数据文件<br />
<code>SQL> drop tablespace jinlian ;</code></p>
</li>
<li>删除表空间同时删除数据文件</li>
</ul>
<pre><code>SQL> drop tablespace pioneer_data including contents and datafiles ;
SQL> drop tablespace pioneedropr_indx including contents and datafiles ;
SQL> drop tablespace jinlian_undo including contents and datafiles ;</code></pre>
<ul>
<li>删除临时表空间，查看临时表空间数据文件的路径使用v$tempfile,v$tablespace 数据字典</li>
</ul>
<pre><code>select f.ts#,f.file#,f.status,f.enabled,f.name,t.name from v$tempfile f,v$tablespace t where f.ts#=t.ts#;
MLDN_TEMP /u01/app/oracle/product/12.2.0/db_1/dbs/D:tablespacesmldn_temp02.dbf
JINLIAN_TEMP /home/oracle/backup/disk8/orcl/jinlian_temp.dbf</code></pre>
<ul>
<li>默认临时表空间不能删除，查看默认临时表空间是哪个</li>
</ul>
<pre><code>select * from database_properties where property_name like 'DEFAULT_%';
DEFAULT_TEMP_TABLESPACE=TEMP;--TEMP临时表空间不能删除，如果要删除要修改默认临时表空间</code></pre>
<ul>
<li>修改默认临时表空间</li>
</ul>
<pre><code>SQL> alter database default temporary tablespace 要设置的默认临时表空间名;</code></pre>
<ul>
<li>删除临时表空间，带参数<br />
<code>SQL> drop tablespace mldn_temp ;</code></li>
<li>删除临时表空间带参数，书中说删除临时、还原表空间使用参数不删除数据文件，我试的能删除，所以每次做变更都要检查变更是否生效。<br />
<code>SQL> drop tablespace jinlian_temp including contents and datafiles ;</code>
<strong>可能要重启数据库才可以删除</strong></li>
</ul>
<h2>学会如何使用OMF管理表空间和数据文件</h2>
<ul>
<li>
<p>创建表空间的应用实例</p>
<blockquote>
<p>为先驱工程创建四个表空间如下：都是本地管理
先驱工程数据表空间 pioneer_data /home/oracle/backup/disk2/moon/pioneer_data.dbf 100M
先驱工程索引表空间 pioneer_indx /home/oracle/backup/disk4/moon/pioneer_indx.dbf 100M
先驱工程还原表空间 pioneer_undo /home/oracle/backup/disk6/moon/pioneer_undo.dbf 50M
先驱工程临时表空间 pioneer_temp /home/oracle/backup/disk8/moon/pioneer_temp.dbf 50M</p>
</blockquote>
</li>
<li>查看原来的表空间的数据文件、表空间、临时表空间</li>
</ul>
<pre><code>select file_id,file_name,tablespace_name,bytes/(1024*1024) from dba_data_files ;
select tablespace_name,block_size,status,contents,extent_management,segment_space_management,min_extents from dba_tablespaces ;
select f.file#,t.ts#,f.name,t.name from v$tempfile f,v$tablespace t where f.ts#=t.ts# ;</code></pre>
<ul>
<li>创建表空间语句</li>
</ul>
<pre><code>create tablespace pioneer_data datafile
'/home/oracle/backup/disk2/moon/pioneer_data.dbf'
size 100 M
extent management local
uniform size 1 M;</code></pre>
<ul>
<li>与数据表空间一样的创建方法</li>
</ul>
<pre><code>create tablespace pioneer_indx datafile
'/home/oracle/backup/disk4/moon/pioneer_indx.dbf'
size 100 M
extent management local
uniform size 1 M;</code></pre>
<ul>
<li>指定为undo表空间，不需要增量uniform 1 M</li>
</ul>
<pre><code>create undo tablespace pioneer_undo datafile
'/home/oracle/backup/disk6/moon/pioneer_undo.dbf'
size 50 M
extent management local;</code></pre>
<ul>
<li>注意指定临时表空间，文件是tempfile，2是那个参数的倍数，速度更快</li>
</ul>
<pre><code>create temporary tablespace pioneer_temp tempfile
'/home/oracle/backup/disk8/moon/pioneer_temp.dbf'
size 50 M
extent management local
uniform size 2 M;</code></pre>
<ul>
<li>检查数据字典和物理文件</li>
</ul>
<h2>还原表空间的管理</h2>
<ul>
<li>创建还原表空间</li>
</ul>
<pre><code>create undo tablespace jinlian_undo datafile
'/home/oracle/backup/disk7/moon/jinlian_undo.dbf'
size 50M;</code></pre>
<ul>
<li>管理还原表空间</li>
<li>增加25M的额外文件 /home/oracle/backup/disk8/moon/jinlian2_undo.dbf</li>
</ul>
<pre><code>alter tablespace jinlian_undo add datafile
'/home/oracle/backup/disk8/moon/jinlian2_undo.dbf'
size 25 M;</code></pre>
<ul>
<li>查看数据文件</li>
</ul>
<pre><code>select file_name,tablespace_name,bytes/(1024*1024) from dba_data_files where tablespace_name like'%JIN%' ;</code></pre>
<ul>
<li>查看还原表空间满了是否可以自动扩展</li>
</ul>
<pre><code>select tablespace_name,autoextensible,bytes/(1024*1024),file_name from dba_data_files where tablespace_name like '%JIN%';</code></pre>
<ul>
<li>不可扩展的改为可扩展</li>
</ul>
<pre><code>alter database datafile
'/home/oracle/backup/disk8/moon/jinlian2_undo.dbf'
autoextend on ;</code></pre>
<ul>
<li>undo表空间的应用</li>
</ul>
<pre><code>select name,value from v$parameter where name like '%undo%';</code></pre>
<ul>
<li>undo_retention=900，已经提交的数据在undo表空间中保留900秒，并非一定这样，空间不够避免停机会进行覆盖的</li>
<li>表的属性retention=noguarantee 时，表示不强制保留，retention=guarantee 时表示强制保留 undo_retention 设定的时间，空间不够事务停止</li>
<li>紧急要数据，数据查询近两小时，就要设置retention=guarantee，undo_retention=7200</li>
<li>查看表空间的属性</li>
</ul>
<pre><code>select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like '%U%';</code></pre>
<ul>
<li>设置undotbs1.retention=guarantee</li>
</ul>
<pre><code>alter tablespace undotbs1 retention guarantee ;</code></pre>
<ul>
<li>设置undo_retention=7200</li>
</ul>
<pre><code>alter system set undo_retention=7200 ;</code></pre>
<ul>
<li>使用完后立即设置回原始值，检查设置是否生效</li>
</ul>
<pre><code>alter tablespace undotbs1 retention noguarantee ;
alter system set undo_retention=900 ;</code></pre>
<ul>
<li>回滚数据的追踪：还原数据的统计信息的工具v$undostat</li>
</ul>
<pre><code>select to_char(begin_time, 'YYYY/MM/DD HH:MM:SS') end_time,
to_char(end_time, 'YYYY/MM/DD HH:MM:SS') begin_time,
undoblks 消耗数据块,
txncount 事务总数,
maxquerylen 最长查询
from v$undostat;</code></pre>
<ul>
<li>设置时间格式</li>
</ul>
<pre><code>alter session set nls_date_format='HH:MM:SS';</code></pre>
<ul>
<li>设置完成格式后可以直接在会话中使用如下查询，不再需要to_char()</li>
</ul>
<pre><code>select * from v$undostat ;</code></pre>
<ul>
<li>静态数据字典获取还原段的信息</li>
</ul>
<pre><code>select * from dba_rollback_segs ;</code></pre>
<ul>
<li>还可以使用如下动态数据字典</li>
</ul>
<pre><code>select * from v$rollname ;
select * from v$rollstat ;
select * from v$session ;
select * from v$transaction ;</code></pre>
<ul>
<li>12c特有：临时还原模式</li>
<li>临时还原数据的管理：temp_undo_enabled，不是很理解这一块</li>
</ul>
<pre><code>select * from v$parameter where name like '%undo%';
show parameter temp_undo;</code></pre>
<ul>
<li>compatible至少要设置为12.1.0.0.0</li>
</ul>
<pre><code>show parameter compatible;</code></pre>
<ul>
<li>物理数据库待机状态时是默认开启的，不影响参数文件</li>
<li>会话一级设置</li>
</ul>
<pre><code>alter session set temp_undo_enabled=true;</code></pre>
<ul>
<li>数据库一级设置</li>
</ul>
<pre><code>alter system seet temp_undo_enables=true;</code></pre>
<ul>
<li>对应地也有一个个类似v$undostat的动态数据字典</li>
</ul>
<pre><code>select * from v$tempundostat ;</code></pre>