Talip Hakan ÖZTÜRK B ANK ASYA – ORACLE DBA OCE, 10g OCA, 10g OCP

1. Veritabanı Objeleri Ölçülebilirlik & Performans TalipHakan ÖZTÜRK BANKASYA – ORACLE DBA OCE, 10g OCA, 10g OCP http://taliphakanozturk.wordpress.com

2. TalipHakan ÖZTÜRK • 2000-2005 AzerbaycanTeknikÜniversitesi – BilgisayarMüh. • BANKASYA - Oracle DBA • OCE, 10g OCA, 10g OCP • Oracle Blogger • http://taliphakanozturk.wordpress.com • Oracle Forum SorumlusuveEditörü • http://www.ceturk.com http://taliphakanozturk.wordpress.com

3. Gündem • Veritabanı Objeleri – Demo • Views • Constraints • DML Triggers • DDL Triggers • B*Tree Indexes • Bitmap Indexes • Function Based Indexes • Index Organized Tables • External Tables • Synonyms • Sequence • Okuma Tutarlılığı (Read Consistency) • Kilitler (Locks) http://taliphakanozturk.wordpress.com

4. Views • Tablo joinleri içerebilirler. • DML? Simple CREATE OR REPLACE FORCE VIEW calisanlar_v AS SELECT c.employee_id, c.first_name, c.last_name, c.hire_date FROM calisanlar c; Complex CREATE OR REPLACE FORCE VIEW calisanlar_v (min_salary, department_name) AS SELECT min(c.salary),d.department_name FROM calisanlar c, departmanlar d WHERE c.department_id=d.department_id GROUP BY d.department_name; http://taliphakanozturk.wordpress.com

5. Constraints • Tablonun ilgili alanına uygulanan kısıtlamadır. ALTER TABLE calisanlar ADD ( CONSTRAINT salary_check CHECK ( NVL(salary,0) >= 1000 )); insert into calisanlar (first_name,last_name,email,salary) values ('talip','ozturk','talipozturk@gmail.com',1001); http://taliphakanozturk.wordpress.com

6. DML Triggers create table calisanlar_log ( user_id varchar2(10), log_date date, action varchar2(1000) ); CREATE OR REPLACE TRIGGER calisanlar_kontrol_delete BEFORE DELETE ON calisanlar REFERENCING NEW AS new OLD AS old FOR EACH ROW DECLARE BEGIN raise_application_error(-20001,'Silme islemi yapamazsiniz! DBA ile gorusunuz!'); end; delete from calisanlar where employee_id=105; CREATE OR REPLACE TRIGGER calisanlar_kontrol_insert AFTER INSERT ON calisanlar REFERENCING NEW AS new OLD AS old FOR EACH ROW DECLARE BEGIN INSERT INTO calisanlar_log (user_id,log_date,action) VALUES (USER, SYSDATE, 'calisanlar tablosuna kayıt insert edildi'); end; insert into calisanlar (first_name,last_name,email,salary) values ('talip','ozturk','talipozturk@gmail.com',1001); http://taliphakanozturk.wordpress.com

7. DDL Triggers CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging on'); END; / create table log_trig_table ( user_id varchar2(10), log_date date, action varchar2(10) ); select * from log_trig_table; http://taliphakanozturk.wordpress.com

8. S D E U AUSBELCAN SCOSPA USA DEN ENG B*Tree Indexes Set autotrace on SELECT * FROM CALISANLAR WHERE EMPLOYEE_ID=105; CREATE INDEX CALISANLAR_ID_X ON CALISANLAR(EMPLOYEE_ID); --ALTER SYSTEM FLUSH SHARED_POOL; --ALTER SYSTEM FLUSH BUFFER_CACHE; analyze index CALISANLAR_ID_X compute statistics; http://taliphakanozturk.wordpress.com

9. Bitmap Indexes create table bitmap_index_demo1 as select * from bitmap_index_demo; insert into bitmap_index_demo1 select decode(mod(rownum,2),0,'M','F') from all_objects; create index bitmap_index_demo1_idx on bitmap_index_demo1(value); -- birinci session insert into bitmap_index_demo1 values ('M'); -- ikinci session insert into bitmap_index_demo1 values ('F'); -- birinci session insert into bitmap_index_demo1 values ('F'); create table bitmap_index_demo ( value varchar2(20) ); insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F') from all_objects; create bitmap index bitmap_index_demo_idx on bitmap_index_demo(value); -- birinci session insert into bitmap_index_demo values ('M'); -- ikinci session insert into bitmap_index_demo values ('F'); -- birinci session insert into bitmap_index_demo values ('F'); http://taliphakanozturk.wordpress.com

10. Function Based Indexes CREATE INDEX CALISANLAR_ID_2X ON CALISANLAR(SALARY); CREATE INDEX CALISANLAR_ID_3X ON CALISANLAR(SALARY*10/100); SELECT * FROM calisanlar WHERE SALARY*10/100>=1000; http://taliphakanozturk.wordpress.com

11. Index Organized Tables Veriler B*Tree index yapısında saklanır. create table iot_table ( id number primary key, name varchar2(5) ) organization index; create table normal_table ( id number primary key, name varchar2(5) ); insert into iot_table values(5,'a'); commit; http://taliphakanozturk.wordpress.com

12. External Tables create or replace directory talipdir as '/oracle/ora11g'; as sysdba grant read,write on directory talipdir to talip; as sysdba create table dblist ( CI_name varchar2(20), ID varchar2(20), logical_name varchar2(20), dbname varchar2(20), thread number, type varchar2(20), subtype varchar2(20), environment varchar2(20) ) organization external ( default directory talipdir access parameters ( records delimited by newline fields terminated by ';' ) location ('dblist.csv') )REJECT LIMIT unlimited; select * from dblist order by ci_name desc; http://taliphakanozturk.wordpress.com

13. VELI SELIM AYSE Synonyms • Private Synonyms • Public Synonyms HESAP_NO Public Synonym To ALI.HESAP_NO ALI . HESAP_NO Select * from hesap_no; create synonym ygm_test.calisanlar for talip.calisanlar; create public synonym calisanlar for talip.calisanlar; http://taliphakanozturk.wordpress.com

14. Sequence CREATE SEQUENCE calisan_id INCREMENT BY 1 START WITH 1 MAXVALUE 100 MINVALUE 1 NOCYCLE CACHE 20 ORDER; http://taliphakanozturk.wordpress.com

15. Read Consistency http://taliphakanozturk.wordpress.com

16. Read Consistency http://taliphakanozturk.wordpress.com

17. Read Consistency http://taliphakanozturk.wordpress.com

18. DML Locks Session 1: UPDATE scott.emp SET ename = 'KING1' WHERE ename = 'KING'; (don NOT commit) Session 2: SELECT ename FROM scott.emp WHERE ename='KING' FOR UPDATE OF ename wait 10; after 10 seconds : * ERROR at line 1: ORA-30006: resource busy; acquire with WAIT timeout expired http://taliphakanozturk.wordpress.com

19. Deadlocks http://taliphakanozturk.wordpress.com

20. http://taliphakanozturk.wordpress.com http://oracleforum.info http://troug.org http://www.ceturk.com http://tahiti.oracle.com http://forums.oracle.com http:/otn.oracle.com http://asktom.oracle.com http://taliphakanozturk.wordpress.com

21. TEŞEKKÜRLER Talip Hakan ÖZTÜRK Bilgisayar Mühendisi OracleCertified Professional 10g OracleCertifiedAssociate 10g OracleCertifiedExpert talip_hakan_ozturk@hotmail.com http://taliphakanozturk.wordpress.com