Description of Oracle default tablespace (default permanent tablespace)

This article is transferred from:

Http://blog.csdn.net/tianlesoftware/article/details/7084700. table Space Description

In Oracle9i, if you do not use the default tablespace clause, all users will use the system tablespace as their default tablespace. You cannot specify a default tablespace for the database.

The database-level default tablespace users is defined in Oracle 10 Gb. When a user is created without a default tablespace, the database-level default tablespace is treated as its own default tablespace.

Before 10 Gb, the system tablespace (systems) was used as the default tablespace. This is unreasonable.

 

Run the following command to view the default tablespace:

[SQL]
View plaincopyprint?

1. /* Formatted on 15:57:48 (qp5 v5.185.11230.41888 )*/
2. Select property_value
3. From database_properties
4. Where property_name = 'default _ permanent_tablespace'

/* Formatted on 15:57:48 (qp5 v5.185.11230.41888) */<br/> select property_value <br/> from database_properties <br/> where property_name = 'default _ permanent_tablespace'
You can use the following command to modify the default tablespace:

Alter database default tablespace users;

 

Here are several notes:

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

2. if we do not specify the user tablespace when creating a user, the default dB tablespace will also be used by default. At this time, if we modify the default dB tablespace, the user tablespace will also change.

3. If the user-specified tablespace is another tablespace during creation, modifying the default tablespace of the database will not affect the user's tablespace.

4. The default tablespace of a database cannot be deleted unless it is directed to another tablespace.

5. If the user's default tablespace points to another tablespace, after the tablespace is dropped, the user's default tablespace will automatically point to the DB's default tablespace.

 

Ii. Example 2.1 view the 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 view the current default tablespace

SQL> select property_value

2 from database_properties

3 where property_name = 'default _ permanent_tablespace ';

 

Property_value

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

Users

 

2.3 create a user without specifying the default tablespace

SQL> create user dave1 identified bydave1;

User Created.

SQL> select default_tablespace from dba_users where username = 'dave1 ';

 

Default_tablespace

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

Users

-- The default tablespace is users.

 

2.4 create the default tablespace specified by the user as the database

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 the default tablespace specified by the user as another tablespace.

-- Create a tablespace:

SQL> Create tablespace dave3 datafile 'd: \ app \ Administrator \ oradata \ newccs \ dave3.dbf' size 10 m;

Tablespace created.

 

-- Create a 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 modify the default tablespace of a Database

SQL> alter database default tablespacedave3;

Database altered.

 

2.7 view the previously created user tablespace

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 in dave1, the default tablespace of dave2 is changed to dave3.

 

 

The default tablespace of 2.8 dB cannot be deleted.

-- Now dave3 is our default tablespace.

SQL> select property_value

2 from database_properties

3 where property_name = 'default _ permanent_tablespace'

4;

 

Property_value

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

Dave3

 

-- Drop dave3:

SQL> drop tablespace dave3 includingcontents and datafiles;

Drop tablespace dave3 including contentsand datafiles

*

Error at line 1:

The ORA-12919: Can not drop the defaultpermanent tablespace

Here error ORA-12919.

 

Change the default tablespace to users. In the test:

SQL> alter database default tablespaceusers;

Database altered.

 

SQL> drop tablespace dave3 including contents and datafiles;

Tablespace dropped.

 

The dave3 tablespace is successfully dropped. Note: Our dave3 user's tablespace points to dave3. Now we drop the dave3 tablespace. Let's take a look at the default tablespace of dave3:

 

SQL> select default_tablespace from dba_users where username = 'dave3 ';

Default_tablespace

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

Users

-- The default tablespace of the database is changed here.