大資料解決方案（一）之表空間--bigfile tablespace

lovehewenyu發表於2016-01-21
大資料




大資料解決方案（一）之表空間--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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章