oracle 查询对应表空间里面存储的表,以及更换表的表空间

转载 2016年08月31日 10:32:20

oracle 查看表空间有哪些表

select * from dba_tables wheretablespace_name='表空间名',注意表空间名大小写敏感。
select table_name,tablespace_name from user_tables;


from http://www.cnblogs.com/xd502djj/archive/2010/11/21/1883467.html

一、使用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列,不仅可以提升性能,而且还可以有效的管理存储空间.

ORACLE 修改表的表空间

1、先找到这哪些表的表空间不对。 select * from dba_tables where tablespace_name=TDB;  2、将表空间在 TDB 中的移到表空间 TDB2009 中语...
  • KimSoft
  • KimSoft
  • 2009年09月09日 17:03
  • 15361

oracle下批量修改表所在的表空间

 特别提醒:所有操作均在system下进行 一、修改索引index的表空间 1、查询当前用户所有LOB索引,并把表空间为“JCMS24”的索引,更新为:JCMS。(手动修改) ALTER ...
  • XMM_1030
  • XMM_1030
  • 2014年04月09日 17:10
  • 1932

Oracle修改用户表所属表空间的步骤

使用脚本进行修改。据目前所了解,正长情况下需要修改表的空间和表的索引的空间,如果涉及到BOLB字段的表,修改的方式又不一样了! 正常情况下的修改脚本: 1 。修改表的空间 alter table...
  • sunboy520555
  • sunboy520555
  • 2018年01月24日 09:32
  • 62

Oracle实践之改变表所属用户(3)

由于前面创建的表属于SYS用户。显然这不是我想要的。就想将其修改为InfoManager用户的表。修改表SQL如下:create SysFunction InfoManager.SysFunction...
  • longronglin
  • longronglin
  • 2007年03月12日 20:23
  • 3377

oracle 查询对应表空间里面存储的表,以及更换表的表空间

from http://blog.sina.com.cn/u/2084780081 oracle 查看表空间有哪些表 select * from dba_tables wheretablesp...
  • ytfy12
  • ytfy12
  • 2015年05月28日 11:19
  • 5201

Oracle中查询当前数据库中的所有表空间和对应的数据文件语句命令

--转载路径:http://www.cnblogs.com/wangsaiming/p/3573917.html ----------------------------------------...
  • czl8897098
  • czl8897098
  • 2016年06月03日 15:37
  • 368

oracle 修改索引现有表空间

工作日记之《修改索引现有表空间》 //dba_indexes可查询所有索引,以及索引部分信息,可以灵活运用于其他用途 //假设用户USER1现有表空间TS1、TS2,需要迁移其下所有表空间TS1的...
  • leandzgc
  • leandzgc
  • 2013年10月10日 20:33
  • 8662

ORACLE修改表空间方法

一、使用imp/exp。先导出源库,再创建新库把表空间创建好,然后再导入。(据说这样可以,前提是新的库里面不能有与源库相同名字的表空间。有待验证!) 二、使用脚本进行修改。据目前所了解,正长情况下需要...
  • yczz
  • yczz
  • 2009年05月27日 17:01
  • 6034

Oracle批量修改用户表table的表空间

一、修改用户表table的表空间 1、修改用户表table的表空间:alter table 表名 move tablespace 新表空间名; 2、查询所有用户表:select * from us...
  • blue225
  • blue225
  • 2012年10月11日 14:16
  • 6493

oracle查看某个表空间下有多少表

oracle查看某个表空间下有多少表   oracle查看某个表空间下有多少表    www.2cto.com   select * from all_tables where...
  • z69183787
  • z69183787
  • 2014年02月27日 16:57
  • 2313
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle 查询对应表空间里面存储的表,以及更换表的表空间
举报原因:
原因补充:

(最多只允许输入30个字)