|
Big and Small file tablespaces in the Oracle 10g
database
It is the Oracle Database 10g feature. A bigfile
tablespace (BFT) is a tablespace containing a single
file that can have a very large size and on the
other hand a smallfile tablespace can contain many
data files. The size of a bigfile can reach to 128TB
depending on the Oracle block size. An Oracle
database can contain both bigfile and smallfile
tablespaces. You can change the default tablespace
type to BIGFILE or SMALLFILE.
To set the default tablespace type to BIGFILE,
either you can use CREATE DATABASE or ALTER
DATABASE.
Use the DATABASE_PROPERTIES dictionary view to
display the default tablespace type for the
database:
SQL> SELECT property_value FROM database_properties
WHERE property_name = �DEFAULT_TBS_TYPE�;
Use the DBA_TABLESPACES dictionary view to display
whether all tablespace is bigfile (YES) or smallfile
(NO).
SQL> SELECT tablespace_name, bigfile FROM
dba_tablespaces;
Use the V$TABLESPACE dynamic view to display whether
all tablespace is bigfile (YES) or smallfile (NO).
SQL> SELECT name, bigfile FROM v$tablespace;
Extended ROWID format:
For Smallfile tablespaces is: Object# - File# -
Block# - Row#
For Bigfile tablespaces is: Object# - Block# - Row#
Now, you can create a temporary tablespace group. It
consists of only temporary tablespaces.
SQL> CREATE TEMPORARY TABLESPACE mytemp1
TEMPFILE �temp_01.dbf� SIZE 500M
TABLESPACE GROUP mygroup;
The mygroup group has one more temporary tablespace
in its groups. If you don�t want to assign any
temporary tablespace to a group do the following:
SQL> CREATE TEMPORARY TABLESPACE mytemp2
TEMPFILE �temp_02.dbf� SIZE 500M
TABLESPACE GROUP ��;
Use the DBA_TABLESPACE_GROUPS view to display all
tablespace associated to their groups.
SQL> SELECT tablespace, group_name FROM
dba_tablespace_groups;
Hands-On #1:
Maintaining BIGFILE:
Create a tablespace with a BIGFILE default
tablespace type.
SQL> CREATE BIGFILE UNDO TABLEPSACE my_big_tbs
DATAFILE �/u01/oradatta/tbs_01.dbf� SIZE
1G;
Try to add more datafile to above tablespace.
SQL> ALTER TABLESPACE my_big_tbs
ADD DATAFILE �/u02/oradata/tbs_02.dbf�
SIZE 100k;
Notice, since a bigfile tablespace can contain only
one data file, your command should fail.
Create a table and add some record into it:
SQL> CREATE TABLE test_rowid (c1 NUMBER, c2
VARCHAR2(100));
SQL> BEGIN
FOR this IN 1..100 LOOP
INSERT INTO test_rowid
VALUES (this, �Test rowid��);
COMMIT;
END LOOP;
END;
/
To get its ROWID, you should use the following
database package (DBMS_ROWID).
SQL> SELECT distinct DBMS_ROWID.ROWID_RELATIVE_FNO
(ROWID,�BIGFILE�)
FROM test_rowid;
If you no longer want to interpret restricted ROWIDs
for rows from BIGFILE tablespaces execute the
following SQL statement.
SQL> SELECT dbms_rowid.rowid_to_restricted (rowid,1)
as rowid
FROM test_rowid WHERE rownum < 10;
|
