年归档：2013

分区默认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 条评论

数据库中记录时间和现实中时间相互转换

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

数据库中记录时间和现实中时间相互转换（如同文件头的kcvfhcrt和v$datafile_header.CREATION_TIME相互转换）
以前写过类似文章，这里提供具体的sql转换语句数据文件的CREATION_TIME来源和算法

--十进制转换为时间
set serveroutput on 
declare
v_yyyy number;
v_mm number;
v_dd number;
v_hh number;
v_mi number;
v_ss number;
begin
select floor(&&crt_num/32140800) into v_yyyy from dual;
select floor((&&crt_num-v_yyyy*32140800)/2678400) into v_mm from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400)/86400) into v_dd from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400)/3600) into v_hh from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600)/60) into v_mi from dual;
select (&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600-v_mi*60) into v_ss from dual;
dbms_output.put_line((1988+v_yyyy)||'-'||(1+v_mm)||'-'||(1+v_dd)||' '||v_hh||':'||v_mi||':'||v_ss);
end;
/

--时间转换为十进制
select 
((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'DD'))-1))*24*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'HH24'))*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MI'))*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'SS')))
from dual;

发表在 Oracle | 评论关闭

plug pdb xml文件不正确错误提示

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

经过测试,发现pdb插入cdb的过程中,对于xml的错误提示功能非常强大,在简单的测试过程中,发现出tablespace部分不能正常提示正确值之外,其他都可以完美的提示正常值,根据他的提示去修改值即可,下面是测试过程中主要的错误值和正确值提示

<cid>2</cid>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of cid (2 in the plug XML file, 4 in the data
file)


<afn>16</afn>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of afn (16 in the plug XML file, 19 in the data
file)


<guid>E0408583F149457AE043161EA8C08888</guid>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of guid (E0408583F149457AE043161EA8C08888 in the
plug XML file, E0408583F149457AE043161EA8C0E5B7 in the data file)


<rdba>4094824</rdba>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of rdba (4094824 in the plug XML file, 4194824
in the data file)


<createscnbas>3283924</createscnbas>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of createscnbas (3283924 in the plug XML file,
3289484 in the data file)


<fcpsb>3289191</fcpsb>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of fcpsb (3289191 in the plug XML file, 3291031
in the data file)

对于表空间部分的这几个值，不能很好的验证，如果人工编辑，需要注意这些值，都可能出现类似错误ORA-65064,提示不太友好（但是测试中表空间名称不正确可以插入进去)

 <name>SYSAUX1</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>

<name>SYSTEM</name>
    <type>0</type>
    <tsn>1</tsn>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65064: 插件 XML 文件的内容不正确

在正常的情况下,如果是做nocdb插入到cdb中或者是pdb的unplug/plug建议直接使用生成的xml文件,不需要人工去编辑它(如果要去修改它,你需要理解它的xml规则)
根据pdb的插入如此强大功能,提供给我们单独pdb数据库异常恢复很好的帮助,相关细节在后续章节揭晓,关于pdb的数据库一些异常恢复

发表在 ORACLE 12C | 评论关闭

17813235971

QQ 咨询

年归档：2013

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

数据库中记录时间和现实中时间相互转换

plug pdb xml文件不正确错误提示

文章分类

最近发表

年归档：2013

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

数据库中记录时间和现实中时间相互转换

plug pdb xml文件不正确错误提示

标签云

文章分类

最近发表