What is big file tablespace in oracle?

A big file tablespace is a single tablespace can have  very large  - up to 4G blocks in a data file. As compare to traditional tablespaces which can contain multiple data files, but the files cannot be as large.

Some benefits of its are as under:

• A Bigfile tablespace with 8K/16K/32K blocks can contain a 32/64/128 TB data file. 
• The maximum number of datafiles in an Oracle Database is limited to Max. 64K files. 
• A Bigfile tablespace can significantly improve the storage capacity of the Database.
• Bigfile tablespaces reduce the number of data files for a database, with this DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted by DBA to reduce the amount of SGA space required for data files and size of the control file.
• Bigfile tablespaces simplify database management by providing data file transparency instead of using multiple data files.
• It supports only for locally managed tablespaces with automatic segment space management, and it’s not used for locally managed undo tablespaces, temporary tablespaces and SYSTEM tablespace.
• It is advised to use big file tablespaces in a database where Automatic Storage Management (ASM) configured or other logical volume managers that support striping or RAID, and dynamically extensible logical volumes.
• Avoid creating big file tablespaces on a database that is not supporting striping because of negative implications for parallel query execution and RMAN backup parallelization.

If the default tablespace type is specified to BIGFILE at a time of database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A big file tablespace is created by default.

CREATE BIGFILE TABLESPACE tbs_bigtable_ex1
DATAFILE '/u02/oracle/data/tbsbig01.dbf' SIZE 80G;

If the default tablespace type is specified to BIGFILE at a time of database creation, but you want to create a traditional - small file tablespace, then uses a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type which creating database.

CREATE SMALLFILE TABLESPACE tbs_smalltable_ex1
DATAFILE '/u02/oracle/data/tbssmall01.dbf' SIZE 80G;

What are Surrogate Keys, Primary Keys and Candidate keys? Where its used?

A Surrogate key is any column or set of columns that can be declared as the primary key instead of a real or natural key. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So we can call a  surrogate key is a candidate key.

A Surrogate key is the alternate of primary key that allows duplication of datas/records. It is an immutable set of attributes that uniquely identify a row that were generated specifically and solely to identify this row which is not in case of natural key or primary key.

A table could actually have more than one surrogate keys, although this would be unusual. A natural key is an immutable set of attributes that uniquely identify a row that occur naturally with the row itself. The most common type of surrogate key is an incrementing integer, such as an auto_increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.

Primary key and Surrogate key are same but surrogate key is a system generated numeric or integer value to identify each row uniquely, it has a define incremental value for each row in a table.

Surrogate key does not have any business importance for the value it holds but primary key has a significant business value.

OLTP Databases are called as of  Normalised Form  whereas  Data warehouses - DWHs  are called as of De-normalised form as DWH is used to maintain the historic data for analyzing. To remain de-normalised, duplication is allowed. When data inserting in DWH, Surrogate key a new column named serial number is introduced to allow duplication.

A Surrogate key in a data warehouse is more than just a substitute for a natural key. In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design. Surrogate Key is the solution for critical column problems.

Ex.  A customer purchases different items from stores at different locations. Here, we have to maintain historical data, by using surrogate key which introduces the row in the data warehouse to maintain historical data. Another example of its, a single mobile number is used by other person if it is not in use for more than one year, it is possible just because of this surrogate key.