Database Reference
In-Depth Information
Now, issue the
CREATE TABLESPACE
statement:
SQL> create tablespace inv1;
This statement creates a tablespace named
INV1
, with a default data file size of 100MB. Keep in mind that you can
override the default size of 100MB by specifying a size:
SQL> create tablespace inv2 datafile size 20m;
To view the details of the associated data files, query the
V$DATAFILE
view, and note that Oracle has created
subdirectories beneath the
/u01
directory and named the file with the OMF format:
SQL> select name from v$datafile where name like '%inv%';
NAME
-----------------------------------------------
/u01/O12C/datafile/o1_mf_inv1_8b5l63q6_.dbf
/u01/O12C/datafile/o1_mf_inv2_8b5lflfc_.dbf
One limitation of OMF is that you're limited to one directory for the placement of data files. If you want to add
data files to a different directory, you can alter the location dynamically:
SQL> alter system set db_create_file_dest='/u02';
Although this procedure isn't a huge deal, I find it easier not to use OMF. Most of the environments I've worked
in have many mount points assigned for database use. You don't want to have to modify an initialization parameter
every time you need a data file added to a directory that isn't in the current definition of
DB_CREATE_FILE_DEST
.
It's easier to issue a
CREATE TABLESPACE
statement or
ALTER TABLESPACE
statement that has the file location and
storage parameters in the script. It isn't cumbersome to provide directory names and file names to the tablespace-
management statements.
Creating a Bigfile Tablespace
The bigfile feature allows you to create a tablespace with a very large data file assigned to it. The advantage of using
the bigfile feature is this potential to create very large files. With an 8KB block size, you can create a data file as large as
32TB. With a 32KB block size, you can create a data file up to 128TB.
Use the
BIGFILE
clause to create a bigfile tablespace:
create bigfile tablespace inv_big_data
datafile '/u01/dbfile/O12C/inv_big_data01.dbf'
size 10g
segment space management auto;
As long as you have plenty of space associated with the filesystem supporting the bigfile tablespace data file, you
can store massive amounts of data in a tablespace.
One potential disadvantage of using a bigfile tablespace is that if, for any reason, you run out of space on a
filesystem that supports the data file associated with the bigfile, you can't expand the size of the tablespace (unless
you can add space to the filesystem). You can't add more data files to a bigfile tablespace if they're placed on separate
mount points. A bigfile tablespace allows only one data file to be associated with it.
You can make the bigfile tablespace the default type of tablespace for a database, using the
ALTER DATABASE
SET DEFAULT BIGFILE TABLESPACE
statement. However, I don't recommend doing that. You could potentially
create a tablespace, not knowing it was a bigfile tablespace (because you forgot it was the default or because
