1 / 21

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

Veritabanı Objeleri Ölçülebilirlik & Performans. Talip Hakan ÖZTÜRK B ANK ASYA – ORACLE DBA OCE, 10g OCA, 10g OCP http://taliphakanozturk.wor dpress.com. Talip Hakan ÖZTÜRK. 2000-2005 Azerbaycan Teknik Üniversitesi – Bilgisayar Müh . B ANK ASYA - Oracle DBA OCE, 10g OCA, 10g OCP

malha
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related