Özellikle datapump ile veritabanını aktardıktan sonra veritabanınızdaki bir çok objenin invalid duruma düştüğünü görebilirsiniz. Invalid durumda olan objeleri Enterprise Manager ile yeniden derleyip valid duruma getirmek isterseniz her obje türü için ayrı ayrı objeleri seçip derleme işlemini yapmanız gerekecektir.
Bu işlemi Enterprise Manager yerine basit bir script ile otomatikleştirmeniz mümkün.
SPOOL recompileall.txt
SELECT decode(object_type,
‘PACKAGE BODY’,
‘ALTER PACKAGE ‘ || owner || ‘.’ || object_name ||
‘ COMPILE BODY;’,
‘ALTER ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name ||
‘ COMPILE;’) As SqlKomut
FROM dba_objects
WHERE status = ‘INVALID’
AND object_type IN ( ‘PACKAGE BODY’, ‘PACKAGE’, ‘FUNCTION’, ‘PROCEDURE’, ‘TRIGGER’, ‘VIEW’)
ORDER BY object_type, object_name;
SPOOL OFF
@recompileall.txt

Bir Oracle kullanıcısı kendi schemasındaki objeleri her zaman silme hakkına sahiptir. Örneğin şemasındaki bir TABLE’ı, DROP TABLE yetkisi olmasa bile silebilir. Özellikle tek schema üzerinde geliştirilmiş uygulamalar ile çalışırken, yaratılmış olan objlerin silinebilmesini engellemek gerekebilir. Böyle bir durumda yapılması gereken bir Database Trigger’ı yaratmaktır.
HR kullanıcısının kendi schemasındaki tabloları silmesini engellemek istediğimizi varsayalım. Bunu engellemek için şöyle bir trigger yazabiliriz:
CREATE OR REPLACE TRIGGER trigger_drop_engeli BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = ‘TABLE’ THEN
IF ora_dict_obj_owner = ‘HR’ AND ora_login_user = ‘HR’ THEN
raise_application_error (-20000, ’TABLOYU DROP EDEMEZSINIZ’);
END IF;
END IF;
END;
HR kullanıcısı kendi schemasında bir tablo silmeye kalktığında ORA-20000 hatası (ve beraberinde Oracle’ın verdiği bir iki internal hata) alacak ve işlemi tamamlayamayacaktır. Örnekte de görüldüğü üzere database trigger’ında kullanabileceğimiz bazı pseudo değişkenler var. Konuyla ilgili olanlar şunlar:
ORA_DICT_OBJ_NAME: Üzerinde işlem yapılan objenin ismi.
ORA_DICT_OBJ_OWNER: Üzerinde işlem yapılan objenin sahibi (schema adı).
ORA_LOGIN_USER: İşlemi yapmak isteyen kullanıcı.
ORA_DICT_OBJ_TYPE: İşlem yapılacak objenin tipi (TABLE, INDEX vs).
ORA_SYSEVENT: Yapılmak istenilen işlem (eğer yazılan trigger birden fazla işlemde çalışacak ise). Örneğimizde bu hep ‘DROP’ olacaktır

Bir objeye bağlı (dependent) objeleri bulmak için data dictionary viewlerinden dba_dependencies view’ini sorgulayabiliriz. Oracle’ın örnek schemalarından HR schemasındaki employees tablosu için bunu yapalım:
SQL> SELECT *FROM dba_dependencies
WHERE referenced_owner = ‘HR’ AND referenced_name = ‘EMPLOYEES’;
Bu sorgunun çıktısı schema üzerinde değişiklik yapılmadıysa şu şekilde olacaktır:

OWNER,NAME
HR,EMP_DETAILS_VIEW
HR,SECURE_EMPLOYEES
HR,UPDATE_JOB_HISTORY
OE,EMPLOYEES

Bu basit sorgu ile kolaylıkla hr.employees tablosuna bağlı olan objeleri listeledik. DBA_DEPENDENCIES viewi olmasına rağmen, Oracle bağlı objelerin bulunması için bize yardımcı bir araç olarak DEPTREE_FILL prosedürünü sunmaktadır. Bu prosedür ve prosedürün çalışmacı için gerekli tablolar ve viewler UTLDTREE.SQL adlı scripte yer alır. Scripti bir kez çalıştırdıktan sonra DEPTREE_FILL prosedürü kullanmaya başlayabiliriz.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba @utldtree.sql

DEPTREE_FILL prosedürü 3 adet parametre alır. Birinci parametre objenin tipi, ikincisi objenin sahibi/scheması, üçüncüsü objenin ismidir. Bu bilgileri verdiğimizde DEPTREE_FILL, objeye bağlı olan diğer objeleri bulur. Prosedür çalıştıktan sonra deptree ve ideptree viewlerini kullanarak sonucu görüntüleyebiliriz:

SQL> EXEC deptree_fill( ‘table’ ,’hr’,'employees’);
SQL> SELECT * FROM deptree;
NESTED_LEVEL,TYPE,SCHEMA,NAME,SEQ#
———————————————————
0,TABLE,HR,EMPLOYEES,0
1,VIEW,HR,EMP_DETAILS_VIEW,22
1,TRIGGER,HR,SECURE_EMPLOYEES,24
1,TRIGGER,HR,UPDATE_JOB_HISTORY,25
1,SYNONYM,OE,EMPLOYEES,26
İlk sorgumuzda yazdığımıza benzer sadece biraz daha detay içeren bir sonuç elde ettik. Bu durumda kafamıza şu soru takılabilir: DBA_DEPENDECIES ile bağlı objeleri bulabildiğime göre neden DEPTREE_FILL prosedürünü kullanayım? Deptree_fill’i kullanmak için veritabanında bir script çalıştırılarak ek bir prosedür oluşturuluyor, ayrıca objeleri listelemek için tek sorgu yerine önce prosedür çalıştırılıp sonra view sorgulamız gerekiyor.
Bunun cevabı nested_level‘de gizli. Deptree_fill ile, bağlı objelerin, asıl objeye kaçıncı seviyeden bağlı olduklarını gösteren nested_level değerini bulabiliyoruz. Yani deptree_fill, bir birine direk olarak bağlı olmayan objeleri de bize gösterebiliyor. Örnekle açıklayalım:
CREATE VIEW hr.bolum_maas
AS
SELECT department_name, MAX(salary) en_yuksek
FROM hr.emp_details_view
GROUP BY department_name
Bu komutla hr.emp_details viewini kullanan bir başka view oluşturuyoruz. Bu view, emp_details’deki bilgilere göre dolayısıyla hr.employees tablosundaki bilgilere göre bize bir sonuç döndürecek. Bu nedenle bu viewin de hr.employees tablosuna bağlı olduğunu biliyoruz, ama dba_dependencies’i sorguladığımızda şu sonucu gelir:
OWNER,NAME
———————————-
HR,EMP_DETAILS_VIEW
HR,SECURE_EMPLOYEES
HR,UPDATE_JOB_HISTORY
OE,EMPLOYEES
Görüldüğü üzere son oluşturduğumuz view yok. Çünkü dba_dependencies sadece 1. dereceden bağlı olan objeleri gösteriyor. Bizim son oluşturduğumuz hr.bolum_maas ise sadece hr.emp_details’e bağlı göründüğünden sorgu sonucunda listelenmiyor.
Yeniden deptree_fill’i hr.employees için çalıştırıp, deptree viewini sorguladığımızda ise şu sonucu alıyoruz:
NESTED_LEVEL,TYPE,SCHEMA,NAME,SEQ#
———————————————————
0,TABLE,HR,EMPLOYEES,0
1,VIEW,HR,EMP_DETAILS_VIEW,22
1,TRIGGER,HR,SECURE_EMPLOYEES,24
1,TRIGGER,HR,UPDATE_JOB_HISTORY,25
1,SYNONYM,OE,EMPLOYEES,26
2,VIEW,HR,BOLUM_MAAS,23

Bu sonuç, dba_dependencies viewini sorgulamak yerine deptree_fill kullanmanın faydasını açıklıyor. Deptree_fill, hr.employees tablosuna bağlı tüm objeleri son oluşturduğumuz (Hr.bolum_maas) view dahil olmak üzere gösterdi. Eğer deptree_fill prosedürünü sys kullanıcısı ile çalıştırırsanız, objeyi kullanan cursorları da görebilirsiniz.