利用Oracle在线重定义Online Redefinition清理历史数据

我在<了解Oracle在线重定义Online Redefinition>一文中介绍了Oracle在线重定义的特点及其使用步骤,Online Redefinition的适用场景很多,包括:

 

Modify the storage parameters of a table or cluster Move a table or cluster to a different tablespace Add, modify, or drop one or more columns in a table or cluster Add or drop partitioning support (non-clustered tables only) Change partition structure Change physical properties of a single table partition, including . . . → Read More: 利用Oracle在线重定义Online Redefinition清理历史数据

Slide:了解Oracle在线重定义online redefinition

Slideshare版的了解Oracle在线重定义online redefinition,可供下载,下载地址。

 

了解Oracle在线重定义online redefinition

View more documents from Maclean Liu

了解Oracle在线重定义Online Redefinition

online redefinition

Online Redefinition在线重定义对象是Oracle中很酷的一种特性,它可以帮助我们在7*24在线的系统中从容地做出数据对象的在线定义修改,是Oracle数据库保证其高可用性的重要技术。

 

 

 

在线重定义Online Redefinition特性在许多场景中都是十分有用的,例如:

修改表的Storage存储参数 在同一Schema下将表移动到不同的表空间 转换非分区表为分区表 添加或删除分区 重新创建表以减少碎片,降低高水位 将堆组织的表改变为索引组织表 添加或删除列

 

使用Online Redefinition在线重定义需要用到DBMS_REDEFINITION程序包,EXECUTE_CATALOG_ROLE角色默认被赋予该PL/SQL Package的执行权限。除了执行该程序包的权限外,用户还需要拥有以下权限:

CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE LOCK ANY TABLE SELECT ANY TABLE

 

若要执行COPY_TABLE_DEPENDENTS存储过程则还需要以下权限:

CREATE ANY TRIGGER CREATE ANY INDEX

 

 

在早期版本中在线重定义(Online Redefinition)对于某些具有特殊数据类型的表存在诸多限制,从Oracle 10g开始拥有以下数据类型的表也支持在线重定义(Online Redefinition)了:

 

存在LONG类型column字段的表可以被在线重定义(redefined online);但是LONG类型字段只能被转换为character large object即CLOB . . . → Read More: 了解Oracle在线重定义Online Redefinition

Migrating PeopleSoft Applications from IBM DB2 to Oracle

Database Migration Planning Build PeopleSoft Demo environment on Oracle Platform Identify Unicode requirement Identify customer code customizations that exist in both online and batch components for DB2 SQL specific syntax Identify any record structure changes that need to be modified for Oracle platform requirements (multiple longs, DATE fields) Validate tablespace strategy required for target Oracle . . . → Read More: Migrating PeopleSoft Applications from IBM DB2 to Oracle

Migrating Away from Sybase to Oracle

Migration process Migration process Convert the schema and create tables in Oracle Move data into Oracle Convert stored procedures Make required changes in client code Document everything

Here are the steps when the client code is conserved. A migration is not necessarily a complete rewriting.

Quite often, before the partner makes the decision to port . . . → Read More: Migrating Away from Sybase to Oracle

Migrate from MySQL to Oracle

Oracle’s Migration Strategy Reduce Risk Proven migration methodology Migration Center of Excellence Extensive migration knowledge base Shorten the Timeline Migration tools Oracle Migration Workbench, Oracle Application Migration Assistant Migration scripts MySQL plug-in is third most popular down load after SQLServer and Access Best practices and proven methodologies Focus on databases and applications MySQL Migration Process . . . → Read More: Migrate from MySQL to Oracle