Questions on Big and Small filespaces

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What is a BIGFILE tablespace?
A: 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.

Q: How do you set the default tablespace type to BIGFILE?
A: To set the default tablespace type to BIGFILE, you can use either CREATE DATABASE or ALTER DATABASE.

Q: how do you display the default tablespace type?
A: You 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’;

Q: Use the DBA_TABLESPACES dictionary view to display whether all tablespace is bigfile (YES) or smallfile (NO).
A:
SQL> SELECT tablespace_name, bigfile FROM dba_tablespaces;

Q: Use the V$TABLESPACE dynamic view to display whether all tablespace is bigfile (YES) or smallfile (NO).
A:
SQL> SELECT name, bigfile FROM v$tablespace;

Q: What are the difference between a BIGFILE rowid and a small file rowid?
A: Extended ROWID format:
For Smallfile tablespaces is Object# - File# - Block# - Row#
For Bigfile tablespaces is Object# - Block# - Row#

Q: Create a temporary tablespace group that it consists of only temporary tablespaces.
A:
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 do not 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 ‘’;

Q: Use the DBA_TABLESPACE_GROUPS view to display all tablespace associated to their groups.
A:
SQL> SELECT tablespace, group_name FROM dba_tablespace_groups;

Q: Create a tablespace with a BIGFILE default tablespace type.
A:
SQL> CREATE BIGFILE UNDO TABLEPSACE my_big_tbs
DATAFILE ‘/u01/oradatta/tbs_01.dbf’ SIZE 1G;

Q: Can you add more datafiles?
A: If you try to add more datafile to above tablespace, do the following.
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.

Q: How do you get a BIGFILE ROWID?
A: 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;