目的： 增加約束 創建索引 使用CREATE TABLE語句創建索引 創建函數索引 刪除列和將列置為UNUSED 平臺FLASHBACK閃回操作 創建臨時表 創建和使用外部表 ALTER TABLE語句 -增加列 -修改已存在的列 -定義列的預設值 -刪除列 語法 ALTER TABLE table ...
目的： 增加約束 創建索引 使用CREATE TABLE語句創建索引 創建函數索引 刪除列和將列置為UNUSED 平臺FLASHBACK閃回操作 創建臨時表 創建和使用外部表 ALTER TABLE語句 -增加列 -修改已存在的列 -定義列的預設值 -刪除列 語法 ALTER TABLE table_name ADD (column datatype [DEFAULT expr] [，column datatype] . . .); ALTER TABLE table_name MODIFY (column datatype [DEFAULT expr] [，column datatype] . . .); ALTER TABLE table_name DROP (column); ALTER TABLE table_name SET UNUSED column_name; ALTER TABLE table_name DROP UNUSED COLUMNS; ALTER TABLE table_name READ ONLY; ALTER TABLE table_name READ WRITE; 增加列 -新增列不能指定列的顯示位置。新列成為最後一列 註意：增加新列NOT NULL約束時，如果表中已有資料NOT NULL約束將失敗。 修改列 -可以對列的數據類型、大小和預設值進行更改。 -滿足以下條件，可以減少列的寬度 -該列僅包含空值 -空表 -減少的列寬不小於該列中的現有值 -如果該列子包含空值，則可以更改數據類型。char-to-varchar2有數據也可以轉換。 -對列的預設值更改只會影響後續的插入。 刪除列 -該列包含或不包含數據 -使用ALTER TABLE 語句，一次只能刪除一列 -表格中必須至少保留一列 -列被刪除後，無法恢復 -如果列是約束的一部分或者是索引鍵上的欄(測試主鍵和外鍵)，則不能刪除列，除非添加了cascade級聯選項 -如果列上有大量的數據，刪除列需要一些時間。這種情況下最好將其設置為UNUSED狀態，併在系統上的用戶較少時將其丟棄，以避免擴大鎖定。 註意：某些列永遠不能被刪除，例如構成分區表分區鍵的列或構成索引組織表的主鍵一部分的列。 SET UNUSED -SET UNUSED將一列或多列標記為未使用，以便在系統資源需求較低時可將其刪除。它並不會從實際空間進行刪除，因此響應時間比執行DROP子句更快。列被標記為未使用之後，將無權訪問該列。 -SET UNUSED信息存儲於USER_UNUSED_COL_TABS數據字典中。 註意：SET UNUSED的原則與DROP相似 約束 -可以增加、刪除、啟用/關閉約束，但不能改變其結構。 -增加NOT NULL約束，必須使用ALTER TABLE MODIFY語句。 註意：只能在空表的列上增加NOT NULL約束或者列上每一行都有值。 語法 ALTER TABLE <table_name > ADD [CONSTRAINT <constraint_name>] TYPE (<column_name>); -ON DELETE CASCADE級聯刪除 允許子表引用的父鍵數據被刪除，但不被更新。當父表中鍵值刪除時，子表中依賴值的所有行也被刪除。在FOREIGN KEY外鍵約束中指定ON DELELTE CASCADE選項。 -ON DELETE SET NULL級聯置空 當父表中鍵值刪除時，子表中依賴值置空。 延遲約束 -DEFERRABLE or NOT DEFERRABLE -INITIALLY DEFERRED or INITIALLY IMMEDIATE 推遲約束檢查，直達事務結束時才進行約束檢查。如果違反約束，則整個事務將回滾。 ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> PRIMARY KEY (column_name) DEFERRABLE INITIALLY DEFERRED; SET CONSTRAINTS <constraint_name> IMMEDIATE; ALTER SESSION SET CONSTRAINTS=IMMEDIATE; 刪除約束 ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>; ALTER TABLE <table_name> DROP PRIMARY KEY|UNIQUE (column) | CONSTRAINT <constraint_name> [CASCADE]; 關閉約束 ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name> [CASCADE]; 註意：關閉唯一性約束和主鍵約束時，相關的索引會進行刪除。 啟用約束 ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name> ; 註意：啟用主鍵約束時，依賴此主鍵的外鍵約束不會級聯啟用。 級聯約束 ALTER TABLE <table_name> DROP COLUMN <column_name> CASCADE CONSTRAINTS;->如果列上有引用使用cascade constraints選項可以級聯刪除列。 索引 索引類型： 唯一性索引：PRIMARY KEY和UNIQUE約束會自動創建唯一性索引。 非唯一性索引：用戶自定義創建索引。CREATE INDEX/CREATE TABLE 註意：創建唯一性索引時，建議創建一個唯一性約束，因為它會隱式的創建一個唯一性索引。 重用Index ALTER TABLE <table_name> ADD PRIMARY KEY (column_name) USING INDEX <index_name>; 基於函數的索引 -索引上是表達式可以是：列、約束、SQL 函數和用戶自定義函數。 註意：使用函數索引必須將參數QUERY_REWRITE_ENABLED設定為TRUE CREATE INDEX <index_name> ON table_name(UPPER(column_name)); select * from ocp_047_base_function where upper(col)=upper('name'); select * from table(dbms_xplan.display_cursor()); 刪除索引 DROP INDEX <index_name>; 刪除表 DROP TABLE <table_name> [PURGE]; Oracle 10g引入了一個刪除表的新功能。刪除表時，資料庫不會立即釋放與該表關聯的空間。而是將表重命名放入回收站中，如果發現錯誤地刪除，則可以使用FLASHBACK TABLE語句恢復該表。如果想立即釋放空間可以使用DROP TABLE [PRUGE]語句。 註意：SYS(AS SYSDBA)賬戶刪除的表不會放入回收站中。 FLASHBACK TABLE閃回 FLASHBACK TABLE [schema .] table [, [schema.] table ...] TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE} TRIGGERS]; -Oracle 10g提供的新特性DDL命令，FLASHBACK TABLE -將表恢復到較早的時間點或防止意外刪除和修改表 -FLASHBACK TABLE 是一種自助修複工具，用於恢復表中的數據以及索引或視圖 -資料庫聯機時完成，只需回滾指定的表。 -與傳統恢復機制相比，此功能提供了易用性、可用性和更快恢復等優勢。 -預設情況下所有涉及的表都禁用觸發器，也可以指定ENABLE TRIGGERS來啟用。 SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP# BIN$X5JCv1b5JHDgU+sCHqzk8Q==$0 TABLE 2017-12-05:13:39:38 EMP$_# BIN$X5JCv1b+JHDgU+sCHqzk8Q==$0 TABLE 2017-12-05:13:40:12 EmP BIN$X5JCv1cDJHDgU+sCHqzk8Q==$0 TABLE 2017-12-05:13:40:21 INSERT_TAB BIN$X6NQR/0SAVbgU+sCHqzqQg==$0 TABLE 2017-12-06:10:39:32 INSERT_TAB BIN$X6NQR/0MAVbgU+sCHqzqQg==$0 TABLE 2017-12-06:10:20:06 SQL> desc recyclebin Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_NAME NOT NULL VARCHAR2(30) ORIGINAL_NAME VARCHAR2(32) OPERATION VARCHAR2(9) TYPE VARCHAR2(25) TS_NAME VARCHAR2(30) CREATETIME VARCHAR2(19) DROPTIME VARCHAR2(19) DROPSCN NUMBER PARTITION_NAME VARCHAR2(32) CAN_UNDROP VARCHAR2(3) CAN_PURGE VARCHAR2(3) RELATED NOT NULL NUMBER BASE_OBJECT NOT NULL NUMBER PURGE_OBJECT NOT NULL NUMBER SPACE NUMBER SQL> select original_name,operation,droptime from recyclebin; ORIGINAL_NAME OPERATION DROPTIME -------------------------------- --------- ------------------- EMP# DROP 2017-12-05:13:39:38 EMP$_# DROP 2017-12-05:13:40:12 EmP DROP 2017-12-05:13:40:21 TAB_04 DROP 2017-12-06:10:00:19 T01 DROP 2017-12-20:17:11:48 INSERT_TAB DROP 2017-12-06:10:39:32 OCP_047_BASE_FUNCTION_V0 DROP 2017-12-20:17:05:53 OCP_047_BASE_FUNCTION DROP 2017-12-20:17:05:53 INSERT_TAB DROP 2017-12-06:10:20:06 閃回表 FLASHBACK TABLE <table_name> TO BEFORE DROP; 臨時表 CREATE GLOBAL TEMPORARY TABLE <table_name> ON COMMIT [PRESERVE | DELETE ] ROWS; -PRESERVE ROWS :保留至會話結束 -DELETE ROWS：保留至事務結束 14:17:38 SQL> create global temporary table temp_t0 14:18:23 2 on commit preserve rows as select * from employees where rownum<20;
已建立表格.
14:19:21 SQL> select count(*) from temp_t0;
COUNT(*)
----------
19
14:19:25 SQL> commit;
確認完成.
14:19:29 SQL> select count(*) from temp_t0;
COUNT(*)
----------
19
14:20:27 SQL> create global temporary table temp_t0 on commit 14:28:25 2 delete rows as select * from employees where rownum<20;已建立表格.
14:28:44 SQL> select * from temp_t0;
沒有任何資料列被選取
14:28:51 SQL> select count(*) from temp_t0;
COUNT(*)
----------
0
14:29:06 SQL> insert into temp_t0 select * from employees;
已建立 107 個資料列.
14:29:23 SQL> select count(*) from temp_t0;
COUNT(*)
----------
107
14:29:33 SQL> commit;
確認完成.
14:29:36 SQL> select count(*) from temp_t0;
COUNT(*)
----------
0
14:29:37 SQL>
外部表 外部表是一個只讀表，元數據存儲在資料庫中，但其數據存儲在資料庫之外。外部表可以被認為是一個視圖，可以對外部數據進行SQL查詢，而不需要將外部數據載入到資料庫中。 外部表不能進行DML操作、建立索引。但可以使用CREATE TABLE AS SELECT載入數據到資料庫中。 外部表提供了兩個訪問驅動程式：ORACLE_LOADER和ORACLE_DATAPUMP -ORACLE_LOADER將外部數據映射到資料庫 -ORACLE_DATAPUMP將資料庫資料卸載到外部文件 外部表的創建 -創建目錄提供給外部表 CREATE [OR REPLACE] DIRECTORY <directory_name> AS 'directory_path'; -授權目錄給使用賬戶 GRANT READ ON DIRECTORY <directory_name> TO schema_name; ORACLE_DATAPUMP CREATE TABLE extract_emps ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ... ACCESS PARAMETERS (...) LOCATION (...) PARALLEL 4 REJECT LIMIT UNLIMITED AS SELECT * FROM ...; create table extract_datapump (last_name,employee_id,salary)organization external
(type oracle_datapump
default directory expdp_path
location('emp_01.bat','emp_02.bat'))
parallel
as
select last_name,employee_id,salary from employees
;
[oracle@xxxxx bak]$ strings emp_01.bat|tailPerkins
Bell
Everett
ORACLE_LOADER CREATE TABLE extract_loader ( --表名稱 fname char(25), --欄位名 lname char(25)) --欄位名 ORGANIZATION EXTERNAL --外部表 (TYPE ORACLE_LOADER --外部表驅動器 DEFAULT DIRECTORY <directory_name> --預設目錄 ACCESS PARAMETERS --標識訪問參數 (RECORDS DELIMITED BY NEWLINE NOBADFILE --不產生故障文件 NOLOGFILE --不產生日誌文件 FIELDS TERMINATED BY ',' --區分符 (fname POSITION (1:20) CHAR, --定義文件類型對應值 lname POSITION (22:41) CHAR)) LOCATION ('file_name'))--載入文件名稱 PARALLEL 5 --並行度 REJECT LIMIT 200; --限制錯誤的個數及停止查詢 外部表創建語法 CREATE TABLE <table_name> (<col_name> <datatype>, ... ) ORGANIZATION EXTERNAL (TYPE <access_driver_type> DEFAULT DIRECTORY <directory_name> ACCESS PARAMETERS ( ... ) ) LOCATION ('<localtion_specifier>') ) REJECT LIMIT [ 0 | <number> | UNLIMITED];
