****ŞU AN ÇALIŞAN SQL LER***************************
select A.USERNAME,b.sql_id,last_call_et/60 dakika, b.sql_text,A.INST_ID,a.sid,A.SERIAL#,a.osuser,A.PROGRAM from
gv$session a ,gv$sql b
where a.inst_id=b.inst_id AND a.sql_id=b.sql_id
and A.USERNAME=B.PARSING_SCHEMA_NAME
and a.status='ACTIVE' and A.TYPE<>'BACKGROUND'
AND WAIT_CLASS <>'Idle'
and state<>'WAITING'
order by last_call_et desc
****OBJEYİ TUTATN SESSİONLAR*********
SELECT a.object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM gv$access a,
gv$session b
WHERE a.sid = b.sid
AND a.inst_id=b.inst_id
AND a.owner = UPPER('SYS')
AND a.object=UPPER('TABLE_NAME')
ORDER BY a.object
****COMPILE INVALID OBJECTS************
SELECT OBJECT_NAME,
CASE
WHEN OBJECT_TYPE = 'PACKAGE BODY'
THEN
'ALTER PACKAGE '
|| OWNER
|| '.'
|| OBJECT_NAME
|| ' COMPILE BODY'
ELSE
'ALTER '
|| OBJECT_TYPE
|| ' '
|| OWNER
|| '.'
|| OBJECT_NAME
|| ' COMPILE'
END
SQL_TEXT
FROM DBA_OBJECTS
WHERE 1 = 1 AND STATUS = 'INVALID';
*****TABLESPACE DOLULUK ORANI******
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
****ANALİZ ALMA*******
begin
dbms_stats.gather_table_stats(ownname=>'SYS',
tabname=>'TABLO_ADI',PARTNAME=>'PART_20190614', DEGREE=> 4,
estimate_percent=>5,block_sample=>TRUE,degree=>8,cascade=>TRUE);
end;
Not cascade =true demek indexlerin de analizini al demek
***BLOCKİNG SESSİON***********
SELECT blocking_session,inst_id,count(*) FROM GV$session where blocking_session is not null
group by blocking_session,inst_id order by 3 desc
*****DYNAMIC SQL*************
Dinamik sql i tırnak içinde yazmak zahmetli bir iş onun yerine ayırac olarak başlangıçta q'{
Sonunda da }'kullanarak daha kolay yapbilirsin
For example, this "execute immediate" escapes the quotes with a q'{xxxxxxxx}'
sqlstring := q'{insert into x values( ' || i || ')}';
execute immediate sqlstring;
From
Örnek
select /*+ parallel (a 16) */ owner,name,type,line,text from tmp_source a where 1=1
and lower(text) like '%project_id%' and exists (select /*+ parallel (a 16) */ 1 from
tmp_source b where 1=1 and a.owner=b.owner
and a.name=b.name
and a.type=b.type
and lower(b.text) like '%edw_projects%')
İfadesini aşağıdaki şekilde yazabiliriz
v_sql:=q'{
select /*+ parallel (a 16) */ owner,name,type,line,text from tmp_source a where 1=1
and lower(text) like '%}'||p_column_name||q'{% and exists (select /*+ parallel (a 16) */ 1 from
tmp_source b where 1=1 and a.owner=b.owner
and a.name=b.name
and a.type=b.type
and lower(b.text) like '%}'||p_table_name||q'{%')
}'
PARALLEL INSERT ONEMLİ
Hem insert hem de select i parallel yapmak için
Alter session enable parallel dml;
Alter table emp parallel 16;
İnsert /*+ append */into emp select /*+ parallel(a,8) */ * from emp2;
Not:hint içinde verilen parallel lik tablonunkini ezer.
Eğer tablo ismi vermeden parallel(8) dersen o sql deki bütün tablolara 8 parallel gider.
İnsert içinde append hintinden başka parallel hinti verirsen direct load yapmaz dolayısıyla kullanma
ÇOK KULLANILAN BIR USER İLE BAŞKA BİR USER ALTINDA SCRIPT ÇALIŞTIRMAK İÇİN
declare
p_sql varchar2(1000);
p_SqlCode varchar2(1000);
p_SqlCodeExp varchar2(1000);
begin
p_sql:=q'{begin dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TABLO_ADI',estimate_percent=>5,block_sample=>TRUE,degree=>8,cascade=>TRUE,force=>TRUE);end;}';
SEMA_ADI.PRC_UTL_PARTITION(p_sql ,p_SqlCode ,p_SqlCodeExp);
dbms_output.put_line(' exception v_created ' ||sqlcode);
end;
***Bir kolona göre gruplayıp son tarihli kaydını alma*****************
SELECT EXTCUSTOMERNO, EXTCHANNEL, CREATEDDATE, DURUM, STATUS
FROM (SELECT EXTCUSTOMERNO, EXTCHANNEL, CREATEDDATE, DURUM, STATUS,
ROW_NUMBER ()
OVER (PARTITION BY EXTCUSTOMERNO
ORDER BY CREATEDDATE DESC)
sira
FROM SYS.TABLO_ADI
WHERE EXTCHANNEL = 'Email')
WHERE sira = 1
****MANUEL ADDM ÇALIŞTIRMA****************
DECLARE
l_task_name VARCHAR2(30) := '40597_40598_akin_inst1';
BEGIN
DBMS_ADDM.analyze_inst (
task_name => l_task_name,
begin_snapshot => 40596,
end_snapshot => 40597,
instance_number => 1);
END;
/
SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF
SELECT DBMS_ADDM.get_report('40597_40598_akin_inst1') FROM dual;
****MANUEL AWR RAPORU ÇALIŞTIRMA******************
set head off
set lines 1000
set echo off
SPOOL D:\AWR_20112017_00_09.htm
EXEC SYS.PCK_AWR_PARAMS.P_SET_DATES('21.11.2017 02:00','21.11.2017 06:00');
SELECT * FROM SYS.V_AWR_HTML_REPORT;
spool off
***** LIKE sql içinde parametre ile kullanımı*******************
Declare
v_table varchar2(100);
begin
v_table:='test_table';
for akrec in (
select line,text,name from dba_source where UPPer(text) like '%'||v_table||'%' and owner NOT IN('SYS','OUTLN','SYSTEM','ORACLE_OCM')) loop
dbms_output.put_line('text '||akrec.text);
end loop;
end;
/
****Exception alması durumunda değişiklik ********************
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error stack at top level:');
errcode := SQLCODE;
errmsg := SUBSTR (SQLERRM (errcode), 1, 400);
dbms_output.put_line('hata kodu '||errcode||'aciklama '||errmsg);
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace); /*+hatanın kacinci satirda oldugunu yazdırır*/
End;
/*alttaki kısmı sp nin en sonundaki exception içine koyuyoruz.ortalara koymuyoruz cunku sp için bir tane log kaydı açılıyor*/
errcode := SQLCODE;
errmsg := SUBSTR (SQLERRM (errcode), 1, 400);
etl.pkg_util_lib.prc_log_fail (errcode, errmsg, v_line_no);
raise;
*** Merge varsa update et yoksa insert et**************
MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:
MERGE /*+parallel(d 16) append*/INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Alternatively you can do this in PL/SQL:
BEGIN
INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE mytable
SET name = 'x'
WHERE id = 1;
END;
Örnek
create table mytable (id number,name varchar2(100))
insert into mytable values(1,'akin')
MERGE INTO mytable d
USING (SELECT 1 id, 'burak' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Mytable tablosunda id=1 olan kayıt için name=akin di,merge de id=1 ise burak yap dedik. 1 id li kayıtın name i burak oldu.
MERGE INTO mytable d
USING (SELECT 2 id, 'hakan' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Tabloda id=2 için bir kayıt olmadığında bunu insert eder.id=2 ve name =hakan olur
From
Benim örnek
yukarıdaki örnek plsql de 5 saatten fazla sürerken merge ile 11 dk da bitti.
***paket neden invalid olmuş***
select * from dba_objects where (owner,object_name) in(
select referenced_owner owner,referenced_name name from dba_dependencies where name='PROCEDURE_NAME')
order by last_ddl_time desc
******EXECUTE IMMEDIATE INTO*****
declare
vcount number;
v_query_str varchar2(300);
begin
v_query_str := 'select count(*) from dba_tables';
EXECUTE IMMEDIATE v_query_str
INTO vcount;
dbms_output.put_line(' cvont= ' ||vcount);
end;
CALL PROCEDURE WITH EXECUTE IMMEDIATE***************
SQL>
SQL> CREATE OR REPLACE PROCEDURE SP_ORNEK(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('SP_ORNEK called with ' || p1);
4 END CallProc1;
5 /
Procedure created.
SQL>
SQL> DECLARE
2 myResult VARCHAR2(50);
3 BEGIN
4 EXECUTE IMMEDIATE 'CALL CallProc1(''Hello from PL/SQL'')';
5 EXECUTE IMMEDIATE 'CALL CallFunc(''Hello from PL/SQL'') INTO :myResult'
6 USING OUT myResult;
7 END;
8 /
CallProc1 called with Hello from PL/SQL
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed
From
****GOTO EXAMPLE *********
Declare
i number;
begin
i:=1;
if i=1 then
goto label1;
end if;
dbms_output.put_line('elsee ' ); --burayı atlayıp label1 e gider
<
dbms_output.put_line(' exception v_created ' );
end;
/
****NUMERİC OLMAYAN KARAKTER İÇEREN DATAYI BULMAK İÇİN******************
SELECT * FROM SYS.TABLO_ADI WHERE REGEXP_LIKE(musterino, '[^0-9]+');
*****ÇALIŞMIŞ SQL LER****************
select * from DBA_HIST_SQLSTAT a,dba_hist_sqltext b
where A.SQL_ID=b.sql_id
and parsing_schema_name='SEMA_ADI'
and upper(b.sql_text) like '%@DB_ADI%'
****PLSQL'DE HANGİ SATIRDA HATA ALINDIGINI BULMAK İÇİN YÖNTEM**********
CREATE OR REPLACE PROCEDURE proc_backtrace
IS
BEGIN
RAISE VALUE_ERROR;
EXCEPTION
WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);
END;
/
exec proc_plsql_line;
Error raised in: PROC_PLSQL_LINE at line 8 - ORA-06502: PL/SQL: numeric or value error
exec proc_backtrace;
Error raised: ORA-06512: at "PROC_BACKTRACE", line 4 - ORA-06502: PL/SQL: numeric or value error
From
http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html
****UTL_FILE İLE DOSYA OKUNMASI *******************************************
declare
f UTL_FILE.FILE_TYPE;
ldir varchar2(100) := 'DATA_PUMP_DIR';
lfile varchar2(100) := 'writefile.sql';
s VARCHAR2(200);
v_row varchar2(2000);
v1 number;
v2 varchar2(50);
BEGIN
f := UTL_FILE.FOPEN(ldir,lfile,'R');
IF UTL_FILE.IS_OPEN(f) THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(f,v_row); /* satır satır dosyayı okur*/
v1:=substr(v_row,1,1);
v2:=substr(v_row,3,50);
INSERT INTO tmp
(empid, first_name)
VALUES
(v1,v2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
END;
From
******REF_CURSOR İLE FONKSİYONDAN DİZİ DÖNDÜRMEK**********
1) Dönüşü ref_cursor olan bir fonksion create edilir
create or replace function get_dept_emps(p_deptno in number) return sys_refcursor is
v_rc sys_refcursor;
begin
open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
return v_rc;
end;
2) ref_cursor dönüşünü select ile alabilmek için type ve bu type lardan oluşan table creae etmelisin
create or replace type emptype as object(empno number,
ename varchar2(10),
mgr number,
sal number);
create or replace type t_emptype as table of emptype;
3)
create or replace function populate_emps(deptno in number := null)
return t_emptype is
v_emptype t_emptype := t_emptype(); -- Declare a local table structure and initialize it
v_cnt number := 0;
v_rc sys_refcursor;
v_empno number;
v_ename varchar2(10);
v_mgr number;
v_sal number;
begin
v_rc := get_dept_emps(deptno);
loop
fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
exit when v_rc%NOTFOUND;
v_emptype.extend;
v_cnt := v_cnt + 1;
v_emptype(v_cnt) := emptype(v_empno, v_ename, v_mgr, v_sal);
end loop;
close v_rc;
return v_emptype;
end;
4) Bundan sonra artık tablodan select edebiliriz
select * from table(populate_emps(30));
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7499 ALLEN 7698 1600
7521 WARD 7698 1250
7654 MARTIN 7698 1250
7698 BLAKE 7839 2850
7844 TURNER 7698 1500
7900 JAMES 7698 950
ya da aşaıdaki gibi subquery de kullanabiliriz
select * from emp where empno in (select empno from table(populate_emps(30)));
OUTER JOIN
create table tab1 (a number)
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
create table tab2 (a number)
insert into tab1 values(2);
insert into tab1 values(3);
insert into tab1 values(4);
Eğer + işareti sağda ise left outer join, solda ise right outer join
+ işaretinin olmadığı tablonun tüm kayıtları getirilir,olmayanlar için + işareti olan tablodan null döner .
Aşagıdaki 2 sql aynı anlama gelir
select * from tab1,tab2
where tab1.a=tab2.a(+)
--and tab2.a is null --bu satırı ekleyince tab1 de olup tab2 de olmayanları döner yani 1
select * from tab1 left outer join tab2
on tab1.a=tab2.a
************DBA_TAB_MODIFICATIIONS***********
1)Eğer bir tablonun istatistiği alınırsa dba_tab_modifications tablosundan kaydı silinir
2)Yapılan bir değişikliğin dba_tab_modifications a yansıması için herhangi bir tablo için dbms_stats.gather_stats çalıştırmak yeterli olur
3)dba_tab_modifications yapılan değişiklikleri (istatistik alınana kadar toplayarak gider) Örneğin bir tabloda önce 60 sonra
40 kayıt update edip sonra istatistik alırsan 100 kayıt update edilmiş gözükür
4)Tabloyu truncate edersen truncated='YES' olur ama update kısmı değişmez hala 100 gözükür
5)Exchange partition delete ve insert olarak gözüküyor(1200 rows deleted,1200 rows inserted. Eğer deleted ve inserted rakamları eşitse bu exchange partition işlemidir )
6)Tabloyu drop edersen view dan silinir
--YAPILAN DEĞİŞİKLİĞİN TABLOYA YANSIMASI İÇİN HERHANGİ BİR TABLONUN İSTATİSTİĞİNİN ALINMASI GEREKİR
drop table tmp1
create table tmp1 as select * from dba_objects
select * from dba_tab_modifications where 1=1 and table_name='TMP1'
update tmp1 set object_id =object_id+1 where 1=1 and rownum<=20
Delete from tmp1 where rownum<=5
commit
begin
dbms_stats.gather_table_stats('SYS','TMP11');
end;
select * from dba_tab_modifications where 1=1 and table_name='TMP1'
--Yapılan değişiklikler dbms_stats çalışana kadar toplanarak güncellenir
update tmp1 set object_id =object_id+1 where 1=1 and rownum<=20
update tmp1 set object_id =object_id+1 where 1=1 and rownum<=30
commit
begin
dbms_stats.gather_table_stats('SYS','TMP11');
end;
-- Tablo truncate olursa Truncated='YES' olur ama insert,update,delete sayıları değişmez
truncate table tmp1
begin
dbms_stats.gather_table_stats('SYS','TMP11');
end;
select * from dba_tab_modifications where 1=1 and table_name='TMP1'
--Tablonun istatistiği alınırsa dba_tab_modifications 'tan silinir
begin
dbms_stats.gather_table_stats('SYS','TMP1');
end;
select * from dba_tab_modifications where 1=1 and table_name='TMP1'
select * from gv$session where 1=1 and client_info is not null
Yorum Gönder