大数据解决方案（一）之表空间--bigfile tablespace

大数据解决方案（一）之表空间--bigfile tablespace

寄语：数据库仓库中一个上T级别的表空间，还继续使用smallfile是不合适的。oracle提出一个解决方案 bigfile tablespace.今天就讲讲bigfile tablespace.


BIGFILE TABLESPACE
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN01102


bigfile tablespace 优点
A bigfile tablespace with 8K blocks can contain a 32 terabyte data file. A bigfile tablespace with 32K blocks can contain a 128 terabyte data file. The maximum number of data files in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.


Bigfile tablespaces can reduce the number of data files needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for data file information and the size of the control file.


Bigfile tablespaces simplify database management by providing data file transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual data files.


总结一句话：更大的数据文件简化了管理（简少了smallfile个数从而简化了管理，简少了smallfile语法调整使用SGA资源，表空间语法直接管理bigfile管理更透明）


bigfile tablespace 使用条件
a.必须在locally managed tablespaces with automatic segment space management条件下使用bigfile tablespace(但是undo,temporary,system 3个表空间例外)
b.支持striping or RAID ; oracle asm 或是 支持striping or RAID的逻辑卷
c.如果不支持striping or RAID，并行查询和RMAN并行备份会受影响
d.系统必须有更大的空间来支持bigfile tablespace




1.CREATE BIGFILE TABLESPACE
CREATE BIGFILE TABLESPACE bigtbs 
    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G;


2.Altering a Bigfile Tablespace
添加bigfile tablespace bigtbs至80G
ALTER TABLESPACE bigtbs RESIZE 80G;


http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11364


以下3个视图管理
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE


3.实际案例
3.1 数据库仓库项目中，存储索引的表空间都调整为bigfile tablespace


3.2 Migrating Oracle smallfile to bigfile tablespace

You have a few options.


Create your new bigfile tablespaces, then either:


1) Move the tables one by one with:


alter table mytable move tablespace bigfile_tablespace;
Remember to move indexes too!


alter index myindex rebuild tablespace bigfile_index_tablespace;
2) Export the database with Data Pump, drop the existing objects, then reimport using a remap_tablespace clause, for example:


impdp remap_tablespace=OLDSMALLTS1:NEWBIGTS1,OLDSMALLTS2:NEWBIGTS2 directory=mydir dumpfile=mydumpfile.dmp logfile=mylogfile.log


Once done, drop all of the old objects and then the tablespaces.


If you can afford the downtime, datapump will be the easiest option.


参考：http://dba.stackexchange.com/questions/35965/migrating-oracle-smallfile-to-bigfile-tablespace


4.bigfile tablespace 备份
RMAN


########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928
本文链接： http://blog.itpub.net/26442936/viewspace-1979963/
########################################################################################

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-1979963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-1979963/