Hasznos adatbázis objektumok oracle adatbázisban

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

select * from show_my_sessions;

---- > Privilégium (jogosultság):

a felhasználó számára lehetővé teszi, hogy az adatbázis sémában bizonyos műveleteket

hajthasson végre.

- A privilégium kéttípusú lehet:

• rendszer-privilégium;

• objektum-privilégium.

--> Priv adományozása:

GRANT SELECT, UPDATE ON sajat_tabla TO teszt_user; -- WITH GRANT OPTION -megadásával tovább adható a jogosultság

GRANT SELECT, UPDATE ON sajat_tabla TO PUBLIC; -- mindenkinek kiajánlott priv

GRANT EXECUTE ON sajat_package TO PUBLIC;

--> priv visszavonása:

REVOKE SELECT, UPDATE ON object FROM teszt_user;

REVOKE SELECT, UPDATE ON sajat_tabla FROM PUBLIC;

REVOKE SELECT, UPDATE ON sajat_tabla FROM teszt_user CASCADE CONSTRAINTS; // a privilégium megvonása esetén törli az összes hivatkozási integritási megszorítást, amelyet a felhasználó hozott létre (ez veszélyes lehet!!!)

select 'GRANT SELECT ON '||table_name||' TO teszt_user;'

from all_tables where owner = 'KLAJOS'

order by table_name

--> user priv lista:

select * from SESSION_PRIVS -- Összes felhasználói munkamenetben (elérhető) privilégium listája // lists the privileges that are currently available to the user.

select * from user_sys_privs -- aktuális user privilégiumai // lists system privileges granted to the current user.

-- összes priv lehetőség:

SELECT UNIQUE privilege FROM dba_sys_privs;

--> milyen priv hiányzik:

SELECT *

FROM sys.dba_sys_privs

-- WHERE EXISTS --- létezik

WHERE not EXISTS -- nem létezik

(SELECT 'x'

FROM sys.dba_users

WHERE username = 'KLAJOS')

and rownum < 50

ORDER BY 1,2;

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

To see which table privileges are granted by you to other users.

SELECT * FROM USER_TAB_PRIVS_MADE

To see which table privileges are granted to you by other users

SELECT * FROM USER_TAB_PRIVS_RECD;

To see which column level privileges are granted by you to other users.

SELECT * FROM USER_COL_PRIVS_MADE

To see which column level privileges are granted to you by other users

SELECT * FROM USER_COL_PRIVS_RECD;

To see which privileges are granted to roles

SELECT * FROM USER_ROLE_PRIVS;

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

-->

select * from ALL_TAB_PRIVS; -- összes tábla jog lista

select * from ALL_COL_PRIVS; --összes mező szintű jog listája

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

----> Szerepkörök (ROLE)

Szerepkör – a privilégiumok együttese, és lehetőség a felhasználók csoportosítására.

A felhasználókat egy csoportba lehet beosztani,

és a csoporton belül mindegyik felhasználó azonos jogosultsággal rendelkezik.

--> szerepkör létrehozása

CREATE ROLE kozos;

--> a szerepkörhöz privilégiumokat kapcsolunk

GRANT INSERT ON sajat_tabla TO kozos;

--> a user_1 és user_2 felhasználók megkapják a kozos szerepkört

GRANT kozos TO user_1;

GRANT kozos TO user_2;

---

----> nézetek

select * from DBA_INDEXES

select * from ALL_INDEXES

select * from USER_INDEXES

---->Szekvenciák (sorozatok)

Ha egy Oracle alkalmazásban szükség van egy numerikus adatsorozatra (például, elsődleges kulcsra), melynek az értékei egyediek legyenek, akkor szekvenciákat lehet alkalmazni.

-- Szekvenciák létrehozása:

CREATE SEQUENCE sorozat START WITH 1

-- A szekvencia aktuális értéke:

sorozat.CURRVAL

-- A szekvencia következő értékét (elemét)

sorozat. NEXTVAL

----> felhasználó létrehozása alap beállítások

create user ujuser identified by ujjelszo;

grant all privileges to ujuser;

grant connect to ujuser;

--> user törlés

drop user ujuser cascade;

--> session adatok lekérdezése

select username, osuser, terminal from v$session;

SELECT name, value FROM v$parameter WHERE name = 'sessions'

SELECT COUNT(*) FROM v$session

----> táblatér foglalás lekérdezése

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

---

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

-- Első lépésben érdemes lekérdezni a block size-ot, hátha nem a default értéken van:

select value from v$parameter where name = 'db_block_size';

-- Következőként a block size méretének definiálása:

define blksize=8192;

-- Az alábbi script kimenetében kiírja melyik adatfájlt mennyire lehet összezsugorítani, illetve a lefuttatandó parancsot is:

select

'alter database datafile '''||file_name||''' resize '||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) ||'m;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b

where

a.file_id = b.file_id(+) and

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

----futtatandó parancs

select

'alter database datafile '''||file_name||''' resize '||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) ||'m;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b

where

a.file_id = b.file_id(+) and

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

----

-- Aktuálisan használt redo log és redo log listájának lekérdezése:

select group#,thread#,sequence#,bytes/1024/1024 size_mb,members,status from v$log;

-- Redo log fájl hozzáadás

alter database add logfile group 4 ('D:\oradata\mydb\redo04.log') size 600M reuse;

alter database add logfile group 5 ('D:\oradata\mydb\redo05.log') size 600M reuse;

---- Egy-egy nyomozásban segíthet a tábla méretek listázása.

select

owner as "Schema"

, segment_name as "Object Name"

, segment_type as "Object Type"

, round(bytes/1024/1024,2) as "Object Size (Mb)"

, tablespace_name as "Tablespace"

from dba_segments

order by owner;

-- Hasonló lista, de már egy konkrét user-rel kapcsolatban.

select

owner as "Schema"

, segment_name as "Object Name"

, segment_type as "Object Type"

, round(bytes/1024/1024,2) as "Object Size (Mb)"

, tablespace_name as "Tablespace"

from dba_segments

where owner='myuser'

order by owner;

----> Mentés és visszatöltés

Kicsit hosszú időt vehet igénybe nagyobb DB méret esetén, akár órákat is,

de elég jónak mondható módszer (datapump).

-- export

expdb schemas=mydb dumpfile=mydb-dump-201509.dmp

-- import

impdp schemas=mydb dumpfile=mydb-dump-201509.dmp

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

Statspack

-- Tablespace létrehozás:

create tablespace statspack datafile 'D:\oradata\mydb\statspack01.dbf' size 100m;

alter database datafile 'D:\oradata\mydb\statspack01.dbf' autoextend on;

alter tablespace statspack add datafile 'D:\oradata\mydb\statspack02.dbf' size 100M;

alter database datafile 'D:\oradata\mydb\statspack02.dbf' autoextend on;

Így jobb, külön tárolódnak, akár másik meghajtón/partición ezek a statspack adatfájlok.

-- DB létrehozás (spcreate) és automatikus report készítés (spauto) beállítása.

A tablespace neve (default_tablespace) az amit az előbb létre lett hozva.

A perfstat_password pedig a “perfstat” user jelszava.

-- create statspack stuff

define default_tablespace='statspack'

define temporary_tablespace='TEMP'

define perfstat_password='myStatPass'

@c:\oracle\app\product\12.1.0\dbhome_1\RDBMS\ADMIN\spcreate

-- create JOB

define default_tablespace='statspack'

define temporary_tablespace='TEMP'

define perfstat_password='myStatPass'

@c:\oracle\app\product\12.1.0\dbhome_1\RDBMS\ADMIN\spauto

-- Használathoz elsőként érdemes lekérdezni az elkészült reportokat

SELECT SNAP_ID, TO_CHAR(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') as snap_time, SNAP_LEVEL FROM STATS$SNAPSHOT order by snap_id;

-- Majd a visszaadott kimenet alapján ki lehet választani azt az “érdekes” idő intervallumot,

aminek a reportjára szükség van.

ALTER SESSION SET CURRENT_SCHEMA = perfstat

define begin_snap=1

define end_snap=24

define report_name=report001

@C:\oracle\app\product\12.1.0\dbhome_1\RDBMS\ADMIN\spreport

----

DECLARE ufile UTL_FILE.FILE_TYPE;

wPath varchar2(256);

wFName varchar2(256);

BEGIN

wPath:='/usr/var/files';

wFName:='temp';

ufile:=UTL_FILE.FOPEN('/usr/var/files','temp','a');

UTL_FILE.PUT (ufile, 'Helló');

UTL_FILE.NEW_LINE (ufile,1);

UTL_FILE.FCLOSE (ufile);

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN

DBMS_OUTPUT.PUT_LINE('UTL_INVALID_PATH');

WHEN UTL_FILE.INVALID_MODE THEN

DBMS_OUTPUT.PUT_LINE('UTL_INVALID_MODE');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN

DBMS_OUTPUT.PUT_LINE('UTL_INVALID_FILEHANDLE');

WHEN UTL_FILE.INVALID_OPERATION THEN

DBMS_OUTPUT.PUT_LINE('UTL_INVALID_OPERATION');

WHEN UTL_FILE.READ_ERROR THEN

DBMS_OUTPUT.PUT_LINE('UTL_READ_ERROR');

WHEN UTL_FILE.WRITE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('UTL_WRITE_ERROR');

WHEN UTL_FILE.INTERNAL_ERROR THEN

DBMS_OUTPUT.PUT_LINE('UTL_INTERNAL_ERROR');

END;

-----

SELECT * FROM all_views

SELECT * FROM all_tab_columns

SELECT * FROM all_db_links

SELECT * FROM all_objects

SELECT * FROM dba_tablespaces

SELECT * FROM v$tablespace

SELECT * FROM user_tablespaces

SELECT * FROM all_constraints WHERE TABLE_NAME = 'MCOMMSISDN'

----

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees

FROM emp

GROUP BY deptno;

---

SELECT deptno, wm_concat(ename) AS employees

FROM emp

GROUP BY deptno;

---

SELECT deptno,

tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees

FROM emp

GROUP BY deptno;

----------

V$SESSION and V$SQL

SELECT sql_text, executionsFROM ( SELECT sql_text, executions, rank() over ( order by executions DESC) exec_rank FROM v$sql )WHERE exec_rank >10

SELECT disk_reads, sql_textFROM ( SELECT sql_text, disk_reads, dense_rank() over ( order by disk_reads DESC) disk_reads_rank FROM v$sql )WHERE disk_reads <=10;

select hash_value,executions, buffer_gets,disk_reads, parse_callsfrom v$SQLAREAwhere buffer_gets > 10000 or disk_reads > 10000order by buffer_gets + 100 * disk_reads desc

select address,hash_value,piece,sql_text from V$sqltext

V$SQL, V$SQLTEXT, V$SQLAREA, V$OPEN_CURSOR, and V$SESSION.

-----

SELECT END_DATE, INTERNAL_KEY, STATEMENT_SEQ, SUM(DB) AS DB

, LISTAGG(TRANS_TYPE, ',') WITHIN GROUP (ORDER BY TRANS_TYPE) list

FROM (SELECT END_DATE, INTERNAL_KEY, STATEMENT_SEQ, TRANS_TYPE, COUNT(1) AS DB

from EBH_KIV_ARCH.KIV_STATEMENT_TR where END_DATE = date'2022-02-21'

group by END_DATE, INTERNAL_KEY, STATEMENT_SEQ, TRANS_TYPE

) dt

GROUP BY END_DATE, INTERNAL_KEY, STATEMENT_SEQ

HAVING SUM(DB) > 200

-------- LISTTAG