Đỉnh NGUYỄN

life's a journey not a destination


Leave a comment

Oracle PL/SQL – Cách viết đệ quy đơn giản


Đây là ví dụ đơn giản cách viết đệ quy trong Oracle PL/SQL dùng mệnh đề “WITH”.

/* ===== code ===== */  
                                                         
WITH  
   factorial ( n, val )  as
   (   /*=== The Recursive WITH ===*/
       select  1 as n,  1 as val  
       from  dual
       UNION ALL 
       select  n+1 as n,  (n+1)*val as val
       from  factorial
       where  n < 8
   )
   select  n
      , val
   from  factorial
   order  by  n 
 ;
                                                                          
/* --- Results --- */    
                                                         
--  N   VAL   
--  --  ------ 
--  1   1     
--  2   2     
--  3   6     
--  4   24    
--  5   120   
--  6   720   
--  7   5040  
--  8   40320 

Nguồn tham khảo: http://steve-lyon.blogspot.com/2011/07/recursive-with-simple-factorial.html

Advertisements


Leave a comment

Oracle–Quản lý Database Instance


    Thực hành: Tạo và quản lý file tham số

Bài viết này giới thiệu cách tạo và dùng SPFILE và duy trì PFILE.

  • Áp dụng cho bất kỳ HĐH
  • Dùng công cụ SQL Plus
  • Tập trung vào cách dùng SPFILE và duy trì PFILE
  • Thư mục default cho Windows là ORACLE_HOME\database\
  • Thư mục default cho UNIX là ORACLE_HOME/dbs/
    1. Dùng SQL Plus, select các tham số non-default từ view v$parameter.
    SQL> l
    
      1  select name, value from v$parameter
    
      2* where isdefault ='FALSE'
    
    SQL> /
    
     
    
    NAME                      VALUE
    
    ------------------------- ---------------------------------------
    
    processes                 150
    
    timed_statistics          TRUE
    
    shared_pool_size          29360128
    
    sga_max_size              135032864
    
    large_pool_size           1048576
    
    java_pool_size            0
    
    control_files            c:\oracle11g\oradata\ORCL\CONTROL01.CTL
    
    db_block_size             4096
    
    db_cache_size             16777216
    
    compatible                11.2.0
    
    fast_start_mttr_target    300
    
    undo_management           AUTO
    
    undo_tablespace           UNDOTBS
    
    remote_login_passwordfile EXCLUSIVE
    
    db_domain
    
    distributed_transactions  0
    
    instance_name             ORCL
    
    background_dump_dest      c:\oracle11g\admin\OEMREP\bdump
    
    user_dump_dest            c:\oracle11g\admin\OEMREP\udump
    
    core_dump_dest            c:\oracle11g\admin\OEMREP\cdump
    
    sort_area_size            524288
    
    db_name                   ORCL
    
    open_cursors              300
    
     
    
    24 rows selected.

    2. Dùng SQL Plus, tạo SPFILE từ PFILE đã có trong thư mục default

    SQL> create spfile from pfile;
    
     
    
    File created.
    3. Dùng SQL Plus, select các tham số non-default từ view v$parameter
    SQL> l
    
      1  select name, isspecified, value
    
      2  from v$spparameter
    
      3* where isspecified = 'TRUE'
    
    SQL> /
    
     
    
    NAME                      ISSPEC VALUE
    
    ------------------------- ------ ----------------------------------------
    
    processes                 TRUE   150
    
    timed_statistics          TRUE   TRUE
    
    shared_pool_size          TRUE   16777216
    
    sga_max_size              TRUE   134217728
    
    large_pool_size           TRUE   1048576
    
    java_pool_size            TRUE   0
    
    control_files             TRUE   c:\oracle11g\oradata\ORCL\CONTROL01.CTL
    
    control_files             TRUE   c:\oracle11g\oradata\ORCL\CONTROL02.CTL
    
    control_files             TRUE   c:\oracle11g\oradata\ORCL\CONTROL03.CTL
    
    db_block_size             TRUE   4096
    
    db_cache_size             TRUE   16777216
    
    compatible                TRUE   11.2.0
    
    fast_start_mttr_target    TRUE   300
    
    undo_management           TRUE   AUTO
    
    undo_tablespace           TRUE   UNDOTBS
    
    remote_login_passwordfile TRUE   EXCLUSIVE
    
    db_domain                 TRUE
    
    distributed_transactions  TRUE   0
    
    instance_name             TRUE   ORCL
    
    dispatchers               TRUE   (PROTOCOL=TCP)(SER=MODOSE)
    
    dispatchers               TRUE   (PROTOCOL=TCP)(PRE=oracle.aurora.server.
    
                                     GiopServer)
    
     
    
    dispatchers               TRUE   (PROTOCOL=TCP)(PRE=oracle.aurora.server.
    
                                     SGiopServer)
    
     
    
    background_dump_dest      TRUE   c:\oracle11g\admin\ORCL\bdump
    
    user_dump_dest            TRUE   c:\oracle11g\admin\ORCL\udump
    
    core_dump_dest            TRUE   c:\oracle11g\admin\ORCL\cdump
    
    sort_area_size            TRUE   524288
    
    db_name                   TRUE   ORCL
    
    open_cursors              TRUE   300
    
    28 rows selected.
    4. Dùng SQL Plus, tạo PFILE từ SPFILE đã có
    SQL> create pfile from spfile;
    
     
    
    File created.
    Thực hành: Mô tả kiến trúc Oracle và các thành phần chính
    Trong bài viết này, bạn sẽ thấy kích thước các thành phần của SGA, và tự động thay đổI kích thước của shared pool và buffer cache.
  • Áp dụng cho bất kỳ HĐH
  • Phiên bản CSDL giả định là 11g
  • Tài khoản database với quyền dba, sysdba
  • Giả sử bạn có SPFILE động thay vì PFILE
    1. Dùng SQL Plus, show kích thước SGA (Shared Global Area):
    SQL> show sga
    
     
    
    Total System Global Area  135032864 bytes
    
    Fixed Size                   282656 bytes
    
    Variable Size             117440512 bytes
    
    Database Buffers           16777216 bytes
    
    Redo Buffers                 532480 bytes
    
    SQL>
    2. Dùng SQL Plus, show cấu trúc bộ nhớ của SGA (bytes)
    SQL> l
    
      1  select name, value from v$parameter where name in
    
      2  ('shared_pool_size',
    
      3   'java_pool_size',
    
      4   'large_pool_size',
    
      5   'db_cache_size',
    
      6   'log_buffer')
    
      7* order by 1
    
    SQL> /
    
     
    
    NAME                      VALUE
    
    ------------------------- -------------------------
    
    db_cache_size             16777216
    
    java_pool_size            0
    
    large_pool_size           1048576
    
    log_buffer                524288
    
    shared_pool_size          16777216
    
     
    
    SQL>
    3. Dùng SQL Plus, tự động alter kích thước của shared pool lên 32 Mb, show kích thước mớI và tự động alter kích thước trở về 16 Mb. Dùng lệnh ALTER SYSTEM cho cả 2 hành động.
    SQL> show parameter shared_pool_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------------
    
    shared_pool_size                     big integer 16777216
    
    SQL> alter system set shared_pool_size = 32m;
    
     
    
    System altered.
    
     
    
    SQL> show parameter shared_pool_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------------
    
    shared_pool_size                     big integer 33554432
    
    SQL> alter system set shared_pool_size = 16m;
    
     
    
    System altered.
    
     
    
    SQL> show parameter shared_pool_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------------
    
    shared_pool_size                     big integer 16777216
    
    SQL>
    4. Dùng SQL Plus, show kích thước của DB_BUFFER_CACHE và tự động thay đổi thành 32 Mb, sau đó thay đổi lại 16 Mb. Dùng lệnh ALTER SYSTEM cho cả 2 hành động.
    SQL> show parameter db_cache_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------
    
    db_cache_size                        big integer 16777216
    
    SQL> alter system set db_cache_size=32m;
    
     
    
    System altered.
    
     
    
    SQL> show parameter db_cache_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------
    
    db_cache_size                        big integer 33554432
    
    SQL> alter system set db_cache_size=16m;
    
     
    
    System altered.
    
     
    
    SQL> show parameter db_cache_size
    
     
    
    NAME                                 TYPE        VALUE
    
    ------------------------------------ ----------- --------
    
    db_cache_size                        big integer 16777216
    
    SQL>
    5. Dùng SQL Plus, select kích thước hiện tạI và kích thước trước đó của default buffer cache.
    SQL> l
    
      1  select name, current_size, buffers, prev_size, prev_buffers
    
      2* from v$buffer_pool
    
    SQL> /
    
     
    
    NAME                      CURRENT_SIZE    BUFFERS  PREV_SIZE PREV_BUFFERS
    
    ------------------------- ------------ ---------- ---------- ------------
    
    DEFAULT                             16       3932         32         7864
    Thực hành: Start up and Shut down Instance
    BàI viết chỉ cách start up và shut down Oracle database
  • Áp dụng cho bất kỳ HĐH
  • Dùng công cụ SQL Plus
  • Cần quyền SYSDBA để shut down database
    1. Sau khi connect vào database vớI quyền SYSDBA trong SQL Plus, start up database ở trạng tháI NOMOUNT
    SQL> connect / as sysdba
    
    Connected to an idle instance.
    
    SQL> startup nomount
    
    ORACLE instance started.
    
     
    
    Total System Global Area  135032864 bytes
    
    Fixed Size                   282656 bytes
    
    Variable Size             117440512 bytes
    
    Database Buffers           16777216 bytes
    
    Redo Buffers                 532480 bytes
    2. Dùng SQL Plus, alter database sang trạng tháI MOUNT
    SQL> alter database oemrep mount;
    
     
    
    Database altered.
    3. Dùng SQL Plus, select trạng tháI của Instance từ view v$instance
    SQL> select instance_name, status from v$instance
    
    SQL> /
    
     
    
    INSTANCE_NAME    STATUS
    
    ---------------- -------
    
    ORCL           MOUNTED
    4. Dùng SQL Plus, alter database sang trạng tháI OPEN. Sau đó select trạng tháI của database từ view v$instance
    SQL> alter database open;
    
     
    
    Database altered.
    
     
    
    SQL> select instance_name, status from v$instance;
    
     
    
    INSTANCE_NAME    STATUS
    
    ---------------- -------
    
    ORCl           OPEN
    Thực hành: Shut down Instance
  • Áp dụng cho bất kỳ HĐH
  • Dùng công cụ SQL Plus
  • Cần quyền SYSDBA để shut down database
    1. Dùng SQL Plus, select trạng tháI database từ view v$instance
    SQL> select instance_name, status from v$instance;
    
     
    
    INSTANCE_NAME    STATUS
    
    ---------------- -------
    
    ORCL           OPEN
    2. Dùng SQL Plus, connect đến database vớI quyền SYSDBA
    SQL> connect / as sysdba
    
    Connected.

3. Dùng SQL Plus, shut down database vớI checkpoint

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


4 Comments

Một số ví dụ về Oracle Job Scheduler


DBMS_SCHEDULER là 1 package của Oracle (có từ version 10) cung cấp các procedure giúp tạo job.

  Tạo Oracle Job gồm 3 bước:

  1. Tạo time schedule – dbms_scheduler.create_schedule
  2. Tạo program declaration – dbms_scheduler.create program
  3. Tạo job (conflation) – dbms_scheduler.create_job

Lưu ý: login vào với vai trò sys và gán quyền cho user cần tạo job:

SQL> conn / as sysdba

GRANT create any directory TO fhr;
GRANT create procedure TO fhr;
GRANT create table TO fhr;

GRANT create job TO fhr;
GRANT manage scheduler TO fhr;

1. Tạo time schedule

begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
 start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
 repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
 comments=>'Runtime: Every day (Mon-Sun) at 22:00 o clock');

-- run every hour, every day
dbms_scheduler.create_schedule(  
schedule_name  => 'INTERVAL_EVERY_HOUR',  
  start_date    => trunc(sysdate)+18/24,  
  repeat_interval => 'freq=HOURLY;interval=1',  
  comments     => 'Runtime: Every day every hour');  

-- run every 5 minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_5_MINUTES',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=5',
  comments     => 'Runtime: Every day all 5 minutes');

-- run every minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_MINUTE',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=1',
  comments     => 'Runtime: Every day every minute');

-- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_EVERY_SUN_1800',
 start_date=> trunc(sysdate)+18/24,
 repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
 comments=>'Runtime: Run at 6pm every Sunday');
end;

2. Tạo program declaration

begin
-- Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> 'PROG_COLLECT_SESS_DATA',
 program_type=> 'STORED_PROCEDURE',
 program_action=> 'pkg_collect_data.prc_session_data',
 enabled=>true,
 comments=>'Procedure to collect session information'
 );
end;

3. Tạo Job

begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
 (job_name => 'JOB_COLLECT_SESS_DATA',
  program_name=> 'PROG_COLLECT_SESS_DATA',
  schedule_name=>'INTERVAL_EVERY_5_MINUTES',
  enabled=>true,
  auto_drop=>false,
  comments=>'Job to collect data about session values every 5 minutes');
end;

4. Thay đổi properties của phần Time scheduler

 

begin
-- change start time
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_5_MINUTES',
   attribute => 'start_date',
   value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')
);

-- change repeat interval
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_MINUTE',
   attribute => 'repeat_interval',
   value => 'freq=MINUTELY;interval=2'
); 
end;

5. Run job

begin
dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);
end;

6. Restart job

begin
dbms_scheduler.disable('JOB_COLLECT_INST_INFO');
dbms_scheduler.enable('JOB_COLLECT_INST_INFO');
end;

7. Job status

-- All jobs
select * from user_scheduler_jobs;

-- Get information to job
select * from user_scheduler_job_log order by log_date desc;

-- Show details on job run
select * from user_scheduler_job_run_details;

8. Alternative Job status

-- View all running jobs, Run the following query from SQL*Plus
SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job AND a.sid = c.sid order by a.this_date;

-- View all jobs, Run the following query from SQL*Plus
SELECT job, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what 
FROM dba_jobs 
ORDER BY next_date;

9. Killing a running job

Ý hay là khi muốn kill 1 job mà không muốn nó tự restart lại là hãy đánh dấu broken. Chạy lệnh sau từ SQL*Plus:

-- Võìi job_id lâìy týÌ câu truy vâìn “View all running jobs”.
execute dbms_job.broken(job_id, true);

Sau đó, kill session bằng 1 trong 2 cách:

#1: (the best / fast way to kill a session)

-- Find the thread you want to kill
SELECT sid, spid as thread, osuser, s.program
FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr;

-- Run the following for Oracle on Linux, sid is the name given to the Oracle Instance 
-- (the name given in the TNSnames.ora fle)
orakill sid thread

#2:

-- Run the following query from SQL*Plus
-- sid and serial# came from the "View all running jobs" query. 
-- The problem with this option is it can sometimes take a long time to kill the session
alter system kill session 'sid, serial#'

Để remove hoàn toàn job, chạy lệnh:

-- job_id is obtained from the "View all running jobs" query.
execute dbms_job.remove(job_id)

Nếu chỉ muốn stop job để fix chứ không muốn remove hoàn toàn, hãy fix job và restart bằng lệnh:

-- job_id is obtained from the "View all running jobs" query
execute dbms_job.broken(job_id, false);

Ghi chú: Khi nào job trở thành ‘broken’?

  • Oracle thực hiện job thành công sau 16 lần cố gắng thực hiện (có lỗi khi thực hiện job). hoặc
  • Người dùng đánh dấu job là ‘broken’, dùng proc DBMS_JOB.BROKEN.
    Khi 1 job được đánh dấu là ‘broken’. Oracle sẽ không cố gắng thực hiện job nữa đến khi job được đánh dấu không còn ‘broken’ hoặc bị ép thực thi bằng lời gọi DBMS_JOB.RUN.

10. Tạo job mà không cần time scheduler và program declaration

begin
dbms_scheduler.create_job (
  job_name            => 'TEST_JOB',
  job_type            => 'PLSQL_BLOCK',
  job_action          => 'begin /* some process code */ commit; end;',
  number_of_arguments => 0,
  start_date          => sysdate +1/24/59, -- sysdate + 1 minute
  job_class           => 'DEFAULT_JOB_CLASS',  -- Normal Group
  enabled             => TRUE,
  auto_drop           => TRUE,
  comments            => 'Testrun');
end;