Oracle's default table space

I. Default table space description

In Oracle9i, all users who are not created with the default TABLESPACE clause will use the system table space as their default tablespace. It is also not possible to specify a default tablespace for the database.

The database-level default tablespace users are defined in Oracle 10g, and when the user is created without a default tablespace, the database-level default table space is treated as its default tablespace.

Before 10g, the system table space (systems) was used as the default tablespace. That's not reasonable.

You can view the default tablespace using the following command:

[SQL]View Plaincopy

1. /* Formatted on 2011/12/19 15:57:48 (QP5 v5.185.11230.41888) */
2. SELECT Property_value
3. From database_properties
4. WHERE property_name = ' default_permanent_tablespace '

You can modify the default tablespace using the following command:

ALTER DATABASE DEFAULT tablespace users;

Here are a few things to note:

1. If we specify a default tablespace when creating a user, the default tablespace will change before the user's default table space is modified.

2. If we do not specify a user tablespace when creating a user, the default tablespace for DB is also used by default, and if we modify the default tablespace for DB, the user's tablespace will change.

3. If we are creating a table space for user-specified users, then we modify the default tablespace for the DB without affecting the user's tablespace.

4. The default tablespace for DB cannot be deleted unless the default tablespace is pointed to a different table space.

5. If the user's default tablespace points to another tablespace, the user's default tablespace will automatically point to the default tablespace of DB when the table space is drop.

Two. Example 2.1 View DB version:

Sql> select * from V$version;

BANNER

-----------------------------------------------------------------------

Oracle Database 11g Enterprise editionrelease 11.2.0.1.0-production

PL/SQL Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for 32-bit windows:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

2.2 Viewing the current default table space

Sql> SELECT Property_value

2 from Database_properties

3 WHERE property_name = ' default_permanent_tablespace ';

Property_value

-------------------------------------------------------------

USERS

2.3 Creating a user without specifying a default table space

Sql> Create user dave1 identified bydave1;

User created.

Sql> Select Default_tablespace from dba_users where username = ' DAVE1 ';

Default_tablespace

------------------------------

USERS

--The default table space is users.

2.4 Create user-specified default tablespace for default tablespace to DB

Sql> create user dave2 identified by dave2 default tablespace users;

User created.

Sql> Select Default_tablespace fromdba_users where username = ' DAVE2 ';

Default_tablespace

------------------------------

USERS

2.5 Create user-specified default tablespace for other tablespaces

--Create TABLE space:

sql> Create tablespace dave3 datafile ' D:\APP\ADMINISTRATOR\ORADATA\NEWCCS\dave3.dbf ' size 10M;

Tablespace created.

--Create User:

Sql> create user Dave3 identified by dave3 default Tablespace dave3;

User created.

Sql> Select Default_tablespace from dba_users where username = ' DAVE3 ';

Default_tablespace

------------------------------

DAVE3

2.6 Modifying the default tablespace for DB

sql> ALTER DATABASE DEFAULT TABLESPACEdave3;

Database altered.

2.7 View a table space before creating a user

Sql> Select Default_tablespace from dba_users where username = ' DAVE3 ';

Default_tablespace

------------------------------

DAVE3

Sql> Select Default_tablespace fromdba_users where username = ' DAVE2 ';

Default_tablespace

------------------------------

DAVE3

Sql> Select Default_tablespace fromdba_users where username = ' DAVE1 ';

Default_tablespace

------------------------------

DAVE3

-Note that the default tablespace for dave1,dave2 users here becomes dave3.

The default tablespace for 2.8 db cannot be deleted

--now DAVE3 is our default table space

Sql> SELECT Property_value

2 from Database_properties

3 WHERE property_name = ' Default_permanent_tablespace '

4;

Property_value

----------------------------------------------------------

DAVE3

--drop Dave3 Look at:

sql> drop tablespace dave3 includingcontents and datafiles;

Drop tablespace dave3 including Contentsand datafiles

*

ERROR at line 1:

Ora-12919:can not drop the defaultpermanent tablespace

Error ORA-12919 here.

We changed the default table space to users, and in the test:

sql> ALTER DATABASE DEFAULT tablespaceusers;

Database altered.

Sql> drop tablespace dave3 including contents and datafiles;

Tablespace dropped.

Dave3 table space successfully drop. Note that the table space between our DAVE3 users is pointing to Dave3, and now that we have the Dave3 table space drop, let's take a look at Dave3 now the default tablespace:

Sql> Select Default_tablespace from dba_users where username = ' DAVE3 ';

Default_tablespace

------------------------------

USERS

-this automatically becomes the default tablespace for our db.

DBAs often encounter a headache: they don't know who created a user on Oracle, and when they do, they don't specify a default tablespace for the user, and the user uses the default tablespace-- The default tablespace used by system users, such as SYS and systems, in the user tablespace is the system tablespace, and the DBA has a way to avoid this problem--to specify the system default Tablespace online:

ALTER DATABASE DEFAULT tablespace <tsname>;

by executing the above command, you can set the default tablespace for the system. In this case, if you do not specify his default tablespace when creating a new user, the system default tablespace specified above will be used as the default tablespace for this purpose.

Sql>conn/as sysdbasql> Create user Zhangsan identified by Zhangsan default Tablespace Myspace1;

The user has created. sql> ALTER DATABASE default Tablespace myspace;

The database has changed.

Sql> create user test identified by test;

The user has created.

Sql> Select Username, default_tablespace defspace from dba_users where username= ' TEST ';

USERNAME Defspace

--------------                 ----------------

TEST MYSPACE

However, once the system default table space has been modified, the original default tablespace for all ordinary users will be specified as the table space, as in the example above , Test1, created by specifying his default tablespace as Myspace1, executed the ' Alter After database default Tablespace myspace , his default tablespace was also changed to MySpace.

Sql> Select Username, default_tablespace defspace from dba_users where username= ' TEST1 ';

USERNAME Defspace

--------------                 ----------------

TEST1 MYSPACE

Oracle's default table space