oracle 如何修改表空间,ORACLE修改表空间方法

1、使用imp/exp。先导出源库，再建立新库把表空间建立好，而后再导入。(听说这样能够，前提是新的库里面不能有与源库相同名字的表空间。有待验证！)

2、使用脚本进行修改。据目前所了解，正长状况下须要修改表的空间和表的索引的空间，若是涉及到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列,不只能够提高性能,并且还能够有效的管理存储空间.程序员

程序员的基础教程：菜鸟程序员数据库