Kategori arşivi: PL/SQL–SQL

PL/SQL – SQL örnekler, plsql kodlar..

PLSQL-TSQL ile Dosya Kontrolü, Boyut Öğrenmek

PL/SQL ile dosya kontrolü yapmak, boyutunu öğrenmek

PL/SQL ile bir oracle directorysinde dosya var mı kontrolü ve dosyanın boyutunu öğrenelim.
PLSQL ile dosyayı okuyacağımız dizinin oracle tarafında directory olarak tanımlanmış olması ve ilgili kullanıcının bu dizine read hakkı olması gerekiyor.

Önce directory’leri create edelim ve HR userına yetki verelim.(SYS)

SQL> create or replace directory hr_dir as '/home/oracle';
Directory created.
SQL> grant read, write on directory hr_dir to hr;
Grant succeeded.

Fonksiyonumuzu create edelim.(HR)

create or replace function FileExists(
p_DirName in varchar2,
p_FileName in varchar2
) return number
is
l_file_loc bfile;
begin
l_file_loc := bfilename(upper(p_DirName), p_FileName);
return dbms_lob.fileexists(l_file_loc);
end FileExists;

Fonksiyonumuzu directory ismi ve dosya ismi vererek test edelim.(HR)

SQL> select HR.FileExists1('hr_dir','deneme.txt') dosya_varmi from dual;
DOSYA_VARMI
-----------
1
SQL> select HR.FileExists1('hr_dir','deneme_test.txt') dosya_varmi from dual;
DOSYA_VARMI
-----------
0

Dosyamızın boyutunu plsql kodumuzda öğrenmek istersek;

 DECLARE
 v_fexists      BOOLEAN;
 v_file_length  NUMBER;
 v_block_size   BINARY_INTEGER;
 BEGIN
  UTL_FILE.FGETATTR('hr_dir', 'deneme_test.txt', v_fexists, v_file_length, v_block_size);
  DBMS_OUTPUT.PUT_LINE (v_file_length);
END;

T-SQL ile dosya kontrolü yapmak, boyutunu öğrenmek

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Fonksiyonumuzu test edelim.

select dbo.fc_FileExists('C:\deneme_test.txt');
-----
1
select dbo.fc_FileExists('D:\notlar\deneme_test.txt');
-----
0

PLSQL Metni Tersten Yazdırmak

PL/SQL Metni Tersten Yazdırmak

Verilen bir metni tam tersten yazdırmak için oracle’ın kendi reverse fonksiyonunu kullanabiliriz,
Eğer daha farklı işlemler yaptırmak için kendi fonksiyonunuza ihtiyaç duyarsanız fonksiyon ve prosedür yazalım.Bu fonksiyon-prosedür ne işimize yarayacak demeyin, bir yerlerde ihtiyaç oluyor.

Oracle Reverse fonksiyonu nasıl kullanılır?

select reverse('122323') from dual;
323221

select reverse('www.OmerAcar.com.tr') from dual;
rt.moc.racAremO.www

select reverse('ReverseTextTest') from dual;
tseTtxeTesreveR

Metni ters çevirme işlemini bir de fonksiyon ve prosedüre ile yapalım.

– Metni ters çevirmek için pl/sql fonksiyon

CREATE OR REPLACE FUNCTION TerstenYazFunc(metin_ IN varchar2) 
  RETURN VARCHAR2 IS
  sonuc_metin_ varchar2(50);
BEGIN   
        FOR i in reverse 1..length(metin_) LOOP
                 sonuc_metin_ := sonuc_metin_||''||substr(metin_, i, 1);
        END LOOP;
        return(sonuc_metin_);
END;

--Fonksiyonu test edelim.
select TerstenYazFunc('Deneme12345') from dual;
54321emeneD

– Metni ters çevirmek için pl/sql procedure.

CREATE OR REPLACE PROCEDURE TerstenYazProc(metin_ IN varchar2) IS
        sonuc_metin_ varchar2(50);
BEGIN
        FOR i in reverse 1..length(metin_) LOOP
                 sonuc_metin_ := sonuc_metin_||''||substr(metin_, i, 1);
        END LOOP;
        dbms_output.put_line(sonuc_metin_);
END;
/

--Prosedürü test edelim.
set serveroutput on;

BEGIN
TerstenYazProc('Deneme12345');
END;

anonymous block completed
54321emeneD

Databasede Tablo Kolonu Arama-Bulma

Databasede Tablo Kolonu Arama-Bulma

Bir kolon hangi tablolarda bulunuyor, tipi ve boyutu nedir, nasıl arayıp buluruz beraber bakalım.
Örneğimizde “musteri_no” kolonu hem oracle hem mssql üzerinde hangi tablolarda geçiyor select çekerek
listeleyelim. Tabi biz burada müşteri no bilgisinin sadece “musteri_no” adlı kolonda geçtiğini varsayıyoruz, yazılımcı ya da database tasarımsıcısı arkadaş hem “musteri_no” hem “customer_no” gibi kolonlar kullanmış olabilir, bu ihtimalleri de göz önünde bulundurmak ve alternatif isimleri de kontrol etmek gerekir. Veritabanı yapısını öğrenmek, tablolar hakkında bilgi sahibi olmak, tablo ilişkilerini çıkarmak amaçlı faydalı olacaktır.

Oracle Tablo Kolonu Arama-Bulma

* Oracle’da aranacak kolon adını küçük harfle yazınız.(musteri_no)

SELECT 'COLUMN' type,owner,table_name,column_name,column_id,data_type
FROM sys.dba_tab_cols
where column_name like '%musteri_no%';

MSSQL Tablo Kolonu Arama-Bulma

* Mssql belirtilen database’de aranan kolonu bulmak

Use DBADI 
go 
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%musteri_no%'

MSSQL Tüm Databaselerde Kolon Arama-Bulma

* Tüm MSSQL instance databaselerini aramak için EXEC sp_MSforeachdb kullanıyoruz.

-- MSSQL instancedaki tüm dbler üzerinde kolon aramak
EXEC sp_MSforeachdb 'Use ?;
SELECT DB_ID() DATABASE_ID,DB_NAME(db_id()) DATABASE_NAME,TABLE_NAME,COLUMN_NAME,DATA_TYPE
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ''%musteri_no%'''

Oracle ve Mssql Database Size Sorgulamak

Oracle ve Mssql Database Size Sorgulamak

Bu makalede Oracle ve MSSQL de database size nasıl hesaplarız, beraber bakalım..

Oracle Database Size Sorgulamak

Oracle veritabanı boyutunu aşağıdaki sql ile bulabiliriz, bunun dışında Enterprise managar ya da Grid control ile görüntülemek de mümkün.Tablespace bazında toplam alan, kullanılan alan ve kullanım yüzdesini hesaplar.

Oracle Tablespace Size Query.

SELECT DF.TABLESPACE_NAME TABLESPACE,
DF.TOTALSPACE TOTAL_MB,
TOTALUSEDSPACE USED_MB,
(DF.TOTALSPACE-TU.TOTALUSEDSPACE) FREE_MB,
ROUND(100 * ((DF.TOTALSPACE-TU.TOTALUSEDSPACE)/DF.TOTALSPACE),2) PCT_FREE
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024*1024)) 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
UNION ALL
SELECT TABLESPACE_NAME,
(BYTES_USED+BYTES_FREE)/1024/1024 TOTAL_MB,
BYTES_USED /(1024*1024) USED_SIZE,
BYTES_FREE /(1024*1024) FREE_SIZE,
ROUND(BYTES_FREE/(BYTES_USED+BYTES_FREE),2) PCT_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 5 ASC;

Temp datafile’da dahil tüm datafile’ların toplam boyutu, kullanılan datafile alanı, allocate edilmiş ama kullanılmayan boş alanın boyutunu da aşağıdaki sql ile bulabiliriz.

Oracle Datafile Size Query

SELECT TABLESPACE_NAME,
FILE_ID,
(INCREMENT_BY * 8) / 1024 EXTEND_SIZE_MB,
FILE_NAME,
TOTAL_SIZE_MB,
TOTAL_SIZE_MB - FREE_SPACE_MB USED_SPACE_MB,
FREE_SPACE_MB,
ROUND(FREE_SPACE_MB / TOTAL_SIZE_MB * 100) FREE_PCT,
STATUS,
ONLINE_STATUS
FROM (SELECT B.TABLESPACE_NAME,
B.FILE_ID,
B.FILE_NAME,
(B.BYTES / 1024 / 1024) TOTAL_SIZE_MB,
ROUND((SELECT SUM(A.BYTES) / 1024 / 1024
FROM DBA_FREE_SPACE A
WHERE A.FILE_ID = B.FILE_ID)) FREE_SPACE_MB,
B.STATUS,
B.ONLINE_STATUS,
B.INCREMENT_BY
FROM DBA_DATA_FILES B);

Oracle Redo log gruplarını ve boyutlarını görmek istersek aşağıdaki sql işimizi görecektir.

Oracle Redo Log Size Query.

select group#,thread#,(bytes/1024/1024)Size_mb,status,archived from v$log ORDER BY 1,2;

MSSQL Database Size Sorgulamak

Aşağıdaki sql ile Mssql database’lerin boyutunu öğrenebiliriz.(*8 Blok size)

with db_size
as
( select database_id, type, size * 8/1024 size
from sys.master_files)
select
name,
(select sum(size) from db_size where type = 0 and db_size.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from db_size where type = 1 and db_size.database_id = db.database_id) LogFileSizeMB
from sys.databases db

TSql Plsql iki tarih arasındaki zaman farkı

PL/SQL İki Tarih Arasındaki Farkı Bulmak

Plsql’de iki tarihin farkını aldığımızda default olarak gün farkını verir, eğer saat, dakika, saniye cinsinden elde etmek istersek bir takım hesaplamalar yapmamız gerekli.(saat-dakika-saniye hesaplamaları)
Bu işlemler nerelerde kullanılır; bu personel ne kadar süredir çalışıyor, bu sipariş ne kadar süredir bekliyor vs gibi soruların cevaplarını bulabiliriz.

Şimdi bir örnekle gösterelim.

10 gün öncesi ile şu an arasındaki farkı bulalım, SYSDATE fonksiyonu plsql’de şimdiki tarihi verir.

select
ROUND(months_between(sysdate,(sysdate-10))/12,1) yil_farki,
ROUND(months_between(sysdate,(sysdate-10)),1) ay_farki,
(sysdate - (sysdate-10)) gun_farki,
(sysdate - (sysdate-10)) * 24 saat_farki,
(sysdate - (sysdate-10)) * 24 * 60 dakika_farki,
(sysdate - (sysdate-10)) * 24 * 60 * 60 saniye_farki,
(sysdate - (sysdate-10)) * 24 * 60 * 60 * 1000 milisaniye_farki
from dual;

Sorgumuzun çıktısı aşağıdaki gibidir..

yil_farki ay_farki gun_farki saat_farki dakika_farki saniye_farki milisaniye_farki
0 0,3 10 240 14400 864000 864000000

MSSQL (T-SQL) iki tarih arasındaki farkı bulmak

Mssqlde iki tarih arasındaki fark nasıl alınır, bunu da bir örnekle açıklayalım, t-sql de bunun için hazır bir fonksiyon mevcut, DATEDIFF fonksiyonunu kullanacağız, örnek için ele alacağımız iki tarih içinde GETDATE() fonskiyonunu kullanacağız. GETDATE() fonksiyonu T-SQL de şu anki zamanı döndürür.
DATEDIFF genel kullanımı DATEDIFF ( datepart , startdate , enddate )  şeklindedir.

declare @ilktarih datetime =getdate()-10  --10 gün önceki zaman
declare @sontarih datetime =getdate()     --Şimdiki zaman

Select DATEDIFF(YEAR,@ilktarih,@sontarih) yil_farki,
DATEDIFF(MONTH,@ilktarih,@sontarih) ay_farki,
DATEDIFF(DAY,@ilktarih,@sontarih) gun_farki,
DATEDIFF(HOUR,@ilktarih,@sontarih) saat_farki,
DATEDIFF(MINUTE,@ilktarih,@sontarih) dakika_farki,
DATEDIFF(SECOND,@ilktarih,@sontarih) saniye_farki,
DATEDIFF(MILLISECOND,@ilktarih,@sontarih) milisaniye_farki

Sorgumuzun çıktısı aşağıdaki gibidir..

yil_farki ay_farki gun_farki saat_farki dakika_farki saniye_farki milisaniye_farki
0 0 10 240 14400 864000 864000000

Oracle Session Kill Etmek

Bu yazımızda oracle sessionu nasıl tespit edilir ve nasıl kill edilir inceleyelim, Oracle’da sessin kill etmek için iki bilgiye ihtiyacımız var, bunlar; SID ve SERIAL#. Bunları gv$session viewinden aşağıdaki gibi yakalayabiliriz.

ALTER SYSTEM KILL SESSION

Genel syntax:

ALTER SYSTEM KILL SESSION 'sid,serial#';

Sessionları belirlemek için SQL sorgusu.


SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.terminal,
s.logon_time,
s.module,
'Alter System Kill Session '||''''||s.sid||','||s.serial#||''''||';' kill_sql
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr
AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

SQL> Alter system kill session '23,45164';

ALTER SYSTEM KILL SESSION IMMEDIATE

Alter system kill session dediğimizde oracle kill edilecek sessionu işaretler ve transactionun sonlanmasını bekler.Eğer işlemlerin bitmesini beklemek istemiyorsak IMMEDIATE vererek kill edebiliriz, IMMEDIATE ile sessionun transactionları rollback edilir ve session sonlandırılır.


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

SQL> ALTER SYSTEM KILL SESSION '24,56898' IMMEDIATE;

Oracle sessionları sqlplus’tan komutlarla, toad, plsql developer,sqldeveloper gibi toollardan arayüz ile
kolayca kill edilebilir, peki operating system tarafında nasıl kill edebiliriz ona da bakalım.

Windows sistemlerde oracle session kill etmek

C:\> orakill ORACLE_SID spid

C:\> orakill TESTDB 324

Unix-Linux sistemlerde oracle session kill etmek

% kill -9 spid
ps -ef | grep ora
Komutu ile unixte çalışan processlere bakabiliriz..

*Komutları çalıştırmadan önce doğru sesionu kill etiğinizden emin olun.
*Yanlış sessionu kill etmeniz oracle’ı ya da işlemtim sisteminin yığılmasına neden olabilir.

Oracle Topluca Tablo Yedeklemek

Oracle Topluca Tablo Yedeklemek

Aşağıdaki script ile oracle’a bağlı olan kullanıcının _TMP uzantısı ile biten tüm tablolarını toplu olarak yedekliyoruz. Eğer istenirse yedeklendikten sonra asıl tablolar drop da edilebilir, benim ihtiyacım olmuştu o yüzden drop da yazdım,ama aşağıda drop komutunu commentledim. Script create table as komutuyla tablo adının sonuna _BKP uzantısı ekliyor ve yeni tablo oluşturuyor.Eğer yeni isim 30 karakterden çok gelirse(bu durumda hata alır ORA-00972) yeni tablo adını 30 karaktere kısaltacak.

Scripti kendinize göre uyarlayabilirsiniz, lütfen scripti önce test ortamlarda deneyerek sonuçlarını görünüz..

DECLARE 
 V_TABLE VARCHAR2(50); 
 V_TABLE_TRIM VARCHAR2(50);
BEGIN
  FOR i IN (select * from user_tables where table_name like '%\_TMP' Escape '\' ORDER BY 1 DESC) LOOP 
    V_TABLE := i.table_name;
    V_TABLE_TRIM := i.table_name;
    DBMS_OUTPUT.PUT_LINE('--------------- Tablo Yedekleniyor.. '||V_TABLE||' ---------------');
        IF LENGTH(V_TABLE) > 26 THEN
           V_TABLE_TRIM := substr(V_TABLE,1,26);
        END IF;
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE ' ||V_TABLE_TRIM||'_BKP as select * from ' ||V_TABLE);  

    EXECUTE IMMEDIATE 'CREATE TABLE ' ||V_TABLE_TRIM||'_BKP as select * from ' ||V_TABLE;

    --DBMS_OUTPUT.PUT_LINE('--------------- Tablo Drop Ediliyor.. '||V_TABLE||' ---------------');
    --DBMS_OUTPUT.PUT_LINE('DROP TABLE ' ||V_TABLE);
    --EXECUTE IMMEDIATE 'DROP TABLE ' ||V_TABLE;
    --DBMS_OUTPUT.PUT_LINE(' ');
    --DBMS_OUTPUT.PUT_LINE(' ');
  END LOOP;
END;