????? ????????? ????? Oracle Oracle9i ??????? ?? ???? ???????? ??????????? ? Oracle9i Database (?????? ???? ??????), Oracle9i Application Server (?????? ??????????) ? Oracle9i Developer Suite (???????? ??????????). ? ???? ?????? ???? ?????? ? ????????????? Oracle9i Database.

????? ??????????? Oracle9i Database ????? ? ????????? ????? ?????????? ????????? ?? ??? ?????? ? ??????????????? ??? ????????????? ?????????? ? ??? ??????????????? ??? ??????. ??? ??????????? ????? ????????????? ???????? ????????? ????? ??????????.

????????????

??????????? ?????????, ??? ??? ?????????? ? ????? ????????? ? ?????? ??????? ?????? ???????????? ???, ????????? ?? ?????-?? ????????????? ????? ???????? ? ?????????? ??????????. ? Oracle8i ????? «??????????» ???? ????????? ?? ??????? ????? Java ??? ???????????? PL/SQL. ? Oracle9i ????????? ????? ??????? ?????????? ????????? ????? ?????????? ?? ?????????. ?? ? ????? ???????????? ?????? ?????? Java ? PL/SQL ?????? ??????????, ????? ????????? ??????? ???????? ??????????, ? ? ????????? ??????? ???????? ?????????? ??????????.

??????? ?????????? ????? ??????????? ?????? ??? ?????????? ??????????. ?? ????? ??????? ? ???, ??? ???????? ???????????? ?????????? ?? ?????? ???????? ?????????? («??? ????????»), ?? ? ???????? ??????????? («?????? ?????? ????????!»). ????? ????, ??? ????????? ???????????? ??????? ????????? ????? ??????????, ??? ?????????? ????????????? ??????????? ????????? ???????????????. ??????? ????? ????? ??????????? ??????????????? ???????.

???????? SQL ??????? ??????? ???????? ? ??????? ???????????? ??????????. SQL ? Oracle9i ????????????? ??????????? ????????? ISO SQL1999. ??? ?????????????? ???? ?????????? ? ???? ??????? ????? ????? ?????????????? ???????????. ???, ????????, ???????? CASE, ????????????? ???????????????? ?????? ?????? ??????? DECODE (? ???????? ?? ???????? ???????????? ???? ????????? ??????? Emp ? Dept):

SELECT ename "???????",
   (CASE WHEN sal<1000 THEN '??????'
         WHEN sal>4000 THEN '???????'
    ELSE '???????' 
    END) "????????"
   FROM emp;

????????? ? ????????? ??????????. ?????? ? ? Oracle ???? ??????? ???????/??????/??????? ???????? ?????????? (outer join), ????????, ??????

select ename, dname from emp right outer join dept 
on (emp.deptno=dept.deptno);

?????????? ??? ?? ?????????, ??? ?

select ename, dname from emp, dept
where emp.deptno(+)=dept.deptno;

? ??? ??????

select ename, dname from emp full outer join dept 
on (emp.deptno=dept.deptno);

? ?????? ?????????? ??????? ?? ?????.

??? ? ??????????? ????? ?????????-?????????????? ???????????? ????? ????? ??????? ??? ???????? ?????????? ?? Oracle ? ?????? ????. ????????, ????????????? ????? ????? ???????, ??????, ? MS SQL ?? Oracle.

???????????? ? PL/SQL ????? ????????????, ??? ????? ??????? ????? ???????????? ????????. ???????????? ????????? ?????????? ??????? ????? ??????, ??? ? ????????? ????? ????????? ????????????, ???????? ???????? ????????????? ????????? ANSI SQL99. ???????????? ? Oracle9i ?????? ?????????????, ????????????? ???????????? ?? ???????????. ????-??????? ????????? ? ?????? ???????? ???????? ? ??????. ???????????, ??????? ????? ????????? ???? ???????? ? ??????, ? ????? ? ?????????????? ?????? ????????. ?????? ????-????????:

CREATE TYPE Person AS OBJECT
(
  person_id NUMBER,
  date_of_birth DATE,
  name VARCHAR2(30),
  address VARCHAR2(100),

  MEMBER PROCEDURE Hire,                  -- ????? ????????????????
  FINAL MEMBER FUNCTION Age RETURN NUMBER -- ?? ????????????????
) NOT FINAL;                              -- ????? ????? ???????

????????? ???????:

CREATE TYPE Student UNDER Person 
  (deptid NUMBER, major VARCHAR2(30));    -- ?????????? ?????????
CREATE TYPE Employee UNDER Person
  (empid NUMBER, mgr VARCHAR2(30))
  NOT FINAL;
CREATE TYPE PartTimeEmployee UNDER Employee
  (numhours NUMBER);

???????? ? ??????? ??????????? (not-instantiable) ????? ? ???????. ??????????? ????? ????????? ??????? ????? ?????, ?? ?? ????? ????????? ?????????? ????? ?????.

PL/SQL-?????? ????? ????????????? ? ???? ??????????? ?????????. ??? ????????? ????????? ??????? ?? ????????????? PL/SQL-????, ??? ????? ???????? ? ????????? ?????????? ? 2-10 ???. ??? ??? ?????? ???????? ?????? ?????? ??????? ?????????? ?????????? ?? PL/SQL, ?? ??????? ??? ???? ???????????????????. ???????, ???? PL/SQL-????????? ?????????? ???????? ? ????? ??????, ????????????? ???????? ?? ?????????? PL/SQL ???????? ?? ???????.

? SQL ? PL/SQL ????????? ????? ???? ??????. ???????? ?????????? ??? AnyData. ? ??????? ????? ???? ? ?????? ?????? ????? ????????? ?????? ?????? ????? (? ????? ?????? ? ??????????, ? ?????? ? ????????, ? ??????? ? ????????? ?? ???? ???????? ? ???? ???????? ?????). ???????? ???? ?????? ? ???? ?????? ?????????? ?????? ???????? ? ????? ????. ?????? ? ?????? ?????? ???? ???? ???????? ?????? ????? OCI.

????? ???????????, ?? ?? ????? ??????? ????? ???? ??? ???? ? ???????. ??-??????, ??? TimeStamp ???? ??????????? ??????? ? ???? ?????? ???? ? ????????? ?? ????? ??????? (?? 9 ?????? ????? ???????). ??-??????, ??? TimeStamp with Time Zone ?????? ? ????? ?????? ??? ???????? ?????. ????????? ?????? ????? ???? ?????????????? ? ?????? ???????? ?????. ??? ????? ????????? ?????? ? ??????????????? ????????? ? ???????????. ??????, ?????? ??????????? ??????? ?????? ? Oracle9i ?????????? ???? ?????? ???. ??????? ? ??? ????????????? ????? ???? ?????? ??? ???????? ?????????? ???????, ?? ??????????? ? ?????????? ???? ? INTERVAL DAY TO SECOND ? INTERVAL YEAR TO MONTH. ????????? ??? ????, ?????? ????????, ????????, ????????????????? ?????????????? ????? ??? ?????? ?? ??????:

Create table Jobs(job varchar2(9), trial_period INTERVAL YEAR TO MONTH);
insert into Jobs               -- ????????????? ????
  values ('MANAGER','1-6')     -- ??? ????????? ? ??????? ????;
  select ename, hiredate,
    hiredate + trial_period    -- ? ????? ?? ?? ??????????? 
    from emp                   -- ? ??? ??? ???? ?????? ??????
    natural join jobs;         -- ?????????? ??????????

????????? Java ?????????? ??????????? ? ??????????? ???????????? ??? ????? ????? ??????????. ? Oracle9i ?????????????? ????????? Servlet 2.2, JavaServer Pages 1.1, JDBC 2.0.

???????? ????? ????????? ? ????????? XML. ?????? ????? ??? ?????? XMLType, ????????????? ??? LOB, ? ?????? ? ??????? ????? ???? ?????????????? ????? SQL, PL/SQL ? Java.

???? ???????????? ????? ???? ?????????? ? ???????? ????????? ????????????? ??? ?????? ? ??????? ?????? ? ????, ??? ???????? ? ? ????????????: ?????? National Language Support ??????? ? Oracle9i ?? Globalization. ????? ???????????? ? ???? ??????? ????? ???????? ????? ????????? ??? ???????????? Unicode (UTF16), Unicode ?? ?????? ????????? EBCDIC (UTFE) ? ????????????? ??????????. ??? ?????????? ???????? ???? ?????? ?? ????? ????????? ???????????? ??????? Character Set Scanner. ??? ????????? ???? ?????? ? ??????????, ????? ??????? ????? ?????? ??????????? ?????????, ? ????? ??????? ????? ???????? ??? ????????. ??????? Locale Builder ????????? ?????????????? ????????, ??????????????? ? ??????????????? ????????? (??????? ??????????, ???????? ???? ? ???????, ??????? ???????? ????? ??????? ? ?????? ??????????, ??????? ???? ? ?.?.), ? ????? ? ??????? ?????????.

?????????????? ?????????, ?? ??????? ???????? ? ?????????? ??????? Oracle ? ??????? ??????? (workspaces). ???????? ??????? ???????? ????? ????????????? ??? ????????? ? ???? ?????? ??? ?????? ?????? ????????????.

????????, ??? ????????? ??????????? ?????? (read consistency) ? Oracle ??????????? ????? ?????????????????. ???? ?????? ???????? ?????? ? ?? ????????????? (commit) ??????????, ?? ?????? ?????? ???????? ?????? ?????? ??????. ????? ???????? ?????????? ?????? ?? ????????? ??? ??????????, ? ??? ?????? ?????? ???????? ?????? ?????????? ????????? ??????. ??????? ??????????? ?????????? ?????????, ????????? ?? ???????? ?????-?????? ??????? ???????? ??????? ???? ??????????? ?????? ???? ????????. ??? ???????? ?????????? ?????? ???? ???? ?????? ?? ?????????, ? ???? ?? ??????????, ??? ????? ?????????? ???????????, ???????? ??? ?????? ??????, ? ?????? ?????????.

?????? ??? ???????? ?????? ????? ??????? ??????? ??????? ? ???????? ? ???. ?????? ??????? ??????? ????????? ??????????? ??????? ? ??????????? ????????? ??????????? ??????????? ??????. ????????????, ?????????? ? ????? ??????? ???????, ????? ?????? ???????, ????????? ?????? ? ??????????? ??????????. ??? ?????????, ???? ???????????????, ????? ?????? ?????????????, ??????????? ? ??? ?? ????? ??????? ???????. ????? ????????? ?????? ? ??????? ???????? ?? ????? ??????????, ??????? ??? ????????? ? ???, ??? ????????? ? ???????? ?????????? ???? ??????. ??? ??? ????????????? ?????? ???????????? ???????? ???: ??????? ??????? ???????, ????????? ? ???, ????????? ?????????, ??????? ?????????. ???? ??? ????????? ? ????????? ? ????????? ???????, ???? ??? ? ?????????? ??????? ??????? ? ???? ???????

??????????? ??????????

? ?????????, ? ?????? ???? ?????? ?????????? ??????? ??? ???????????? ???????????? ????????????. ????? ????????? ? ???, ??? ??????????? ??? ?????? ????? ?????????? ????????? ???????????? ????? ???????, ????????????? ?????????? ????????????, ? ????? (???? ?????? ?? ????) ??????? ?????????? ?????????? Oracle. ????????? ??????????? ????????????? ???????? ????????? ????? (stored outlines).

?????????? ???????? ??? ???????? ??? ???? ???????????? ????????? ? ??????? ??????? ?????????? (bitmap join indexes, BJI). ??? ??????? ??????? ???????, ?? ??????????? ?? ????????, ??????? ? ????????????? ??????? ???. ????????, ? ??????? ?????? (??????? ?? ?? ???????? Sales) ???? ??? ??????? ???????, ?? ??? ??? ????????. ???????? ???? ? ??????????? ???????? (Regions). ? ?? ?? ????? ??????, ?????????? ??? ??????? ?? ??????? ? ???????? ?????????, ???????? ???????. ??? ??? ?????????? ?????? ??? ????????? ?????????? (join) ?????? Sales ? Regions. ?????????? ??????.

???????? ???????:

create table Regions 
(
  region_id number primary key,   -- ????????? ???? ?????????? ??? ???????? BJI
  region_name varchar2(200)
);
create table Sales 
(
  id number primary key,
  region_id number,               -- ??????? ??????????? ???????? ?????
  product_id number,
  amount number, 
  sal_date date
);

???????? ??????:

Select sum(amount) 
  from sales, regions
  where
       regions.region_id = sales.region_id
   and regions.region_name = '?????'

?????????? ???? ?????????? ????? ??????? (??? ?? ??????????? ?????????, ?? ?????? ????????? ????). ? ?????, ???????????, ???????????? ???????? ????? ?????? ? ???????? ?? ??????????:

SELECT STATEMENT Optimizer=ALL_ROWS
  SORT (AGGREGATE)
  NESTED LOOPS 
    TABLE ACCESS (FULL) OF 'SALES' 
    TABLE ACCESS (BY INDEX ROWID) OF 'REGIONS'
      INDEX (UNIQUE SCAN) OF 'REGIONS_PK' (UNIQUE)

???? ?? ??? ?????????? ????????? ???? ??? ??? ?????????? ???????, ? ? ??????? ??????? ????????? ?? ?????? ??????? Sales ?????? ? ???????????????? ?????????? ???????? ?? ??????? Regions, ?? ??? ?????????? ?????????????? ??????? ??????? Regions ?????? ?? ???????????, ? ????? ???????? ????? ????????????? ???????? ??????????.

???????? ??????:

create bitmap index Sales_reg_bji 
  on Sales(regions.region_name)
  from Sales, Regions
  where sales.region_id=regions.region_id

????? ???????? ??????????? ???????????? ???? ??????, ???????? ????????? (hint). ? ??????????, ???? ??????? ????? ?????????? ????????, ????????? ?????? ????? ?? ???????????, ?.?. ????????? ?????????? ????? ??????? ? ?????????????? ??????? ????? ??????????? ??????, ??? ??? ????:

select /*+ index(sales sales_reg_bji) */
   sum(amount) from sales, regions 
   where
         regions.region_id   = sales.region_id
     and regions.region_name = '?????'

?????????? ???? ?????????? ? ????????, ??? ???????? ??????? Regions ? ???????? ?????????? ???????:

SELECT STATEMENT Optimizer=ALL_ROWS
  SORT (AGGREGATE)
    TABLE ACCESS (BY INDEX ROWID) OF 'SALES' 
      BITMAP CONVERSION (TO ROWIDS)
        BITMAP INDEX (SINGLE VALUE) OF 'SALES_REG_BJI'

???????? ??????????? ???????????????? ?????????? ?????????? ?????????? ???????? ?? ???????????, ?? ????????? ???????? ??? ??????????? ?????????? ??? ?????????? ?????? ?????? ??? ?????????? ?????????????.

Real Application Cluster

?????????? Real Application Cluster ??????????? ????????? ?????? ??? ???? ????? ??????. ??? ?????????? ????????? ????????? ??????????, ????????????? ??? ????? ???????????? ?????????? ???????????, ?? ???????, ? ???????? ??? ???? ???????????? ?????????? ? ??????????????????, ? ??????????????????.

????? ????? ? ????????? ??????????? ???????????????? ????? ??????? ?????????????? ????????????? ?????? PGA ? ?????? ????????????? ???????? ??????? (???????? ?????????? Database Resource Manager).

????? ??????? ???????? ? Oracle9i ??????? ????????? ?????? ? ??????????? ??????. ??? ?????? ? ????, ??? ???????, ????????? ?????? ??? ???????? ???????? ? ??? ????????? ?????? ? ????????? ? ??? ?? ????? ?????????????.

??? ??????? ?????? ???????? ?????? ????? ??????? ??????? ?? ??????-???? ????????? (????? ???? ?????????? ???????? OLTP-???????), ????? ????????????? (????????? ??????????????, ?????????????? ?? ??????-???? ?????????? ? ?.?.), ? ????? ????????? ? ???? ??????. ??? ??????????? ????? ???????? ?????? ???????????? ???????????? ETL (Extraction, Transformation and Loading).

???? ?? ???????? ????? ? ???????? ETL ? ??????????, ????? ?????? ???? ???????? ????? ????????? ???????? ?????????. ?????????? Change Data Capture ????????? ??????????? ??? ????????? ??????, ????????? ?????????? ?? ?????????? ? ???????? ????????? (change tables) ? ????????????? ????????? ?????? ?? ?????? ?????????? ???????.

???? ?????? ? ????????? ??????????? ?? ??????? ??????, ?? ????????? ??????? ?????? (External Tables) ????????? ???????? ?????? ?? ????? ? ETL-????????. ??????? ??????? ? ??? ???????, ????????? ??????? ??????? ? ???? ??????, ? ?????? ???????? ? ??????? ?????. ? ???? ?????? ?????????? ??????? ?????? ????? ?????, ??? ????? ???????????? ????, ????? ??????? ?? ???? ??????????? ?????? SQL*Loader. ???????????, ??????? ??????? ????? ???????????? ?????? ??? ????????. ??????????? ??? ?????????????? ???:

create table emp_external
(
  empno number(4),
  ename char(10),
  job char(9),
  mgr number(4),
  sal number(7,2),
  comm number(7,2),
  deptno number(2)
)
organization external
(
  type oracle_loader                 -- ??????? ???? ???
  default directory emp_dir          -- ?????? ???? Directory ? ???? ??????
  access parameters
  (
    fields rtrim
    (
      EMPNO    (01:04),
      ENAME    (06:15),
      JOB      (17:25),
      MGR      (27:30),
      SAL      (32:39),
      COMM     (41:48),
      DEPTNO   (50:51)
    )
  )
  location ('ulcase2.dat')
)

??? ?????????? ???? Transformation ? ETL-???????? ???????????? ??????????? (pipelined) ???????. ??? ???????, ??????? ?? ????? ???????? ????? ????? (ref cursor) ? ?? ?????? ?????? ???? ????? ????? (nested table). ?? ?????????????? ??????? ? ???, ??? ??? ????????? ????? ??? ?????? ?? ?????, ? ?? ?????. ?????? ????? ??????? ???????? ???????? PIPE ROW, ??????? ?????? ???? ?????? ?????????? ? ???????????????? ?????????? ??????? ?? ??? ???, ???? ?????, ????????? ??? ??????? (??? ????? ????, ????????, ???? ??????????? ???????), ?? ????????? ????????? ??????. ?????????? ?????? ??????????? ???????.

?????????? ???? ??? ????????? (producer) ??????:

create or replace package emp_pipe is
  type strong_refcur_t is ref cursor return emp%rowtype;
end;

?????????? ???? ??? ????????? (consumer) ??????:

create or replace type emp_t is
  object (empno number, ename varchar2(10), sal number);
create or replace type emp_t_table as table of emp_t;

???? ??????????? ???????:

create or replace FUNCTION emp_pipe_fun(cur emp_pipe.strong_refcur_t)
  RETURN emp_t_table
  PARALLEL_ENABLE (PARTITION cur BY ANY)
  PIPELINED is
  one_row cur%rowtype;
BEGIN
   LOOP
     FETCH cur INTO one_row;
     /* ????? ????? ???????? ????? ????????? ?????????? ?????? */
     EXIT WHEN cur%NOTFOUND;
     /* ???????? PIPE ROW ?????????? ???? ?????? ?????????? */
     PIPE ROW (emp_t(one_row.empno, one_row.ename, one_row.sal*10));
  END LOOP;
 CLOSE cur;
 /* RETURN ?????????? ??? ??????????, */
 /* ?.?. ??? ?????????? ??????? ??? ??????? ????? PIPE ROW */
 RETURN;
END;

????????????? ???? ???????:

select * from table(emp_pipe_fun(cursor(select * from emp)));

????????? ?????? emp_pipe_fun ????? ????????? ?????????? ?????? ??? ?????? ??????????? ??????? (??????? ?? another_fun):

Select * 
  from table(another_fun(cursor(
     select * from table(emp_pipe_fun(cursor(select * from emp))))));

??? ????????? «??????????» ????? ??????? ???? ?? ????? ? ?????????????? ???????? ?????????????? ??????.

????? SQL-???????? Merge ? ????? ?????????????? ????????? ????????? Insert ????????? ??? Loading. ???????? Merge ? ?????????? ??????????? ??? ???????? ?????? ???????? UPSERT, ??????????????? ??? ??????? ????? ???? «???? ??????? ? ?????? ??????? ??? ???? ? ????????? ????? ?????? (Update) ?? ???? ???????, ???? ?? ???? ? ???????? ?????? ? ????? ? ?????? ?????? (Insert)». ???????? (?????????? ??? ????????????? ??????? Sales ? Regions):

???????? ????????? ??????? ?????? ?? ????????:

create table Sales_sum (region_id number, sum_amount number);

?????? ? ??? ?????? ? ???????? ?? ????????? ?????:

merge into sales_sum SS
  using (select region_id, amount from sales where sal_date>sysdate-1) S
  on (SS.region_id=S.region_id)
  when matched then             -- ??????? ?? ??????? ??? ????
    update set SS.sum_amount=SS.sum_amount+S.amount
  when not matched then         -- ?????? ??????? ? ?????? ???????
    insert (SS.region_id, SS.sum_amount)
  values (S.region_id, S.amount);

?????????????? Insert ????????? ????? ?????????? ???????? ?????? ????? ? ????????? ??????, ?????? ????? ???????? ??????? ??????? ?????? ??? ?????? ???????. ????????, ??? ???????? ?????? ? ???????? ? ????? ????????? ???????? ????????? ????? ??????? ??????.

???????? ??????? ??? ???????? ?????? ? ??????? ??????:

create table sales_arch as select * from sales where 0=1;
create table super_sales as select * from sales where 0=1;

????????? ??????:

insert all
  when amount>100000
    then into super_sales values (id, region_id, product_id, amount, sal_date)
  when 1=1 
    then into sales_arch values (id, region_id, product_id, amount, sal_date)
  select * from sales;

??????????????

????????????? ???? ?????? ? ???????, ?????????? ? ?????? ??????? ?? ??????????? ?????? ? ????????????????? ???????. ????? ??????? ??????????? ????????????? ?????? ???????? ??? ???? ????? ????????.

????????? ???? ?????? ????? ????????? ?? ???????? (????????, ??? ????????? ?????????? ??????????) ? ??????????? (????????? ????). Oracle9i ????????? ??????????? ???????? ??????? ???????? ?????????, ????????? ????????? ???????????? ????????? ????? ???? ?????????? ??????????, ? ??? ????? ? ????? ?????????? ????????? ?????????? ???????, ??? ?????? ???? ??????? ???? ?????? ??? ???????????? ????. ??? ?? ???????? ?????, ?? ????????????? ?? ?????????? ?? ????? ?????. Oracle9i ???????? ???????? ??? ???????? ??????????? ????? ? ??????????? ??????????????? ?????????? ?? ???????????.

?????????? Oracle Data Guard ? ???????? ?????? ???? ????????? ???? ?????? (Standby Database). ???????? ??????????? ??????? ???????? ???? ????????????? ??????? ?????????????? ????????? ???? ????? ?? ????????????. ???? ??? ?????? ????????? ??????? ??????????? ??????? ?????? (current redo log), ?? ??? ????????? ???? ??????, ?????????? ? ???? ??????, ???????? ????????????. ??? ?????? OLTP-?????? ??? ???? ???????????. ?????????? Oracle Data Guard ????????? ?????????????? ???? ?????? ?????? ???????, ??? ??? ???? ?????? ? ??????????? ?????? ???? ?????? ?????? ?????????? ?????? ??? ???????????? ??????????????????. ???? ?????? ?????? ???????????, ?? ????? ????????? Oracle ?????????? ????????? ?? ????????? ?????? ? ?????????? ??????, ?.?. ???? ??? ????????? ?? ?????????? ?? ????????? ???? ??????, ?????????? ?? ????????? ???????????????. ???????????, ??? ????? ???????? ? ????????? ??????? ? ??????????????????. ????? ???????????? ?????? ????? ? ????????? ?????????? ?? ????????? ?????? ?????? ????? ?????????? ?????????? ??????? ?? ???????? ???????. ??? ?? ???????? ???????????? ?????? ???? ??????????????????. ? ???? ????????????? ??????? ? ????????? ?????????? ?? ????????? ?????? «????? ?????????», ????? ????????? ?????? ?????? ? ?????? ?????? ????????, ?? ????????????.

??????? ???????? ??????????? ????? ??????????? Recovery Manager ? ?????????????? ?????????? ????? ????? ??????. ?????? ????? ?? ??????????? ???????? ?????????????? ??? ??????????? ????????????? (corrupted) ????? ???? ??????????? ?????, ??????????? ???? ????, ?? ????????? ????? ? ?????????? ? ???? ???????? ? ??????????? ????????. ??? ????????? ??? ??????? ???????? ????????????? ?????????? ???????????? ? ?????????? ????? ?? ????? ??????????? ?????, ??? ????????? ? ????????????? ????? ?????? ?? ???????? ?????????? ????. ?????? Recovery Manager ????? ??????? ?? ????????? ????? ?????? ???????????? ????, ????????? ??? ?????????????? ??? ?????? ???????? ? ???????? ??????????????? ???? ? ???? ??????. ? ???? ?????? ???????? ???????????? ??????????? ?????? ?????? ? ???????????? ?????.

??? ?????????? ?????????????? ???????? (Resumable Operations) ????????? ???????? ? ?????? ????? ???????????????? ????? ????? ? ????????? ???????????? ? ???? ??????. ?????? ????????????? ????????? ?? ??? ??????? ? ????????, ????? ???????? ????? ???????????? ???????? ??????? ??? ????????????? ???????? ??????? ???????? ????????? ????? ? ?????????? ??-?? ???????? ????? ? ????????? ???????????? (?????????? ??? ?????????) ??? ????????????? ????????? ??????? ??????. ? Oracle9i ????? ???????? ????? ???????? ??????????????. ????? ? ?????? ????????????? ???????? ?????? ??????? ?? ???????????, ? ?????????????????? ?? ??? ???, ???? ????????????? ?? ???????? ??????????? ??? ?? ??????? ???????? ???????. ??? ??? ??? ????????.

???????? ??????? (??? ??????? ???????????? ? ? ??????????? ???????? (dictionary managed) ????????? ????????????, ?.?. ? ????????-??????????? (locally managed) ?? ????????? ???????? Maxextents):

create table Small_extent_table 
  tablespace dict_tbs
  storage (initial 10K maxextents 1)
  as select * from emp;

????????? ??? ???????????? ? ??? ??????:

insert into Small_extent_table 
  select * from Small_extent_table;

???????? ?? ?????-?????? ??????? ??????? ?????? «ORA-01631: max # extents (1) reached in table SCOTT.SMALL_EXTENT_TABLE». ??????? ?????????????? ???????? (??? ????? ???????????? ?????? ????? ?????????? Resumable):

alter session enable resumable;

????????? ??? ???:

insert into Small_extent_table 
  select * from Small_extent_table;

?????? ???????? ? ???? ??????????? ???????? ??????? ? ???????. ?? ?????? ?????? ????????? ???:

select name, sql_text, error_msg from dba_resumable;

???????:

User SCOTT(70), Session 7, Instance 1
insert into small_extent_table s select * from small_extent_table
ORA-01631: max # extents (1) reached in table SCOTT.SMALL_EXTENT_TABLE

???????? ??????? ??????:

alter table Scott.Small_extent_table storage (maxextents 10);

?????? ???????? ?????? ?????????, ??? ? ?????? ?????? ???????? Insert ??????? ??????????.

??? ???? ???????????? ??? ????? ? ?????? ?????????????. ???? ???-?? ?? ?????????????, ????????????? ??? ??? ????????????? ???????? ?????? ??????? ??? ???????? ???????????? ?????????, ???????????? ?????????? ?????? ?????? ????? ??????. ? Oracle8i ? ????? ??????? ????? ???????? ??????? Log Miner. ? Oracle9i ??? ??????? ??????????? ?????? ?????????????, ?????? ??? ?????????????? ?????? DDL-???????? ??? ??????????? ???????? ???????????? ????? ???????.

????? Log Miner, ???????????? ?????? ????? ???????????????? ?????? ???????? ??? ???? ???????????? ? ??????????????? ??????? (flashback query). ? ??????? ???????? ?????? dbms_flashback ???????????? ????? ?????? ?????? ?????? ??????? (?????????????? ??????), ? ????? ????? ??? ??? ??????? ????? ?????????? ????????? ?????? ?? ????????? ??????. ????????? ??? ???????.

?????? ?????? ?????? ?? ???????:

delete from small_extent_table;
Commit;

????????? ????????? ?????? ?? ??????, ?????????????? ???????? ?????????? (?????? ????? ???????? ????? ???????? ? ??????? LogMiner):

exec dbms_flashback.enable_at_time(?05.12.2001 15:21:58')
select * from small_extent_table;

?????????? «??????» ?????? ????? ????????? ? ???? ? ????????????? ??????????? ??????:

declare
  cursor c is (select * from scott.small_extent_table);
  cc c%rowtype;
begin
  exec dbms_flashback.enable_at_time(?05.12.2001 15:21:58')
  open c;   -- ????????? ??????, ???????? ? ?????? FlashBack
  dbms_flashback.disable;  -- ????????? FlashBack, ????? ????????? DML
  loop
    fetch c into cc;
    exit when c%notfound;
    insert into scott.small_extent_table values
      (cc.empno, cc.ename, cc.job, cc.mgr,
       cc.hiredate, cc.sal, cc.comm, cc.deptno);
  end loop;
end;
/
commit;

???????????, ??????????? ?????????????? ??????? ????????? ?????? ?????????? ??????? ????????? ??????.

? ?????????????? ???? ?????? ???????? ??? ?????? ???????????? ????????? ???? ??????. ?? ??????, ??? ????? ?? ???????? ???????????????? ?????? ????? ???????? ????????? ???????? ????? ???? ?????? ???????????????, ????????, ??? ???????? ?????????? ????????????. ? Oracle9i ????????? ?????, ??????????? ???????? Oracle (Oracle Managed Files, OMF). ??? ?????????? ????? ? ???? ?????????? ??????? ??? ??????, ? ??? ??? ????? ????????????? Oracle. ??? ???????? ????? ?? ???? ?????? Oracle ??? ??????? ??? ? ?????. ????? ???????, ? ?????????????? ???? ?????? ????????? ??????????? ?????????????? ??????? ???? ??????, ? ??????, ??????????? ??????????? ??????.

????? ?????????????? ???? ?????? ? Oracle9i ?????? ??????? ?????????, ?????? ???????? ???????? ?? ????????????????? Oracle ????????? ???. ???????????? ????????? ?????????? ?????????? ? ?????????????? ?????????? ??????? ??? ???????????. ????? ?????, ????? ????????? Oracle ?????????????? ??????????????? ???????? ??????. ????????????? ?????? ?????? ??????? ??????????? ????????? ???????????? ??? ???????? ?????????? ??????, ? ??????????? ?????????? ????????? ?????? ???????????? ??????? ??????? ??? Oracle. ? ???? ?? ????????????? ????? ??????, ??????? ??????? Oracle ?????? ????????? ?????????? ?????? ???? ????? ???????? ??????????. ??? ????????? ???? ?? ?????????? ?? ???????? ?????? ?????? «ORA-1555: snapshot too old», ?? ??????????? ??????? ??????????? ?? ??????????.

?????? ???????? ?????? ?? ????? ???? ? ?????? ???? ??????? ???????????. ??????????? ????????????? ??????????? ????????? ??????????? (transportable tablespaces) ?????? ??????????? ?????????????? ??????????? ??????????? ??????? ????? ? ????-????????? ? ????-?????????. ? Oracle9i ? ????? ???? ?????? ????? ?????????????? ????????? ???????????? ? ?????? ???????? ?????, ??? ??? ??? ??????????? ?????????. ????????? ?????? ??????? ????? ? ????? ???? ??????, ????? ?????????? ???????????? ???????? ?????? ?????????? ?????????.

?????? ?? ????? ? ??????, ?? ?? ???????????? ?????? ?????????????? ???? ??????. ?????? ?? ????? ????? ?????? ?????? ?? ???????????????????? ???????. ???? ?? ???????, ???????????? Oracle9i ??? ?????? ?????? ? ??????????? ??????????? ???? ?????? (Virtual Private Database, VPD). ??? ???????? ?????????? ????????????????? ???????? ??????? (Fine-Grained Access Control), ????????????? ? Oracle8i. ??? ????????, ? ??????? ???? ?????????? ????? ???? ? ????? ??????? ???????????? ????? ???????, ??????? ??????????? ??? ?????? ??????? ??? ????????? ???????. ??????? ?????????? ????????? ??????, ??????? ?????????????? ? ??????? WHERE ??????? ??? DML-????????? ? ????? ??????? ???????????? ????? ?????, ??????? ???????????? ??? ?????? ??? ????????. ??? ??????? ?????? ???????????? ??? ?????????? ???????? ?????????? (application context). ???????? ?????????? ????? ????????????? ??? ????? ??????????, ?????????? ???????? ????????????, ????? ??? ????????? ????????????, ????? ??? ??????, ??????? ??????? ? ?.?. ???????? ?????? ?????????? ????????? ??? ????? ???????????? ? ???? ??????, ? ??????? ????????????????? ???????? ??????? ???????????? ???????? ????????? ??? ????, ????? ??????????, ? ????? ?????? ????? ?????? ?????? ????????????.

??? ??????? ?????? ???????? ? ???????????? ??????-????????? ??????????, ????? ???????????? ????????? ?????? ? ???? ?????? ? ????????? ??? ?????? ?? ?????. ? ?????????????? ??????????? ?????? ?????????? ????? ????????? ? ???? ?????? ????? ????????? ?????? ? ????? ???????? ??? ?????? ?????????? ????? ???? ?????????????. ?????? ????? ???? ?????? ????? ???????? ????????? ?????????????, ??????? ?????? ????? ? ?????? ???????, ??????? ??? ?????? ????? ? ?????? ?????????. ? VPD ??? ???????? ???????? ????????? ??????? ??????????? ????????? (Global Context) ? ?????????????? ???????. ?????? ? ????? ?????? ? ????? ????????? ????? ???? ????????? ?????????? ????????? (????????, ????????? ??????????) ? ??????? ?????????? ? ??????? ???????????????? ???????. ?????? ??????????, ????????? ?????? ????????????, ?????? ?????????? ?????????????, ? ???????????? ?????? ???????? ?? ?????? ?????????? ????????? ?????????.

? Oracle9i ?????? ? ??????? ??????? ?????????? ???? ?????????? ? Oracle Label Security. ??? ???????????????? Trusted Oracle, ????????????? ?????????? VPD. ???????? ??????? ? ??????? ??????? ?? ??????? ?????, ??????? ????? ????????? ??????? ????????????, ?????????? ? ?????? ? ???????. ??? ??????? ????????????? ????? ??????????? ????????? Oracle Policy Manager, ?? ?????? ???????? ????????????????.

??????????

????? ????? ?????? ?? ????????? ???????? ??????? ??? ????? ???????? Oracle9i Database, ?? ?????? ??? ? ?????? ????????? ????? 9i. ??????? ? ???? ?????? ?? ????????????? ???? ???????? ?? ???????? ?????????? ?? ??? ?????? ??????????. ? ?????????? ?????????? ?? ????????? ??????????? ??????????? ????????, ?? ???????? ? ???? ?????.

??? ???????, ??????????? ? ???? ??????, ???? ????????? ??????? ?? Oracle9i Enterprise Edition Release 9.0.1.1.1 ? Production ?? Windows NT Version 4.0 Service Pack 5.

?c??????: rsdn.ru

Readers Comments (Комментариев нет)