Thursday, August 29, 2013

change default tablespace of db setting

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';


PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   SYSTEM                         Name of default permanent tablespace



SYS@cust1> create user liqy identified by liqyliqy;

User created.

SYS@cust1> select * from dba_users where username='LIQY';

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE         DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------ -------------------------------- ------------------- ------------------- ------------------------------
TEMPORARY_TABLESPACE           CREATED             PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------- ------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PASSWORD E AUTHENTI
-------- - --------
LIQY                                   58                                OPEN                                                                     SYSTEM
TEMP                           2013-08-26 14:43:47 DEFAULT                        DEFAULT_CONSUMER_GROUP

10G 11G  N PASSWORD



SYS@rcat> desc database_properties
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_NAME                             NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                     VARCHAR2(4000)
 DESCRIPTION                                        VARCHAR2(4000)

SYS@rcat> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE
SYSTEM
Name of default permanent tablespace

DEFAULT_EDITION
ORA$BASE
Name of the database default edition

DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type


SYS@rcat> ALTER database default tablespace users;

Database altered.

SYS@rcat> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE
USERS
Name of default permanent tablespace

DEFAULT_EDITION
ORA$BASE
Name of the database default edition

DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type