Oracle数据库分区表清理总结)

分区表的讲解
如何选择分区的类型，如何选择分区的列呢。在这之前你必须明确你的目的——易管理性和性能，你更注重哪个方面。分区表所影响的方面可以归类为以下几种：性能、易管理性、数据清理。
下面分别说说分区表对每一项的具体影响，
性能：
这一般是分区的主要目的。分区将大表变成了小表，当where之后的条件体现分区字段的具体值时，避免了全表扫描。
易于管理：
对于包含海量数据的大表，分区带来的易于管理性是非常明显的。当你建议一个基于非分区表的索引时，唯一的选择就是创建整个索引。如果表被分区，你就可以根据分区并行为此表创建索引，例如：
alter index par_ind_01 reuild partition yy05;
除此之外你还可以同时的做很多事情，像改变表所在表空间、导出表，删除表数据等等。
数据清理：
我们经常会需要删除表的一些历史数据，一般做法是delete，但是这会导致undo和redo的信息快速增长，而且影响数据库整体性能。这时我们就可以利用drop某个分区来完成此任务，例如：
alter table tab_a drop partition yy01;
当一个表的分区被删除，对应的local索引也同时被删除。如果还存在着global索引，那么它会变成unusable状态。为了避免此事情的发生，你可以使用：alter table tab_a drop partition yy01 update global indexes;
以上是互联网用户为您的的内容，在阿里云内部有更多的关于ORACLE 分区表的设计_oracle的内容，欢迎继续使用右上角搜索按钮进行搜索oracle、分区表、以便于您获取更多的相关信息。
Oracle分区表之间先区分一下分区表和表空间的个概念：
表空间：表空间是一个或多个数据文件的集合，所有数据对象都存放在指定的表空间中，但主要存放表，故称表空间。
分区表：分区致力于解决支持极大表和索引的关键问题。它采用他们分解成较小和易于管理的称为分区的片(piece)的方法。一旦分区被定义，SQL语句就可以访问的操作某一个分区而不是整个表，因而提高管理的效率。分区对于数据仓库应用程序非常有效，因为他们常常存储和分析巨量的历史数据。当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表分区的具体作用：
Oracle的分区表功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。
通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。
在什么时候使用分区表呢？
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。
表分区的优缺点：
优点：

1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

1、分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

2、分区致力于解决支持极大表和索引的关键问题。它采用他们分解成较小和易于管理的称为分区的片(piece)的方法

第一步：查看Oracle数据库的版本和使用的操作系统

     select * from v$version

1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2 PL/SQL Release 11.2.0.3.0 - Production
3 "CORE 11.2.0.3.0 Production"
4 TNS for HPUX: Version 11.2.0.3.0 - Production
5 NLSRTL Version 11.2.0.3.0 - Production
第二步：查看数据库的表空空间使用率
SELECT d.status "Status",

   d.tablespace_name "Name",
   d.contents "Type",
   d.extent_management "Extent Management",
   to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",
   to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
           '99999999.999') "Used (M)",
   to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",
   to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
           '990.00') "Used %"

FROM sys.dba_tablespaces d,

   (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_data_files
     GROUP BY tablespace_name) a,
   (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_free_space
     GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT

    (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

UNION ALL
SELECT d.status "Status",

   d.tablespace_name "Name",
   d.contents "Type",
   d.extent_management "Extent Management",
   to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
   to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",
   to_char((nvl(a.bytes / 1024 / 1024, 0)) -
           (nvl(t.bytes, 0) / 1024 / 1024),
           '99999999.999') "Free (M)",
   to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

FROM sys.dba_tablespaces d,

   (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_temp_files
     GROUP BY tablespace_name) a,
   (SELECT tablespace_name, SUM(bytes_cached) bytes
      FROM v$temp_extent_pool
     GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
ORDER BY "Used %" DESC
发现sjck_bz用户的表空空间的使用率已经到达97%。
统计sjck_bz用户的数据量大小。
SELECT T.OWNER,T.TABLE_NAME,T.NUM_ROWS,T.LAST_ANALYZED FROM DBA_TABLES T WHERE T.LAST_ANALYZED IS NOT NULL ORDER BY T.NUM_ROWS DESC
统计sjck_bz用户占用存储的大小。
SELECT t.segment_name,SUM(T.BYTES/1024/1024/1024) FROM DBA_SEGMENTS T group by t.segment_name order by SUM(T.BYTES/1024/1024/1024) desc

发现数据量和占用存储的最多10张表占用了3个T左右的表空间
FP_ZZSFP_HWMX
FP_ZZSPTFP
FP_ZZSZYFP_HWMX
FP_ZZSFP
FP_ZZSPTFP_HWXX
FP_ZZSZYFP
SB_ZZS_2013_FB2
CW_ZCFZB_XQYKJZZ
CW_ZCFZB_QYKJZZ
SB_ZZS_2013_FB1

第三步清理分区表
例如
查看哪个表占的空间
使用的视图是user_segments，查看这个表占用空间的大小
SELECT t.segment_name, SUM(t.bytes / 1024 / 1024)
FROM user_segments t where t.segment_name='FP_ZZSFP_HWMX'

查看每个子分区的记录

SELECT a.table_name,a.partition_name FROM user_tab_partitions a WHERE a.table_name ='FP_ZZSFP_HWMX';

检查分区表的索引是否LOCAL类型
--先查看该表的索引

SELECT * FROM User_Indexes a WHERE a.table_name='FP_ZZSFP_HWMX';

--在查看索引是否为local类型；若不存在；则为全局索引

select table_name,index_name,LOCALITY from user_part_indexes where table_name='FP_ZZSFP_HWMX' ;

如果都是LOCAL索引，直接清除表分区数据（如果是全局索引，清除完成后需要重建

本地索引在你删除子分区表的时候会跟着删除；不会影响。

--1.查看表大小情况

--记录的大小
select count(*) from FP_ZZSFP_HWMX;
select count(*) from FP_ZZSPTFP;
--物理的大小
select segment_name,sum(bytes)/1024/1024
from user_segments
where segment_name in ('FP_ZZSFP_HWMX',

                   'FP_ZZSPTFP') 

group by segment_name;
--2.查看表是否有触发器
select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS
from dba_triggers
where table_name in ('FP_ZZSFP_HWMX',

                   'FP_ZZSPTFP');  ---3.查看表结构情况--查看表信息

/*
通过temporary,partitioned,iot_type三个字段可知表的类型是否是全局临时表、是否是分区表、是否是索引组织表
通过degree字段可得是否有被设置为并行度的属性(值大于1)
通过观察last_analyzed字段值可知是否有正常收集，看有无值，或者是看时间是否很久以前的。
*/
select t.table_name,

    t.num_rows,
    t.blocks,
    t.degree,
    t.last_analyzed,
    t.temporary,
    t.partitioned,
    t.iot_type,
    t.pct_free,
    t.tablespace_name

from user_tables t
where table_name in ('FP_ZZSFP_HWMX',

                 'FP_ZZSPTFP')   ;                   
             
                  

--查看分区表相关信息(user_part_tables记录分区的表的信息,user_tab_partitions记录表的分区的信息）

--了解这些表的分区是什么类型的，有多少个分区
select t.table_name,

   t.partitioning_type,                                            
   t.partition_count                                               

from user_part_tables t
where table_name in ('FP_ZZSFP_HWMX',

                  'FP_ZZSPTFP') ;
     了解这些表以什么列作为分区                                

select name,object_type, column_name
from user_part_key_columns
where name in ('FP_ZZSFP_HWMX',

            'FP_ZZSPTFP') ;                 

--了解这些表的分区范围是多少
SELECT table_name,partition_name, high_value, tablespace_name
FROM user_tab_partitions t
where table_name in ('FP_ZZSFP_HWMX',

                  'FP_ZZSPTFP') 

order by table_name,t.partition_position;

ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201305;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201306;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201307;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201308;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201309;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201310;
提供了两种方法

删除分区,数据也自动删除
alter table tableName DROP PARTITION partionName;
-- 只删除数据
alter table tableName TRUNCATE PARTITION partionName;

注意：

Oracle中删除的分区不会进入回收站（Recyclebin）

在Oracle数据库中，单个删除的分区并不会进入回收站，全表删除的分区才可能和全表一起放入回收站。这是因为单个分区删除之后，是无法通过简单的闪回加入原分区表中，既然无法保证一致性，这个分区就不会进入回收站中

第四步：
收缩表空间降低高水位

1.了解段收缩
2.自动执行Segment Advisor
3.收缩段

1. 了解段收缩

应用场景：如果对一张表频繁执行插入、更新和删除操作，时间长了可能会出现大量碎片，Oracle针对这种场景推出段收缩功能，以便减少碎片。
Oracle的段收缩执行两项不同的任务：(1)压缩数据行，(2)移动高水位线（HWM）。
因为重新定位HWM可能阻塞用户的DML操作，而存粹的收缩操作则不会产生这种影响。
操作期间会维护表上的所有索引。

1. 自动执行Segment Advisor
   服务器 -> Oracle Scheduler -> 自动维护任务 -> 配置，来启用或禁用“段指导”任务，同时这里还可以设置“优化程序统计信息搜集”，“自动 SQL 优化

2.2 使用SQL命令设置

--禁用自动执行Segment Advisor
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;
/

--启用自动执行Segment Advisor
BEGIN
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;

3.3 设置启动Segment Advisor作业
放在命令执行
variable task_id number;
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name := ''; --unique name
descr := 'Check FP_ZZSFP_HWMX table';
dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, null);
dbms_advisor.create_object(name, 'TABLE', 'SJCK_BZ', 'FP_ZZSFP_HWMX', null, null, obj_id);
dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.execute_task(name);
end;
print task_id

TASK_ID

  1358

根据上面的TASK_ID查询DBA_ADVISOR_FINDINGS

select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id=FP_ZZSFP_HWMX;

MESSAGE 查出可以收缩100G的表空间左右
执行段收缩

alter table FP_ZZSFP_HWMX enable row movement;
alter table FP_ZZSFP_HWMX shrink space;

select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name = 'FP_ZZSFP_HWMX';
发现这个表占用存储空间缩小了100G左右

注意shrink命令有两个限制:

一. 没法在基于Oracle 10g上的LOB Segment上工作.

二. 不允许在包含任何函数索引规则的表进行操作,不管是使用10g或11g.

第五步：
由于删除了分区表字分区导致全局索引和主键索引失效，现在要重建全局索引和主键索引
注意：注意：注意
（删除分区只会导致全局索引和主键索引失效，本地索引不会失效，所以要重建全局索引和主键索引）

删除分区,数据也自动删除
alter table tableName DROP PARTITION partionName;
-- 只删除数据
alter table tableName TRUNCATE PARTITION partionName;

查看索引失效命令：
登录sjck_bz用户查看
select t.index_name,t.table_name,t.status,t.last_analyzed from user_indexes t where t.table_name='FP_ZZSFP';
查看具体的分区表的情况
select * from user_ind_partitions

重建主键索引先删除删除主键

注意由于创建主键的时候，会默认创建创建主键索引，无法使用nologging 和parallel，导致创建主键唯一索引非常慢，500G的数据量，我这相需要大约5个小时
非常慢。所以考虑一种方法先创建主键唯一索引（nologging 和parallel）然后创建主键，最后再关闭并行（使用这种方法可以25分钟可以建完）
具体如下(主键索引和主键名称都是一样的)
总结：
对于已有大量数据的表无法并行建立主键，可以先并行建立惟一性索引然后再加上主键。
主键不能为空，惟一性索引可以为空。
1: alter table FP_ZZSFP drop constraint PK_FP_ZZSFP;
2: drop index PK_FP_ZZSFP;
3：create unique index PK_FP_ZZSFP on FP_ZZSFP (xxx, xxx, xxx) nologging parallel 8 online ;
我们在用create index inx_1 on t(col1)创建索引时（表数据量巨大时）oralce会lokc会全表,这时如果是生产系统，可能会引起重大事故，所有的dml操作均需要等待.
但有时我们为了创建索引时不影响生产系统的dml执行，可以创建索引时使用online关键字,这样，创建的索引时，再执行dml时，不会阻塞dml操作
4：alter table FP_ZZSFP add constraint PK_FP_ZZSFP primary key (FP_ID, FPHM, KPYF_ID);
5：alter index PK_FP_ZZSFP noparallel;
6：select t.degree from dba_indexes t where t.index_name='PK_FP_ZZSFP';

第六步：
收集表的统计信息
begin
dbms_stats.gather_table_stats(
OWNNAME=>'SJCK_BZ', --用户名
TABNAME=>'FP_ZZSFP',--表名
ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size repeat',
DEGREE=>8,
GRANULARITY=>'ALL',
CASCADE=> true,
NO_INVALIDATE=>false
);
end;
/

500G的数据量收集大约需要40分钟。

第七步：统计表空间每天的数据增长量
在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息：DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.
抓AWR快照. 默认是开启的.然后查
SYS@ora11g> select SNAP_ID,ts.name,round(TABLESPACE_SIZE blocksize/1048576) TABLESPACE_SIZE_mb,round(TABLESPACE_USEDSIZEblocksize/1048576) TABLESPACE_USED_maxSIZE,RTIME from DBA_HIST_TBSPC_SPACE_USAGE tsu,ts$ ts where tsu.TABLESPACE_ID = ts.ts# and ts.name='USERS' order by SNAP_ID,ts.name;

SNAP_ID NAME TABLESPACE_SIZE_MB TABLESPACE_USED_MAXSIZE RTIME

如何估算oracle 数据库，数据库对象历史增长情况

-----最近七天数据库的增长情况，这个只是一个估算值。

select sum(space_used_total)/1024/1024/1024 "last 7 days db increase - G"
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and begin_interval_time > sysdate-8
order by
begin_interval_time
/

以下再补充两个类似的脚本脚本来之网上：

scripts:查看数据库历史增长情况

查看数据库历史增长情况
此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。
--不含undo和temp

with tmp as
(select rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in ('TEMPORARY','UNDO'))
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select max(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) FP_ZZSPTFP
where FP_ZZSPTFP.rtime = tmp.rtime;

--含undo和temp

with tmp as
(select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME)
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select min(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) FP_ZZSPTFP where FP_ZZSPTFP.rtime = tmp.rtime

SQL脚本:列出相关段对象在 快照时间内的使用空间的历史变化信息：

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in ('SYS','SYSTEM')
and end_interval_time between to_timestamp('17-FEB-2014','DD-MON-RRRR')
and to_timestamp('25-FEB-2014','DD-MON-RRRR')
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name ;

第八步：查看每天的抽取情况
select * from T_CTL_LOG_FLOW A where A.FLOW_ID like 'ETL_%' order by to_number(A.batch_ID) desc;