oracle子分区表,Oracle分区表，子分区测试

分区表,Oracle分区表，子分区测试"/>

oracle子分区表,Oracle分区表，子分区测试

– Oracle分区表

– 在PDB1里面创建分区表

sqlplus cog/cog@pdb1

drop table temp_table purge;

CREATE TABLE temp_table

(

id VARCHAR2(32) DEFAULT sys_guid() NOT NULL,

datetime DATE,

filepath VARCHAR2(500 BYTE),

filestate CHAR(1 BYTE),

areacode VARCHAR2(20 BYTE),

fancode VARCHAR2(20 BYTE)

) nologging

partition by range(DATETIME) interval(numtoyminterval(1,‘year’))

subpartition by list(areacode)

subpartition template

(

subpartition part_0001 values (‘0001’) tablespace USERS,

subpartition part_0002 values (‘0002’) tablespace USERS,

subpartition part_0003 values (‘0003’) tablespace USERS,

subpartition p_other values (default)

)

(

partition sp1 values less than (to_date(‘2020-01-01’, ‘yyyy-mm-dd’)) tablespace USERS,

partition sp2 values less than (to_date(‘2021-01-01’, ‘yyyy-mm-dd’)) tablespace USERS

);

查询表分区数据信息：

select * from temp_table PARTITION(sp1);

select * from temp_table SUBPARTITION(SP1_PART_0001);

– 添加分区会不成功

alter table temp_table add partition sp2 values less than (to_date(‘2022-01-01’, ‘yyyy-mm-dd’)) tablespace USERS;

– oerr ora 14760 建不了分区，直接插入新的数据可以生成新的分区

–创建索引；分区键作为索引定义的第一列

create index indl_temp_table_fancode on temp_table (fancode) local;

set line 150 pages 90

col filepath format a60

col fancode format a20

col AREACODE format a10

alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

col table_name format a15

col DEF_TABLESPACE_NAME format a15

col PARTITION_NAME format a15

col HIGH_VALUE format a83

col table_name format a15

col PARTITION_NAME format a15

col SUBPARTITION_NAME format a15

set long 10000

查询表分区信息：

select * from user_tab_partitions a where a.table_name=upper(‘temp_table’); – 分区信息；

select * from user_tab_subpartitions a where a.table_name=upper(‘temp_table’); – 子分区信息；

SELECT table_name,partition_name,high_value from user_tab_partitions where table_name=‘TEMP_TABLE’; – 分区

select table_name,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE from user_tab_subpartitions a where a.table_name=upper(‘temp_table’); – 子分区

– 查看当前分区表

select table_name,partitioning_type,subpartitioning_type,partition_count,status,def_tablespace_name from user_part_tables order by 1;

查看范围分区表建表语句

select dbms_metadata.get_ddl(‘TABLE’,‘TEMP_TABLE’,‘COG’) from dual;

– 插入分区未指定分区，如果分区不存在，会创建新的分区

INSERT /+APPEND/ INTO temp_table

select

sys_guid() id,

sysdate +360 + rownum/86400 datetime,

dbms_random.string(‘x’, 60) filepath,

dbms_random.string(‘x’, 1) filestate ,

‘0009’ areacode,

dbms_random.string(‘x’, 20) fancode

from dual connect by level <= 10;

commit;

– 插入分区SP1_PART_0001(未指定logging)

INSERT /+APPEND/ INTO temp_table subpartition (SP1_PART_0001)

select

sys_guid() id,

sysdate -360 + rownum/86400 datetime,

dbms_random.string(‘x’, 60) filepath,

dbms_random.string(‘x’, 1) filestate ,

‘0001’ areacode,

dbms_random.string(‘x’, 20) fancode

from dual connect by level <= 10;

commit;

– 插入数据到子分区SP2_PART_0002

INSERT /+APPEND/ INTO temp_table subpartition (SP2_PART_0002) NOLOGGING

select

sys_guid() id,

sysdate + rownum/86400 datetime,

dbms_random.string(‘x’, 60) filepath,

dbms_random.string(‘x’, 1) filestate ,

‘0002’ areacode,

dbms_random.string(‘x’, 20) fancode

from dual connect by level <= 10;

commit;

– 查询子分区

select * from temp_table subpartition(SP1_PART_0001);

select * from temp_table subpartition(SP2_PART_0002);

– RMAN恢复数据文件

alter database datafile 28 offline;

! rm -f /data/oradata/RMANTEST/datafile/o1_mf_users_hd9op2gx_.dbf;

restore datafile 28;

recover datafile 28;

alter database datafile 28 online;

– 恢复之后查询

select * from temp_table subpartition(SP1_PART_0001);

select * from temp_table subpartition(SP2_PART_0002);

– 结论：

noforcelogging,建表的时候未指定nologging,insert append插入的时候，不管是否指定nologging，都会记日志

noforcelogging,建表的时候指定了nologging,insert append插入的时候，不管是否指定了nologging,都不会记日志

– 查询数据文件

col FILE_NAME format a60

col TABLESPACE_NAME format a15

col STATUS format a15

select file_name,tablespace_name,STATUS,file_id from dba_data_files;

FILE_NAME TABLESPACE_NAME FILE_ID

/data/oradata/RMANTEST/datafile/o1_mf_system_hd9oom7r_.dbf SYSTEM 26

/data/oradata/RMANTEST/datafile/o1_mf_sysaux_hd9omf1s_.dbf SYSAUX 27

/data/oradata/RMANTEST/datafile/o1_mf_users_hd9op2gx_.dbf USERS 28

– nologging报错信息

select * from temp_table subpartition(SP1_PART_0001); – 通过RMAN恢复不写日志的数据表，查询时会报错，表需要载断或重建

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 28, block # 530)

ORA-01110: data file 28: ‘/data/oradata/RMANTEST/datafile/o1_mf_users_hdyb96cl_.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

– 截断分区表

truncate table temp_table; – 截断整个分区表

alter table temp_table drop partition SP1 update global indexes; – 删除分区，同时维护全局索引 大分区表truncate partition后，需要对全局索引进行维护，否则，global index会变成unusabl

alter table temp_table truncate partition SP1 drop storage; – 截断指定的分区

alter table temp_table truncate partition SP1 drop storage update indexes;

alter table temp_table truncate partition SP1 drop storage update global indexes; –

列出受nologging影响的datafile

select UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME from v$datafile where file#=28;

RMAN > REPORT UNRECOVERABLE

RMAN > list backupset of datafile 28;