Sunday , October 22 2017
Home / Oracle DBA / BIGFILE TABLESPACE

BIGFILE TABLESPACE

BIGFILE TABLESPACE

  1. For managing the data files in VLDB, oracle introduced big file table space in 10g.
  2. Big file table space’s data files can grow into terabytes based on the block size. For example, for a 8KB block size a single file can grow till 4TB.
  3. Big file table spaces should be created only when we have stripping and mirroring implemented at storage level in real time.
  4. We can’t add another data file to a big file table space until it reaches max value.
  5. Bigfile tablespaces can be created only as LMT and with ASSM.
Note: Either in LMT or DMT, ASSM once defined cannot be changed
COMMANDS
# To create a big file table space
SQL> create big file table space big tbs
data file ‘/u02/prod/big tbs01.dbf’ size 50m;
CAPACITY PLANNING
  1. It is the process of estimating space requirement for future data storage.
  2. We will do this by collecting free space info for all the table spaces in the database either daily, weekly or monthly basis.
  3. We need to observe the difference in free space and should able to analyze how much space is required in future.
  4. Following query is used to find free space
SQL> select table space_name,sum(bytes/1024/1024) from dba_free_space group by
table space_name;
  1. Eg: If we observe table space USERS free space is reducing 500 m daily, for next 1 month we need 500*30=15GB.
  2. Once we analyze the required space, we need to check the availability for the same in mount point. If space not there, contact storage team to get space added.
Read More:
STEPS to create database manually
Some Interview Questions
Using the Result Cache

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *