oracle的預設資料表空間

標籤：blog   http   io   ar   使用   for   sp   strong   資料   

一.預設資料表空間說明

在oracle9i中，如果未用 DEFAULT TABLESPACE 子句建立的所有使用者將以SYSTEM資料表空間作為它們的預設資料表空間。同時也不能為資料庫指定一個預設的資料表空間。

在Oracle 10g中定義了資料庫層級的預設資料表空間USERS，在建立使用者時沒有定義預設資料表空間，就會把資料庫層級的預設資料表空間當作自己的預設資料表空間。

在10g以前，是把系統資料表空間（SYSTEMS）當作預設資料表空間。這樣很不合理。

 

            可以使用如下命令查看預設資料表空間：

 

[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‘  

可以使用如下命令修改預設資料表空間：

 

ALTER DATABASE DEFAULT TABLESPACE users;

 

這裡有幾個注意事項：

1.     如果我們在建立使用者時指定了預設資料表空間，那麼在修改預設資料表空間後，之前使用者的預設資料表空間也會發生改變。

2.     如果我們在建立使用者時沒有指定使用者資料表空間，那麼預設也會使用DB的預設資料表空間，這時候如果我們修改了DB的預設資料表空間，使用者的資料表空間也會發生改變。

3.     如果我們在建立使用者指定使用者的資料表空間是其他的資料表空間，那麼我們修改DB的預設資料表空間不會影響使用者的資料表空間。

4.     DB的預設資料表空間不能刪除，除非將預設資料表空間指向其他資料表空間之後才可以刪除。

5.     如果使用者的預設資料表空間指向其他的資料表空間，當這個資料表空間被drop 之後，使用者的預設資料表空間會自動指向DB的預設資料表空間。

 

二.樣本2.1查看DB 版本：

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查看當前的預設資料表空間

SQL> SELECT PROPERTY_VALUE

 2    FROM database_properties

 3   WHERE PROPERTY_NAME =‘DEFAULT_PERMANENT_TABLESPACE‘;

 

PROPERTY_VALUE

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

USERS

 

2.3 建立使用者，不指定預設資料表空間

SQL> create user dave1 identified bydave1;

User created.

SQL> select default_tablespace from dba_users where username =‘DAVE1‘;

 

DEFAULT_TABLESPACE

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

USERS

--預設資料表空間為users。

 

2.4 建立使用者指定預設資料表空間為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 建立使用者指定的預設資料表空間為其他資料表空間

 --建立資料表空間：

SQL> create tablespace dave3 datafile‘D:\APP\ADMINISTRATOR\ORADATA\NEWCCS\dave3.dbf‘ size 10M;

Tablespace created.

 

--建立使用者：

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 修改DB的預設資料表空間

SQL> ALTER DATABASE DEFAULT TABLESPACEdave3;

Database altered.

 

2.7 查看之前建立使用者的資料表空間

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

--注意這裡Dave1，dave2使用者的預設資料表空間都變成了dave3.

 

 

2.8 DB的預設資料表空間不能刪除

--現在dave3是我們的預設資料表空間

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:

ORA-12919: Can not drop the defaultpermanent tablespace

這裡報錯ORA-12919.

 

我們把預設資料表空間改成USERS，在測試：

SQL> ALTER DATABASE DEFAULT TABLESPACEUSERS;

Database altered.

 

SQL> drop tablespace dave3 including contents and datafiles;

Tablespace dropped.

 

Dave3資料表空間成功drop。 注意，我們的dave3使用者的資料表空間之間是指向dave3的，現在我們把dave3資料表空間drop了，我們看一下dave3 現在預設資料表空間：

 

SQL> select default_tablespace from dba_users where username =‘DAVE3‘;

DEFAULT_TABLESPACE

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

USERS

--這裡自動變成了我們的DB 預設的資料表空間。

 

 

  DBA們經常會遇到一個這樣令人頭疼的問題：不知道誰在Oracle上建立了一個使用者，建立時，沒有給這個使用者指定預設資料表空間，這個使用者就會採用預設的資料表空間——users資料表空間（sys和system等系統使用者採用的預設資料表空間是system資料表空間），DBA有辦法避免這種問題了——線上指定系統預設資料表空間：

    ALTER DATABASE DEFAULT TABLESPACE <tsname>;

    通過執行以上命令，可以設定系統的預設資料表空間。這樣的話，在建立新使用者時，如果不指定他的預設資料表空間，就會採用上面指定的系統預設資料表空間作為這個用的預設資料表空間。

SQL>conn /as sysdbaSQL> create user zhangsan identified by zhangsan default tablespace myspace1;

使用者已建立。SQL> alter database default tablespace myspace;

資料庫已更改。

SQL> create user test identified by test;

使用者已建立。

SQL> select username, default_tablespace defspace from dba_users where username=‘TEST‘;

USERNAME                       DEFSPACE

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

TEST                           MYSPACE

    然而一旦將系統預設資料表空間修改了以後，原有的所有普通使用者的預設資料表空間都會被指定為這個資料表空間，如上例中test1，建立時指定了他的預設資料表空間為myspace1，執行了’alter database default tablespace myspace’後,他的預設資料表空間也被改為了myspace。

SQL> select username, default_tablespace defspace from dba_users where username=‘TEST1‘;

USERNAME                       DEFSPACE

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

TEST1                          MYSPACE

oracle的預設資料表空間