pdb正常运行丢失数据文件相关恢复

发表于 2013 年 8 月 13 日 由 惜分飞

上篇文章(某个pdb可以在root pdb open状态下进行恢复)发布后,还是有不少朋友有各种争议,比如我的cdb本来就是open的,比如与oracle 12c 新特性文档描述不符等等,这里根据weibo上的各种争论和各位想的测试情况,我继续测试如下
查询数据库相关状态

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:49:47 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> select name from v$datafile where con_id=3;

NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

删除pdb system01.dbf文件

SQL> host dir E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
 驱动器 E 中的卷没有标签。
 卷的序列号是 000C-3B41

 E:\APP\XIFENFEI\ORADATA\CDB\PDB 的目录

找不到文件

做checkpoint操作

--删除pdb的数据文件(本测试中是system01.dbf),因为dbwr无法写入数据到文件，导致crash，在实际中也有可能是ckpt,lgwr进程等
SQL> alter system checkpoint ;
alter system checkpoint
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 9316
会话 ID: 136 序列号: 3165

alert日志中写明是dbwr进程无法写文件导致整个cdb crash

Tue Aug 13 12:51:10 2013
Thread 1 advanced to log sequence 316 (LGWR switch)
  Current log# 4 seq# 316 mem# 0: E:\APP\XIFENFEI\ORADATA\CDB\REDO04.LOG
Tue Aug 13 12:51:12 2013
Archived Log entry 5 added for thread 1 sequence 315 ID 0x7377d8de dest 1:
Tue Aug 13 12:51:16 2013
KCF: read, write or open error, block=0x6ad1 online=1
        file=7 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
        error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。'
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
ORA-63999: 数据文件出现介质故障
ORA-01114: 将块写入文件 7 时出现 IO 错误 (块 # 27345)
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
USER (ospid: 8612): terminating the instance due to error 63999
Tue Aug 13 12:51:17 2013
System state dump requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_diag_9720.trc
Dumping diagnostic data in directory=[cdmp_20130813125117], requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
Tue Aug 13 12:51:29 2013
Instance terminated by USER, pid = 8612

open cdb数据库

SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  521936896 bytes
Fixed Size                  2404552 bytes
Variable Size             293605176 bytes
Database Buffers          218103808 bytes
Redo Buffers                7823360 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'

--直接在cdb中无法offline pdb数据文件
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
第 1 行出现错误:
ORA-01516: 不存在的日志文件, 数据文件或临时文件 "7"


SQL> alter session set container=pdb;

会话已更改。

SQL> alter database datafile 7 offline;

数据库已更改。

SQL> conn / as sysdba
已连接。
SQL> alter database open;

数据库已更改。

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

恢复并open pdb

C:\Users\XIFENFEI>rman target /

恢复管理器: Release 12.1.0.1.0 - Production on 星期二 8月 13 12:56:54 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: CDB (DBID=1937199326)

RMAN> restore datafile 7;

启动 restore 于 13-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=361 设备类型=DISK

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在读取备份片段 D:\PDB_SYS_01OH54LF_1_1.RMAN
通道 ORA_DISK_1: 段句柄 = D:\PDB_SYS_01OH54LF_1_1.RMAN 标记 = TAG20130812T223943
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
完成 restore 于 13-8月 -13

RMAN> recover datafile 7;

启动 recover 于 13-8月 -13
使用通道 ORA_DISK_1

正在开始介质的恢复

线程 1 序列 314 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.
ARC 存在于磁盘上
线程 1 序列 315 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_315_90MGSZ0X_.
ARC 存在于磁盘上
线程 1 序列 316 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_316_90MH1FGD_.
ARC 存在于磁盘上
归档日志文件名=E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.ARC 线程=1 序列=314
介质恢复完成, 用时: 00:00:02
完成 recover 于 13-8月 -13

RMAN> exit


恢复管理器完成。


C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:58:42 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=pdb;

会话已更改。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01147: SYSTEM 表空间文件 7 处于脱机状态


SQL> alter database datafile 7 online;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> conn / as sysdba
已连接。
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

整个操作过程说明一下问题
1.当pdb丢失数据文件或者影响dbwr,lgwr,ckpt等后台进程工作之时,将导致整个cdb crash,因为这些进程都是公用的
2.如果pdb丢失数据文件,通过在pdb中offline该文件,可以正常open cdb和其他pdb,不会长时间影响其他pdb工作
3.在cdb open的情况下,可以恢复pdb的任何数据文件(特殊pdb除外,root,seed之类)

发表在 ORACLE 12C | 标签为 , , | 评论关闭

某个pdb可以在root pdb open状态下进行恢复

发表于 2013 年 8 月 13 日 由 惜分飞

最近在weibo上有流言:pdb的system还原的时候必须整个cdb处于mount状态下进行,我怎么也想不通ORACLE会这么蠢,如果这样,那如果一个pdb异常了,其他pdb都不能工作对业务影响太大了,经过测试证明事实是:某个pdb可以直接在root pdb open的状态下进行,不影响其他pdb

pdb的system文件备份

E:\dul10>rman target sys/xifenfei@pdb

恢复管理器: Release 12.1.0.1.0 - Production on 星期一 8月 12 22:38:58 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: CDB (DBID=1937199326)

RMAN> backup tablespace system format 'd:/pdb_sys_%U.rman';

启动 backup 于 12-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=367 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00007 名称=E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在启动段 1 于 12-8月 -13
通道 ORA_DISK_1: 已完成段 1 于 12-8月 -13
段句柄=D:\PDB_SYS_01OH54LF_1_1.RMAN 标记=TAG20130812T223943 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:15
完成 backup 于 12-8月 -13

启动 Control File and SPFILE Autobackup 于 12-8月 -13
段 handle=E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\AUTOBACKUP\2013_08_12\O1_MF_S_823300799_90KWY0OR_.BKP comment=NONE
完成 Control File and SPFILE Autobackup 于 12-8月 -13

使用系统命令强制删除pdb的system文件,使得open pdb之时提示文件不存在,然后使用rman进行还原恢复操作

还原恢复pdb system文件

E:\dul10>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production onXQ 星期一 8月 12 22:43:27 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Product
With the Partitioning, OLAP, Advanced Analytics and Real Application Test

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> alter session set container=pdb;

会话已更改。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'

SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

E:\dul10>rman target sys/xifenfei@pdb

恢复管理器: Release 12.1.0.1.0 - Production on 星期一 8月 12 22:44:39 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: CDB (DBID=1937199326, 未打开)

RMAN> restore datafile 7;

启动 restore 于 12-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=369 设备类型=DISK

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在读取备份片段 D:\PDB_SYS_01OH54LF_1_1.RMAN
通道 ORA_DISK_1: 段句柄 = D:\PDB_SYS_01OH54LF_1_1.RMAN 标记 = TAG20130812T223943
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:35
完成 restore 于 12-8月 -13

RMAN> recover database;

启动 recover 于 12-8月 -13
使用通道 ORA_DISK_1

正在开始介质的恢复
介质恢复完成, 用时: 00:00:01

完成 recover 于 12-8月 -13

RMAN> alter database open;

已处理语句

RMAN>

RMAN> exit


恢复管理器完成。

E:\dul10>slqplus / as sysdba
'slqplus' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

E:\dul10>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期一 8月 12 22:46:36 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
发表在 ORACLE 12C | 评论关闭

分区默认segment大小变化(64k—>8M)

发表于 2013 年 8 月 12 日 由 惜分飞

在11.2.0.3(从11.2.0.2开始)创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M,这里对于_partition_large_extents为true和false的情况进行了测试
_partition_large_extents=true

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> show parameter _partition_large_extents;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE

SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;

Table created.

--数据库延迟对象创建
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2 like 'SP%' group by TABLESPACE_NAME;

no rows selected

--只插入一个分区1,2013
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),1,'2013',
   2 rpad('aaafdfafd',4000,'b'));

1 row created.

SQL> commit;

Commit complete.

--所有分区全部都创建了segment
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name like 'SP%' 
   2 group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                           8
SP_2022                                           8
SP_2021                                           8
SP_2025                                           8
SP_2011                                           8
SP_2008                                           8
SP_MAX                                            8
SP_2020                                           8
SP_2012                                           8
SP_2010                                           8
SP_2024                                           8
SP_2019                                           8
SP_2015                                           8
SP_2014                                           8
SP_2013                                           8
SP_2023                                           8
SP_2017                                           8
SP_2016                                           8
SP_2009                                           8

19 rows selected.

SQL> begin      
  2  for i in 3 .. 200 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),mod(i,5),
     '2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

--只是在2013的分区(1,子分区2013)中插入了对象,但是其他分区也都创建了segment(extent)
SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name 
   2  like 'SP%' group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                          32
SP_2021                                          32
SP_2022                                          32
SP_2008                                          32
SP_2011                                          32
SP_2025                                          32
SP_2010                                          32
SP_2012                                          32
SP_2020                                          32
SP_MAX                                           32
SP_2015                                          32
SP_2019                                          32
SP_2024                                          32
SP_2013                                          40
SP_2014                                          32
SP_2023                                          32
SP_2009                                          32
SP_2016                                          32
SP_2017                                          32

19 rows selected.

SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SYS_SUBP128                    SP_2015
SYS_SUBP148                    SP_2015
SYS_SUBP168                    SP_2015
SYS_SUBP188                    SP_2015

--因为在创建表语句中有partition part_init values less than (1)，隐藏之类对于小于1的分区没有子分区，只有PART_INIT_SP_2013
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
PART_INIT_SP_2013              SP_2013
SYS_SUBP126                    SP_2013
SYS_SUBP146                    SP_2013
SYS_SUBP166                    SP_2013
SYS_SUBP186                    SP_2013

_partition_large_extents=false

SQL> alter system set "_partition_large_extents"=false;

System altered.

SQL> show parameter _partition_large_extents 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE

SQL> drop table test_com_partition_1 purge;

Table dropped.

SQL> alter system set deferred_segment_creation=true;

System altered.

SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;

Table created.

SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  
   2 where tablespace_name like 'SP%' group by TABLESPACE_NAME;

no rows selected

SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),
   2 1,'2013',rpad('aaafdfafd',4000,'b'));

1 row created.

SQL> commit;

Commit complete.

SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name 
   2 like 'SP%' group by TABLESPACE_NAME;

Tablespace           SUM(BYTES)/1024/1024
-------------------- --------------------
SP_2018                             .0625
SP_2021                             .0625
SP_2022                             .0625
SP_2008                             .0625
SP_2011                             .0625
SP_2025                             .0625
SP_2010                             .0625
SP_2012                             .0625
SP_2020                             .0625
SP_MAX                              .0625
SP_2015                             .0625
SP_2019                             .0625
SP_2024                             .0625
SP_2013                             .0625
SP_2014                             .0625
SP_2023                             .0625
SP_2009                             .0625
SP_2016                             .0625
SP_2017                             .0625

19 rows selected.

SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';

Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015

SQL>  select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';

Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP326          SP_2013

SQL> begin      
  2  for i in 3 .. 2000 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),
     mod(i,5),'2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';

Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015
SYS_SUBP348          SP_2015
SYS_SUBP368          SP_2015
SYS_SUBP388          SP_2015

SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';

Partition Name       Tablespace
-------------------- --------------------
PART_INIT_SP_2013    SP_2013
SYS_SUBP326          SP_2013
SYS_SUBP346          SP_2013
SYS_SUBP366          SP_2013
SYS_SUBP386          SP_2013

通过测试证明,设置_partition_large_extents参数确实是能够控制分区表的extent大小,而且对于分区表,deferred_segment_creation虽然为true，但是在一个分区表中如果有一个子分区插入了记录,那么其他子分区会同时创建segment.对于数据量不多，而且数据大量集中在某几个分区,那强烈建议设置_partition_large_extents为false,节约空间.如果数据量较大,而且数据分布较为均匀,建议设置_partition_large_extents为true.另外对于分区的index也有同样的参数为_index_partition_large_extents

发表在 Oracle | 2 条评论