** Verifying that we have no existing tablespace groups. SQL> SQL> select * from dba_tablespace_groups; no rows selected ** Creating our first temporary tablespace and implicitly creating ** our first group by assigning this table to it during creation. SQL> create temporary tablespace temp01_tbs 2 tempfile '/u01/oracle/oradata/sndbx/temp01.dbf' size 30M 3 extent management local uniform size 1M 4 tablespace group temp_group; Tablespace created. ** Verifying that the group was automatically created SQL> SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP_GROUP TEMP01_TBS ** Creating our second temporary tablespace and assigning it ** to our temporary tablespace group SQL> SQL> create temporary tablespace temp02_tbs 2 tempfile '/u01/oracle/oradata/sndbx/temp02.dbf' size 30M 3 extent management local uniform size 1M 4 tablespace group temp_group; Tablespace created. ** Verifying that our second tablespace was added to the group SQL> SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP_GROUP TEMP02_TBS TEMP_GROUP TEMP01_TBS ** Creating our third temporary tablespace and not making it a member ** of a group. SQL> SQL> create temporary tablespace temp03_tbs 2 tempfile '/u01/oracle/oradata/sndbx/temp03.dbf' size 30M 3 extent management local uniform size 1M; Tablespace created. ** Verifying that the third tablespace has not been added to the group. SQL> SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP_GROUP TEMP02_TBS TEMP_GROUP TEMP01_TBS ** Altering our third tablespace to add it to the temp_group SQL> SQL> SQL> alter tablespace temp03_tbs tablespace group temp_group; Tablespace altered. ** Verifying that it is now a member of our group SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP_GROUP TEMP02_TBS TEMP_GROUP TEMP03_TBS TEMP_GROUP TEMP01_TBS ** Creating our first user and specifying our new group as their ** temporary tablespace SQL> create user user01 2 identified by user01 3 default tablespace users 4 quota 20M on users 5 temporary tablespace temp_group; User created. ** Verifying that our user has been assigned to our new group SQL> SQL> SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 where username in ('USER01', 'USER02', 'USER03') 4 order by username; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ USER01 USERS TEMP_GROUP 1 row selected. ** Creating our second user and not assigning them to our new group SQL> SQL> create user user02 2 identified by user02; User created. ** Verifying that user02 has been assigned to the database default group of temp ** and not our tablespace group temp_group SQL> SQL> SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 where username in ('USER01', 'USER02', 'USER03') 4 order by username; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ USER01 USERS TEMP_GROUP USER02 USERS TEMP 2 rows selected. ** Altering user02 to switch their temporary tablespace from temp to temp_group SQL> SQL> alter user user02 2 default tablespace users 3 quota 20M on users 4 temporary tablespace temp_group; User altered. ** Verifying that the user is now assigned to our new group SQL> SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 where username in ('USER01', 'USER02', 'USER03') 4 order by username; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ USER01 USERS TEMP_GROUP USER02 USERS TEMP_GROUP 2 rows selected. ** Creating our third user and assigning them to a tablespace that is a member of our group. ** Verifying that you can assign a single tablespace out of a group to a user. SQL> SQL> create user user03 2 identified by user03 3 default tablespace users 4 quota 20M on users 5 temporary tablespace temp02_tbs; User created. ** Verifying that user03 is using a tablespace that is a member of our group SQL> SQL> SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 where username in ('USER01', 'USER02', 'USER03') 4 order by username; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ USER01 USERS TEMP_GROUP USER02 USERS TEMP_GROUP USER03 USERS TEMP02_TBS ** Dumping the dba_users view to identify all of our users' temporary tablespaces SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 order by default_tablespace; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ DBSNMP SYSAUX TEMP SYS SYSTEM TEMP OUTLN SYSTEM TEMP SYSTEM SYSTEM TEMP TEST USERS TEMP BRIAN USERS TEMP CHRIS USERS TEMP DIP USERS TEMP TSMSYS USERS TEMP USER02 USERS TEMP_GROUP USER01 USERS TEMP_GROUP USER03 USERS TEMP02_TBS MSHORE USERS TEMP 13 rows selected. ** Altering the database to use our temp_group as the default temporary tablespace at the ** database level. SQL> SQL> SQL> alter database 2 default temporary tablespace temp_group; Database altered. ** Dumping the dba_users view again to make sure they were switched to our group. ** Since user03's temporary tablespace was specified during creation (and not ** defaulted to the database level), we will have to use the ALTER USER command ** to manually switch them to our temporary tablespace group SQL> SQL> SQL> select username, default_tablespace, temporary_tablespace 2 from dba_users 3 order by default_tablespace; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ DBSNMP SYSAUX TEMP_GROUP OUTLN SYSTEM TEMP_GROUP SYSTEM SYSTEM TEMP_GROUP SYS SYSTEM TEMP_GROUP TEST USERS TEMP_GROUP BRIAN USERS TEMP_GROUP CHRIS USERS TEMP_GROUP DIP USERS TEMP_GROUP TSMSYS USERS TEMP_GROUP USER01 USERS TEMP_GROUP MSHORE USERS TEMP_GROUP USER03 USERS TEMP02_TBS USER02 USERS TEMP_GROUP 13 rows selected.