|
‘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 dom and
liberty with him.’ Gandhi |
Big and small file
tablespaces in the Oracle 10g database
Introduction
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.
Set the Default
To set the default
tablespace type to BIGFILE, you can use either CREATE DATABASE or
ALTER DATABASE.
The DATABASE_PROPERTIES
dictionary view
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’;
The DBA_TABLESPACES
dictionary view
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;
The V$TABLESPACE dynamic
view
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 ‘’;
The DBA_TABLESPACE_GROUPS
view
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
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;
|
“Our lives begin to
end the day we become silent about things that matter.” Martin
Luther King Jr. |
Questions:
Questions on
Big and
Small file tablespaces in the Oracle 10g database
Q: What is a BIGFILE
tablespace?
Q: How do you set the
default tablespace type to BIGFILE?
Q: how do you display the
default tablespace type?
Q: Use the DBA_TABLESPACES
dictionary view to display whether all tablespace is bigfile (YES) or
smallfile (NO).
Q: Use the V$TABLESPACE
dynamic view to display whether all tablespace is bigfile (YES) or
smallfile (NO).
Q: What are the difference
between a BIGFILE rowid and a small file rowid?
Q: Create a temporary
tablespace group that it consists of only temporary tablespaces.
Q: Use the
DBA_TABLESPACE_GROUPS view to display all tablespace associated to
their groups.
Q: Create a tablespace with
a BIGFILE default tablespace type.
Q: Can you add more
datafiles?
Q: How do you get a BIGFILE
ROWID?
|