2018. június 10., vasárnap

DWH és DM alapok fogalmak

DWH (Data Ware House) vagy EDW (Enterprice Data Warehouse)  értelmezésba az adatárház ill. vállalti adattárházzal foglakozunk.


Inmon: Az adattárház egy témaorientált, integrált, historikus  és nem változó adatgyűjtemény a menedzsment döntéseinek támogatására.

  Bill Inmon:  Az adattárház csak egy része a vállalat teljes üzleti intelligencia rendszerének.
  A vállalatnak egy adattárháza van és az adatpiacok ebből táplálkoznak.
  Az adattárház adatmodellje normalizált.
  A tranzakciókat adatkörökre osztja (pl.: vevő adatok)

 Kimball szerint az adattárház a szervezet adatainak elemzésére, gyors lekérdezhetőségére és könnyű használatra strukturált halmaza

  Ralph Kimball: Az adattárház az adatpiacok összessége. Az adattárház adatmodellje csillagsémás.
   A tranzakciókat „tény”-re és dimenziókra osztja

  Csillagsémás (Kimball) vagy normalizált (Inmon) adattárházat építsünk?

  Az adatpiac az adattárház része amely tipikusan egy üzleti folyamat, vagy egy szervezeti egység támogatására készül.
 (Köznapi értelemben egy olyan adattárház amely egy szervezeti egység igényeinek kielégítésére készül)
 Szerkezetét tekintve csillagsémás

  Mi az OLAP: A megosztott többdimenziós(Fast Analysis of Shared Multidimensional Information (FASMI, Nigel Pendse)

- Mi az adatbányászat: Az adatbányászat a nagymennyiségű adatokban rejlő információk fél-automatikus feltárása mesterséges intelligencia algoritmusok alkalmazásával (például neurális hálók, szabálygenerálók, asszociációs modellek)

Az „üzleti intelligencia célja végső soron az, hogy akik a döntéseket hozzák (legyenek azok bármilyen döntések),  minél relevánsabb, minél magasabb feldolgozottsági szinten álló, minél aktuálisabb,  minél könnyebben emészthető és felhasználható információkat kapjanak.

 A jobb információ jobb döntést eredményez, a jobb döntés nagyobb hasznot hoz.”


DWH építés feladatok:
- Kezdeti Scope kijelölése.
- Mérhető célok meghatározása
- Kockázat elemzés (Készen áll a bevezetésre)
- Beruházás gazdaságossági vizsgálatok elkészítése
- Koncepcióterv és megvalósíthatósági tanulmány
- Projekt megtervezése: Ki mit fog csinálni (staffing), ki kinek mikor számol be, …

Mi a projekt terjedelme? (Scope) 
- Mi az a Scope? Kerítés az igényeink körül. Terület, amin belül az ajánlatadók focizhatnak. Nekünk kell kijelölni


Adatprofilozási , adatminőségi észrevételek,  interfész specifikációk (hogyan, mikor fogjuk elérni a forrásrendszereket, hogyan válogatjuk le őket, …), adatfolyam diagram (mi honnan jön, hova megy (stage, pre stage, business meta), adatmennyiség becslések.


Az újonnan keletkező DWH / DM táblákat adatkörhöz kell rendelni. Adatkörnek egy üzleti szempontból összetartozó táblahalmazt nevezünk


Integráció az adattárházban létrejövő tárgyterületekhez kapcsolódó adatokat az érintett adatforrásokból szabványosított, egységes formára alakítva egy helyre gyűjti és egységbe rendezve kezeli.


DWH fő kiszolgáló lépései:
Adatkinyerés a tranzakciós (vagy más vállalat-működtetési) forrásrendszerekből
A kinyert adatok átformálása riport (beszámoló) készítés számára
A riportok, beszámolók elérhetővé tétele a döntéshozók számára.



DWH modell építés szintjei:

A koncepcionális (vagy szemantikai) szintű adatmodellek a felhasználók adatleíró módszereit takarják, függetlenek a konkrét implementációtól.
A logikai szintű adatmodellek már függnek az adatbázisszervertől, de még mindig egy absztrakt, bár alacsonyabb rendű felhasználói nézetet biztosítanak.

A fizikai szint adatmodelljei már teljesen a konkrét adatbázis implementációtól függnek, azt írják le, hogyan is tároljuk fizikailag az adott adatokat.



Hiereachia:
Téma -> modul -> csomag -> objektum ( saját process flow package, táblák, mapping-ek, stb )


Mapping-eket csak blokk-szinten lehet létrehozni. Modul-szinten csak blokkokat hivatkozhatunk.
egy, a modul több további blokkjának alapul szolgáló tábla PREP-töltése, - érintett táblák (load, stage, dwhd, prep...),

A nézetek használata nem javasolt, az üzleti logikát a mapping-ekbe kell rakni.

A fentiek alapján látható, mennyire hasznos a névkonvenció: bizonyos tapasztalattal minden objektumról lehet tudni hogy hova tartozik, mi a szerepe.

A kollekciókba nem kell betenni az adatpiaci táblákat, csak a historizálás előtét-tábláit, mivel az adatpiaci táblával az üzleti blokk nem foglalkozik (csak a generált historizáló).

Oracle (ETL) transzformálás SQL használatával:

A CREATE TABLE ... AS SELECT utasítás (CTAS) hatékony eszköz nagy mennyiségű adat transzformálására.

Sok adat transzformáció leírható SQL kifejezésekkel, a CTAS utasítás használatával a transzformált adatokat hatékonyan egy új táblába írhatjuk.
Meglevő táblák esetén a INSERT /*+APPEND*/ ... AS SELECT utasítás alkalmazható.

Adattárház környezetben a CTAS utasítás tipikusan párhuzamosan, NOLOGGING módban fut.
DW_DEV Fejlesztői környezet
DW_TEST Teszt környezet
DW_PROD Éles környezet


Az OWB és az ODI is egy grafikus ETL-eszköz, vagyis elsődleges rendeltetése az, hogy az adattárházas műveleteket (Extraction, Transformation, Load) vizuálisan megjeleníthető módon, könnyen definiálni lehessen.
Az OWB / ODI adatbázisa egy úgynevezett tervadattár (repository),


ODS (operational data store) :
Az ODS a tranzakciós adatok egy olyan nagy részletezettségű gyűjtőheje, amit az adatok egyesítésére és tisztítására használhatunk, esetleg a teljes részletezettségű adatok elérésére.


Az adattárház fogalmat akkor használjuk, ha vállalati szinten lát el adatgyűjtő, adatszolgáltató funkciókat, ehhez általában több adatforrást felhasználva.

ETL (Extract, transform, load  || kinyerés, átalakítás, betöltés):
    Adatkinyerés az operatív rendszerekből (extraction)
    Adattranszformáció (különböző adatformátumok, mértékegységek, nyelvek stb.)
    Adatminőség ellenőrzése, adattisztítás (cleaning) ***
    Adatbetöltés az adattárház struktúráiba (loading)


Ezek az objektumok a repository-ban csak tervként, metaadatként léteznek. Innen telepíthetőek a fizikai adatbázisba (deploy-művelet).

 Metaadat kezelés

Az adattárház metaadat-szótára kulcsfontosságú a használhatósága és a hatékonysága szempontjából.
Fontos ezért, hogy kialakításuk jól átgondoltan, esetleg megfelelő formalizmusok használatával történjen.

A metaadat egy átfogó jelző olyan fogalomrendszerre és leíró módszerekre, amelyek egy eredeti fogalomrendszerrel foglalkoznak, abból származnak. Innen adódik a metaadat kifejezésre az "adatokat leíró adatok" meghatározás.

Fontos még az általános használhatóság, a könnyen illeszthetőség feltétele is más rendszerekhez,
valamint lehetőség szerint a minél teljesebb elfogadottság, a nagy piaci szereplők meggyőzése a metaadatkezelő szabvány használatáról, így az egységesítés.


Módszertanok

SAS rendszerben a SEMMA módszertan lépései:

Sample: mintavételezés, azaz egy vagy több kisebb tábla létrehozása megfigyelési, tanulási célra.
Explore: az adatok természetének vizsgálata, a bennük rejlő összefüggések, trendek, ellentmondások, hiányosságok meghatározása.
Modify: a modellezés számára szükséges változók készítése, kiválasztása, átalakítása.
Model: olyan modellek készítése, amelyek a mintaadatokban rejlő összefüggések alapján a lehető legjobb előrejelzéseket tudják adni.
Assess: a különböző modellek kiértékelése, összehasonlítása.

A CRISP-DM (CRoss Industry Standard Process for Data Mining) módszertan lépései:

Üzleti probléma megértése
Adatok megismerése
Adatelőkészítés
Modellezés
Kiértékelés
Bevezetés


10 fázisú, az előzőeknél általánosabb módszertan [Bodon]

1. Az alkalmazási terület feltárása és megértése, fontosabb előzetes ismeretek begyűjtése, és a felhasználási célok meghatározása.
2. Céladatbázis létrehozása: kiválasztani a használandó adatbázist vagy annak egy részét, amiből a tudást ki akarjuk nyerni.
3. Adattisztítás és előfeldolgozás: itt olyan alapvető operációkat értünk, mint a téves bejegyzések eltávolítása, hiányos mezők pótlása, zajok szűrése stb.
4. Adatintegráció: a feldolgozás számára fontos, esetleg elosztott adatbázisok egyesítése.
5. Adattér csökkentés: az adatbázisból a cél szempontjából fontos attribútumok kiemelése.
6. Adatbányászati algoritmus típusának kiválasztása: eldönteni, hogy a megoldandó feladat klaszterezés, szabály-, illetve mintakeresés, esetleg osztályozás-e.
7. A megfelelő adatbányászati algoritmus meghatározása: előnyeinek, hátrányainak, paramétereinek vizsgálata, futási idő- és memóriaigény elemzése.
8. Az algoritmus alkalmazása.
9. A kinyert információ értelmezése, esetleg visszatérés az előző lépésekhez a további finomítások céljából.
10. A megszerzett tudás megerősítése: összevetése az elvárásokkal, előzetes ismeretekkel. Eredmények dokumentálása és átadása a felhasználónak.




Az OWB/ODI-ben az adatok mozgatását mapping-ekkel, vagyis leképezésekkel lehet leírni. Egy mapping egy adatfolyamot ír le.

A mapping-ben az adatok mozgását nyilakkal definiálhatjuk, az átalakító lépéseket ún. operátorokkal. Két tábla kulcsmezőkkel való összekapcsolásának pl. egy join-operátor felel meg.

A feldolgozások vezérlése, process flow-k
 Egy csomópont egy adott adatmozgató lépésnek (pl. mapping-nek) felel meg, amit már a process flow elkészítése előtt definiálni kell. Ezután lehet bekötni (bind) a process flow-ba.

A process flow-k irányított gráfok. Azt írják le, hogy az egyes adatfeldolgozási lépések milyen módon, ill. milyen feltételek mentén követhetik egymást.


- ROI : (Return on Investment),   a befektetés megtérülésének mutatója.
 A ROI a jövedelmezőségi mutatók egyik típusa, amely megmutatja a befektetések hatékonyságát.
 A mutató nagy népszerűségnek örvend, mert egyszerű kiszámítani, és egyértelműen megmutatja, hogy érdemes-e befektetni az adott lehetőségbe.

A ROI számítása
ROI = (befektetés bevétele - befektetés költségei) / befektetés költségei
Amennyiben a ROI értéke negatív, vagy létezik egy másik befektetési lehetőség, amely ROI-ja magasabb, akkor adott beruházásba nem ajánlott befektetni.
(http://ecopedia.hu)



A blokk process flow-inak a független kezelhetőség okán nem szabad a blokkon kívülre hivatkozniuk (a standard historizálásra sem, ami szintén az üzleti blokkon kívülről hívódik).


A kétdimenziós táblákban (ezeket hívjuk relációknak).  
A relációk sorokból és oszlopokból állnak, ahol az oszlopokat attribútumoknak nevezzük.
A relációk közös attribútumok segítségével összekapcsolhatók.
A reláció nevét és a reláció-attribútumok halmazát együtt relációsémának nevezzük.
A relációs modellben a terv egy vagy több relációsémát tartalmaz.

Ezen relációsémák halmazát nevezzük relációs adatbázis-sémának.



Táblajellemzők
   Partícionált táblák – Jellemzően Stage, ODS, DM rétegekben

o   LIST

o   RANGE

o   HASH (jellemzően nem használt)

·         Snapshot táblák – jellemzően DW rétegben

o   Állandó sorok kulcs szerint

o   Kieső sorok kulcs szerint

·         DELETE és DELETE WHERE táblák

·         Source_system, source_tch



Adatfolyamról általában

·         Nagy tömegű és számoságú táblák és eljárások indokolják az folyamatütemező használatát (Pl. PWM)

Performancia praktikák

·         XCHANGE, DROP, CREATE PARTITION

·         INSERT /*+ APPEND */

·         TRUNCATE,DELETE

·         UPDATE kerülendő

·         Asszociatív memóriatömbök használata.

·         Result cache

·         BULK COLLECT

·         FORALL UPDATE (Példa)


Programozás praktikák

·         Logolás

·         A programok legyenek újrafuttathatóak

o   előkészít maga előtt

o   takarít maga után

·         Készítsünk csomagot a közösen használt azonos funkciókra

o   egyszer kell jól és hibátlanul megírni

o   start_map – töltés előtt mindig futó programrész

o   end_map – töltés után mindig futó programrész

o   Authid Current_User – egy sémában van csak, de úgy fut, mintha ott lenne

Logolás
·         pragma autonom_transaction

·         log sequence

·         OS USER

o   V_USER       := SYS_CONTEXT('USERENV', 'OS_USER');

·         session_id

o   V_SESSION_ID := SYS_CONTEXT('USERENV', 'SESSIONID');



Közösen használt
·         Minden program esetében (~95 %) azonos teendőnk van a töltés kezdetekor és elvégzését követően

·         Töltés előtt

o   logbejegyzés a folyamat indulásáról

o   ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE

o   dbms_session.set_nls('NLS_SORT', 'BINARY');

o   dbms_session.set_nls('NLS_COMP', 'BINARY');

o   Output táblák ürítése, ill. előkészítése az adatbetöltésre

·         Töltés után

o   ANALYZE

o   archiválás

o   logbejegyzés a folyamat végeztéről

·         Hiba esetén – exception

o   logolás, SQLERRM, stb.



Analyze Partícionált tábla esetében
dbms_stats.gather_table_stats(ownname          => l_owner

                             ,tabname          => t_table(x)

                             ,partname         => l_partname

                             ,granularity      => 'PARTITION'

                             ,estimate_percent => l_analize_percent

                             ,degree           => 6

                             ,cascade          => True

                             ,no_invalidate    => True);



Teljes tábla esetében
dbms_stats.gather_table_stats(ownname          => l_owner

                             ,tabname          => l_table_name

                             ,estimate_percent => l_analize_percent -- 30

                             ,degree           => 6

                             ,cascade          => True

                             ,no_invalidate    => True);



Töltések hasonlósága
·         Az adattárház folyamatok ~50 %-a azonos algoritmus mentén működik

·         GNRC_... programok készítése (Synonyms használata) – Példa

·         Programgenerátorok használata

o   Pl.: Változáskövetéses táblák töltése esetében



Az optimális teljesítmény eléréséhez fogadjuk meg a következő javaslatokat:
  • a ténytáblák minden idegen kulcs mezőjére építsünk egy-egy bitmap indexet;
  • a STAR_TRANSFORMATION_ENABLED inicializálási paraméter értékét állítsuk TRUE-ra!

A fenti feltételeket teljesítő adattárházakban a legtöbb csillaglekérdezés ún. csillagtranszformációt alkalmaz végrehajtáskor, ami nagyon hatékony.
A csillagtranszformáció egy olyan optimalizációs technika, melyben a csillaglekérdezés SQL-jét az Oracle-adatbázis implicit átalakítja a hatékony végrehajtás érdekében.

Az optimalizáló automatikusan alkalmazza a csillag-transzformációt, amikor alkalmas, a felhasználónak nem is kell feltétlen tudni róla.


Az Oracle-csillagtranszformáció választásakor a lekérdezést két lépésben hajtja végre.
   Az első lépésben a ténytábla a szűréseknek megfelelő sorait határozza meg. Bitmap indexeket használva ezt hatékonyan meg tudja tenni.
   A második lépésben a ténytábla kiválasztott sorait a dimenziótáblákhoz kapcsolja. A csillag-transzformáció feltétele, hogy a ténytábla valamennyi joinban szereplő oszlopára legyen csak ezt az egy mezőt tartalmazó bitmap index.

Az Oracle optimalizáló a következőképp dönti el, hogy alkalmazza-e a csillag-transzformációt, ha annak feltételei különben adottak: elkészít egy optimális tervet az eredeti, majd egy másikat a transzformált SQL-re. Az alacsonyabb becsült költségű tervet alkalmazza végül.

Ha a lekérdezés a ténytábla sorainak nagy részét érinti, előfordul, hogy a csillag-transzformáció és a bitmap indexek használata helyett a tábla teljes végigolvasása a hatékonyabb.

CREATE BITMAP INDEX bji ON f (d1.c1, d2.c2) FROM f, d1, d2 WHERE 1.pk = f.fk1 AND d2.pk = f.fk2; 



 EXECUTE IMMEDIATE / EXECUTE_CLOB_SCRIPT

Oracle Katalógus táblák és saját vezérlő-paramétertáblák

OLOCAL és GLOBAL index
OTöltés után a GLOBAL INDEX invaliddá válik
OREBUILD INDEX használata javasolt
ÉALTER INDEX <INDEX> REBUILD PARTITION <PART>;
ÉÖsszes használhatatlan indexre (GLOBAL):
 For i In (Select index_name

              From user_indexes

             Where table_name = p_table_name

               And status = 'UNUSABLE')

  Loop

    Execute Immediate 'alter index ' || i.index_name || ' rebuild';

  End Loop;



A particionálás nemcsak új adatok hozzáadásakor hasznos, hanem adatok kimozgatásánál és archiválásánál is. Sok adattárház esetén gördülő ablak van az adatokra. Ezzel a módszerrel egy régi partíciót a többitől függetlenül és gyorsan elvehetünk a táblából.
Egy partíció kimozgatása a táblából nem feltétlenül jelenti az adatok fizikai törlését az adatbázisból. Két lehetőség van a régi adatok kimozgatására egy particionált táblából. 

  • Az egyik módszer az adatok fizikai törlése a partíció eldobásával, így a lefoglalt terület is felszabadul.
  • A másik módszer szerint a régi partíciót kicserélhetjük egy ugyanolyan struktúrájú üres táblával. 



További hasznos SQL parancsok (oracle)

Paraméteres változó használata (SQLdeveloper)



define a = 1;
select &a from dual;
undefine a;
This is bind (bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):



var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from dual;
exec print x;
SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 10

.?

DEFINE szam_valt = 80
select '&1' as parameter_bekeres, &szam_valt as valami from dual

------------

---- 1.  féle paraméteres lekérdezés (normál futás)
define valtozo = 4321;
select 20110501 into &valtozo from dual;
select &valtozo as ez_egy_szam from dual;
select '&&valtozo' as ez_egy_szam from dual;
undefine valtozo;



---- 2.  féle paraméteres lekérdezés (procesként futás)
variable myid number
exec :myid := 4321
select :myid as szam_2 from dual;





---define start_value = 4;  &lala
SELECT :start_value + LEVEL -1 n
FROM dual
CONNECT BY LEVEL <= :end_value - :start_value + 1







select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))

select * FROM table(sys.odcivarchar2list('A','B','C','D'))



select column_value
from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));


select column_value
from table(sys.dbms_debug_vc2coll(1,2,3,4));


SELECT LTRIM('<===>HELLO<===>', '=<>')as e1
,RTRIM('<===>HELLO<===>', '=<>') as e2
FROM dual;





create type number_tab is table of number;

select * from table (number_tab(1,2,3,4,5,6));






SELECT * --username,opname,target_desc,sofar,totalwork,message
FROM V$SESSION_LONGOPS



select * from   v$sql_plan where SQL_ID ='dsn8x9b2bb3kj'


select * from   v$sqlarea



select * from v$sqltext_with_newlines



 ---- ORA12

SELECT EV, NAP_EVBEN

FROM DATUM_D

ORDER BY NAP_EVBEN DESC

FETCH FIRST 5 ROWS ONLY;





--- ORA11

SELECT * FROM (

SELECT EV, NAP_EVBEN

FROM DATUM_D

ORDER BY NAP_EVBEN DESC

) WHERE ROWNUM <= 5;





---- ORA12

SELECT Id, Col1

FROM TableName

ORDER BY Id

OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;





--- ORA11

SELECT Id,

Col1

FROM (SELECT Id,

Col1,

ROW_NUMBER() over (ORDER BY Id) RowNumber

FROM TableName)

WHERE RowNumber BETWEEN 21 AND 40





---- ORA12

SELECT Id, Col1

FROM TableName

ORDER BY Id

OFFSET 5 ROWS;





--- ORA11

SELECT Id,

Col1

FROM (SELECT Id,

Col1,

ROW_NUMBER() over (ORDER BY Id) RowNumber

FROM TableName)

WHERE RowNumber > 20



WITH generator ( VALUE ) AS (

SELECT 1 FROM DUAL

UNION ALL

SELECT VALUE + 1

FROM generator

WHERE VALUE < 10

)

SELECT VALUE

FROM generator;





CREATE INDEX ord_customer_ix ON orders (customer_id);





CREATE INDEX first_name_idx ON user_data (UPPER(first_name));





CREATE BITMAP INDEX

emp_bitmap_idx

ON index_demo (gender);





INSERT /*+append*/ INTO Employees

SELECT *

FROM Employees;





SELECT /*+ PARALLEL(8) */ first_name, last_name FROM employee emp;





SELECT /*+ PARALLEL(emp,8) */ first_name, last_name FROM employee emp;





SELECT /*+use_hash(e d)*/ *

FROM Employees E

JOIN Departments D ON E.DepartmentID = D.ID

(As explained in many places, "in a HASH join, Oracle accesses one table (usually the smaller of the joined results)

and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one)

and probes the hash table for matches to it."

It is preferred against Nested Loops method when the tables are big, no indexes are at hand, etc.)





SELECT /*+ result_cache */ NUMBER FROM main_table;

(Oracle (11g and above) allows the SQL queries to be cached in the SGA and reused to improve performance. It

queries the data from cache rather than database. Subsequent execution of same query is faster because now the

data is being pulled from cache.

)





-- DBLINK

CREATE DATABASE LINK dblink_name

CONNECT TO remote_username

IDENTIFIED BY remote_password

USING 'tns_service_name';



The remote DB will then be accessible in the following way:

SELECT * FROM MY_TABLE@dblink_name;





SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 10



SELECT username,opname,target_desc,sofar,totalwork,message FROM V$SESSION_LONGOPS



select * from   v$sql_plan where SQL_ID ='dsn8x9b2bb3kj'



COLUMN percent FORMAT 999.99



SELECT sid, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent

FROM v$session_longops

WHERE sofar/totalwork < 1

/



WITH generator ( VALUE ) AS (

SELECT 1 FROM DUAL

UNION ALL

SELECT VALUE + 1

FROM generator

WHERE VALUE < 10

)

SELECT VALUE

FROM generator;





Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/



Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15



select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
--  and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/

SQLs Running from longtime
--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining
from
       v$session_longops
/

Active Sessions running for more than 1 hour
---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

Session details associated with SID and Event waiting for
---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

Session details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on

Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops
-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;


Session details with SPID
-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session
------------------------------------------
select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sid';

To list count of connections from other machines
------------------------------------------------
select count(1),machine from gv$session where inst_id='&inst_id' group by machine;

To get total count of sessions and processes
--------------------------------------------
select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress
-------------------------------
select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue
-------------------------------------------
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions
----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username
---------------------------------------------
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs
------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR
----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

SELECT   /*+ parallel(c,2) */ 
* FROM   sh.customers c ORDER BY   
cust_first_name, cust_last_name, cust_year_of_birth

----

 MERGE /*+ parallel(s) parallel(u) */ INTO sales s USING sales_updates u 3     ON (s.prod_id=u.prod_id AND s.cust_id=u.cust_id AND s.time_id=u.time_id 4         AND s.channel_id=u.channel_id AND s.promo_id = u.promo_id) 5    WHEN MATCHED THEN 6  UPDATE SET  s.amount_sold  =u.amount_sold, 7              s.quantity_sold=u.quantity_sold 8  WHEN NOT MATCHED THEN 9  INSERT VALUES ( u.prod_id, u.cust_id, u.time_id  , 10                  u.channel_id, u.promo_id, 11                  u.quantity_sold, u.amount_sold);

 CREATE DATABASE LINK aramis CONNECT TO user1 IDENTIFIED BY jelszó1
USING 'aramis';
--
CREATE DATABASE LINK aramis2 CONNECT TO user1 IDENTIFIED BY jelszó1
=>
SELECT * FROM dolgozo d, osztaly@aramis o WHERE d.oazon = o.oazon;



Select TO_CHAR(current_timestamp,'YYYY-MM-DD hh24:mi:SS') AS TIMESTAMP,
TO_CHAR(current_timestamp+10/24/60/60,'YYYY-MM-DD hh24:mi:SS') AS TIMESTAMP_PLUS_10SEC
from dual;

=>

TIMESTAMP                     TIMESTAMP_PLUS_10SEC
2020-02-18 16:05:24       2020-02-18 16:05:34



----  DATE_FROM   DATE_TO generálás:

select aa.SNAPSHOT_TABLE_NAME, aa.START_TS, nvl(aa.kovetkezo_datum,  date'9999-12-31') as END_TS
, nvl(aa.kovetkezo_datum  -numToDSInterval( 0.000001, 'second' ),  date'9999-12-31') as END_TS2
, nvl(aa.kovetkezo_datum  - interval '0.000001' second,  date'9999-12-31') as END_TS3
from (
select EDW_IF_PROCESS_ID,    SNAPSHOT_TABLE_NAME,        START_TS,         
LAG( START_TS, 1) OVER (PARTITION BY SNAPSHOT_TABLE_NAME ORDER BY START_TS DESC) as kovetkezo_datum,
END_TS
from ebh_tf_edw_if.tf_edw_etl_snapshot where SNAPSHOT_TABLE_NAME ='E_TASK_EVENT'
) aa


-----

DECLARE
v_commit  NUMBER := 0;
v_new     varchar2(1000) := '';
BEGIN
                FOR rec IN
                (
                  SELECT 'Lajos' as "NEVEM",  aa.* FROM    all_tables aa where aa.OWNER like 'HN%'
                ) LOOP
                --
        BEGIN
          FOR rec2 IN
          (
            SELECT     * from all_tables  WHERE 1= 1 and table_name = rec.table_name
          ) LOOP
          --
           v_commit := v_commit + 1;
           DBMS_OUTPUT.PUT_LINE(rec.OWNER ||' - '|| rec2.table_name ||' -' || to_char(v_commit));
           v_new := '';
            --
          END LOOP;
          COMMIT;
        END;
                               --
                    --
                END LOOP;
                COMMIT;
END;






Hasznos SQL paracsok (oracle)

---- DUPLIKÁCIÓ törlése
delete from kl.kl_tbl t
  Where t.run_date = DATE '2017-05-12'
    And rowid in (select rowid
                    from (select row_number() over(partition by account_id order by account_id) as rowno,
                                 account_id,
                                 rowid
                            from kl.kl_tbl t
                           where t.run_date = DATE '2017-05-12')
                  where rowno > 1);

-----  PARTICIÓ létrehozása
CREATE TABLE KL_GENERAL
(MONTH NUMBER (4),
BATCH VARCHAR2(4),
JIB number
);

ALTER TABLE  KL_GENERAL ADD PARTITION P_20180530 VALUES LESS THAN ( DATE'2018-06-01' )           ;
ALTER TABLE KL_GENERAL ADD PARTITION  P_20180631 VALUES LESS THAN (TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

----- Jog osztás
grant select on KL_CTRL to DBLINK_KL_STAGE;  -- DBlink


----- Szinonima létrehozása
create or replace synonym T_KL_EXP_01 for TBL_COLL_EXP@klajos.hu;

                                                
------ PARTICIÓ lekérdezés
select tablespace_name --into --v_old_tablespace
from user_tab_partitions
where table_name='table_name'
and partition_position=(
select max(partition_position) from user_tab_partitions
where table_name='table_name')
;

-------  Könyvelés minta
  LEFT JOIN ACCOUNTING_STRUCTURE_ITEM I ON 1=1
      AND I.ACCOUNTING_STRUCTURE_ITEM_ID = X.ACCOUNTING_STRUCTURE_ITEM_ID
      AND I.START_OF_VALIDITY  <= TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM')-1
      AND I.END_OF_VALIDITY    >  TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM')-1       
WHERE
  EFFECTIVE_LOAD_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM'),-2)
  AND EFFECTIVE_LOAD_DATE <= NVL(
      (SELECT LAST_ACCOUNTING_DAY
         FROM EBH_DW_SRC.LAST_ACCOUNTING_DAY
        WHERE PERIOD = TO_CHAR(TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM')-1,'YYYYMM') -- Előzőt megelőző hónap
          AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
          AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
      ), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/)
  AND (
    ( /* Aktuális havi könyvelések */
      AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM'),-1) -- Előző hónap kezdete
      AND SAP_VALUE_DATE <= TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM')-1              -- Előző hónap vége
    ) OR ( /* Előző hónapról átcsúszó könyvelések */
      EFFECTIVE_LOAD_DATE > NVL(
        (SELECT LAST_ACCOUNTING_DAY
           FROM EBH_DW_SRC.LAST_ACCOUNTING_DAY
          WHERE PERIOD = TO_CHAR(TRUNC(ADD_MONTHS(&EFFECTIVE_LOAD_DATE,-1) +1,'MM')-1,'YYYYMM') -- Előzőt megelőző hónap
            AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE
            AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
        ), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/)
      AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM'),-2)     -- Előző hónapot megelőző hónap kezdete
      AND SAP_VALUE_DATE <= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM'),-1)-1   -- Előző hónapot megelőző hónap vége
    )
  )

-----  Tárhely lekérdezése
SELECT   tablespace_name,
   SUM(max_bytes)/1024/1024 total_Mbyte,
   SUM(bytes)/1024/1024 used_Mbyte
FROM   USER_TS_QUOTAS
WHERE  tablespace_name = 'USERS'
GROUP BY tablespace_name;

-------  TÁBLA mezők megjegyzésekkel
select col.owner, col.table_name, col.column_id, col.column_name,
       col.data_type, col.data_length, col.nullable, com.comments
from all_Tab_columns col, all_col_comments com
where col.owner=com.owner(+)
  and col.table_name=com.table_name(+)
  and col.column_name=com.column_name(+)
  and col.table_name in ('TABLAIM')
order by col.owner, col.table_name, col.column_id

------  Tömeges mező bővítés
DECLARE

   PROCEDURE add_column(
      pv_table_name IN VARCHAR2,
      pv_column_name IN VARCHAR2,
      pv_data_type IN VARCHAR2,
      pv_data_length IN VARCHAR2 DEFAULT NULL,
      pv_default_value IN VARCHAR2 DEFAULT NULL
   )
   IS
      column_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(column_exists, -1430);
      x VARCHAR2(512 CHAR);
   BEGIN  
      EXECUTE IMMEDIATE 'ALTER TABLE ' || pv_table_name || ' ADD ' || pv_column_name
         || ' ' || pv_data_type || CASE pv_data_type WHEN 'VARCHAR2' THEN '(' || pv_data_length || ' CHAR)' END
         || CASE WHEN pv_default_value IS NOT NULL THEN ' DEFAULT ''' || pv_default_value || '''' END;

   EXCEPTION
      WHEN column_exists THEN
         dbms_output.put_line('[INFO:] ' || pv_table_name || '.' || pv_column_name || ' már létezik.');
   END add_column;
   

BEGIN
   add_column('TBL001', 'YN_TO_PURGE', 'VARCHAR2(1 CHAR)', NULL, 'N');
   ...
   add_column('TBL100', 'YN_TO_PURGE', 'VARCHAR2(1 CHAR)', NULL, 'N');
END;

----- Saját session kilővésee
dbadm.kill_own_session

------ ODI sql kikérése
--session no: 7937
select s.sess_name, l.* from ODI_REPO.snp_sess_task_log l , ODI_REPO.snp_session s
where l.sess_no = s.sess_no
and sess_name like '%MAP_DMEXPO'
--and (task_status in ('R','E') or l.nb_row > 0)
and def_txt is not null
order by 2 desc,5 desc;
------
select
    EBH_META.get_odi_task_code(7937)
from
    dual;
------

--------- ODI konstans
select def_v from ODI_REPO.snp_var v
where var_name = 'C_CONST_VARTERM'
    
-------- ODI tábla rövidneve
select
  t.res_name,
  th.full_text
from
  odi_repo.snp_table t
  join odi_repo.snp_model m on t.i_mod = m.i_mod
  left join odi_repo.snp_txt_header th on th.i_txt = t.i_txt_desc
where
 table_name = 'KL_TABLA_HOSSZU_NEVU'
  and m.lagent_name = 'KL_TBL';     

MS oktatások

Kicsit fejlesztettem és teszteltem magamat. https://docs.microsoft.com/hu-hu/ Érdemes végig csinálni, vagy legalább kipróbálni. ...