Thursday, September 1, 2011

What is a Bigfile tablespace?


Bigfile tablespaces are tablespaces with a single large datafile(means you can't add more than one datafile).

Why do i use it?

-An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity. By allowing tablespaces to have a single large datafile the total capacity of the database is increased. A bigfile tablespace with 8K and 32K blocks can contain 32 terabyte and 128 terabyte datafiles respectively.

-Using fewer larger datafiles allows the DB_FILES and MAXDATAFILES parameters to be reduced, saving SGA and controlfile space.

-The ALTER TABLESPACE syntax has been updated to allow operations at the tablespace level, rather than datafile level.


The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

How do i create it?

CREATE BIGFILE TABLESPACE mybigtbs 
  DATAFILE 'd:\mybigtb.dbf' SIZE 100G;

How do i resize it?

ALTER TABLESPACE mybigtb AUTOEXTEND ON NEXT 10G;

How do i recognize it?

A BIGFILE column (YES or NO) has been added to the DBA_TABLESPACES, USER_TABLESPACES and V$TABLESPACE views to indicate the tablespace type

HTH:-)

3 comments:

Noons said...

You should perhaps add that until 11g, RMAN cannot multi-thread bigfile backups. With the result that if we use bigfiles before that release, RMAN may become slower due to lesser parallelism.
It's not just the features we should mention. It's also the consequences - unintended or otherwise - of using them.
No criticism meant, just and addenda. Good series of posts.

Vishwanath Sharma said...

Hi Noon! Your Suggestion are all welcome

5689 said...

zzzzz2018.8.31
nike requin
nike soldes femme
moncler outlet
ralph lauren outlet
canada goose jackets
canada goose outlet
canada goose outlet
moncler online
nike air max
pandora