ORACLE中，Schema的理解

一直沒明白Schema的實質含義，今天網上看到一篇文章，說的是在是好，特地儲存下來，雖然說的是SQL SERVER 2005的。 引自：http://oracle.banma.com/news/20080703/12566.shtml

 

 假如我們想瞭解資料庫中的User和Schema究竟是什麼關係，首先必須瞭解一下資料庫中User和Schema到底是什麼概念。

在SQL Server2000中，由於架構的原因，User和Schema總有一層隱含的關係，讓我們很少意識到其實User和Schema是兩種完全不同的概念，不過在SQL Server2005中這種架構被打破了，User和Schema也被分開了。

首先我來做一個比喻，什麼是Database，什麼是Schema，什麼是Table，什麼是列，什麼是行，什麼是User？我們可以可以把Database看作是一個大倉庫，倉庫分了很多很多的房間，Schema就是其中的房間，一個Schema代表一個房間，Table可以看作是每個Schema中的床，Table（床）就被放入每個房間中，不能放置在房間之外，那豈不是晚上睡覺無家可歸了J。，然後床上可以放置很多物品，就好比Table上可以放置很多列和行一樣，資料庫中儲存資料的基本單元是Table，現實中每個倉庫放置物品的基本單位就是床， User就是每個Schema的主人，（所以Schema包含的是Object，而不是User），其實User是對應與資料庫的（即User是每個對應資料庫的主人），既然有操作資料庫（倉庫）的權利，就肯定有操作資料庫中每個Schema（房間）的權利，就是說每個資料庫映射的User有每個Schema（房間）的鑰匙，換句話說，如果他是某個倉庫的主人，那麼這個倉庫的使用權和倉庫中的所有東西都是他的（包括房間），他有完全的操作權，可以扔掉不用的東西從每個房間，也可以放置一些有用的東西到某一個房間，呵呵，和現實也太相似了吧。我還可以給User分配具體的許可權，也就是他到某一個房間能做些什麼，是只能看（Read-Only），還是可以像主人一樣有所有的控制權（R/W），這個就要看這個User所對應的角色Role了，至於分配許可權的問題，我留在以後單獨的blog中詳述。比喻到這裡，相信大家都清楚了吧。

在SQL Server2000中，假如我們在某一個資料庫中建立了使用者Bosco，按麼此時後台也為我們預設地建立了預設Schema 【Bosco】。Schema的名字和User的名字相同，這也是我們分不清楚使用者和Schema的原因。

在SQL Server2005中，為了向後相容，當你用sp_adduser 預存程序建立一個使用者的時候，SQL Server2005同時也建立了一個和使用者名稱相同的Schema，然而這個預存程序是為了向後相容才保留的，我們應該逐漸熟悉用新的DDL語言Create User和Create Schema來操作資料庫。在SQL Server2005中，當我們用Create User建立資料庫使用者時，我們可以為該使用者指定一個已經存在的Schema作為預設Schema，如果我們不指定，則該使用者所預設的Schema即為dbo Schema，dbo 房間（Schema）好比一個大的公用房間，在當前登入使用者沒有預設Schema的前提下，如果你在大倉庫中進行一些操作，比如Create Tabe，如果沒有指定特定的房間（Schema），那麼你的物品就只好放進公用的dbo房間（Schema）了。但是如果當前登入使用者有預設的Schema，那麼所做的一切操作都是在預設Schema上進行（比如當前登入使用者為login1，該使用者的預設Schema為login1，那麼所做的所有操作都是在這個login1預設Schema上進行的。實驗已經證明的確如此）。估計此時你會有一點暈，為什麼呢？我剛才說dbo是一個Schema，但是你可以在資料庫中查看到，dbo同時也是一個user，暈了吧，呵呵。

在SQL Server2005中建立一個資料庫的時候，會有一些Schema包括進去，被包括進去的Schema有：dbo，INFORMATION_SCHEMA, guest，sys等等（還有一些角色Schema，不提了，有暈了）。

我在上文中已經提到了，在SQL Server2005中當用預存程序sp_adduser建立一個user時，同時SQL Server2005也為我們建立了一個預設的和使用者名稱相同的Schema，這個時候問題出來了，當我們create table A時，如果沒有特定的Schema做首碼，這個A表建立在了哪個Schema上，即進入了哪個房間？答案是：

1.如果當前操作資料庫的使用者（可以用Select current_user查出來）有預設的Schema（在建立使用者的時候指定了），那麼表A被建立在了預設的Schema上。

2.如果當前操作資料庫的使用者沒有預設的Schema（即在建立User的時候預設為空白），但是有一個和使用者名稱同名的Schema，那麼表A照樣被建立在了dbo Schema上，即使有一個和使用者名稱同名的Schema存在，由於它不是該使用者預設的Schema，所以建立表的時候是不會考慮的，當作一般的Schema來處理，別看名字相同，可是沒有任何關係哦。

3.如果在建立表A的時候指定了特定的Schema做首碼，則表A被建立在了指定的 Schema上（有許可權嗎？）

現在問題又出來了，在當前操作資料庫的使用者（用select current_user可以查看到，再次強調）沒有預設Schema的前提下，當我們用Create table A語句時，A表會去尋找dbo Schema，並試圖建立在dbo Schema上，但是如果建立A表的使用者只有對dbo Schema的唯讀許可權，而沒有寫的許可權呢？這個時候A表既不是建立不成功，這個就是我以後會提及到的Login,User, Role和Schema四者之間的關係。在這裡，為了避免混淆和提高操作資料庫的速度（在少量資料範圍內，對我們肉眼來說幾乎看不到差異），我們最好每次在操作資料庫對象的時候都顯式地指定特定的Schema最為首碼。

現在如果登入的使用者為Sue，該使用者有一個預設Schema也為Sue，那麼如果現在有一條查詢語句為Select * from mytable, 那麼搜尋每個房間（Schema）的順序是怎樣的呢？順序如下：

1. 首先搜尋sys.mytable （Sys Schema）

2. 然後搜尋Sue.mytable (Default Schema)

3. 最後搜尋 dbo.mytable (Dbo Schema)

執行的順序大家既然清楚了，那麼以後在查詢資料庫表中的資料時，最好指定特定的Schema首碼，這樣子，資料庫就不用去掃描Sys Schema了，當然可以提高查詢的速度了。

另外需要提示一下的是，每個資料庫在建立後，有4個Schema是必須的（刪都刪不掉），這4個Schema為：dbo，guest，sys和INFORMATION_SCHEMA，其餘的Schema都可以刪除。