表空间的管理

创建本地管理表空间

• 查看所有表空间$数据文件位置

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_%';

• 创建表空间，两个数据文件路径如下

/home/oracle/backup/disk2/orcl/jinlian01.dbf
/home/oracle/backup/disk2/orcl/jinlian02.dbf

• 创建表空间语句，每个段大小是1M，本地管理

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;

• 查看表空间及其数据文件

• MLDN_DATE、MLDN_TEMP、PIONEER_DATA、PIONEER_INDX、JINLIAN学会怎么删除表空间的时候，回来删除掉

创建还原表空间

• 还原表空间数据文件如下，其大小20M

/home/oracle/backup/disk7/orcl/jinlian_undo.dbf

• 创建语句

create undo tablespace jinlian_undo 
datafile '/home/oracle/backup/disk7/orcl/jinlian_undo.dbf'
size 20M ;

• 查看表空间及其数据文件，另外也要查看物理文件否存在

创建临时表空间

• 临时表空间数据文件如下，其大小创建为10M,相关目录要手动创建，不然就会报错,

/home/oracle/backup/disk8/orcl/jinlian_temp.dbf

• 查看临时表空间及其数据文件

• 创建临时表空间语句,uniform size 2 M是show parameter sort_area_size的整倍数，效率更高

create temporary tablespace jinlian_temp
tempfile '/home/oracle/backup/disk8/orcl/jinlian_temp.dbf'
size 10M
extent management local
uniform size 2 M;

• 查看临时表空间及其数据文件

默认临时表空间

• 查看当前默认临时表空间

select * from database_properties where property_name like 'DEFAULT_TEMP%';

• 修改默认临时表空间

alter database default temporary tablespace jinlian_temp;

• 修改回来原来的默认临时表空间

alter database default temporary tablespace temp;

设置表空间为脱机状态，该变化会写到数据字典、控制文件、报警文件

• 查看表空间的状态

select tablespace_name,status,contents from dba_tablespaces where tablespace_name like 'JINL%';

• 查看数据文件的状态

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%';

• 设置表空间为脱机状态
  alter tablespace jinlian offline ;

• 设置表空间为联机状态
  alter tablespace jinlian online;

设置表空间为只读模式

• 查看status=online，联机才能被设置为只读，执行完后status=read only
  select tablespace_name,status,contents from dba_tablespaces where tablespace_name like 'JINL%';
• 设置jinlian表空间为read only状态
  alter tablespace jinlian read only ;
• 设置回读写状态
  alter tablespace jinlian read write ;
• 该记录写到报警文件可，去查看一下报警文件

select value from v$diag_info where name='Diag Trace';
[oracle@oracle trace]$ adrci
adrci> show alert -tail 10 --可以看到以上修改信息

重置表空间的大小

• 数据字典管理的、本地管理的，重置方法不同
• 本地管理的：改变数据文件的大小，增加数据文件的数量
• 查看表空间对应的数据文件能否自动扩展

select file_id,tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name like 'JINL%';

• 设置数据文件自动扩展

alter database datafile
'/home/oracle/backup/disk2/orcl/jinlian01.dbf'
autoextend on
next 1M;

• 手工重置数据文件的大小
• 查看数据文件的大小
  select file_id,file_name,tablespace_name,bytes/(1024*1024) mb from dba_data_files where tablespace_name like 'JIN%';
• 增加数据文件的大小到100M
  alter database datafile '/home/oracle/backup/disk2/orcl/jinlian01.dbf' resize 100M;
• 为jinlian表空间增加数据文件

/home/oracle/backup/disk4/orcl/jinlian03.dbf
alter tablespace jinlian add datafile 
'/home/oracle/backup/disk4/orcl/jinlian03.dbf'
size 80M;

移动数据文件

• 两种方法：表空间可置为脱机状态、表空间不可置为脱机状态

表空间可置为脱机的移动方法

• 查看表空间信息
  select file_name,tablespace_name,online_status from dba_data_files where tablespace_name like 'JINL%';

• 设置表空间为脱机状态
  alter tablespace jinlian offline ;

• 移动数据文件，到源数据文件的目录下移动
  [oracle@oracle orcl]$ cp jinlian03.dbf /home/oracle/backup/disk8/orcl/jinlian03.dbf

• 执行数据文件重命名命令

alter tablespace jinlian rename datafile
'/home/oracle/backup/disk4/orcl/jinlian03.dbf'
to
'/home/oracle/backup/disk8/orcl/jinlian03.dbf';

• 将表空间设置为联机状态
  alter tablespace jinlian online ;

• 删除旧的数据文件，整个变化过程都要确认的

表空间不可置为脱机状态的情况

• 有两个要求：数据库置于mount状态，目标数据文件必须存在，示例移动system表空间，文件：/u01/app/oracle/oradata/orcl/system01.dbf
• 关闭数据库SQL> shutdown immediate
• 移动源数据文件到目标数据文件

[oracle@oracle orcl]$ cp /u01/app/oracle/oradata/orcl/system01.dbf  /home/oracle/backup/disk8/orcl/system01.dbf

• 置数据库于mount状态SQL> startup mount

• 执行移动语句

alter database ORCL rename file 
'/u01/app/oracle/oradata/orcl/system01.dbf'
to
'/home/oracle/backup/disk8/orcl/system01.dbf';

• 打开数据库
  alter database open;

• 使用数据字典查看移动的情况，不要的文件可以删除，试验完全恢复原始状态

• 同样的操作移动回来，旧的数据文件不能使用了，要重新移动一次

alter database ORCL rename file 
'/home/oracle/backup/disk8/orcl/system01.dbf'
to
'/u01/app/oracle/oradata/orcl/system01.dbf';

删除表空间

• 查看表空间的情况

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

• 无参数删除MLDN_DATE，ERROR：tablespace not empty, use INCLUDING CONTENTS option。
• 无and datafiles 参数，要手动删除物理文件

SQL> drop tablespace mldn_date ;
SQL> drop tablespace mldn_date including contents;

• 再试试无参数删除jinlian，无活动段可删除，手动删除数据文件
  SQL> drop tablespace jinlian ;

• 删除表空间同时删除数据文件

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 ;

• 删除临时表空间，查看临时表空间数据文件的路径使用vtablespace 数据字典

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

• 默认临时表空间不能删除，查看默认临时表空间是哪个

select * from database_properties where property_name like 'DEFAULT_%';
DEFAULT_TEMP_TABLESPACE=TEMP;--TEMP临时表空间不能删除，如果要删除要修改默认临时表空间

• 修改默认临时表空间

SQL> alter database default temporary tablespace 要设置的默认临时表空间名;

• 删除临时表空间，带参数
  SQL> drop tablespace mldn_temp ;
• 删除临时表空间带参数，书中说删除临时、还原表空间使用参数不删除数据文件，我试的能删除，所以每次做变更都要检查变更是否生效。
  SQL> drop tablespace jinlian_temp including contents and datafiles ;
  可能要重启数据库才可以删除

学会如何使用OMF管理表空间和数据文件

• 创建表空间的应用实例

为先驱工程创建四个表空间如下：都是本地管理
先驱工程数据表空间 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

• 查看原来的表空间的数据文件、表空间、临时表空间

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# ;

• 创建表空间语句

create tablespace pioneer_data datafile 
'/home/oracle/backup/disk2/moon/pioneer_data.dbf'
size 100 M
extent management local
uniform size 1 M;

• 与数据表空间一样的创建方法

create tablespace pioneer_indx datafile 
'/home/oracle/backup/disk4/moon/pioneer_indx.dbf'
size 100 M
extent management local
uniform size 1 M;

• 指定为undo表空间，不需要增量uniform 1 M

create undo tablespace pioneer_undo datafile 
'/home/oracle/backup/disk6/moon/pioneer_undo.dbf'
size 50 M
extent management local;

• 注意指定临时表空间，文件是tempfile，2是那个参数的倍数，速度更快

create temporary tablespace pioneer_temp tempfile 
'/home/oracle/backup/disk8/moon/pioneer_temp.dbf'
size 50 M
extent management local
uniform size 2 M;

• 检查数据字典和物理文件

还原表空间的管理

• 创建还原表空间

create undo tablespace jinlian_undo datafile
'/home/oracle/backup/disk7/moon/jinlian_undo.dbf'
size 50M;

• 管理还原表空间
• 增加25M的额外文件 /home/oracle/backup/disk8/moon/jinlian2_undo.dbf

alter tablespace jinlian_undo add datafile
'/home/oracle/backup/disk8/moon/jinlian2_undo.dbf'
size 25 M;

• 查看数据文件

select file_name,tablespace_name,bytes/(1024*1024) from dba_data_files where tablespace_name like'%JIN%' ;

• 查看还原表空间满了是否可以自动扩展

select tablespace_name,autoextensible,bytes/(1024*1024),file_name from dba_data_files where tablespace_name like '%JIN%';

• 不可扩展的改为可扩展

alter database datafile 
'/home/oracle/backup/disk8/moon/jinlian2_undo.dbf'
autoextend on ;

• undo表空间的应用

select name,value from v$parameter where name like '%undo%';

• undo_retention=900，已经提交的数据在undo表空间中保留900秒，并非一定这样，空间不够避免停机会进行覆盖的
• 表的属性retention=noguarantee 时，表示不强制保留，retention=guarantee 时表示强制保留 undo_retention 设定的时间，空间不够事务停止
• 紧急要数据，数据查询近两小时，就要设置retention=guarantee，undo_retention=7200
• 查看表空间的属性

select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like '%U%';

• 设置undotbs1.retention=guarantee

alter tablespace undotbs1 retention guarantee ;

• 设置undo_retention=7200

alter system set undo_retention=7200 ;

• 使用完后立即设置回原始值，检查设置是否生效

alter tablespace undotbs1 retention noguarantee ;
alter system set undo_retention=900 ;

• 回滚数据的追踪：还原数据的统计信息的工具v$undostat

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;

• 设置时间格式

alter session set nls_date_format='HH:MM:SS';

• 设置完成格式后可以直接在会话中使用如下查询，不再需要to_char()

select * from v$undostat ;

• 静态数据字典获取还原段的信息

select * from dba_rollback_segs ;

• 还可以使用如下动态数据字典

select * from v$rollname ;
select * from v$rollstat ;
select * from v$session ;
select * from v$transaction ;

• 12c特有：临时还原模式
• 临时还原数据的管理：temp_undo_enabled，不是很理解这一块

select * from v$parameter where name like '%undo%';
show parameter temp_undo;

• compatible至少要设置为12.1.0.0.0

show parameter compatible;

• 物理数据库待机状态时是默认开启的，不影响参数文件
• 会话一级设置

alter session set temp_undo_enabled=true;

• 数据库一级设置

alter system seet temp_undo_enables=true;

• 对应地也有一个个类似v$undostat的动态数据字典

select * from v$tempundostat ;