How To Move Or Rebuild A Lob Partition
How To Move Or Rebuild A Lob Partition [ID 761388.1]
改动时间 29-JUN-2010 类型 HOWTO 状态 MODERATED
In this Document
Goal
Solution
References
Platforms: 1-914CU;
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.
Goal
How to move or rebuild a LOB partition since if use traditional syntax, it will fail.
SQL> alter table SHOW_LOB_STORAGE move lob(DDD)
2 store as (tablespace PARTFORLOB03);
alter table SHOW_LOB_STORAGE move lob(DDD)
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
Solution
The syntax to move a LOB partition is the following.
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as <optional lob partition name> (tablespace <lob tablespace name>);
-or-
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as (tablespace <lob tablespace name>);
A working example.
SQL> connect testlob/testlob
Connected.
1. Create a partitioned table that contains a LOB.
SQL> create table show_lob_storage
2 (aaa number(5),
3 bbb varchar2(10),
4 ccc number(5),
5 ddd CLOB )
6 PARTITION BY RANGE(aaa)
7 (PARTITION p1 VALUES LESS THAN (50) tablespace part01
8 LOB (ddd) STORE AS (tablespace partforlob01),
9 PARTITION p2 VALUES LESS THAN (100) tablespace part02
10 LOB (ddd) STORE AS (tablespace partforlob02),
11 PARTITION p3 VALUES LESS THAN (MAXVALUE) tablespace part03
12 LOB (ddd) STORE AS (tablespace partforlob03));
Table created.
2. Show objects created on DB from above SQL.
SQL> set pagesize 10000
SQL> col segment_name format a25
SQL> col segment_type format a16
SQL> col tablespace_name format a13
SQL> col partition_name format a15
SQL> select segment_name, segment_type, tablespace_name,
2 partition_name from user_segments
3 order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NA PARTITION_NAME
------------------------- ---------------- ------------- ---------------
SHOW_LOB_STORAGE TABLE PARTITION PART01 P1
SHOW_LOB_STORAGE TABLE PARTITION PART02 P2
SHOW_LOB_STORAGE TABLE PARTITION PART03 P3
SYS_IL0000054529C00004$$ INDEX PARTITION PARTFORLOB01 SYS_IL_P150
SYS_IL0000054529C00004$$ INDEX PARTITION PARTFORLOB02 SYS_IL_P151
SYS_IL0000054529C00004$$ INDEX PARTITION PARTFORLOB03 SYS_IL_P152
SYS_LOB0000054529C00004$$ LOB PARTITION PARTFORLOB01 SYS_LOB_P147
SYS_LOB0000054529C00004$$ LOB PARTITION PARTFORLOB02 SYS_LOB_P148
SYS_LOB0000054529C00004$$ LOB PARTITION PARTFORLOB03 SYS_LOB_P149
9 rows selected.
3. Show the partition name corresponding to each LOB partition.
SQL> select partition_name, lob_partition_name, tablespace_name
2 from user_lob_partitions where table_name = 'SHOW_LOB_STORAGE';
PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NA
--------------- ------------------------------ -------------
P1 SYS_LOB_P147 PARTFORLOB01
P2 SYS_LOB_P148 PARTFORLOB02
P3 SYS_LOB_P149 PARTFORLOB03
4. Move LOB partition SYS_LOB_P147 to tablespace PARTFORLOB3.
SQL> alter table SHOW_LOB_STORAGE move partition P1 tablespace PART01
2 lob(DDD) store as SYS_LOB_P147 (tablespace PARTFORLOB03);
Table altered.
5. Show location of LOB partition after the move.
SQL> select partition_name, lob_partition_name, tablespace_name
2 from user_lob_partitions where table_name = 'SHOW_LOB_STORAGE';
PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NA
--------------- ------------------------------ -------------
P1 SYS_LOB_P147 PARTFORLOB03
P2 SYS_LOB_P148 PARTFORLOB02
P3 SYS_LOB_P149 PARTFORLOB03
6. Show the LOB index partition followed the LOB partition to new tablespace.
SQL> select partition_name, tablespace_name, status from user_ind_partitions;
PARTITION_NAME TABLESPACE_NA STATUS
--------------- ------------- --------
SYS_IL_P151 PARTFORLOB02 USABLE
SYS_IL_P152 PARTFORLOB03 USABLE
SYS_IL_P154 PARTFORLOB03 USABLE
7. Syntax if just want to rebuild a LOB partition on the existing tablespace.
SQL> alter table SHOW_LOB_STORAGE move partition P2
2 lob(DDD) store as (tablespace PARTFORLOB02);
Table altered.
最新文章
- android 入门-本地化语言
- 如何用MAT分析Android应用内存泄露
- scjp考试准备 - 5 - 重载和重写
- php获取指定日期所在星期的开始时间与结束时间
- title滚动
- [CLR via C#]5.1 基元类型
- ThreadPoolExecutor线程池参数设置技巧
- Lucene初体验——Hello Word实现
- react-router路由地址变了页面却没有跳转的解决办法
- 【数据结构】算法 LinkList (Insertion Sort List 链表插入排序)
- jquery怎么实现点击一个按钮控制一个div的显示和隐藏
- Zabbix-2.4-安装-2
- windwos平台安装phpredis模块
- ThinkPHP5微信扫码支付
- (转)Android DiskLruCache完全解析,硬盘缓存的最佳方案
- (新)解决php版本ueditor中动态配置图片URL前缀(imageurlprefix)的方法
- JPEGView——专业、免费、开源的图像浏览器
- Hibernate进阶学习4
- BZOJ 1036: [ZJOI2008]树的统计Count(树链剖分)
- kubernetes要实现的目标——随机关掉一台机器,看你的服务能否正常;减少的应用实例能否自动迁移并恢复到其他节点;服务能否随着流量进行自动伸缩
热门文章
- HDU2699 扩展欧几里德
- laravel-admin列表排序在使用了$grid->;model()->;latest()后$grid其它加上sortable()可排序的列在排序时不起作用
- 读书笔记--Head First Python 目录
- Prime Ring Problem HDU - 1016 (dfs)
- Elasticsearch连接类(带密码)
- python基础--类的继承以及mro
- centos6.5 zabbix2.2 亲测安装
- Oracle启动和禁用约束及删除违反约束的记录
- PAI-STUDIO通过Tensorflow处理MaxCompute表数据
- Spring 社区的首个国产开源项目顺利毕业