Improved Tablespace Managment

Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. Oracle9i alleviated this problem by allowing the DBA to specify a default, temporary tablespace for all users created without an explicit temporary tablespace clause

   In Oracle Database 10g, you can similarly specify a default tablespace for users. During database creation Or After creation, you Can make a tablespace default by issuing.

    ALTER DATABASE DEFAULT TABLESPACE tsname;

scott@10G> SELECT NAME FROM V$TABLESPACE;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS_DATA
TEMP
EXAMPLE
TTS_TBL
TTS_INDX

8 rows selected.

scott@10G> ALTER DATABASE DEFAULT TABLESPACE TTS_TBL;

Database altered.

scott@10G> CREATE USER TEST_USER IDENTIFIED BY TEST_USER;

User created.

scott@10G> SELECT username, default_tablespace, temporary_tablespace
  2  FROM dba_users
  3  WHERE username ='TEST_USER'
  4  /

USERNAME        DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
------------------ ------------------------------ --------------------------
TEST_USER       TTS_TBL                         TEMP

If the default Tablespace is not specified during the database creation , it defaults to SYSTEM.
But how do you know which tablespace is default for existing database?

scott@10G> SELECT property_value
  2  FROM database_properties
  3  WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
---------------------------------------------------------------
NEW_TBLS

It is common in data warehouse environments, typically for data mart architectures, to transport tablespaces between databases. But the source and target databases must not have tablespaces with the same names. Oralce 10g offers a Convenient solution:
You can simply rename an existing tablespace using the command: 

ALTER TABLESPACE old_name  RENAME TO  new_name;

scott@10G> ALTER TABLESPACE TTS_TBL RENAME TO NEW_TBLS;

Tablespace altered.

scott@10G> SELECT username, default_tablespace, temporary_tablespace
  2  FROM dba_users
  3  WHERE username ='TEST_USER'
  4  /

USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------------- ---------------------------  -----------------------------
TEST_USER    NEW_TBLS                     TEMP