送38元注册彩金

書名： Oracle DBA基礎教程
作者名：林樹澤
本章字數： 2243字
更新時間： 2021-03-26 13:21:42

4.2 創(chuàng)建表

本節(jié)從實用的角度介紹如何創(chuàng)建一個表以及如何使得創(chuàng)建的表易于管理。我們先介紹Oracle創(chuàng)建表的規(guī)則，并通過實例說明如何創(chuàng)建一個表。

4.2.1 Oracle創(chuàng)建表的規(guī)則

Oracle數據庫推薦了一些與表相關的標準，讀者在實際中最好使用這些標準，對于維護數據庫表和順利建表都很有好處。

● 命名盡量簡單，表名要具有一定的意義，即表名要清楚描述表中存儲的數據內容，如一個臨時員工表的表名為temp_employees。

● 每個表都有一個表空間，這樣易于管理和維護，對一個表空間的維護不影響其他的表，并且該表空間是本地管理的。

● 使用標準EXTENT尺寸減少表空間碎片。

● Oracle數據庫允許表名的最大長度為30個字符。

4.2.2 創(chuàng)建普通表

創(chuàng)建數據庫的目的是存儲數據，而這些數據就存儲在表中，表是數據庫中最基本的數據存儲結構。下面我們使用CREATE TABLE指令來創(chuàng)建表。

要創(chuàng)建表，用戶必須就要創(chuàng)建表的屬性，此時，我們使用dba用戶登錄數據庫服務器，如例子4-2所示。

例子4-2 使用dba用戶登錄數據庫服務器

    SQL> conn /as sysdba
    已連接。

接下來創(chuàng)建一個臨時員工表，該員工表屬于SCOTT用戶，并且存儲在USERS表空間中，如例子4-3所示。

例子4-3 創(chuàng)建一個臨時員工表temp_employees

    SQL> create TABLE scott.temp_employees
      2        (employee_id         number(4),
      3         employee_name       varchar(30),
      4         employee_sex        char,
      5         department          varchar(30))
      6         TABLEspace users;
    
    表已創(chuàng)建。

一旦使用DDL語句創(chuàng)建了表對象，對象的信息如表名，表存儲的表空間等將記錄在數據字典中，數據字典將在第7章講，這里只需要讀者知道這個概念，例子4-3中創(chuàng)建表的信息將記錄在數據字典dba_tables中。下面使用例子4-4驗證是否成功創(chuàng)建該表。

例子4-4 驗證例子4-3中是否成功創(chuàng)建表TEMP_EMPLOYEES

    SQL> select owner,table_name,tablespace_name
      2  from dba_tables
      3  where owner = 'SCOTT';
    
    OWNER                        TABLE_NAME            TABLESPACE_NAME
    ------------------------------ ----------------------------------------------------- ----------------------------
    SCOTT                          BONUS                      USERS
    SCOTT                          DEPT                       USERS
    SCOTT                          DEPT_TEMP                 SYSTEM
    SCOTT                          EMP                        USERS
    SCOTT                          EMP_TEMP
    SCOTT                          ORD                        SYSTEM
    SCOTT                          PRODUCT                   SYSTEM
    SCOTT                          SALGRADE                  USERS
    SCOTT                          SUPPLIER                   SYSTEM
    SCOTT                          TEMP_EMPLOYEES           USERS
    
    已選擇10行。

輸出結果的最后一行說明，已經成功創(chuàng)建了表TEMP_EMPLOYEES，該表所屬的用戶為SCOTT，而存儲該表的表空間為USERS。

說明

如果在創(chuàng)建表時不指定用戶名字，直接寫表名，則默認是當前用戶創(chuàng)建的表，如果不指定表空間名，則Oracle將使用默認表空間創(chuàng)建該表。

在創(chuàng)建表的原則中，Oracle推薦了一個表最好放在一個表空間而且該表空間是本地管理的（減少維護數據字典的負擔），所以如果我們已經創(chuàng)建了一個本地管理的表空間，可以使用更多的參數在本地管理的表空間中創(chuàng)建表，在Oracle10g中創(chuàng)建的表空間，本地管理是默認方式。如例子4-5所示，我們先創(chuàng)建一個本地管理的表空間lin，然后再在該表空間中創(chuàng)建一個表。

例子4-5 創(chuàng)建一個本地管理的表空間lin

    SQL> create TABLEspace lin
      2  datafile 'd:\temp\lin.dbf'
      3  size 30M
      4  extent management local
      5  uniform size 1M;
    
    表空間已創(chuàng)建。
    SQL> create TABLE scott.employees
      2         (ecode    number(4),
      3          ename    varchar2(25),
      4          eaddress varchar2(30),
      5          ephone   varchar2(15))
      6  storage (initial 100k next 100k pctincrease 0 minextents 1
      7  maxextents 8)
      8* TABLEspace lin
    
    表已創(chuàng)建。

這里需要解釋storage中的參數含義，initial指對于該表而言，當表的數據量增加時，需要的自動分配磁盤空間第一次分配100KB，第二次也是100KB，所分配的最大磁盤為8個EXTENTS。最小為1個EXTENTS，而PCTINCREASE是一個權值參數，指當第三次為該表增加磁盤空間時，需要按規(guī)則計算：NEXT*(1+PCTINCRASE/100)(n-2)，其中n>=3，即如果第三次需要增加磁盤空間時，分配100*(1+0/100)(3-2)=100K，第四次需要增加磁盤空間時，分配100*(1+0/100)(4-2)=100K，可以看出如果選擇PCTINCRASE為0，則每次分配的磁盤空間和NEXT參數值相同。

下面使用例子4-6，驗證是否成功建立表employees。

例子4-6 驗證是否成功建立表employees

    SQL> select TABLE_name,TABLEspace_name ,initial_extent,next_extent
      2  from dba_TABLEs
      3  where owner = 'SCOTT'
      4  and TABLE_name = 'EMPLOYEES';
    
    TABLE_NAME    TABLESPACE_NAME    INITIAL_EXTENT    NEXT_EXTENT
    ------------------------- ----------------------------------- ------------ -------------- -------------------
    EMPLOYEES      LIN                              102400         1048574

上述輸出說明，表employees已經成功創(chuàng)建，并且在表空間Lin中，該表的參數INITIAL_EXTENT為100KB(102400/100 = 100KB)，而且NEXT_EXTENT也為100KB。

4.2.3 創(chuàng)建臨時表

臨時表是非常特殊的表，該表只對當前用戶的當前會話有效。創(chuàng)建臨時表的目的就是使得某些操作效率更高。臨時表中的數據是當前會話的私有數據，當前會話只操作自己的數據，沒有數據鎖的爭用，這極大地提高了臨時表操作的效率。下面依次對創(chuàng)建臨時表和臨時表的可見性做詳細介紹。

下面通過使用CREATE GLOBAL TEMPORARY指令來創(chuàng)建臨時表。如例子4-7所示，該臨時表為SCOTT用戶的EMP表中所有JOB為MANAGER的員工信息。

例子4-7 使用CREATE GLOBAL TEMPORARY指令來創(chuàng)建臨時表

    SQL>  create global temporary TABLE
      2   scott.emp_temporary
      3   on commit preserve rows
      4   as
      5   select *
      6   from scott.emp
      7   where job = 'MANAGER';
    
    表已創(chuàng)建。

注意

該臨時表默認存儲在系統(tǒng)的臨時段中，如果臨時表空間為空，也無法創(chuàng)建成功，會有如下錯誤提示。

    SQL> create global temporary TABLE
      2  scott.emp_temporary
      3  on commit preserve rows
      4  as
      5  select *
      6  from scott.emp
      7  where job = 'MANAGER';
    from scott.emp
               *
    ERROR 位于第 4 行:
    ORA-25153: 臨時表空間為空

遇到這樣的問題，只要新建立一個臨時表空間，然后改變系統(tǒng)的臨時表空間為新建立的表空間即可。

讀者可以根據例子4-8驗證是否成功創(chuàng)建該臨時表。

例子4-8 驗證是否成功創(chuàng)建該臨時表

    SQL> select owner,TABLE_name,TABLEspace_name
      2  from dba_TABLEs
      3  where TABLE_name = 'EMP_TEMPORARY';
    
    OWNER                TABLE_NAME           TABLESPACE_NAME
    -------------------- -------------------- ---------------------------- ----------------------------
    SCOTT                EMP_TEMPORARY

輸出結果中TABLESPACE_NAME列為空，說明臨時表并不存放在默認表空間，也不存放在臨時表空間中，而是存儲在臨時段中，臨時段是一個磁盤區(qū)，當用戶使用SQL語句執(zhí)行查詢時，如果需要對返回的數據進行排序，Oracle首先需要在內存中完成排序工作，如果內存容量不夠，就需要把計算的中間結果放在臨時段中。

例子4-9 查詢表EMP_TEMPORARY是否為臨時表

    SQL> select table_name,tablespace_name,temporary
      2  from dba_tables
      3  where owner = 'SCOTT'
      4  and  table_name = 'EMP_TEMPORARY';
    
    TABLE_NAME           TABLESPACE_NAME                T
    ------------------------------------ -------------------------------------------------------
    EMP_TEMPORARY                                           Y

例子4-9的輸出說明，表EMP_TEMPORARY為臨時表，而且該表沒有存放在用戶SCOTT的默認表空間中，而是存儲在臨時段中。

臨時表在當前用戶的當前會話下可用。如果用戶使用其他用戶登錄如使用dba用戶，或者重新啟動了數據庫，則無法使用該臨時表。例子4-10說明了當使用相同的用戶名，如SYS用戶重新登錄數據庫時，查詢臨時表emp_temporary的輸出結果。

例子4-10 查詢臨時表emp_temporary的輸出結果

    SQL> conn /as sysdba
    已連接。
    SQL> desc scott.emp_temporary;
     名稱                                                  是否為空? 類型
     --------------------------------------------- -------- -------------
     EMPNO                                                          NUMBER(4)
     ENAME                                                          VARCHAR2(10)
     JOB                                                            VARCHAR2(9)
     MGR                                                            NUMBER(4)
     HIREDATE                                                       DATE
     SAL                                                            NUMBER(7,2)
     COMM                                                           NUMBER(7,2)
     DEPTNO                                                         NUMBER(2)
    
    SQL> select *
      2  from emp_temporary;
    from emp_temporary
         *
    ERROR 位于第 2 行:
    ORA-00942: 表或視圖不存在

上例說明，使用DBA（用戶名為SYS）用戶重新登錄數據庫時，可以查看到臨時表emp_temporary的數據字典定義，但是不能成功查詢該表中的數據。也就是說臨時表只對當前用戶的當前會話有效，一旦用戶退出當前會話，則臨時表就失去了作用。

如果不再使用臨時表，則最好刪除，畢竟它占用存儲空間，而且一旦用戶改變或重新登錄，都無法重新使用該表，如例子4-11所示為刪除臨時表。

例子4-11 刪除臨時表

    SQL> DROP TABLE scott.emp_temporary;
    
    表已丟棄。

官术网_书友最值得收藏!

Oracle DBA基礎教程

4.2 創(chuàng)建表

4.2.1 Oracle創(chuàng)建表的規(guī)則

4.2.2 創(chuàng)建普通表

4.2.3 創(chuàng)建臨時表