ORACLE修改表空间方法-阿里云开发者社区

开发者社区> 长征2号> 正文

ORACLE修改表空间方法

简介:
+关注继续查看
一、使用imp/exp。先导出源库,再创建新库把表空间创建好,然后再导入。(据说这样可以,前提是新的库里面不能有与源库相同名字的表空间。有待验证!) 

二、使用脚本进行修改。据目前所了解,正长情况下需要修改表的空间表的索引的空间,如果涉及到BOLB字段的表,修改的方式又不一样了! 
正常情况下的修改脚本: 
1.修改表的空间 
alter table TABLE_NAME move tablespace TABLESPACENAME 

查询当前用户下的所有表 
select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables; 

2.修改表的索引的空间 
alter index INDEX_NAME rebuild tablespace TABLESPACENAME 

查询当前用户下的所有索引 
select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes; 

可以使用脚本执行查询的结果,这样就可以批量处理! 

不正常情况即含有BLOB字段的表: 
参考下面的文档 

本人已经执行过,是可行的,但是不知道有没有漏掉数据库中其它对象。是否还存在有对象的表空间需要修改,需要进一步发现或有数据库高手帮忙讲解则不胜感激!目前的修改在项目中还没有出现问题! 

在移植看注意研究了下ORACLE ALTER TABLE MOVE 的语法: 

       ALTER TABLE table_name MOVE [ONLINE] tablespace_name; 
通过上面的语句可以移植表到新表空间, 

如果要移植LOB字典需要参考以下语法: 
ALTER TABLE table_name LOB (lob_item) STORE AS [lob_segment] 
      ( 
        TABLESPACE tablespace_name 
                   (STORAGE.....) 
           ENABLE|DISABLE STORAGE IN ROW 
           CHUNK integer 
           PCTVERSION integer 
            RETENTION 
            FREEPOOLS integer 
            CACHE|NOCACHE|CACHE READS 
           INDEX lobindexname 

(TABLESPACE tablesapce_name 

((STORAGE.....)) 


.... 

注解: 

LOB (lob_item):表中的lob字段 
STORE AS [lob_segment]:每个lob字段在表创建后系统都会自动单独创建一个段,可以通过这个参数手动指定一个段名 
   tablespace_name:LOB字段新的存储表空间 
(STORAGE.....):指定tablespace_name的存储属性 
    ENABLE STORAGE IN ROW:如果设置了enable storage in row 那么oracle会自动将小于4000bytes的数据存储在行内, 这是ORACLE的默认值,对于大于4000字节的lob字段保存在lob段(同disable storage in row),在表段将保留36-84字节的控制信息。对于disable storage in row,Oracle将lob字段分开保存在lob段中,而仅仅在行位置保留20字节的指针。对于相当于disable storage in row的这部分(也就是单独保存在LOB段的这部分数据),UNDO仅仅是记录指针与相关lob索引改变,如果发生更新操作等DML操作,原始数据将保留在LOB段。 

    DISABLE STORAGE IN ROW:如果DISABLE这个属性,那么lob数据会在行外存储,行内只存储该lob值得指针,而且这个属性在表 
创建后只能在MOVE表时才可以被改变 
    CHUNK:是一个很特别的属性,对一次LOB数据的操作(插入或更新),因该分配多少存储空间,指定的值最好是数据库块的倍数,而且指定的值不能大于表空间区间中NEXT的值, 要不然ORACLE会return一个错误,如果以前已经设置这个值了,那么在后期指定的值是不能被改变的。 

storage as ( CHUNK bytes )表示对于disable storage in row的这部分,最小的LOB块的大小,必须是数据库块(DB_BLOCK_SIZE)的整数倍。一个chunk最多只保留一行LOB数据,也就是说,如果你设置了32K的CHUNK,但是如果LOB字段大小只有4K,也将占用32K的空间 

storage as(cache|nocahce)表示是否允许lob段经过buffer cache并缓存。默认是nocache,表示直接读与直接写,不经过数据库的data buffer。所以,默认情况下,对于单独保存在LOB段的这部分数据,在发生物理读的时候,是直接读,如direct path read (lob) 

storage as(nocache logging |nocache nologging),logging/nologging属性只对nocache方式生效,默认是logging,如果是nologging方式,对于 保存在行外的log部分,在update等DML操作时将不记录redo日志。 

    PCTVERSION integer、RETENTION:都是ORACLE用来管理LOB字段镜像数据的。在LOB 数据的更新过程中, 
ORACLE没有用UNDO TABLESPACE空间,而是从LOB字段所在的表空间里划分一段空间来做镜像空间的, 
这个空间的大小由PCTVERSION参数控制,默认值为10,代表划分表空间的10%作为镜像空间, 
每个镜像空间的单元大小由CHUNK参数指定,pctversion可以使用在manual undo mode和automatic undo mode 环境中. 
retention应用了automatic undo mode中的undo_retention通过时间来管理lob镜像空间. 
pctversion和retention不能同时被指定.建议数据库在automatic undo mode下使用retention参数。 
FREEPOOLS integer:给LOG segment指定free list.RAC环境下integer为实例的个数.单实例环境下为1.在automatic undo mode下oracle默认采用 
FREEPOOLS来管理空闲块列表。除非我们在表的storage配置中指定了freelist groups参数. 
CACHE|NOCACHE|CACHE READS:指定lob块是否在database buffer中缓存. 
INDEX lobindexname (TABLESPACE tablesapce_name ((STORAGE.....):给lob列指定索引存储参数 
举例: 
SQL> show parameter db_create_file_dest 

SQL> create tablespace test datafile size 100M autoextend off; 
SQL> create table test(a varchar2(100), b clob, d blob) pctfree 10 tablespace test; 

SQL> desc test 
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST'; 

我们发现每个LOB字段单独有一个LOGSEGMENT和LOBINDEX; 

SQL> set linesize 200 
col table_name format a5 
col column_name format a5 
SELECT b.table_name, 
a.segment_name, 
b.index_name, 
a.segment_type, 
b.column_name, 
a.tablespace_name, 
b.chunk, 
b.cache, 
b.freepools, 
b.pctversion, 
b.retention 
FROM dba_segments a,dba_lobs b 
WHERE a.segment_name = b.segment_name 
AND a.tablespace_name = 'TEST' 


SQL> 

从上面的结果我们可以观察到LOB字段的各个属性. 
下面我们对LOB字段move到另一个表空间 

SQL> create tablespace lob_test datafile size 100M autoextend off; 

SQL> ALTER TABLE TEST MOVE LOB(B) STORE AS TEST_B ( 
TABLESPACE lob_test 
DISABLE STORAGE IN ROW 
CHUNK 16384 
RETENTION 
FREEPOOLS 1 
NOCACHE); 
SQL> ALTER TABLE TEST MOVE LOB(D) STORE AS TEST_D ( 
TABLESPACE lob_test 
DISABLE STORAGE IN ROW 
CHUNK 16384 
RETENTION 
FREEPOOLS 1 
NOCACHE); 
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST'; 

SQL> set linesize 200 
col table_name format a5 
col column_name format a5 
SELECT b.table_name, 
a.segment_name, 
b.index_name, 
a.segment_type, 
b.column_name, 
a.tablespace_name, 
b.chunk, 
b.cache, 
b.freepools, 
b.pctversion, 
b.retention 
FROM dba_segments a,dba_lobs b 
WHERE a.segment_name = b.segment_name 
AND a.tablespace_name = 'LOB_TEST' 
/SQL> 

在一些复杂情况下可能需要连表一起移植 
alter table table_name move [tablespace_name] lob (lob_item) store as [lobsegmentname] (tablespace tablespace_name.....); 
移植分区中lob 
alter table table_name move partition [partition_name] lob (lob_item) store as [logsegmentname] (tablespace_name.....); 
移植分区表 
alter table table_name move partition [partition_name] tablespace_name lob (lob_item) store as [logsegmentname] (tablespace_name.....); 
如果不需要修改lobsegmentname,可以同时移植多个列 
alter table table_name move lob (lob_item1,lob_item2,lob_item3...) store as [lobsegmentname] (tablespace tablespace_name.....); 



    LOB段也可以利用move来重整数据,以下的语句会将表与lob字段move到指定的表空间: 

   alter table table_name move [tablespace tbs_name] 

lob(lob_field1,lob_field2) store as (tablespace new_tbs_name); 

如果LOB字段在分区表中,则增加partition关键字,如 

   alter table table_name move [partition partname] [tablespace tbs_name] 

lob(field) store as (tablespace new_tbs_name); 



在数据库中合理的存储LOB列,不仅可以提升性能,而且还可以有效的管理存储空间.


本文转自茄子_2008博客园博客,原文链接:http://www.cnblogs.com/xd502djj/archive/2010/11/21/1883467.html,如需转载请自行联系原作者。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
2.当vmware中的Linux磁盘大小不足了的时候,修改vmware中Linux磁盘空间大小的解决方案
 重新设置虚拟机下的大小: 修改扩展后的磁盘大小 3第三步 4 启动Linux 5使用fdisk –l命令查看磁盘信息 6打开一个终端窗口,并查看parted命令 man parted 输入parted命令: 使用mkpart命令进行分区
1169 0
innodb表空间加密
表空间加密的注意事项
104 0
ORACLE RAC 修改 SCAN IP
oracle rac 修改 scan ip -- 场景 : -- 在我们升级一个系统或更换了网卡后,为了减少对原机的影响及避免修改大量的客户端,新系统的 ip 又会修改回原 ip. -- 在这里我们简单演示下如何修改其中的 scan ip -- 环境信息: scan ip 如下 192.
1085 0
Confluence 6 升级自定义的站点和空间仔细测试你的修改
修改可能对 Confluence 的后续版本不兼容,当你对 Confluence 进行升级的时候,你应该总是对你自定义修改的模板文件进行仔细的测试来确定所有的修改对新版本的 Confluence 兼容。
690 0
ORACLE SQL脚本能否修改字段名称?
在看到标题时,你先想想:在ORACLE中能否修改一个表的某个字段名呢?如果能的话,你是否还记得SQL脚本如何写的呢,呵呵,写这个的目的是因为在论坛上看见许多信誓旦旦的说ORACLE中不能修改字段名称,只能先删除、后添加字段或是其它方法来处理。
648 0
oracle表空间,角色,权限,表,索引,序列号,视图,同义词,约束条件,存储函数和过程,常用数据字典,基本数据字典信息,查看VGA信息,维护表空间,创建表空间等信息
查看当前用户的缺省表空间   SQL>select username,default_tablespace from user_users;   查看当前用户的角色   SQL>select * from user_role_privs;   查看当前用户的系统权限和表级权限   SQL>select * from user_sys_privs;       
1225 0
oracle修改表字段
增加字段     alter   table   docdsp     add   dspcode   char(200)     删除字段     ALTER   TABLE   table_NAME   DROP   COLUMN   column_NAME     修改字段类型     ALTER   TABLE   table_name     ALTER   COLUMN   column_name   new_data_type     改名     sp_rename     更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。
583 0
+关注
1469
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载