17.管理表(筆記)

17.管理表

建立普通表

下面以建立DEPARTMENT表為例,說明使用CREATE TABLE語句建立普通表的方法.

CREATE TABLE department(

Deptno NUMBER(4),dname VARCHAR2(14), loc VARCHAR2(13)

)PCTFREE 20 PCTUSERD 50 TABLESPACE user01

STORAGE(MINEXTENTS 3);

PCTFREE:用於指定資料塊為UPDATE操作所預留空間的百分比.當資料塊剩餘空間低於20%(PCTFREE=20)時,不好在該資料塊中繼續插入資料,並且這些剩餘空間是為UPDATE操作所保留的.合理地設定PCTFREE,可以避免出現行遷移.

PCTUSED:用於指定資料塊上可以重新插入資料的已用空間最低百分比.當資料佔用空間達到80%後,該資料塊將不能插入資料;刪除部分資料後,如果資料時間佔用空間低於50%(PCTUSED=50),那麼可以在該資料塊上重新插入資料.

TABLESPACE:用於指定表段所在表空間,如果不指定該選項,oracle會將表段建立在使用者的預設表空間上.

STORAGE;用於指定表段的儲存引數.其中,MINEXTENTS用於指定表段DEPARTMENT的初始區個數為3,透過查詢資料字典檢視USER_SEGMENTS,可以顯示段所在的表空間,區個數以及尺寸.

SELECT tablespace_name,extents,bytes, FROM user_segments

WHERE segment_name=’DEPARTMENT’;

建立臨時表

臨時表用於存放會話或事務的私有資料,建立臨時表後,其結果會一直存在,但其資料只在當前事務內或當前會話內有效.需要注意,當在臨時表上執行DML操作時,既不會加鎖,也不會將資料變化寫到重做日誌中.

1,事務臨時表是指資料只在當前事務內有效的臨時表.如果建立臨時表時沒有指定ON COMMIT選項,則預設為事務臨時表.透過指定ON COMMIT DELETE ROWS選項,也可以指定事務臨時表.下面以建立和使用事務臨時表TEMP1為例,說明建立和使用的方法.

CREATE GLOBAL TEMPORARY TABLE temp1(cola INT)

ON COMMIT DELETE ROWS;

事務臨時表TEMP1的資料只在當前事務內可以檢視;當使用COMMIT或ROLLBACK結束事務後,其臨時資料會被自動清除.

2,會話臨時表時資料只在當前會話內有效的臨時表.建立臨時表時,透過使用ON COMMIT PRESERVE ROWS選擇.可以指定會話臨時表.

修改普通表

當給表增加列,刪除列,重新組織表,分配和釋放表空間時,需要使用ALTER TABLE修改表.

1,修改表的物理屬性.

使用ALTER TABLE改變塊空間使用引數PCTFREE和PCTUSED時,新設定對所有塊都起作用,但對應已分配塊不會立即生效,而使用ALTER TABLE改變事務入口INITRANS時,該設定只會對將來分配的資料庫生效.

ALTER TABLE department

PCTFREE 40 PCTUSED 35 INITRANS 3;

2,重新組織表

若在表上頻繁地執行DML操作時,會產生空間碎片和行遷移.當使用ALTER TABLE重新組織表時,還可以使用TABLESPACE選項將表移動到其他表空間.

ALTER TABLE department MOVE TABLESPACE user02;

執行ALTER TABLE命令重新組織表時,因為ROWID會發生改變,從而導致表的所有索引轉變為無效狀態,所有在重新組織表之後必須重新建立索引.

3,手工分配和釋放空間

擴充套件表空間

ALTER TABLE department ALLOCATE EXTENT

(SIZE 500K DATAFILE ‘g:demouser02.dbf’);

如果表段實際佔用空間多於所需空間,那麼可以釋放所佔用的剩餘空間.預設情況下釋放剩餘表空間後表段的區個數不會低於MINEXTENTS,而如果要釋放MINEXTENTS下的剩餘空間,需要帶有KEEP 0 選項.

4,修改列定義

使用ALTER TABLE … MODIFY命令可以修改列定義,包括列的資料型別,列的長度和列的預設值.

ALTER TABLE department MODIFY loc VARCHAR2(15) DEFAULT ‘BEIJING’;

需要注意,當縮減CHAR型別列的長度時,如果該表已經包含資料,就必須將初始化引數blane trimming設定為TRUE

5增加列

使用ALTER TABLE … ADD 命令可以增加列,增加列時需要指定列名及其資料型別,另外還可以指定預設值和NOT NULL約束.

ALTER TABLE department ADD remark VARCHAR2(200) DEFAULT ‘GOOD’;

需要注意,如果表採用壓縮選項(COMPRESS),在增加表列時將不能為其指定預設值.

6,修改列名.

使用ALTER TABLE … RENAME COLUMN 可以修改表列的名稱,但修改列名會使檢視,過程等相關物件轉變為無效狀態.

ALTER TABLE department RENAME COLUMN loc TO location;

7,刪除列

使用ALTER TABLE …. DROP COLUMN 可以刪除一列.使用ALTER TABLE … DROP 選項可以刪除多列.

ALTER TABLE emp DROP COLUMN comm.;

ALTER TABLE department DROP (phone,manager);

8,使用UNUSED選項刪除列

刪除表列時,如果該表包含大量資料,刪除列的時間會很長.在資料庫執行的高峰階段,刪除表列會明顯地影響效能.在這種情況下,如果確定某些表列不再需要,應該首先將表列標記為UNUSED,然後在資料庫空閒階段刪除表列.

使用ALTER TABLE … SET UNUSED COLUMN選項可以將列標記為UNUSED列,

使用ALTER TABLE…. DROP UNUSED COLUMN可以刪除UNUSED列.

ALTER TABLE emp SET UNUSED COLUMN comm.;

ALTER TABLE emp DROP UNUSED COLUMN CHECKPOINT 1000;

刪除表列時,CHECKPOINT 1000用於指定每刪除1000行發出一次檢查點,以節省UNDO段的空間使用.如果在刪除表列的過程中出現例程失敗,那麼在重新啟動資料庫之後使用CONTINUE選項可以繼續刪除操作.

ALTER TABLE emp DROP COLUMNS CONTINUE CKECKPOINT 1000;

管理索引表

索引表以B-樹結構來組織表的資料,它是主鍵B-樹索引的變種.對於普通表而言,其資料以無序方式儲存;而對於索引表來說,其資料以B-樹結構來組織,並且其葉塊既包含鍵列資料,也包含非鍵列資料.一般情況下,表及其索引資料分別存放在表段和索引段中.當在WHERE子句中引用索引列時,首先定位索引資料並取得ROWID,然後根據ROWID取得表的資料.如果要經常基於主鍵列檢索表資料,那麼ORACLE建議使用索引表,建立索引表時,oracle會將表及其主鍵索引的資料一起存放到索引段中.當在WHERE子句中引用主鍵列時,oracle可以根據主鍵索引值阻斷錶行資料.

建立索引表是使用CREATE TABLE語句完成的,需要注意,建立索引表是,必須指定ORGANIZATION INDEX關鍵字,並且必須定義主鍵約束.下面以建立索引表SALES_INFO為例,說明建立索引表的方法

CREATE TABLE sales_info(

Id NUMBER(6) CONSTRAINT pk_sale PRIMARY KEY,

Customer_name VARCHAR2(30),sales_amount UNMBER(10,2),

Sales_date DATE,remark VARCHAR2(2000)

) ORGANIZATION INDEX TABLESPACE user01

PCTTHRESHOLD 20 INCLUDING remark

OVERFLOW TABLESPACE user02;

執行上述語句後,會建立索引表SALES_INFO,其鍵列和非鍵列的資料會放到主鍵約束所對應的索引段PK_SALE中,而溢位資料則會存放到溢位段SYS_IOT_OVER_n(n:索引表的物件號)中,定義索引表時,主鍵約束和ORGANIZATION INDEX選項時必須指定的,而PCTTHRESHOLD,INCLUDING和OVERFLOW TABLESPACE 選項既可以指定,也可以不指定.

ORGANIZATION INDEX:用於指定索引表,而TABLESPACE則用於指定主鍵索引所在的表空間.

PCTTHRESHOLD:用於指定資料塊中為鍵列和部分非建列資料所預留空間的百分比

INCLUDING column:該選項用於指定資料被存放到溢位段所在的表空間!

修改索引表

修改索引表是使用ALTER TABLE命令完成的.與修改普通表一樣,所有修改選項(ADD,MODIFY,DROP COLUMN, DROP CONSTRAINT)都可以在說要表上使用.需要注意,索引表的主鍵約束不能被刪除,延期和禁止.

1,移動索引表

ALTER TABLE sales_info MOVE TABLESPACE user01;

2,增加溢位段

ALTER TABLE iot1 ADD OVERFLOW TABLESPACE user02;

3,修改其他選項

當修改索引表時,OVERFLOW選項之前的所有選項只使用於索引段,而OVERFLOW之後的選項只使用於溢位段.

ALTER TABLE sales_info

INITRANS 4 PCTTHRESHOLD 15 INCLUDING remark

OVERFLOW INITRANS 6;

4,轉換索引表為普通表.

建立索引表後,使用CREATE TABLE AS SELECT 語法可以將其轉變為普通表.

CREATE TABLE sales_info_new AS SELECT * FROM sales_info;

管理外部表

外部表是表結構被存放在資料字典,而表資料被存放在OS檔案中的表.透過使用外部表,不僅可以在資料庫中查詢OS檔案的資料,還可以使用INSERT方式將OS檔案資料裝載到資料庫中,從而實現SQL*Loader所提供的功能,建立外部表後,可以查詢外部表的資料,在外部表上執行連線查詢,或對外部表進行排序.需要注意,在外部表上不能執行DML修改,也不能在外部表上建立索引.

(具體省略)

截斷和刪除表

1,截斷表

當表結構必須保留,而表資料不再需要時,可以使用TRUNCATE TABLE命令截斷表.執行改命令時,會刪除表的所有資料,並釋放表所佔用的空間,但會保留表的結構

TRUNCATE TABLE emp;

2,刪除表

當表不再需要時,可以使用DROP TABLE命令刪除表.執行DROP TABLE時,不僅會刪除表的所有資料,也會刪除表結構.

DROP TABLE department CASCADE CONSTRAINTS;

CASCADE CONSTRAINTS用於級聯刪除,如果被刪除表與其他表具有主從關係,那麼刪除主表時必須帶有該選項.

顯示錶資訊

1,顯示特定使用者的表

DAT_TABLES 可以顯示所有資料庫表的詳細資訊

ALL_TABLES 可以顯示使用者可以訪問的所有表資訊

USER_TABLES 可以顯示當前使用者所有表的資訊

SELECT table_name,num_rows,pct_free,blocks,chain_cnt

FROM dba_tables WHERE owner=’SCOTT’;

Table_name用於標識表名,num_rows用於標識表所包含的行數.pct_free用於標識PCTFREE的值,blocks用於標識表資料已經佔用的塊個數,chain_cnt用於標識鏈行的個數,owner用於標識物件所有者.

2,顯示列資訊.

DBA_TAB_COLUMNS,可以顯示資料庫任何表的列資訊.

ALL_TAB_COLUMNS,可以顯示使用者可訪問的列資訊

USER_TAB_COLUMNS,可以顯示當前使用者所擁有表的列資訊.

3,顯示錶的註釋資訊

DBA_TAB_COMMENTS,顯示資料庫任何表或檢視的註釋;

ALL_TAB_COMMENTS,顯示使用者可訪問表的註釋

USER_TAB_COMMENTS,顯示當前使用者所擁有表的註釋.

4,顯示列的註釋資訊

DBA_COL_COMMENTS,

ALL_COL_COMMENTS,

USER_COL_COMMENTS.

5,顯示UNUSED列資訊

DBA_UNUSED_COL_TABS,可以顯示任何表的UNUSED列個數

ALL_UNUSED_COL_TABS,可以顯示使用者可訪問表的UNUSED列個數

USER_UNUSED_COL_TABS,顯示當前使用者所擁有表的UNUSED列個數.

6,顯示外部表的資訊

DBA_EXTERNAL_TABLES,顯示資料庫所有外部表的資訊.

ALL_EXTERNAL_TABLES,顯示使用者可訪問的所有外部表的資訊.

USER_EXTERNAL_TABLES,可以顯示當前使用者所擁有外部表的資訊

7,顯示外部表的位置

DBA_EXTERNAL_LOCATIONS,顯示資料庫所有外部表的位置.

ALL_EXTERNAL_LOCATIONS

USER_EXTERNAL_LOCATIONS

8,顯示錶段資訊

DBA_SEGMENTS,可以顯示資料庫所有段的詳細資訊.

USER_SEGMENTS,可以顯示當前使用者段的資訊

9顯示行所在的實際位置

執行INSERT操作時,oracle會將資料插入到表段的相應資料塊中,並且oracle會生成唯一的ROWID對應於該行資料.因為ROWID是頁碼格式存放的,所有用於不能直接讀懂其內容.透過使用DBMS_ROWID包,可以將ROWID轉變成行所在檔案號,塊號以及行號.