?????????????? ???? ??????, ??? ? ??????????? ??????????????, ????? ????????? ?????????? ?????? ?? ????, ??????? ?????? ???????? ? ???????. ? ??????? ??????? ??????? ??? ???????? ???? ??????????? ??????, ??????????? ?? ??? ? ???, ??? ????????. ?? ??????????? ???????? ?? ????? ?? ?????, ? ??? ??????????? ????? ???????????, ? ???? ?????? Oracle ????? ?? ???????? ??????????? ?? ????? ???????. ? ??????? 8i, 9i ? 10g ???? ??????? ??????? ????? ???????????, ??????? ??????? ?????????? ????? ????, ??? ??????? ?????? ? Oracle. ??????? ???????? ?? 15 ???????? ?????????, ??????? ?????????????? ??? Oracle, ? ???????, ?????? ??? ?????????, ????????, ????????? ???? ?????????????.
????????? ?1: ?????? ????? ????????????
??????????????? ???????? ???????? ? Oracle ???????? ???? ? ?????????? ?????????? ??????, ??????? Oracle ????? ????????? ??? ???????? ?? ???? ???. ?????? ????? ? 2, 4, 8, 16 ??? 32 ????? ? ?????????? ??? ?????????????? ???????? ???? ?????? ? ???????????? ??? ??? ??????????? ????????? ????????????, ??? ? ??? ??????? ? ??????????? ??????. ??????????? ??? ????????, ??? ????? ????, ??? ?????? ????? ??? ??????, ?? ?? ????? ???? ??????? ??? ???????????? ???? ??????, ? ??? ??? ????????? ?? ???? ????????? ????????????? ? ???? ??????. ??? ???, ??????? ? Oracle 9i, ?? ???? ?? ???? ????????? ?? ???????? ????????.
?????? ????????? ???????????? ????? ?????? ???????????? ????????? ??????? ?????. ??? ????? ???????, ????????, ????? ???????:
CREATE TABLESPACE book_data
BLOCKSIZE 8K
DATAFILE ‘/u3/oradata/prod/bookdata01.dbf’ SIZE 100M;
?? ?????? ?????????, ????? ??????? ????? ???????????? ? ????????? ?????, ????????? ????? ??????? BLOCK_SIZE ? ?????????????? V$DATAFILE ? DBA_TABLESPACES.
???? ?? ??????????? ?????? ?????, ???????? ?? ???????? ?? ????????? ??????? ????? ??? ???? ??????, ?? ?????? ?????????? ??? ???? ??? ??????, ???????????? ???? ?????????? ?????????????. ?????????? ????? ????? ??????????. db_cache_size ???????? db_block_buffers, ????? ??????? ?????? ????????? ???? ??? ??????, ?????? ??????? ??????????????? ?? ?????????. db_2k_cache_size ????????? ?????? ????????? ???? ??? ?????? ?? 2 ?????, ???? ????? ????? ???????????? ??? ???? ??????. ????? ??????? ?????? ?? 4 ?????, 8 ?????, 16 ????? ? ?? 32 ?????. ? ??????? ?? ?? ??????????????? ? ????????? db_block_buffers ? ??? ??? ????????? ?????????? ? ??????, ? ?? ??????????? ?????????? ???????.
????????? ?2: ??????? ? ????? ????????? ???????
???????? ??? Oracle ??????, ??? ?????? ?????????????????? ?????/?????? (I/O) ???????????, ????? ??? ????? ??????? ????????? ? ???????????? ??????? ????????. ??? ?????? ? ??????. ?? ?? ?? ??? ???????, ??????? ?? ????? ?? ???????.
? ??????????? ?????????, ??????? ?????????? «??? ?????????? ?????????? ??????????????? ? ?????? ????? (??? ?????????? ? ??? ?????????? ?????????? ???????????), ??????? ??????????? (Bhaskar Himatsingka) ? ???? ?????? (Juan Loaiza ) ?? ?????????? Oracle ??????????, ??? ????????? ?? ?????????? ????????? ??????? ????? ?? ??????????? ???????? ????, ??? ??????? ? ???????????? ?????????. ??? ????? ?? ????? ????, ?????????? ???, ??? ??? ?????? ?????????. ??? ?????????? ??????? ??????? ???????? ???????? Oracle ????? ???????????? ??????????? ??????????????? ?????????? ???? ?? ????????? ?????????.
??? ??????? ????????????? ????? ????????? ?????? ???????????, ????? ??? ?????? ???????????? ??? ???? ??? ??????? ????????? ? ??????????? ?? ???? ???????? ?????? ??: 128 ????? ??? ????????? ??????, 4 ????? ??? ??????? ?????? ? 128 ????? ?????? ??? ????? ??????? ??????. ????????????? ???????? ???????? 128 ????? ????? ?????????? ????????? ?????? ????????????, ?? ????????? ????? ???????????? ??????? ???????????? ?????????? ?? ????????? ?? ?????????? ???????, ??????? ?????? ???, ?????????? ??? ?????? ?????????????? ?????????????? ??????. ??? ???????? ???? ???????? ? ????? ??????????? Oracle 8i, ??? ?????????? ???????? ??????????? ????????? ???????????? (Locally Managed Tablespaces).
????????? ?3: ??????? ? ?????? ? ???????????? ??????? ????????????? ??????
??????? ??? ?????? ???, ???, ????? ??????? ?????? ???? ??????????, ?????????????? ??? ?????????????????, ??? ?? ???????? ?????, ??? ??????????? ?????????. ??????? ??????? ?????? ???? ?????????????? ?? ??????? ???? «?????». ????? ????? ?????????????? ??????? ?????? ???? ???????, ??? ??????? ? ????????? ??????? ???????? ??????? ???????, ?, ???????, ????????????? ????? ?? ????? ?????. ??? ??? ?????? ????? ????????? ????????, ??????? ? Oracle 8i.
????????????? ? ??????? ALTER TABLE ????? ????? MOVE ????????? ??? ???????? ????????? ?????????? ???????????? ?/??? ???????? ??????? Oracle, ?? ????????? EXPORT ? IMPORT. ???????? ??? ????????? ???????:
ALTER TABLE author MOVE
TABLESPACE book_data
STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0);
? ???? ??????, ????? TABLESPACE ??????? Oracle ? ???, ??? ?????????? ??????????? ??????? AUTHOR ?? ?? ???????? ?????????? ???????????? ? ????????? ???????????? book_data. ????? STORAGE ???????? ????? ??? ?? ??? ??????? CREATE TABLE, ????????, ????????? ??????? ??????? ?????????? ????????, ? ???????? ??? (????????) ?????. ????? ?? ????? TABLESPACE ??? STORAGE ???????? ??????????????, ??? ???? ??? ??????????? ??????????? ???????, ?? ??????????? ??, ? ????????. ???????????????: ? ?????? 8i ??????? ????? ??????????? ?? ??? ????? ?????????? ??????? ALTER TABLE, ????? ???????, ????? ????? ?????? ????? ?????? ? ?????? ????????? ????????. ? ?????? 9i ??? ??????????? ?????. ????? ?? ?????????? ? ?????????? ???????? ????? ONLINE, ??????? ????? ???? ??????????, ???? ? ?? ????? ??? ???????????? ????????? ??.
????? ????, ????????, ??? ?? ?????? ????? ??????????? ?????????? ????????? ???????????? ??? ???????? ???? ????? ???????: ?????? ? ?????. Oracle ?? ??????? ?????? ???????, ???? ?? ????? ????????? ????????? ?????. ???? ???? ??????? ???????? ??????? ??????? ??? ????????? ???????, ??? ???????? ????????? ??? ??????????? ???????? ? ????????? ??????? ? ??????.
????????? ?4: ??????? ?? ????? ???? ???????
??? Oracle ???????? ? ?????, ??? ????? ???? ??? ??? ??????? ??? ????????? ???????, ?? ?? ????? ???? ???????????? ??? ??????. ????? ?????????? ?? ????, ?? ?????? ???? ??????? ????? ??????? ??? ????? ???????, ? ????? ????????? ??? ??????? ??????? ?? ?????????????? ???????. ?, ???????, ?? ?????? ???? ???????????? ??? ????? ??????? ??? ??????????, ???????, ????????, ? ?.?., ??????? ??????? ??? ?????????????? ???????. ?? ??????, ? ?? ??????. ??????? ? Oracle 9i R1, ?? ?????? ??????? ??????? ? ???????? ?????? ???? ??????????. ??? ????? ???????????? ????? SET UNUSED ? DROP ??????? ALTER TABLE. ???? ?????????? ??????:
ALTER TABLE author
DROP (birthplace, birthyear);
ALTER TABLE author
SET UNUSED (birthhospital);
??? ????? ???????????? ??????? ???????. ???????????? ???????? ???????????, ????? Oracle ????????? ???????. ???????, ??????? ??? ????????? ??? ??????????????, ???????? ?? ??????? ??????, ? ????? ???????, ???????????? ?? ????? ?????? ????????? ?? ????. ?? ?????????? ???????????? ??? ??? ?????????? ?????????? ???????, ???? ??? ?? ????? ??????? ??????? ???. ??? ????????????? ????? DROP ??????????? ???????? ??????????. ????? SET UNUSED ????????? ??? ?????? ??????? ??????????? ??????????, ?? ???????? ???????????? ????????????? ??-?? ????????? ???????? ?? ???????.
???????????????: ???? ??????? ??????? ??? ?????????????? ??? ?????????, ??? ??????, ????????????? ? ???????, ??????????? ???????????? ???????????. ??? ??????? ???????? ????????? ????? ??????????? ?????? (DDL), ??? ??? ??? ??? ??? ???????? ????????? ROLLBACK. ?????? ??????????? ? ?????? ??????? ????????? ?????!
????????? ? 5: ???????? ????????? ?????? ??????????? ? ??????? ?????????
????? ???????????? Oracle ???? ?????????? ????????? ???????? ?????????, ??? (??? ??) ?????? ???? ?????????? ??????? ?????, ??? ?????? ??? ???????? ?????????. ?????????? ?? ????, ??????? ?????? ????????? ?????????, ??? ??? ??? ????????? ?? (???? ?????? ??????), ???????????? ????? ??????? ??? ???. ??????? ???????, ???????? ?????????, ???????????? ???????? ??? ????? ?????????. ??????, ??????? ? Oracle 8i, ???????? ????????? ????? ???? ??????? ??? ? ??????? ????????? ??? ? ??????? ???????????? ?? ????????. ??? ????? ????? ???????????? ????? AUTHID ??????? CREATE PROCEDURE. ????????:
CREATE PROCEDURE count_authors
(num_books OUT NUMBER)
AUTHID CURRENT_USER
IS
SELECT COUNT(*) INTO num_books
FROM author;
END;
???????? ?????????, ???????????? ??? AUTHID CURRENT_USER ???????? ?????? ? ???????, ?????? ? ??? ??????, ???? ???????????? ??????? ???????? ??? ??????? ?????????? ???????????? ??. ????? ????, ?????? ?? ??????????????????? ????? ??????, ????????, ?? ??????? author ? ??????????? ???? ???????, ????? ?????????? ? ???????????????? ????? author, ? ?? ? ??????? ??????????????? ?????????. ??? ?????????? ?????? ???????????? ????? ??? ?????? ???????? ??????????????????? ? ????????? ????? ????????????.
????????? ? 6: ?????? ??? ????? ???????????? ??????
????, ??????? ??????????????? ???????? ?? ????? SQL ? ??? ? ???????????? IT ? ????? ????????? ??? ???????? ?????? ??-?? ????? ????????? ? ??????? ???????. ??????????, ?? ?????? Oracle, ?????? ?????? ???????????? ???????? ????? ????? ????????????? ???????? ???????? ??? ??????. ???????, ????????? ?? ???????????? ???? ?????? ?????? ???? ?????? ??????????, ????? ???????? ? ?????????? ???????? ?????????? ? ???? ??? ?????????????. ???? ???????????? ?????????? ????? ???????? ? ????????? ??????????? ??????? ?? ???? ??????. ?????????????? ????? ????? ?????? ?????? ???? ?????????? ????? ??????? ???????, ??????? ??? ????????? ?????? ???. ??, ??????? ? Oracle 9i, ???????????? ????? ????????? ????? ????? ?????? ??????????????? ????? ??????? SQL. ?????????? ??? ????? ????? ????? ??????????? 9i, ?????????? ???????? Flashback Query.
???? ?????????? ??????, ???????????? ???????????????? ?????? ? ????? SCOTT. ????? ????????? ??????, ? ????????? ????????? ?????????????:
DELETE FROM emp WHERE empno = 7934;
COMMIT;
?????? ????????? ??????? ??? ?????????? ?????????? SELECT, ? ???? ??????? ROLLBACK ?? ????? ??????? ??. ??? ?? ?????, Flashback Query ????? ?????????? ?????????? (???????) ???????, ????? ??? ???? 10 ????? ?????, ????? ????????? ?????? ??? ??? ????????????:
SELECT * FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP — INTERVAL ’10’ MINUTE)
WHERE empno = 7934;
???? ???????? SELECT ????? ???? ??????????? ??? ????????? ? ????????? INSERT, ????? ????????????? ????????? ??????. ??????, ??????, ??? ???????? INSERT ????? ???????? ???? ????????????, ?????????? ?? ???????, ? ??? ????? ???????? INSERT ??? ??????? ????? ???????????. ????? ????????? ???? ????? ???????????? ? ??????? ? ??????????? ??????? ???????, ??????????? ????? DBMS_FLASHBACK:
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TIMESTAMP ?yyyy-mm-ddhh:mi:ss?);
??????, ? ??????? ?????????? ? ??????? ?????? ????????????, ?? ????? ???? ????????, ??? ???????? ?????? ??? ??????. ??? ?????? ?? ??????-?????????????? ????????, ?????????????? ? ???????????? ?? ???????: ?? ?????? ???????? ???????, ?? ?? ?? ?????? ???????? ???! ????? ?????????? ????? ? ????????? ?????, ????????:
EXEC DBMS_FLASHBACK.DISABLE;
??? ???? ????? ????? Flashback Query ????? ????????, ???? ?????? ?????? ???????????? ????? ??????????????? ?????????? ????????????? ?????? (Automatic Undo Management ? AUM) ? ?????? ???? ??????? ????????? ???????????? ??????. ?????, ?? ??????? ???????????? ????? ???????????? ? ???????, ?????????? ????????? ????????? ????????????? undo_retention ? ???????? ?????????? ???????????? ??????. ???? ??? ????? ????? ??????? ???????? ????? ??? ????????????? IT, ????????? Oracle ??????????? ? ???, ????? ????? ????????????, ??????? ??????????????? ???????? ????????? SQL, ??? ? ????????? ?????????????? ????? ????? ??????????? ??????. ???????????? ???????????? ????? ????????? ??????????, ??? ??? ?? ????? ????????????? ??? ?????????????? ?????? ??? ??? ???? ??????, ????????? ????????? ??????????
FLASHBACK ANY TABLE
?? ?????????: ??? ?????????? ?????! ? ?????? 9i, ??????????? Flashback ?????????? ????????? ????? ??????????????? ??????? (DML), ???? SELECT, INSERT, UPDATE ? DELETE. ?? ? Oracle 10g, ??????????? Flashback ????? ???? ????????????? ???? ????????? ???????.
????????? ? 7: Oracle ?? ????? ??????? ???? ??????
? ???? ?????? DATE Oracle ?????? ?????? ????? ? ??????????? ?? ?????? ??????. ????????????, ??????????? ? ????? ?????? ?????????? ???????, ????? ?????? DATE ???????????? ??? ?????? NUMBER. ???????? ???????? ???????? ? ????, ??? ?????????? ?????????? ??????? ?????????? ??????? ?????.
??????? ? ?????? 9i, ? Oracle ??????? ??????????????????? ??? ?????? ????/???????, ??????????? ?? ?????????? SQL 1999 ????. ????? ???????? ????? ???????, ??????????? ??? ?????? TIMESTAMP ? ??????? ??????????? ?????????? ???? ??????? ????? (???????? ?????????? ???? ?? ????????? ? 6):
CREATE TABLE event_ts (
event_id NUMBER(6)
,event_name VARCHAR2(40)
,start_time TIMESTAMP(2)
,elapsed_time TIMESTAMP(2)
);
??????? TIMESTAMP, ??? ? ??????? DATE, ?????? ???? ???????? ? ????????? ???????. ??????, ? ??????? ?? ???, ????????? ????? ? ????? TIMESTAMP. ????????? ??????? ???????????? 23 ????? 2004, ?????? ?????????? ????? ???????:
TIMESTAMP ?2004-03-23 00:00:00.50?
???? ? ??????????? ??????? ???? DATE ????? ?? ??????????, ? ??????????? ??????? ??? ???????? TIMESTAMP ??????? ??????? ???????? ????????????. ???????? ?????? NLS_TIMESTAMP_FORMAT ????????? ???????? ????? ?? ???????, ??? NLS_DATE_FORMAT ????????????? ??????????? ?????? ??? DATE. ????? ??????? ???????????????, TO_TIMESTAMP, ??????? TIMESTAMP ?? ?????? ??????? ????????, ? ??????? TO_CHAR ???? ?????????, ????? ????? ???? ?????????? ?????????? TIMESTAMP ? ????? ???????.
????? ???????? ??????? ???? ? ????? ? ??????? TIMESTAMP, ??????????? ?????? SYSDATE ??????? SYSTIMESTAMP. ????????:
SELECT SYSTIMESTAMP FROM DUAL;
????????? ? 8: ??????????? ???? ??????? ???????? ???????
???????????? IT ?????? ????????? ?? ?????? Oracle ORA-1578: «Oracle data block corrupted» (???? ?????? Oracle ????????). ?????????? ????????? ?????? ?? ?????? ???? ?????? ????????? ???? ??????????. ????????? ?????????????? ????????? ???? ?? ?????? ????? ? ?????? ?????. ??? ????????? ???????? ????? ????????? ?????? ????:
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id =
AND BETWEEN block_id AND block_id + blocks — 1;
??? < ?????_?????> ? < ?????_?????> ???????? ??????? ?? ????????? ?? ??????. ???? ?????? ??????, ????? ?????? ???????? ??????????? ????. ????? ?????, ? ??????????? ?? ???? ???????, ?????????????? ???????? ???? ?????? (??? ???????? ? ????????? ?????????), ???? ??????? (??? ??????), ???? ????? ??????? (??? ???????? ????????? ?????? ? ?????? ??????? ??????).
??????, ? Oracle 9i Enterprise Edition ????? ??????? ?????????? ?????????????? (RMAN) ? BLOCKRECOVER ? ????? ???????????? ???? ?? ?????, ?? ??????, ? ????? ???????? ???????? ??????????? ??????. ????? ??????????? ? RMAN ? ?????????? ? ??????? ????? ?????? ????????:
BLOCKRECOVER DATAFILE BLOCK ;
??? ?????????? ?????????? ??????????? ? RMAN ??????????? ????? ?????????????, V$DATABASE_BLOCK_CORRUPTION, ? ???? ?????? ???? ?????? ? ??????, ??? ???????????, ??? ???????????? ?????????? BLOCKRECOVER. ????? ???????????? ??? ?????, ??????? ???? ???????? ??? ???????????, ????? ???? ???????????? ????????? ?????????????????? RMAN:
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;
???? ?????? ??????????, ???? ? ?????????????? ????????? ?????? ????????? ??????. ??? ???????????????? ?????????? (???????????) ????? ?????????? ???????????? (restore) ?????????????? ????????????? ????? ??????, ? ????? ???????????? (recover) ???? ???? ?????? ? ??? ?????????????? (????? ???????????) ?????????. ??? ? ??? ????? ????? ???????????, ????????? ????????? ?? ????? ?????????????? ??? ???????????? ???? ??????.
????????? ? 9: ??????? ?? ????? ???? ????????????? ??? ??????????????
?????????????? ??????? ??????? ??? ????????? ??? ???? ?????? ?????? ???????? ???????? ????? ??????? ? ??????????? ? ??? ?????? ??????. ??????? ?????? ?? ????? ???? ?????????????, ? ???? ?????? ????? ???? ????????, ?????? ???? ? ??? ?? ??????? ??????? ?????? (??????????? ?????? ???????? NULL).
? Oracle 9i ????????? ?? ????, ? ????? ??? ??????????? ??? ??????????? ???? ???????????. ??????? ALTER TABLE ????? ?????? ??????????????? ??????????????? ???????:
ALTER TABLE books RENAME COLUMN tiitle TO title;
??????? ?? ???? ??????? ? ??????????? ????? ????????????? ???????????? ????? ??? ???????. ??????, ??????? ??????? ???? ?????????????, ?????????, ???????? ? ??????? ????? ???????? ????????????????? ? ???? ????? ????????? ? ?????? ???? ???????? ?????????????. ????? ??????????? ?????????????? ??????? ?????????, ??? ?? ??????? ??? ??????????? ?? ??? ??????????? ???! ???????, ???? ?? ?????? ??? ??????? ???????, ? ??? ??????? ????????? ????????, ??? ??????? ?????? ??????????? ????????.
???????????? ????? PL/SQL ?? ????? DBMS_REDEFINITION ????????? ??? ???????? ????????? ??????? ???????, ?????? ?? ????? ????? ????????? ??????? ???????? ? ??????????? ????????? ? ???????? ????????? ??? ?????????????. ??? ???????? ??????? ?????????, ??, ? ?????, ??????????? ????????? ????:
???????, ??????????????? ??????? ?? ???????? ????????????? ? ?????????? ?????? ?? ?? ?? ???? ??????????? ????, ??????? ?????????? ? ???? ???????. ?????????? ?????? ???? ???????? ? ?????????????? ????????. ?????? ??, ??? ?????? DBMS_REDEFINITION, ????????? ?????, ? ??????? ???????? ??????? ?????????? ????????????? ?? ????? ????????????.
????????? ? 10: ?????? ???????? ??????? ????? ???????????? ?????????? ?? ?? ?????????????
????? ? ???????? ??????????? ???????????? Oracle ? ?????????? ???????, ??????? ?? ????? ????????, ??? ??? ?? ??? ???????????? ?????????? ?? ???????, ???? ???????? ??????? ??????? ?? ??????????? ??? ???. ??? ?? ?????, ??????????? ??? ????????????? ????? ?????. ??????????? ???? ?????? ??????? Oracle, ?? ??? ?????? ????????????????? ???????. ? Oracle 9i ????? ????????? ?????????? ???????? ????? ????????? ?????.
?????? ???? ??? ????? ????????? ?????????? ?? ????? GRANT ANY OBJECT PRIVILEGE. ? ??????? ???????? ????
GRANT SELECT ON scott.emp TO giselle;
?????????? ?? ????????, ???? SCOTT ?? ??????????? ??????? ??? ?????????? SELECT WITH GRANT OPTION ?? ??? ???????. ??????, ??? ?? ????? ???????? ????? ????????. ??? ?????????? ????? ????? ?????????????? ???????? ??????????????, ????? ???????????? ?????????? ?? ??????? ?????, ?? ???? ????????????? ????????????????, ??? ???????? ???? ?????.
????????? ? 11: ????????????? ??????????????? ??????? ? SQL ???????? % ? _
????? ??????? ?? ???????????? ??????? ? ?????????? LIKE, ??? ???, ??? ? ???????????? ??????? ???????? ? ???????? ? ?? ???????????? ????? ???? ????????? ????????: ???????? (%), ??????? ????????????? ???? ??????, ? ??????? ????????????? (_), ??????? ????????????? ?????? ?????? ???????. ??? ??????????? ????? ???????? ???????????? ?? ??????????? ?????? ?? PL/SQL.
??????? ? Oracle 10g, ???????? ?????????????? ??????????? ????????? ???? ???????????. ?????? ????? ?????????? ?????????? ?????????, ???? ????, ??? ???????????? ? UNIX ??? ???????? ?????????, ?????? ???????? ??????????????? ? SQL. Oracle ???????????? ?????? ????? ??????????? ?????????? ????????? (Extended Regular Expressions ? ERE) ????????? POSIX. ???????? ???? ????????? ????? ????? ? ?????????? C ? ????????? Oracle 10g SQL Reference.
???????? REGEXP_LIKE ???????? LIKE, ? ?????????? ????????? ?????? ???? ????????? ? ??????? ?????? ? ? ????????? ???????. ????????, ????????? ????? WHERE ???? A1, A2, ??? A3, ????????????? ?????? ? ?????? ?????? ??????:
WHERE REGEXP_LIKE (partno, ?^A[123]?)
???????? ????????, ??? ? ????? ??????????? ????????? ?? ????????? ????????? ?????? *. ? ??????? ?? ????????? ????????, ???????????? ? ?????????? LIKE, ?????????? ????????? ???????????? ????????? ????????????, ???? ?? ?? ?????????? ? ?????????????? ??????? ??????? ????????????. ????? ???????, ??????????????? ????????? ????? ??????????????? ?A1? ? ?A3?, ?? ????? ? ?A234?. ????? ??????? ?????? ????????????, ??????????? ???? $, ????? ??????? ????? ??????:
WHERE REGEXP_LIKE (partno, ?^A[123]$?)
??????? REGEXP_INSTR, REGEXP_SUBSTR ? REGEXP_REPLACE ????????? ??????? INSTR, SUBSTR ? REPLACE, ????? ???????????? ?????????? ????????? ? ????????? ????????????. ? ?????, ???????? ????? ????? ????? ? ????????? Oracle 10g SQL Reference.
????????? ? 12: ?? ?????? ??????????? ???????, ????? ???????? ?? ?????? ????????????? ??????????
???????? ???????????? ????? ???????? ????????, ?????? ????? ? ??????? ? ????????? ??????????? ????? ????? ?????? ???????? ????? ???????. ??? ?????, ???, ???? ? ??????? ?????-?? ???? ??????? ?????????? ?????, ????? ????? ???????????, ?????? ??? Oracle ?????? ??????????? ?????? ????, ? ??????? ?????-???? ??????????? ?????? ? ?????? ?? ??????? ????????????? ?????????? ??????? (High Water Mark ? HWM). ????? ????, ??? ????? ????? ????????, ??? ???????????? ?????? ???????? ???????? HWM ??????? ? ???, ????? ???????? ??????? ???????, ???? ??????????? ????????/????????/???????, ???? ? ??????? ??????? ALTER TABLE MOVE.
? 10g ??? ?????? ?? ???????? ???????????. ????? ?????, ????????? Online Segment Shrink (??????????? ?????? ????????), ????? ?????????? ????????? ???????????? ? ???????, ? ????? ??????????????? ???? HWM. ????????? ?????:
ALTER TABLE ???_??????? SHRINK SPACE [COMPACT] [CASCADE];
??????? ???? ??????? ??? ????? ???????? ? ?????????????? ??????? ? ?????????? ?? ?????. ????? HWM ?????????????? ? ????? ??????? ??????? ? ??????????? ??????????????? ????????????.
????? COMPACT ???????? ??????????????, ?? ?? ???????????? HWM, ? ?? ??????????? ??????????????? ????????????. ????? CASCADE ??????? ?? ?????? ????????? ???????, ?? ? ????? ????????? ???????, ????????, ???????.
? ?????? ??, ? ??? ?????? ????????? ??????? ????????. ????????? ????????????, ? ??????? ???????? ???????, ?????? ???? ??????????? ??? ??????????????? ?????????? ????????????? ? ????????? (Automatic Segment Space Management), ? ??? ????? ??????? ?????? ???? ???????????? ??????????? ?????. ????????? ???????????? ?????? ????? ????? ????? ROWID, ?? ?????? ????????? ????? ????????, ??????? ??????????? ?? ????????? ROWID, ??? ??? ????? ????????? ????????. ??????? ????? ? ?????? ???????????: ??????????????????? ? ????????????.
????????? ? 13: ???????? ?????????????? ??????? ?????????????? ?????? ?????? ??????
??? Oracle ?????, ??? Oracle ????????? ??????????????? ???? (recovers) ????? ?????? ?????????? ??? ??????????? ???????, ? ?? ?????????? ???????, ???? ??????? ????????, ? ??????? ??????? RECOVER ? RMAN ??? ? SQL*Plus. ??????, ??? ????????, ??? ????? ?????????? ?????????? ???????????, ?? ???????????? ???????? ?????? ????? ???? ?????????????? ?????? ???? ?????? ? ????????? ?????, ?????? ?? ????, ??? ????????? ????????, ? ?????? ??????? (roll forward) ? ???????????? ??????? ????? ???????.
? Oracle 10g ???? ???????? ?????? ???????: ?????????? ???? ?????? ? ??????? ??????? ????? ????????????, ????????? ??????? ????? ??????. ??? ??????????? ????? ?????????? ????? ??????? ? ???????? ????????? ??????????????.
????? Flashback, ????????? ? Oracle 9i, ???? ???????????? ????????? ? 10g ????? ???????? ????? FLASHBACK DATABASE (????????? ? ??? ??????? ? RMAN, ? ??? ???????? ? SQL*Plus). ??? ?????????? ????????? ?? ?????? ?????? ???????????? ???? ??????, ??????????? ?? ????? ?? ?? ???????? ?????????, ?????? ????, ????? ???????????? ?? ?????? ?? ?????? ?????-???????????. ??? ????? ???? ??????? ???????, ????????? ?????? ??????? ? ????????????? ??????? ??????. ?? ????????? ?????????????? ??????? ?????? ??????.
??????????????? ??????????? ? ??????? ??? ???? ???? ?????? ? ??? ??? ??? ???????? ??????????????: ???? ??? ???? ???????? ????? ??????, ????????? ????? ???? ??????? ???????, ? ???????? ?? ???????????? ? ???????. ???? ???????? (? ????? ????? ??????), ??? ???????? ????? ?????????:
???? ?? ?????? ?????????? ???? ?????? ? ????? ??????? ???????:
??? ???????? ???????? ?? ??? ??????? ??????????, ??, ??? ? ? ????? ????????? ???????, ?? ????????? ???????????? ????????? ?????, ????? ???????? ??????? ??????? ?????? ? ??????, ???? ???????? ???-???? ??????????. ??? ????????? ?????????????? ?????????? ?????????? ? ????????? Oracle Database Backup and Recovery Advanced User?s Guide, ????? 9 (?Flashback Technology: Recovering from Logical Corruptions?).
????????? ? 14: ????????? ???????????? ????? ?????????????????? ?????? ?? ?? ?? ????? ?????????
????? ???????????? ?????????? ????????????, ????????? ? Oracle 8i, ????????? ??????????????? ?????????? ????? ?????? ?? ?????? ?????????? ? ??????. ????????? ????????? ???????????? ??????? ?????????? ??? ???????? ?????? ????????? ??????? ?????? (?endianness?), ?????? ??? ????????, ??? ?? ?? ?????? ???????????????? ????????? ???????????? ? ????????? ? ?????? ???????? ????? ??? ?? ?????? ?????????? ?????????.
? Oracle 9i ???? ???????? ? ???????? ?????, ?????? ??? ???? ????????? ????? ? ????? ?????????? ????????? ???????? ?????. ? Oracle 10g ????? ??? ?? ???? ? ???????? endianness (??????? ?????????? ??????), ?????? ??? ?? ?????? ???????????? RMAN, ????? ????????????? ??????? ?????????? ?????? ??? ??????. ? ?????????? ?????????? ????? ????? ??????, ??????????????? ??? ???????????? ???????????? ???????. ????? ????? ????? ????????????????, ??? ??? ????????? ? ?????????? ???????, ??????????? ??? ????????? ?? ? ?????? ?????????.
??? ????? ???????????? ??????? RMAN CONVERT. ????????:
CONVERT TABLESPACE example TO PLATFORM ?HP-UX (64-bit)?;
????????????? V$TRANSPORTABLE_PLATFORMS ???????? ??????????, ? ???, ????? ????????? ???????? ????????????, ? ??? ????? ????????? ???????????? ??????? CONVERT.
? ????? 8 (??????????? ?????????? ???????????????) ????????? Database Administrator?s Guide ????? ????? ?????????????? ?????? ???????????? ??????????????? ????????? ???????????.
????????? ? 15: ???? CONNECT, RESOURCE ? DBA ? ??? ??????? ?????? ???????? ?????????????
?????? ??? ??? ??? ?????????? ???? CONNECT, RESOURCE ? DBA ??? ???????? ????? ??????? ??????? ?????????????, ??? ? ?????????????????? ?????????, ??? ? ??? ?????? ???????, ?????? ?? ????????. ?????? ????, ?? ??????? ?? ?????? ????????? ? ??? ????? ??????? ? ??????? ????, ??? ?????? ??????????? ??????? ??? ???? ??????, ????? ???????????? ???????. ??? ??????????? ??? ? ?????????? ???? ???? ??????? ??? ? Oracle 7 ? ??-??, ??? ??? ???????? ?????? ????? ? ??? ???? ????? ??????? ??????? ?????? Oracle 6 ? ????? ?????????, ??????? ?? ???????? ? ??? ???. ?? ??? ? ???, ??? ? ??? ????: ????????? ????????.
??????? ??????????? ?????????? ?????????? ? ???????????? ???????????? ??????????, ??? ???????????? ?????? ????? ?????? ??????????? ??????????, ??????????? ??? ?????????? ?????????? ?? ???????. ???? CONNECT, ????????, ???????? ????????? ??????????, ???? CREATE TABLE ? CREATE SEQUENCE, ????, ? ??????? ???? ?? ????? ????????? ??????????? ???????? ?????????????. ???? RESOURCE ???????? ?????? ?????????? UNLIMITED TABLESPACE, ??????? ???????? ??????? ???? ?????????? ????????????.
????? ?????????? ???? ?? ???????????????? ?????????? ????????? ????? ??????? ? ? ???????????? ? ????? ???????????? ??????? ???????? ????. ???????????? ???? ????? ??????????? ????????? ? ????????? ??????????, ? ????? ???????????? ???? ?????????????. ???????????? ????????????? ????? ??? ????????? ??????????? ?????? ? ??? ???????, ???? ??? ????? ????????? ? ???? ????????? ????????????? ???????. (???? ????? ?????? ???? ??????????? ??????????????? ??? ?????????????, ? ?? ??? ?????, ?? ?????? ????????? ??????? ? Enterprise Manager ????? ????????????? ??????? «Create like», ????? ??????????? ???????????? ??????? ?????? ????????????.)
???????????? ??????????
???? ???????????????? ?????? ? Oracle ?????? ?? ????? ? ?????????????? ????????. ???? ??????????? ?? ??? ????????, ??? ?? ?????? ????????? ????????? ????? ??????, ?? ?????? ????????, ??? ???? ? ?????, ?? ??????? ?? ?????? ??????????. ?????? ?????????? ????? ?????????, ? ? ??? ????? ????? ??????????? ????? ???????????????? ??????? ??? ?????????? ??????.
?c??????: http://www.oracle.com
Tags: cache, forward, Oracle, SQL, UNIX