Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 12, 2015

Create BIGFILE tablespace

Bigfile Tablespaces 


A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:
  • A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles 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 datafiles 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 datafile information and the size of the control file.
  • Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

Set Datafile destination:

SHAIKDB>alter system set db_create_file_dest='/u01/app/oracle/mydb';

System altered.

SHAIKDB>show parameter create_file_dest

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest            string     /u01/app/oracle/mydb

SHAIKDB>create bigfile tablespace BIGTBS;      

Tablespace created.

SHAIKDB>select ts.name TSNAME,df.name DFNAME,bigfile from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

TSNAME       DFNAME                                   BIG
---------- ------------------------------------------------------------                 ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf                       NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db                       NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf                       NO
USERS       /u01/app/oracle/mydb/users01.dbf                       NO
BIGTBS       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.dbf YES


You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.


SHAIKDB>col tsname for a10
SHAIKDB>col dfname for a50

SHAIKDB>select ts.name TSNAME,df.name DFNAME,file#,rfile#,bigfile BIGFILE  from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;
TSNAME       DFNAME                           FILE#     RFILE# BIG
---------- -------------------------------------------------- ---------- ---------- ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf                  1      1 NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db                  2      2 NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf                  3      3 NO
USERS       /u01/app/oracle/mydb/users01.dbf                  4      4 NO
BIGTBS       /u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf  5   1024 YES
USERS       /u02/app/oracle/mydb/users02.dbf                  6      6 NO

6 rows selected.




if the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.

CREATE DATABASE MYDB
    USER SYS IDENTIFIED BY test123
    USER SYSTEM IDENTIFIED BY test123
    SET DEFAULT BIGFILE TABLESPACE
    UNDO TABLESPACE undotbs
    DEFAULT TEMPORARY TABLESPACE tempts1;

1 comment:

Nail Obrain said...

Strategies on how to write a medical school personal statement that will make you stand out from the crowded field of applicants and get you admitted to medical school. See more mba statement of purpose sample