Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
Sep 152011
 

Nedávno mi vykouzlil na tváři takový škodolibý úsměv výraz “oracle commit zpět” se kterým přišel nějaký “programátor” na tuto stránku ;)

Každá relační databáze musí splňovat ACID test, tedy atomicity, consistency, isolation a durability. Důležité je v tomto případě slovo durability, tedy “trvalost/stálost”. A proto po commitu nelze vrátit uvedenou transakci, nelze.
Každý z nás chybuje a proto dříve nebo později napíšeme commit tam, kde by si přál programátor spíše rollback a často i nespustit takhle hloupý příkaz vůbec. Oracle a jiné databáze pochopitelně tedy musí nabídnout jak z toho ven. ACID řiká, že transakci nelze vrátit, ale lze: se vrátit do okamžiku před transakcí, lze se vrátit zpět v čase, lze se navrátit do doby před transakcí + před všechny závislé změny, lze vygenerovat příkaz, který udělá opravý opak (nikoliv vrátí změnu, ale vykoná další, která má opačný efekt) a lze pokračovat spoustu dalších technologíí od Oracle, které vrátí data po commitu a programátorovi zdraví ;) Vyžaduje to mít správně nakonfigurovanou zálohu, nastaveny správně parametry instance a v některých případech i štěstí.

Velice zajímavá technologie je Flashback, představená v Oracle 9i, rozšířená v 10g a dopracovaná v 11g. V jednom z prvních postů na tomto blogu jsem ukazoval Flashback Query, takže se pojďme podívat na další z flashback technologií Oracle – Flashback Versions Query

1) Založíme testovací tabulku
CREATE TABLE tbl_flashback (v_test) AS SELECT 'X' t FROM DUAL;

2) Vytvoříme PL/SQL anoymní blok ve kterém nasimulujeme několik transakcí vykonávající postupně několik UPDATE transakcí po 30s intervalu:

BEGIN
UPDATE tbl_flashback set v_test='A';
commit;
dbms_lock.sleep(30);
UPDATE tbl_flashback set v_test='H';
commit;
dbms_lock.sleep(30);
UPDATE tbl_flashback set v_test='O';
commit;
dbms_lock.sleep(30);
UPDATE tbl_flashback set v_test='J';
commit;
END;

3) A nyní, dokud nejsou tyto změny přepsány v UNDO (v závislosti na incializačních parametrech databáze UNDO_MANAGEMENT, UNDO_RETENTION a RETENTION GUARANTEE, aktuálním využití UNDO atd.) se lze pomocí Flashback Versions Query dotázat na minulé hodnoty i transakce, které tyto hodnoty měnily:

select

VERSIONS_STARTTIME,VERSIONS_ENDTIME,v_test,

VERSIONS_XID,VERSIONS_operation

from tbl_flashback versions between timestamp sysdate-6/1440 and sysdate;

Názvy pseudosloupců jsou více než výřečné – časy transakce (VERSIONS_STARTIME, VERSIONS_ENDTIME), transakce (VERSIONS_XID) a operace (VERSIONS_OPERATION). Tyto sloupce a další (VERSIONS_STARTSCN
a VERSIONS_ENDSCN) jsou dostupné pouze u tohoto typu dotazu, který se liší od běžného selectu klauzulí VERSIONS (červeně).

A nyní výsledek dotazu, zobrazující transakce a změny v testovací tabulce:

VERSIONS_STARTTIME VERSIONS_ENDTIME V_TEST VERSIOMS_XID VERSIONS_OPERATION
13.09.11 23:20:44,000000000 null J 010015002C9C0000 U
13.09.11 23:20:14,000000000 13.09.11 23:20:44,000000000 O 0200170040A50000 U
13.09.11 23:19:44,000000000 13.09.11 23:20:14,000000000 H 01002000309C0000 U
null 13.09.11 23:19:44,000000000 A null null

Flashback Query, Flashback Version Query jsou jedny z mnoha techologíí, které Oracle databáze nabízí (první možnost Flashback již od verze od 9i) přístup k datům i po commitu. Flashback Version Query často použije programátor i při analýze běžícího kódu – speciálně u ETL dokáže často ušetřit spoustu práce, šikovná věcička, že?

 Posted by at 20:37
Mar 262011
 

Občas se stane, že některý nezbeda (co se budem povídat, nezbedou jsem občas i já…) nekomitne do CVS nějakou změnu nebo prostě po přidání sloupce zapomene nějakou změnu zpropagovat na produkční prostředí atd. Podle důležitého dokumentu Oracle Database Concepts Oracle databáze sama o sobě netrackuje DDL změny a je pak složité dohledat hříšníka. Co však databáze umožňuje je definovat si vlastní trigger pro DDL operace a tedy napsat si vlastní PL/SQL kód, který DDL zaloguje nebo odešle všem kolegům email nebo cokoliv, co dokážete naprogramovat (například i commitnutí do CVS) ;)

V Databázi Oracle se Triggery dělí na tři hlavní typy DML (INSERT,UPDATE a DELETE), DDL (CREATE, ALTER…) a databázové (LOGON,LOGOFF..). Dnes nás bude zajímat především DDL, tedy trigger kterým jsem schopni hlídat změny v definici dat na úrovni schématu/databáze. Nejprve založíme tabulku, která bude sloužit k logování a následně v PL/SQL napíšeme trigger, který bude DDL operace insertovat do této tabulky.

Nejprve vytvoříme samotnou tabulku:

CREATE TABLE T_Ddl_Log_Rls (
OPERATION VARCHAR2(10) NOT NULL,
OBJ_OWNER VARCHAR2(30 CHAR) NOT NULL,
OBJECT_NAME VARCHAR2(30) NOT NULL,
SQL_TEXT VARCHAR2(4000) NOT NULL,
ATTEMPT_BY VARCHAR2(30) NOT NULL,
USER_OS VARCHAR2(20) NOT NULL,
ATTEMPT_DT DATE NOT NULL
);
table T_DDL_LOG_RLS created.

Dále by to chtělo ještě okomentovat tabulku a sloupce, tedy:

COMMENT On TABLE T_DDL_LOG_RLS IS 'Tabulka osabuje log DDL operací';
Comment on table t_ddl_log_rls 'TABULKA succeeded.

COMMENT ON COLUMN T_DDL_LOG_RLS.OPERATION IS ‘Typ DDL Operace – CREATE, DROP, ALTER..’;
COMMENT ON COLUMN T_DDL_LOG_RLS.OBJ_OWNER IS ‘Vlastník objektu’;
COMMENT ON COLUMN T_DDL_LOG_RLS.OBJECT_NAME IS ‘Jméno objektu nad kterým byla provedena změna’;
COMMENT ON COLUMN T_DDL_LOG_RLS.SQL_TEXT IS ‘Část Sql Textu’;
COMMENT ON COLUMN T_DDL_LOG_RLS.ATTEMPT_BY IS ‘Databázové jméno uživatele’;
COMMENT ON COLUMN T_DDL_LOG_RLS.USER_OS IS ‘Uživatelské jméno v operačním systému’;
COMMENT ON COLUMN T_DDL_LOG_RLS.ATTEMPT_DT IS ‘Datum změny’;
COMMENT on column t_ddl_log_rls.operation ‘TYP succeeded.
COMMENT on column t_ddl_log_rls.obj_owner ‘VLASTNÍK succeeded.
COMMENT on column t_ddl_log_rls.object_name ‘JMÉNO succeeded.
COMMENT on column t_ddl_log_rls.sql_text ‘ČÁST succeeded.
COMMENT on column t_ddl_log_rls.attempt_by ‘DATABÁZOVÉ succeeded.
COMMENT on column t_ddl_log_rls.user_os ‘UŽIVATELSKÉ succeeded.
COMMENT on column t_ddl_log_rls.attempt_dt ‘DATUM succeeded.

A nyní to nejdůležitější – trigger. Z praktických důvodů je vhodné vytvořit after trigger nicméně DDL trigger může být také before.

create or replace
TRIGGER trg_ddl_trigger
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
DECLARE
li ora_name_list_t;
n NUMBER;
BEGIN
n:= ora_sql_txt(li);
IF n>=1 THEN
INSERT INTO
T_Ddl_Log_Rls
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, substr(li(1),1,4000), USER,
NVL (SYS_CONTEXT ('USERENV', 'os_user'),
SYS_CONTEXT ('USERENV', 'bg_job_id')), SYSDATE
FROM dual;
ELSE
INSERT INTO
T_Ddl_Log_Rls
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER,
NVL (SYS_CONTEXT ('USERENV', 'os_user'),
SYS_CONTEXT ('USERENV', 'bg_job_id')), SYSDATE
FROM dual;
END IF;
END trg_ddl_trigger;

V PL/SQL kódu jsou použity některé vychytávky, které je třeba okomentovat – především jsou zde použity PL/SQL funkce, které lze použít jen v triggerech, typicky vrací hodnotu, která se týká triggeru. Nejdůležitější takováto PL/SQL funkce je ora_sql_txt, která vrací počet chuck (částí) SQL textu a zároveň vrací přes out parametr out vrací collection s částmi textu. Ve triggeru si beru jen část první části, tak aby se vešla do VARCHAR2(4000), asi není přímo účelem zalogovat celý SQL Text DDL operace, typicky DDL operace stejně nemá delší text. Dalé je v použita funkce ora_sysevent, která vrací typ DDL operace, procedura ora_dict_obj_owner vrací vlastníka objektu, funkce ora_dict_obj_name vrací majitele objektu a funkce SYS_CONTEXT vrátí hodnoty z USERENV (uživatelské proměnné) os_user pro jméno uživatele v operačním systému a bg_job_id vrací id/sid jobu, který běží v pozadí Oracle – například dbms_scheduler job.

No, a nyní nezbývá než daný trigger otestovat :


CREATE TABLE X (A VARCHAR2(1));
DROP TABLE X;
table X created.
table X dropped.

A výsledek :

OPERATION OBJ_OWNER OBJECT_NAME SQL_TEXT ATTEMPT_BY USER_OS ATTEMPT_DT
CREATE PLSQL X CREATE TABLE X (A VARCHAR2(1)) PLSQL AZOR 26.3.2011
DROP PLSQL X DROP TABLE X PLSQL AZOR 26.3.2011

 

Tohle je samozřejmě jen ukázka, která by chtěla vylepšit – především jsem vytvořil tabulku se surfixem RLS, což je stejný surfix jako má PL/SQL balík dbms_rls o kterém chci v blízké době napsat také článek a který možňuje filtrovat obsah tabulky například na základě jména v operačním systému – a tedy v této tabulce umožňit pohled jen na vlastní DDL operace.

 Posted by at 15:17
Mar 152011
 

Ve svém životě jsme se setkal s několika databázovými administrátory a z větší části jsem s nima naprosto spokojen. Sice jsem s několika DBA narazil na několik ostřejších emailů typu “tohle by na produkční databázi nikdy němělo být spuštěno” nebo dokonce “to si snad děláte prdel” , ale většinou jsem byl s jejich službama spokojen či dokonce potěšen jejich profesionálními a často velice rychlými reakcemi. Tedy z pohledu firmy ve které pracuji naprostá spokojenost s DBA.

Obecně si myslím, že pokud PL/SQL vývojář požaduje po DBA nějako službu či laskavost (ať už v rámci jejich práce nebo nad její rámec) , je dobré mít nějaký vzor a model v jakém s DBA komunikujeme. Jsem obecně proti striktnímu zakazování psaní DBA způsobu jakým si přejeme danou činnost vykonat. Někteří to zakrývají za podle mne naprosto závadějící větu “nekecajte jim do toho, vědí co dělají”.  S touto větou opravdu nelze souhlasit – jednak svět opravdu není ideální a existují PL/SQL vývojáři s většími znalostmi  z oblasti DBA než pracovníci příjmo najmutí na DBA (pochopitelně ale i opačně) a druhak především záleží na formě – nové funkcionality a nastavení často vyžadujeme, zatímco způsob realizace je v jejjich kompetenci a ten si tedy pouze přejeme. Uvádím krátky vzor jak komunikovat s DBA:

  • Vždy, ale naprosto vždy hned po požadavku nejprve uvádějme důvod proč tuto fukncionalitu potřebujeme.
  • Na druhém místě by dle mého názoru měl být přesný popis použití, který je vždy nutný,  čím více mají DBA informací tím lepší nám mohou poskytnout službu.  Některé informace ohledně použití by měly být součástí každého požadavku na DBA. Například: Očekávaná velikost objektů, informace související s performance (počty dotazů, počty indexů, typ databáze  tisíce dotazů za sekundu vs. dlouhotrvající datazy, očekávaný růst objektu,  očekávané přístupy k objektu, způsob růstu), informace souvisí s právy (kdo musí, může a nesmí přistupovat k objektu) a případná pomoc s auditací, důležitost objektu z pohledu DB i businessu, tresholdy. Závislost na ostatních objektech – nikoliv jen DB závislosti, které si DBA zjistí z systémových tabulek ale i business závislosti, typický denní čas využivání, zodpovědnou osobu…
  • A teprve až na posledním místě uvádějme přání - jakým způsobem si přejeme požadavek splnit (kvoty, tablespace, tresholdy) a ideálně s patřičním odůvodněním, proč si myslíme, že takto to je z našeho pohledu vhodné

Proč jako PL/SQL vývojáři můžemě či měli bychom po DBA někdy i požadovat “náš” způsob řešení?

  • Především proto, že jsou informace, které získáváme až v průběhu testů/vývoje a nejsme ne schopni přímo intepretovat DBA.
  • Protože jako vývojáři máme blíže k businessu a významu dat/objektů v databázi a proto při RFC a aktualizacích ( prvotního vývoje a návrhu se pochopitlně musí vždy účastnit PL/SQL vývojáři s DBA týmy) máme více informací a lepší odhad ohledně dopadu na výkon, způsob využivání atd.
  • Časem si u DBA získáme nějaký ten “znalostní kredit” a naše přání mají nějakou váhu jako převážně dobrá řešení
  • Můžeme jim ušetřit práci
  • Máme větší přehled o tom jakým způsobem se bude databáze/aplikace dále vyvýjet
  • Prováděli jsme výkonostní testy
  • Jsme také lidi odkojení firmou Oracle ;)

Závěr? Nevhýbejme se radám DBA jak mají dělat svou práci – ovšem tady rady, pakliže jsou přány, musí být psány ve formě přání a spatřičným odůvodněním, speciálně jde-li o nějaký specifický požadavek, který bychom si přáli vyřešit jinak než typicky řeší. Nadruhou stranu jsou témata, kterých bychom se jako vývojáři neměli prakticky dotýkat vůbec : RMAN,  zálohování , dostupnost, servery .. to jde uplně mimo nás.

 Posted by at 22:46
Feb 262011
 

Pokud potřebujete zjistit aktuální schéma svoje či uživatele, který pouští váš PL/SQL kód,  je možné použít

select sys_context( 'userenv', 'current_schema' ) from dual;

Zjištění aktuálního schématu se velice hodí pokud použiváte  ve svém programu authid user v některé z funkcí.

 Posted by at 20:52