iSelfSchooling - 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;