Big
and Small file tablespaces in the Oracle 10g database
"Woman
is the companion of man, gifted with equal mental capacities. She has
the right to participate in the minutest details in the activities of man,
and she has an equal right of freedom and liberty with him."
Gandhi
|
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;
|