ORACLE DB 的學習者們

2018年11月30日 星期五

如何快速新增大筆資料至資料庫

如果測試時，希望一次新增10000筆資料該如何處理，以下整理一些常見語法


  1.  使用傳統FOR LOOP
    2. FOR LOOP 的方法可以在迴圈內部放你要的SQL 
           create table large(c1 char(1000));
       begin
          for i in 1..2000 loop
             insert into large values ('a row');
          end loop;
       end;
        /
  2.  使用 CONNECT BY LEVEL 語法
    3. 這是階層式查詢的技巧，其中的LEVEL是一個虛擬欄位，紀錄的是在階層結構中的階層數 
             CREATE TABLE test_tab (
              id          NUMBER,
              description VARCHAR2(50),
              CONSTRAINT test_tab_pk PRIMARY KEY (id)
          );

         INSERT /*+ APPEND */ INTO test_tab (id, description)
            SELECT level,
                   'Description for ' || level
            FROM   dual
             CONNECT BY level <= 10000;
    其中SQL的DIRECTIVE /*+ APPEND */是指忽略HIGH WATER MARK，從HIGH WATER MARK其後的位置新增資料
張貼者： 於

2018年4月23日 星期一

ORACLE AUDIT

參考文件



建立稽核專用Tablespace

        
    CREATE TABLESPACE AUX_AUDIT DATAFILE 
      'C:\SID\TBS_AUDIT_01.dbf' SIZE 900M AUTOEXTEND OFF
      LOGGING
      EXTENT MANAGEMENT LOCAL AUTOALLOCATE
      BLOCKSIZE 8K
      SEGMENT SPACE MANAGEMENT AUTO
      FLASHBACK ON;

AUDIT的環境設定

要設定的內容包含稽核預設的TABLESPACE，預計清除紀錄的時間間隔等。使用的工具為 DBMS_AUDIT_MGMT，必須以SYS身分使用SQLPLUS來執行。
ORACLE的AUDIT有兩個系統預設的TABLE，分別為 SYS.AUD$ (基本) 與SYS.FGA_LOG$ (細微)。 此二TABLE的預設TABLESPACE皆為SYSTEM，若不先將其移至其他位置，若紀錄持續成長到佔滿空間， 將使DB無法登入。

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

更改標準AUDIT的位置 

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => ' AUX_AUDIT ');
END;

更改細微AUDIT的位置

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => ' AUX_AUDIT ');
END;

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

設定清除區間

以下設定每12小時清除一次 
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;

修改AUDIT基本參數(重啟DB)

開啟AUDIT前先使用SHOW PARAMETER AUDIT確認稽核檔的寫入位置有設定且路徑是對的。 設定參數時須以SYS身分在DB主機以SQLPLUS執行。執行完畢需重啟DB。 
SQL> alter system set audit_sys_operations=TRUE scope=spfile;

SQL> alter system set audit_trail=xml,extended scope=spfile;

SQL> shutdown immediate;

設定系統稽核範圍

以下CONSOLE以SYSDBA身分執行 稽核指令稿如下：將下列寫成一個SCRIPT檔，稱為audit.sql
設定稽核範圍
--紀錄以下使用者的登錄(假設想記錄SUPERUSER的動作) 

noaudit all;  --先取消所有稽核選項，重新設定選項
audit session by system;
audit session by sys;
audit session by SUPERUSER;

--紀錄重要異動
audit select any table by SUPERUSER;
audit insert any table by SUPERUSER;
audit delete any table by SUPERUSER;
audit update any table by SUPERUSER;

--紀錄對AUDIT TRAIL 本身之異動，以保護稽核紀錄
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

與AUDIT內容有關的資料字典

以下資料字典可觀察DB裡被稽核的內容。



  • Statement:  DBA_STMT_AUDIT_OPTS
  • Object:  DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS
  • Privilege:  DBA_PRIV_AUDIT_OPTS


與AUDIT設定有關的資料字典

AUDIT設定的項目可由dba_audit_mgmt_config_params 查詢，下列SQL查詢目前的設定值 SQL> select * from dba_audit_mgmt_config_params;

設定XML AUDIT FILE大小

由圖可知預設值是 10000kb，透過下列SQL可調整為15000kb 
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    audit_trail_property_value => 15000);
END;

設定XML AUDIT FILE最大天數

以下將其天數由五天改為10天。 
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    audit_trail_property_value => 10);
END;

稽核紀錄查詢

audit_actions

稽核紀錄中的欄位ACTION是用代碼顯示，每個代碼的意義存放於此TABLE，代碼共計約118筆

V$XML_AUDIT_TRAIL

若稽核的選項是 XML, EXTENDED，它會將紀錄以XML方式記錄於指定位置，但可透過此VIEW來查詢它的內容，其中有DB_USER, OS_USER, TERMINAL, OBJECT_SCHEMA, OBJECT_NAME 和 SQL_TEXT等欄位。

稽核紀錄管理

稽核紀錄如果不處理，會持續成長到影響系統效能。紀錄可運用時間戳記來管理，為刪除或整理設立一個時間點。 AUDIT的時間戳記紀錄在dba_audit_mgmt_last_arch_ts，可透過指令來設定 : SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

設定稽核的時間戳記

使用時間戳記來管理紀錄,先設定最後截止時間戳記,以下設定五天前,設定後可清除在此戳記前的資料 
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT. AUDIT_TRAIL_XML,
    last_archive_time => SYSTIMESTAMP-5);
END;

運用時間戳記管理紀錄

紀錄可透過CLEAR_LAST_ARCHIVE_TIMESTAMP來管理。 以下將所有紀錄皆刪除(參數 use_last_arch_timestamp 設為FALSE )。 

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   use_last_arch_timestamp => FALSE);
END;
以下將時間戳記前的所有紀錄皆刪除(參數 use_last_arch_timestamp 設為TRUE)。 
BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   use_last_arch_timestamp => TRUE);
END;
以下將時間戳記前的所有紀錄皆刪除 (使用CLEAR_LAST_ARCHIVE_TIMESTAMP) 
BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(
    audit_trail_type     =>  DBMS_AUDIT_MGMT. AUDIT_TRAIL_XML);
END;














張貼者： 於

2018年1月18日 星期四

資料庫從TXT檔案直接匯入或讀取 -- DIRECT PATH LOAD 與 External Tables

DIRECT PATH LOAD : 從TXT檔案直接匯入資料庫

ORACLE可以從純文字檔直接匯入到資料表，稱之為 DIRECT PATH LOAD ，有別於傳統的匯入方式 (CONVENTIONAL)，一定使用INSERT指令，一定有COMMIT。

  1. 傳統的INSERT，會使用Database Buffer Cache，且資料寫入在HIGH WATER MARK範圍之內，DIRECT PATH LOAD跳過 DATABASE BUFFER CACHE，且寫入DATAFILE的時候， 從HIGH WATER MARK之後插入，稱之為 DATA SAVE
  2. 傳統方式需要COMMIT， DIRECT PATH LOAD不用COMMIT
  3. 傳統方式會受限於Referential Integrity Constraints ，且TRIGGER可被觸發。 DIRECT PATH LOAD必須停掉Referential Integrity Constraints，且TRIGGER不會觸發
  4. DIRECT PATH LOAD時，TABLE會被LOCK不允許DML
  5. DIRECT PATH LOAD插入資料時，只允許UNIQUE、PRIMARY KEY和NOT NULL 三個Constraints
  6. DIRECT PATH LOAD 不能被用在CLUSTER TABLE
DIRECT PATH LOAD 需要下列條件或準備
  1. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位
  2. CONTROL FILE : 跟資料庫的 CONTROLFILE沒有關係，是一個純文字檔，描述匯入的程序和指令
  3. LOG FILES : 寫在前述 CONTROL FILE，紀錄匯入紀錄
  4. BAD FILES : 寫在前述 CONTROL FILE，已經讀出，但被SQL LOADER拒絕不能匯入的資料寫入此，例如因為格式不合
  5. REJECT FILES : 寫在前述 CONTROL FILE，因違反表格限制條件而不能匯入的資料寫入此
  6. 匯入前，必須在資料庫先將TABLE建立好
在DB先建立下列表格 
CREATE TABLE HR.DEPTS
(
  DEPTNO  NUMBER(2) NOT NULL,
  DNAME   VARCHAR2(14) ,
  LOC     VARCHAR2(13)
);
在主機中建立下列純文字檔，命名為 depts.txt 
60,CONSULTING,TORONTO
70,HR,OXFORD
80,EDUCATION
在主機中建立下列 CONTROL FILE指令檔，命名為 DEPTS.CTL 
load data
infile 'depts.txt'            --匯入的檔案
badfile 'depts.bad'           --壞掉的檔案放此
discardfile 'depts.dsc'       --被拒絕匯入的放此
append                        --以插入方式匯入到WATER MARK後面
into table depts              --插入新增到在資料庫中建好的表格
fields terminated by ','      --欄位，以逗點分隔
trailing nullcols             --空白的欄位，以NULL填寫
(deptno integer external(2),  --欄位的定義
dname,
loc)
指令檔建好以後，在命令列下執行下列命令 
[oracle@db01 plsql]$ sqlldr userid=hr/XXXXX control=DEPTS.CTL direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 3.
匯入完成以後，看看紀錄檔產生些什麼 
[oracle@db01 plsql]$ cat DEPTS.log

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   DEPTS.CTL
Data File:      depts.txt
  Bad File:     depts.bad
  Discard File: depts.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table DEPTS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     2   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER


Table DEPTS:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Jan 18 05:23:56 2018
Run ended on Thu Jan 18 05:23:58 2018

Elapsed time was:     00:00:01.15
CPU time was:         00:00:00.01
插入後的表格可看到結果如下

使用SQL*LOADER : 從TXT檔案直接匯入資料庫

使用 SQL*LOADER 需要下列條件或準備
  1. DIRECTORY物件 : 需存在建好的目錄物件，並且賦予讀寫權限
  2. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位，需放在上述目錄物件指向之位置
  3. SQL FILES : 在TOAD中執行即可
在上述目錄的位置放置要匯入的純文字檔，例如，在/home/oracle/bkup 目錄下有兩個純文字檔 empxt1.dat 和 empxt2.dat，如下圖所示
例如，文字檔有四個欄位 
123,Dah-Lih,Jeng,01-jan-2018
124,Mary,Jeng,02-may-2017
125,Ezekiel,Jeng,12-feb-2015
然後，透過下列SQL將文字匯入到資料庫中 
create table extab_employees
( employee_id   number(4),
  first_name    varchar2(20),
  last_name     varchar2(25),
  hire_date     date)
organization external
(  type  ORACLE_LOADER  DEFAULT DIRECTORY EXT_DIR
   ACCESS PARAMETERS
   (  records delimited by newline
      badfile EXT_DIR:'empxt%a_%p.bad'
      logfile EXT_DIR:'empxt%a_%p.log'
      fields terminated by ','
      missing field values are null
      ( employee_id, first_name, last_name,
        hire_date char date_format date mask "dd-mon-yyyy"))
        LOCATION ('empxt1.dat','empxt2.dat'))
        parallel reject limit unlimited;
匯入後查詢可確認是否成功

External Tables : 從資料庫直接讀取主機上以純文字建立之表格

External Tables 的使用是在有需要從主機的純文字檔讀取資料，可是又不想把該文字檔匯入成表格的時候，可以改用此方法，讀取外部的表格。該表格建立以後，可以用以查詢，使用於JOIN，或用以建立VIEWS或子查詢，但是不可以進行任何DML。

External Tables 的建立只要透過create table，配合 關鍵字 organization external ，就可以達成，但需要下列條件或準備

  1. 建立DIRECTORY : 以SYSDBA登入，建立DIRECTORY以後，將讀寫權限ASSIGN給該TABLE的USER
  2. 將純文字檔放至該DIRECTORY指定之目錄。
  3. 建立 External Tables
先建立DIRECTORY (以SYSDBA) 
CREATE OR REPLACE DIRECTORY 
EXT_DIR AS 
'/home/oracle/bkup';
GRANT READ, WRITE ON DIRECTORY EXT_DIR TO HR;
把剛才的純文字檔複製到該目錄去

[oracle@db01 plsql]$ cp depts.txt ../bkup/

複製完成後，使用TOAD或是SQLPLUS執行下列SQL，建立外部表格 

create table new_dept
( deptno number(2),
  dname varchar2(14),
  loc varchar2(13))
  organization external (
  type oracle_loader
  default directory ext_dir
  access parameters
  ( records delimited by newline
    badfile 'depts.bad'
    discardfile 'depts.dsc'
    logfile 'depts.log'
    fields terminated by ','
    missing field values are null)
    location ('depts.txt'));
該語法跟前述 DIRECT PATH LOAD的CONTROL FILE非常接近，然後透過TOAD可以看到該表格已經建立成功
當然，上述的CONTROL FILE稍嫌複雜，也可反向操作，將前述使用SQL LOADER匯入表格的命令，改為讓它產生EXTERNAL TABLE的SQL 

[oracle@db01 plsql]$  sqlldr userid=hr/xxxx control=DEPTS.CTL external_table=generate_only  

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 08:07:03 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
執行完畢，去檢查所產生的LOG檔，該檔案有完整的步驟：建立目錄、執行SQL產生EXTERNAL TABLES ，但是在進行下一步前，使用編輯器將一些該檔案內的路徑、檔案名稱、TABLE名稱等資訊換成正確的路徑和名稱，同時先將文字檔放在目錄所指向的路徑上，最後再去執行該檔案的SQL就可順利建立一個EXTERNAL TABLE。

External Tables 支援以下的特性

  1. 欄位加密 (Encrypted Columns)
  2. Clustered Tables
  3. Different partition at unload and load time, and others (see Notes)

與External Table 有關的資料字典

External Tables 有關的資料字典如下
  1. (DBA/ALL/USER)_EXTERNAL_TABLES
  2. (DBA/ALL/USER)_EXTERNAL_LOCATIONS
  3. (DBA/ALL/USER)_TABLES, and others
張貼者： 於

2018年1月15日 星期一

ORACLE WITH 的用法

ORACLE 的 WITH 語法，可以將複雜的 SQL 語句，以 WITH 語法並給予其名稱，在其後的查詢中，透過呼叫該名稱，可反覆使用。

參考文章：其一、 其二 。

當查詢很複雜，又需要反覆使用子查詢的結果時，WITH便很方便。如果想查出各部門中，哪一部門的總薪資，高於所有部門的平均薪資，那總平均薪資就屬於反覆使用的子查詢。透過WITH語法，將查詢語句包圍在一個WITH語句中，透過反覆呼叫該語句，便可以達成子查詢。

下面的 SQL 想查詢在每個部門裡有多少人，原來的SQL如下 

SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id, dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc
WHERE  e.department_id = dc.department_id;
改用WITH語法後的SQL如下 

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.first_name,e.last_name AS employee_name,
       dc.department_id,dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.department_id = dc.department_id;
從上面的語法，以WITH語法定義了一段子查詢，命名為 DEPT_COUNT，用在其後的SQL當中，如下圖所示。
要從本身具備階層關係的表格中，把部門員工和所屬主管及各部門人數拉出來，若用傳統SELF-JOIN的方式處理，其SQL如下： 

SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc1,
       emp m,
       (SELECT department_id, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY department_id) dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;
上述SQL的執行結果如下圖所示。

相同的查詢，若改以 WITH 的語法，看起來會較有結構。 

WITH dept_count AS (
  SELECT department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT e.last_name AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.last_name AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.department_id = dc1.department_id
AND    e.manager_id = m.employee_id
AND    m.department_id = dc2.department_id;
上述的語法可以結合兩種編譯註解 (HINTS) ，MATERIALIZE 使其以 global temporary table 的方式處理 WITH 的語句，INLINE 則是以內嵌的方式處理 
WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ............ ;

WITH dept_count AS (
  SELECT /*+ INLINE */ department_id, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY department_id)
SELECT ..............;
WITH 語法還可以讓重複使用的查詢，以代稱命名後，反覆呼叫使用。以下範例，透過 WITH 建立兩個子查詢，分別查詢部門總薪資(部門總成本或總薪資)，及總平均薪資，簡化後的 SQL ，主體SQL變得非常簡潔。 
WITH 
  dept_costs AS (
    SELECT department_name, SUM(salary) dept_total
    FROM   emp e, departments d
    WHERE  e.department_id = d.department_id
    GROUP BY department_name),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY department_name;
上述查詢得到銷售部門總薪資高於全體平均薪資。
張貼者： 於
訂閱： 文章 (Atom)