845      阿裏雲  技術社區[雲棲]

以12c Identity類型示範自我探索式學習方法

Oracle ACE總監，ACOUG聯合創始人，雲和恩墨的聯合創始人，致力於通過不斷的技術探索，幫助中國用戶理解和接觸新技術，推廣數據庫技術應用；熱切關注Oracle技術和其他相關技術。作為社區和網絡的活躍者，在公開演講和出版方麵，多有建樹。

題記：這篇文章首先我會從幾個方麵介紹下我的學習思路和方法，供大家參考。然後借助案例分析的過程來分享在技術研究中的一些技巧。作為一個做技術的人，方法很重要。凡事隻有掌握了有效的方法，才能在學習的過程中事半功倍。但我並不主張籠統地談正確的學習方法是什麼，因為不同的技術領域，甚至是不同的知識背景，都有其特殊性，那就要求我們在學習的過程中發現知識的特殊性，並找到最適合的學習方法。

首先描述一下我自己在學習新知識的時候大概是什麼狀態，什麼思路，因為自認為自己的學習能力還不錯，因此也期望這樣的學習方法對其他人會有幫助。看這篇文章的時候，你可以同步地想一想如果是你遇到這樣的錯誤，你會怎麼處理，怎麼發散，怎麼研究？

Oracle Database12c作為最新一代的Oracle數據庫產品，已經廣為使用，那麼，如果學習一個新版本的數據庫，應該如何開始呢？

我通常是從New Features Guide文檔看起，先通覽文檔的目錄，遇到感興趣的新功能點，就開始做實驗來驗證這個新功能。當然，這之前需要先把新版本的數據庫安裝好、新版本的全部文檔下載到本地，這樣即使你坐在飛機上也有文檔可查。

這次我的計劃是實驗一下Identity類型的字段，這個字段可以用來作主鍵，會自動遞增，這種類型的字段在SQL Server中早就存在，但是Oracle直到12c才推出這個功能。

通常我不會用sys用戶進行任何實驗（除非是驗證sysdba的新功能），因此總是會先創建一個我自己的dba用戶。

在12c中創建這個用戶首先就遇到了錯誤（測試環境啟用了多租戶架構）。

SQL> CREATE USER kamus IDENTIFIED BY oracle DEFAULT tablespace users;

ERROR at line 1:

ORA-65096: invalid common USER OR ROLE name

對於一個不熟悉的錯誤，第一件事情不是去Google，而是用oerr實用程序來看看Oracle自己對這個錯誤是怎麼解釋的。為什麼我喜歡非Windows環境中的Oracle？oerr的存在也是很大一個原因。

錯誤信息的解析非常明確地告知“試圖創建一個通用用戶，必須要用C## 或者c##開頭”，這時候心裏會有疑問，什麼是common user？但是我通常不會先急著去翻文檔，而是先把手頭的事情做完，也就是先把用戶創建上。

SQL> CREATE USER c##kamus IDENTIFIED BY oracle DEFAULT tablespace users;

USER created.

SQL> GRANT dba TO c##kamus; 

GRANT succeeded. 

創建C##KAMUS用戶成功之後，再返回去解決心中的疑問，什麼是common user？在聯機文檔的左上角搜索關鍵字common user，會得到如下圖所示的結果。

通常我會先瀏覽目錄，如果看完覺得心中疑問已經解決，就會返回繼續做之前的實驗，不會再瀏覽其他的鏈接；如果想要查詢怎麼做，比如說如何創建common user，才會繼續去看正文部分。這樣的好處是可以保持專注不至於被過多文檔分心。

但是由於common user這個概念幾乎是嶄新的，所以我很有興趣繼續探索一下：跟common user相對的local user該如何創建。繼續去看正文當然是個方法，但是這裏我選擇的是直接去看SQL Language Reference，因為我們知道一定是在Create User語法裏麵會有不同的定義，進入Create User語法頁麵，直接搜索common user，就可以看到如下這段話。

CONTAINER Clause：

To create alocal user in a pluggable database （PDB）， ensure that the current container is that PDB and specify CONTAINER= CURRENT. To create a common user， ensure thatthe current container is the root and specify CONTAINER = ALL. The name of thecommon user must begin with C## or c##. If you omit this clause and the currentcontainer is a PDB， then CONTAINER = CURRENT is thedefault. If you omit this clause and the current container is the root， then CONTAINER = ALL is the default.

也就是說我們一定要先登錄進一個PDB，才可以創建本地用戶，那麼如何知道現在的SQL*Plus是登錄進了哪個DB呢？這個疑問其實是一個很簡單的聯想，既然需要去一個地方，那麼一定有方法知道我現在在什麼地方，通過簡單地查詢文檔，可以得知以下的方法。現在確實在CDB中。

dbca建庫的時候，有一個新選項是“同時創建PDB”，我勾選過（對於dbca中出現的新選項，如果不是條件不允許，我都會選中進行測試），創建了名字為pdbtest的PDB，那麼現在我想嚐試登錄這個PDB，去創建一個local user。

如何登錄PDB？

Administrator’s Guide中有專門的一個章節“PartVI Managing a Multitenant Environment”來描述如何管理多租戶環境，瀏覽目錄就可以直接找到“Connecting to a PDB with SQL*Plus”這部分，如下所示。

You can use thefollowing techniques to connect to a PDB with the SQL*Plus CONNECT command:

Databaseconnection using easy connect

Databaseconnection using a net service name

那嚐試直接使用easy connect來登錄PDB。

進行如下操作。

PDB沒有Open？

嚐試打開。無法使用startup命令。原因是我使用了舊版本的SQL*Plus（如上所示是10.2.0.4.0）連接到12c數據庫的PDB中，某些新特性不被支持。

SQL> startup

ORA-24543: instance startup OR shutdown NOTallowed IN pluggable DATABASE

使用12c自帶的SQL*Plus登錄，就可以使用startup命令將PDB打開，使用SQL*Plus管理PDB的詳細命令可以參看文檔描述。

或者可以使用如下語句打開PDB。

到此，可以創建localuser了。

SQL> CREATE USER kamus IDENTIFIED BY oracle; 

USER created. 

SQL> GRANT dba TO kamus; 

GRANT succeeded.

那麼在一個PDB中可以看到多少用戶呢？可以看到CDB中的用戶嗎？

這又是一個簡單的聯想，學習的過程其實是一個發散再收縮的循環。看來不可以，隻能看到自己的用戶，當然這裏有很多common user。可以看到即使是在PDB中，cdb_視圖也是可以使用的。

SQL> SELECT CON_ID,COUNT(*) 

FROM cdb_users GROUP BY con_id;

CON_ID   COUNT(*)

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

         3      38

再回到CDB中看一下，會是什麼情況？可以看到所有容器數據庫中的用戶都可以查詢到。

SQL> SELECT CON_ID,COUNT(*) 

FROM cdb_users GROUP BY con_id;

    CON_ID   COUNT(*)

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

         1         36

         2         35

         3         38

終於，我可以回到最開始的實驗目標上去了，在PDB中創建了T1表，id列為Identity類型。

SQL> CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY);

TABLE created.

根據文檔描述，Identity類型仍然是通過Sequence來實現的，那麼應該是自動創建了一個Sequence，果然如此。在你學習的過程中會多此一步來查詢一下Sequence視圖嗎？

SQL> SELECT SEQUENCE_NAME FROM user_sequences;

SEQUENCE_NAME

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

ISEQ$$_91620

默認創建的Sequence，CACHE_SIZE是20，開始值是1，這都跟單獨創建的Sequence默認值一樣。

插入一條數據試一下，報錯報錯還是報錯。所以是generated always的identity列，如果隻有這一列，就沒法插入數據。

換GENERATED BYDEFAULT ON NULL 類型試一下，Wait，如果刪除了表，對應的序列會自動刪除嗎？理論上應該會，當然還是要測試一下。

SQL> DROP TABLE t1;

TABLE dropped.

序列還在？

再建一張測試表。

SQL> CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT ASIDENTITY);

TABLE created.

現在是2個序列了。

寫完整的Drop語句試一下。

SQL> DROP TABLE t2 cascade CONSTRAINT purge;

TABLE dropped.

後麵創建的序列已經被自動刪除了，之前創建的還在。

兩者的不同應該是purge，如果被刪除的表還在回收站中，序列是會保留的，因為表還可能從回收站裏麵再restore回來，需要保證序列仍然有效。那麼清空回收站實驗一下。

SQL> purge recyclebin;

Recyclebin purged.

果然，相應的序列也被刪除了。

SQL> SELECT * FROM user_sequences;

no ROWS selected

再回到正題，創建T3表，插入一條數據。

序列的LAST_NUMBER已經增加為21。

後台如何操作的？使用10046trace，再插入幾條數據。

SQL> INSERT INTO t3 VALUES(NULL);

1 ROW created.

SQL> INSERT INTO t3 VALUES(NULL);

1 ROW created.

SQL> SELECT * FROM t3;

      ID

----------

      1

      2

      3

查看10046 trace的結果。可以看到執行計劃中直接調用了SEQUENCE，就跟之前插入記錄的時候明確指定SEQ.NEXTVAL一樣。其實Oracle的實現方法非常簡單，這一列其實就是Number類型，然後將這一列的Default值設置為”KAMUS”.”ISEQ$$_91624″.nextval，僅此而已。

使用DBMS_METADATA.GET_DDL獲取到的DDL信息，已經符合12c語法的樣式了，顯示出了Sequence的具體信息。

係統自動產生的序列無法手工修改屬性。

係統自動產生的序列也不允許刪除。

在11gR2中，錯誤信息編號在ORA-32790和ORA-32800之間是空白，而12c使用了這其間的8個錯誤號作為新特性的報錯。

ORA-32791: prebuilt table managed column cannot have a default on null.

Cause: An attempt was made to create a materialized view on a prebuilt table that has a managed column with a default on null expression.

Action: Either remove the default on null property, or do not include the column in the materialized view definition.

ORA-32792: prebuilt table managed column cannot be an identity column

Cause: An attempt was made to create a materialized view on a prebuilt table that has a managed column that is an identity column.

Action: Either remove the identity property, or do not include the column in the materialized view definition.

ORA-32793: cannot alter a system-generated sequence

Cause: An attempt was made to alter a system-generated sequence.

Action: A system-generated sequence, such as one created for an identity column, cannot be altered.

ORA-32794: cannot drop a system-generated sequence

Cause: An attempt was made to drop a system-generated sequence.

Action: A system-generated sequence, such as one created for an identity column, cannot be dropped.

ORA-32795: cannot insert into a generated always identity column

Cause: An attempt was made to insert a value into an identity column created with GENERATED ALWAYS keywords.

Action: A generated always identity column cannot be directly inserted. Instead， the associated sequence generator must provide the value.

ORA-32796: cannot update a generated always identity column.

Cause: An attempt was made to update an identity column created with GENERATED ALWAYS keywords.

Action: A generated always identity column cannot be directly updated.

ORA-32797: identity column sequence mismatch in ALTER TABLE EXCHANGE PARTITION.

Cause: The two tables specified in the EXCHANGE have identity columns with sequences that are neither both increasing nor decreasing.

Action: Ensure that the identity columns have sequences with INCREMENT BY having the same sign.

ORA-32798: cannot use ANSI RIGHT or FULL outer join with a left correlation.

Cause: An attempt was made to use a lateral view with a left correlation to the first operand of an ANSI RIGHT or FULL outer join.

Action: Rewrite the query without the left correlation.

到此為止可以休息一下了，從ORA-65096開始大概花費了1個多小時的時間，我學習到了：

1、什麼是common user，什麼是local user？

2、如何查詢現在的環境是CDB還是某個PDB？

3、如何登錄PDB？

4、如何啟動PDB？

5、PDB和CDB中視圖看到的內容有怎樣的不同？

6、如何創建Identity類型的列？

7、刪除表以後，對應的Sequence如何處理？

8、Oracle後台對於Identity列是如何處理的？

文章轉自數據和雲公眾號，原文鏈接

最後更新：2017-07-18 11:32:51

  上一篇：  【趣味問答 12C新特性】時間有效性(temporal validity)

  下一篇：  PHP訪問access數據庫