PL/SQL Enhancements in Oracle Database 11g

1
Veritabani gelistiricileri için Oracle 11g yeni
özellikleri TROUG Oracle Day etkinligi Lütfi
Kirdar Kongre Merkezi 04/11/2010
H.Tonguç Yilmaz - tonguc.yilmaz_at_gmail.com
http//friendfeed.com/TongucY
2
http//tonguc.wordpress.com/about/

• H.Tonguç Yilmaz Oracle veritabani uzmani - iyi
  bir Fenerbahçe taraftari - Metal müzik sever -
  Bol bol DVD izler vs.
• Fethiye Lisesi, ITÜ Bilgisayar Müh., Bilgi MBA
• C Developer, Informix DBA-Hp Unix Admin, Oracle
  DBA, Oracle ETL Developer, DW Development Team
  Leader
• 1996 Tekstilbank, 2000 Turkcell, 2008 Turkcell
  Teknoloji
• 10 sene Oracle veritabani tecrübesi, Oracle
  8i,9i,10g OCP 2007 Oracle ACE, 2005 Oracle
  blogger
• 2002 Turkcell Akademi Oracle veritabani iç
  egitmeni 2002 TTech Paf koçu
• 1999 OracleTurk moderatör, 2010 TROUG kurucu üye

3
Her yeni sürüm ile birlikte ilk göz atilacak ilk
yer
4
Bu oturum veritabani gelistiricileri içindir ?

• SQL iyilestirmeleri
• Native Support for Pivot and Unpivot, Recursive
  WITH clause, Nth value for inter-row calculation,
  New aggregate function LISTAGG, Hash based
  DISTINCT aggregation, NULL-aware anti join, GROUP
  BY aggregation improvements
• PL/SQL iyilestirmeleri
• Transparent performance Fine Grained Dependency
  Tracking, Parameterized Performance Real native
  compilation, Usability of the language Sequence
  in a PL/SQL expression, PL/Scope, PL/SQL
  Hierarchical Profiler, When others then null
  warning, Named and Mixed Notation in SQL
• Sema yönetimi iyilestirmeleri
• Virtual Columns, Faster Add Not Null Column with
  Default Value, Read Only table, Invisible
  indexes, DDL can now wait, Segment creation on
  demand
• Sorgu çalistirma iyilestirmeleri
• Guaranteed plan stability with controlled plan
  evolution SQL Plan Management, Collect more
  statistics better, faster, Multi-column
  statistics, Faster calculation of NDV, Control
  and manage new statistics propagation Separate
  statistic discovery and publication, Enhanced
  bind peeking Properly address data skew,
  Multi-Predicate Pruning

• Real Time SQL Monitor
• Flashback Data Archive, Total Data Recall
• Cube Organized Materialized Views
• Real Application Testing Database Replay
• SQL Performance Analyzer (SPA)
• Partitioning, Advanced Compression,
• Parallel Query, Resource Manager
• Unstructured Data Application Development
• Securefiles, Multimedia, Spatial, XML DB
• Java PHP
• Exadata V2 Hybrid columnar compression,
• Storage indexes, Enhanced offloading
  capabilities,
• data mining model scoring
• Online application upgrade with edition based
  redefinition

5
Oracle kisa tarihçe

• 1978 Oracle V1 pazardaki ilk satis amaçli
  iliskisel SQL veritabani yönetim sistemi
• ...
• 7.3 1996
• 8.0 1997 Oracle 8
• 8.1.5 1999 Oracle 8i Release 1
• 8.1.6 1999 Oracle 8i Release 2
• 8.1.7 2000 Oracle 8i Release 3
• 9.1 2001 Oracle 9i Release 1
• 9.2 2002 Oracle 9i Release 2
• 10.1 2004 Oracle 10g Release 1
• 10.2 2005 Oracle 10g Release 2 ( XE ! )
• 11.1 2007 Oracle 11g Release 1
• 11.2 2009 Oracle 11g Release 2

6
Artik Apex OWB standart kurulumun parçasi

• Apex ve OWB standart ETL özellikleri veritabani
  lisansinin içindedir, ek maliyet yaratmazlar ?

7
Güle güle SQLPlus, hos geldin SQL Developer

• SQLPlus komut satiri hala bizimle, panik yok ?
• SQLPlus 11g ile BLOB ile BFILE kolonlari da
  artik gösterebiliyor.

8
Asynchronous Change Data Capture LOB'leri artik
destekliyor

• Asynchronous Change Data Capture henüz
  desteklenmeyen veri tipleri
• BFILE
• LONG
• ROWID
• UROWID
• object types (XMLType gibi)
• virtual columns
• Asynchronous Change Data Capture desteklenmeyen
  tablo yapilari
• temporary tables
• object tables
• Desteklenmeyen veri tipleri içeren(LOB kolonlar
  dahil) veya overflow segments sahibi olan
  Index-organized tables

9
Sifrelerde küçük-büyük harf kullanimi

• CONN / AS SYSDBA
• CREATE USER test2 IDENTIFIED BY Test2
• GRANT CONNECT TO test2
• SQLgt CONN test2/Test2
• Connected.
• SQLgt CONN test2/test2
• ERROR
• ORA-01017 invalid username/password logon
  denied
• SQLgt SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
• NAME TYPE
  VALUE
• ------------------------------------ -----------
  ------------------------------
• sec_case_sensitive_logon boolean
  TRUE
• CONN / AS SYSDBA
• ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON
  FALSE

10
SQLPlus ERRORLOGGING özelligi

• SQL, PL/SQL veya SQLPlus hatalari artik
  SPERRORLOG tablosunda kaydediliyor.
• create table tong.tong_sperrorlog(username
  varchar(256),
• timestamp TIMESTAMP,
• script varchar(1024),
• identifier varchar(256),
• message CLOB,
• statement CLOB)
• set errorlogging on table tong_sperrorlog
• create table tong ( c1 number )
• set errorlogging off
• select timestamp, statement, message from
  tong_sperrorlog
• TIMESTAMP 22-JUN-08 07.00.27.000000 PM
• STATEMENT create table tong ( c1 number )

11
ALTER TABLE .. ADD COLUMN iyilestirmeleri

• 11g öncesinde DEFAULT degerli yeni bir kolon ve
  NOT NULL constraint eklemek tablo üzerinde
  exclusive kilit aliyordu ve varsayilan deger
  tablodaki tüm satirlara güncellenmesi
  gerektiriyordu.
• 11g sonrasinda varsayilan deger data dictionary
  kaydediliyor ve büyük tablolar için tüm
  kayitlarin güncellenmesi isleminden kurtulunuyor.
  Ek olarak alttaki ADD COLUMN operasyonlari artik
  DML cümleleri ile birlikte problemsiz
  çalisabiliyor
• Varsayilan degerli NOT NULL kolon ekleme
• Varsayilan degeri olmayan bir nullable kolon
  ekleme
• Virtual kolon ekleme
• -- 10.2
• create table tst_source nologging as select
  rownum id, text from dba_source
• set timing on
• ALTER TABLE tst_source ADD (name VARCHAR2(16)
  DEFAULT 'N' NOT NULL)
• Elapsed 000030.43
• -- 11.1
• create table tst_source nologging as select
  rownum id, text from dba_source
• set timing on
• ALTER TABLE tst_source ADD (name VARCHAR2(16)
  DEFAULT 'N' NOT NULL)
• Elapsed 000000.10

12
Sanal Kolonlar

• Var olan kolon degerlerinden hesaplanan yeni
  kolonlar, DML göremezler, indeksler üzerindeki
  function based index olarak yönetilir,
  Index-organized, external, object, cluster, veya
  temporary tablolar için desteklenmiyor henüz.
• CREATE TABLE employees (
• id NUMBER, first_name VARCHAR2(10),
• last_name VARCHAR2(10), salary
  NUMBER(9,2),
• comm1 NUMBER(3), comm2 NUMBER(3),
• salary1 AS (ROUND(salary(1comm1/100),2)),
• salary2 NUMBER GENERATED ALWAYS AS
  (ROUND(salary(1comm2/100),2)) VIRTUAL,
• CONSTRAINT employees_pk PRIMARY KEY (id) )
• INSERT INTO employees (id, first_name, last_name,
  salary, comm1, comm2) VALUES (1, 'JOHN', 'DOE',
  100, 5, 10)
• INSERT INTO employees (id, first_name, last_name,
  salary, comm1, comm2) VALUES (2, 'JAYNE', 'DOE',
  200, 10, 20)
• COMMIT
• SELECT FROM employees
• ID FIRST_NAME LAST_NAME SALARY
  COMM1 COMM2 SALARY1 SALARY2
• ---------- ---------- ---------- ----------
  ---------- ---------- ---------- ----------
• 1 JOHN DOE 100
  5 10 105 110
• 2 JAYNE DOE 200
  10 20 220 240

13
DDL bekletme opsiyonu (DDL_LOCK_TIMEOUT)

• DDL_LOCK_TIMEOUT parametresi bir DDL komutunun
  kaç saniye bekleyecegini ilgili aktif islemi
  belirliyor, ALTER SYSTEM veya ALTER SESSION ile
  kullanilabiliyor. Var sayilan deger 0 saniye.
• -- session 1
• CREATE TABLE lock_tab ( id NUMBER )
• INSERT INTO lock_tab VALUES (1)
• -- session 2
• ALTER TABLE lock_tab ADD ( description
  VARCHAR2(50) )
• ALTER TABLE lock_tab ADD (
• ERROR at line 1
• ORA-00054 resource busy and acquire with NOWAIT
  specified or timeout expired
• ALTER SESSION SET ddl_lock_timeout30
• ALTER TABLE lock_tab ADD ( description
  VARCHAR2(50) )

14
Görünmez Indeksler

• CBO'nun indeksiz davranmasina zorlamak amaci ile,
  arkada indeks bakimi devam ediyor aslinda.
  VISIBILITY kolonu DBAALLUSER_INDEXES
  görüntülerinde degeri görmek için kullanilabilir.
  DROP INDEX etkisi testi için kullanisli olabilir.
• CREATE TABLE ii_tab ( id NUMBER )
• BEGIN
• FOR i IN 1 .. 10000 LOOP
• INSERT INTO ii_tab VALUES (i)
• END LOOP
• COMMIT
• END
• /
• CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE
• EXEC DBMS_STATS.gather_table_stats(USER,
  'ii_tab', cascadegt TRUE)
• SET AUTOTRACE ON
• SELECT FROM ii_tab WHERE id 9999
• --------------------------------------------------
  --------------------------
• Id Operation Name Rows
  Bytes Cost (CPU) Time
• --------------------------------------------------
  --------------------------
• 0 SELECT STATEMENT 1
  3 7 (0) 000001

15
Salt okunur tablolar

• 11g öncesinde bir tabloyu sahibi disinda
  GRANT'ler üzerinden salt okunur kilmak mümkündü,
  bu yeni özellikle sahibi dahil tüm kullanicilara
  DML/DDL islemler tablo üzerinde yasaklanabiliyor.
  READ_ONLY kolon DBAALLUSER_TABLES
  görüntülerinde durumu gösteriyor.
• CREATE TABLE ro_tab ( id NUMBER )
• INSERT INTO ro_tab VALUES (1)
• ALTER TABLE ro_tab READ ONLY
• SQLgt INSERT INTO ro_tab VALUES (2)
• SQLgt UPDATE ro_tab SET id 2
• SQLgt DELETE FROM ro_tab
• SQLgt TRUNCATE TABLE ro_tab
• SQLgt ALTER TABLE ro_tab ADD (description
  VARCHAR2(50))
• ORA-12081 update operation not allowed on table
  "TEST"."RO_TAB
• ALTER TABLE ro_tab READ WRITE

16
SEQUENCE kullanimi
declare l_seq number begin select
orders_seq.nextval into l_seq from
dual ... end
declare l_seq number orders_seq.nextval begi
n ... end
17
SQL motorundan PL/SQL islev çagirimi iyilestirmesi
function f_get_cnt (p_deptno in dept.deptnotype
null ,p_loc in
dept.loctype null )
return number
SQLgt select ename 2 , f_get_cnt (p_loc gt
NEW YORK) 3 from emp 4 /
18
CONTINUE
begin for i in 1..3 loop
dbms_output.put_line ('i 'to_char(i)) if
i 2 then null else
dbms_output.put_line ('Only if i is not equal to
2') end if end loop end
i 1 Only if i is not equal to 2 i 2 i 3 Only
if i is not equal to 2
begin for i in 1..3 loop
dbms_output.put_line ('i 'to_char(i))
continue when i 2 dbms_output.put_line
('Only if i is not equal to 2') end loop end
19
Trigger tetiklenme sirasi yönetimi

• Hangisi daha önce çalisacak?

create or replace trigger emp_bir_1 before
insert on emp for each row begin ... end
emp_bir_1
create or replace trigger emp_bir_1 before
insert on emp for each row begin ... end
emp_bir_1
create or replace trigger emp_bir_2 before
insert on emp for each row follows
emp_bir_1 begin ... end emp_bir_2
create or replace trigger emp_bir_2 before
insert on emp for each row begin ... end
emp_bir_2
CREATE OR REPLACE TRIGGER trigger_control_test_trg
BEFORE INSERT ON trigger_control_test FOR
EACH ROW DISABLE BEGIN DBMS_OUTPUT.put_lin
e('TRIGGER_CONTROL_TEST_TRG - Executed') END /
DISABLED Trigger yaratma
20
Bagimlilik takibi 10g
create table t (x int ,y int )
create package body pkg is procedure
only_on_y is begin for rec in (select
y from t )
loop ... end loop end end
pkg
alter table t add z int
INVALID
21
Bagimlilik takibi 11g
create table t (x int ,y int )
create package body pkg is procedure
only_on_y is begin for rec in (select
y from t )
loop ... end loop end end
pkg
alter table t add z int
VALID
22
WHEN OTHERS derleme uyarisi
23
SIMPLE_INTEGER

• PLS_INTEGER tipinden daha etkin
• NOT NULL constraint nedeni ile
• ön deger atanmali

declare cnt simple_integer 0 begin cnt
cnt 1 end
declare cnt if dbms_db_version.ver_le_10_2
then pls_integer not null else
simple_integer end 0 begin
cnt cnt 1 end
24
SQL Operatörleri ve Islevleri için Metadata
bilgisi

• VSQLFN_METADATA ve VSQLFN_ARG_METADATA isimli
  yeni görüntüler üzerinden erisilebiliniyor,
  özellikle 3. parti ürünler için kullanisli
  görüntüler.
• SQLgt set linesize 2500
• SQLgt SELECT FROM vsqlfn_metadata where name
  'REGEXP_SUBSTR'
• FUNC_ID NAME
  MINARGS MAXARGS DATATYPE VERSION ANA AGG
  DISP_TYPE USAGE
  DESCR
• ---------- ------------------------------
  ---------- ---------- -------- ------------ ---
  --- ------------- ------------------------------
  ------------------------------
• 526 REGEXP_SUBSTR
  2 5 STRING V10 Oracle NO NO
  NORMAL
• SQLgt SELECT FROM vsqlfn_arg_metadata WHERE
  func_id 526
• FUNC_ID ARGNUM DATATYPE DESCR
• ---------- ---------- -------- -------------------
  -----------
• 526 1 STRING
• 526 2 STRING
• 526 3 NUMERIC
• 526 4 NUMERIC
• 526 5 STRING

25
Güle güle Outlinelar

• metalink 11g Upgrade Companion
• Oracle highly recommends the use of SQL plan
  baselines instead of the stored outlines after
  11gR1. With Oracle Database 11g using the SQL
  Plan Management (SPM) the optimizer automatically
  manages plans and ensures that only verified or
  known plans are used.
• SQL Plan Management allows controlled plan
  evolution by only using a new plan after it has
  been verified to be perform better than the
  current plan. You should also use SQL Plan
  Management as part of your upgrade strategy.
  Please see the best practices section for more
  information.
• /metalink 11g Upgrade Companion
• Baska güle güleler de var Oracle Ultra Search,
  Java Development Kit(JDK) 1.4, CTXXPATH index

26
DBMS_COMPARISON 1/2

• Tablo ve görüntüleri kiyaslamak için bir hazir
  paket, dblink üzerinden de çalisabiliyor
• Paketin arkasinda 10g ile gelen ORA_HASH SQL
  islevi kiyaslama amaci ile kullaniliyor.
• conn hr/hr
• DROP TABLE depts PURGE
• CREATE TABLE depts NOLOGGING AS SELECT FROM
  departments
• CREATE UNIQUE INDEX ui_depts ON
  depts(department_id) NOLOGGING
• DELETE FROM depts WHERE department_id 10
• COMMIT
•  
• exec dbms_comparison.create_comparison(comparison_
  namegt'compare_test', schema_namegt'HR',
  object_namegt'DEPTS', dblink_namegtNULL,
  remote_schema_namegt'HR', remote_object_namegt'DEP
  ARTMENTS', scan_percentgt90)
•  
• set serveroutput on
• DECLARE
• retval dbms_comparison.comparison_type
• BEGIN
• IF dbms_comparison.compare('compare_test',
  retval, perform_row_difgtTRUE) THEN
• dbms_output.put_line('No Differences')
• ELSE

27
DBMS_COMPARISON 2/2

• set null _at_
• set colsep
• set linesize 2500
• select FROM user_comparison_row_dif
•  
• COMPARISON_NAME
  SCAN_IDLOCAL_ROWID
• REMOTE_ROWID
•  
• INDEX_VALUE
•  
• STALAST_UPDATE_TIME
•  
• COMPARE_TEST 4_at_
  AAARADAAFAAAAA3AAA
• 10
• DIF04-OCT-08 02.58.03.306182 PM
•  
• exec dbms_comparison.drop_comparison(
• comparison_namegt'compare_test')

SELECT ..FROM   USER_COMPARISON_COLUMNS c,     
USER_COMPARISON_ROW_DIF r,     
USER_COMPARISON_SCAN sWHERE  c.COMPARISON_NAME
'COMP_EMP_N_CLONE' AND    r.SCAN_ID        
s.SCAN_ID AND    s.scan_id scan_idAND   
r.STATUS          'DIF' AND   
c.INDEX_COLUMN    'Y' AND    c.COMPARISON_NAME
r.COMPARISON_NAME
DECLARE  scan_info DBMS_COMPARISON.COMPARISON_TYP
EBEGIN  DBMS_COMPARISON.CONVERGE  (
comparison_name  gt 'compare_emp_and_clone'  ,
scan_id          gt scan_id  , scan_info       
gt scan_info , converge_options gt
DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS 
)  DBMS_OUTPUT.PUT_LINE('Remote Rows Merged '
scan_info.rmt_rows_merged)
  DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted '
scan_info.rmt_rows_deleted)
END
28
Global temporary tablelariniz için baska bir
TEMP Tablespace belirtme

• SQLgt create temporary tablespace etl_temp
• 2gt tempfile 'DG1/etl_temp_01.dbf'
• 3gt size 1G
• Tablespace created.
• SQLgt create global temporary table data_load (
• 2gt input_line varchar2 (2000)
• 3gt )
• 4gt on commit preserve rows
• 5gt tablespace etl_temp
• Table created.
• GTT kullanicinin default temporary tablespace'i
  TEMP yerine ETL_TEMP tablespacei altinda
  yaratilir.

29
LISTAGG

• SELECT deptno, LISTAGG(ename, ' ') WITHIN
  GROUP (order by ename)
• FROM emp
• GROUP BY deptno
• DEPTNO LISTAGG(ENAME,'')WITHINGROUP(ORDERBYE
  NAME)------ ----------------------------------
  --------- 10 CLARK KING MILLER 20
  ADAMS FORD JONES SCOTT SMITH 30
  ALLEN BLAKE JAMES MARTIN TURNER WARD

29
30
Pivot
SALES_BY_QUARTER SALESREP QU
REVENUE ---------- -- ---------- 100 Q1
230 100 Q2 240 100 Q3
160 100 Q4 90 100 Q3
100 100 Q4 140 100 Q4
70 101 Q1 200 101 Q2
220 101 Q3 250 101 Q4
260 102 Q1 260
SALESREP 'Q1' 'Q2' 'Q3' 'Q4' ----------
----- ----- ----- ----- 100 230 240
260 300 101 200 220 250 260
102 260 280 265 310

select from sales_by_quarter pivot
(sum(revenue)? for quarter in ('Q1','Q2','Q3','Q4'
))? order by salesrep
31
Unpivot
SALESREP QU REVENUE ---------- --
---------- 100 Q1 230 100 Q2
240 100 Q3 260 100 Q4
300 101 Q1 200 101 Q2
220 101 Q3 250 101 Q4
260 102 Q1 260 102 Q2
280 102 Q3 265 102 Q4
310
QUARTERLY_SALES SALESREP Q1 Q2 Q3
Q4 ---------- ----- ----- ----- ----- 100
230 240 260 300 101 200 220
250 260 102 260 280 265 310

select from quarterly_sales unpivot include
nulls (revenue for quarter in (q1,q2,q3,q4))? orde
r by salesrep, quarter
32
SQL Result Cache Kavrami
Select deptno , avg(sal) from huge_emp
group by deptno
2 sn.(parse fetch)
Select deptno , avg(sal) from huge_emp
group by deptno
1.23 sn. (parse yok ama yine fetch)
Select deptno , avg(sal) from huge_emp
group by deptno
1.22 sn. (yine sadece fetch)
33
Result Cache Hint
select / result_cache / deptno ,
avg(sal) from super_emp group by deptno
Execution Plan -----------------------------------
----------------------- Plan hash value
3044997086 --------------------------------------
--------------------------------------------------
---------- Id Operation Name
Rows Bytes Cost (CPU)
Time ---------------------------------------
--------------------------------------------------
--------- 0 SELECT STATEMENT
3 21 4 (25)
000001 1 RESULT CACHE
7nvxyd4t2fu8rc25rh5w5p922n
2 HASH GROUP BY
3 21 4
(25) 000001 3 TABLE ACCESS FULL
SUPER_EMP 14 98
3 (0) 000001 ------------------------------
--------------------------------------------------
------------------ Result Cache Information
(identified by operation id) --------------------
---------------------------------- 1 -
column-count2 dependencies(SCOTT.SUPER_EMP)
name"SELECT / result_cache / deptno, avg(sal)
34
PL/SQL Result Cache Kavrami
create or replace function test_result_cache(
p_in in number) return number is begin
dbms_lock.sleep (5) return p_in end
declare l_num number begin l_num
test_result_cache(1) l_num
test_result_cache(1) l_num
test_result_cache(1)end
15.13 sn.
35
PL/SQL Result Cache Kavrami
create or replace function test_result_cache
(p_in in number) return number
result_cache is begin dbms_lock.sleep (5)
return p_in end
declare l_num number begin l_num
test_result_cache(1) l_num
test_result_cache(1) l_num
test_result_cache(1)end
5,02 sn.
36
Result Cache Invalidasyonu
create or replace function test_result_cache
(p_in in number) return number
result_cache is retval number begin select
rn into retval from t where rn
p_in dbms_lock.sleep (1) return
retval end
Referans edilen tablo içerigi degisse bile hep
ayni sonucu döndürür
37
Result Cache Invalidasyonu
create or replace function test_result_cache
(p_in in number) return number result_cache
relies_on (T) is retval number begin
select rn into retval from t where
rn p_in dbms_lock.sleep (1) return
retval end
Referans edilen tablolar degistiginde Result
Cache de kullanilmaz duruma geçsin..
38
Kiyamadiklarim

• REGEXP_COUNT yeni Regular Expression SQL islevi
  aranan paternin kaç kez kaynak string içinde
  geçtigini döndürüyor.
• 11g ile artik cidden online index rebuild ONLINE
  exclusive lock artik olusmuyor ve DMLler
  etkilenmiyor.
• DBMS_SCHEDULER Lightweight Jobs, Email
  Notification, File Watcher, Remote Database Jobs
• DBMS_HPROF PL/SQL Hierarchical Profiler
• DBMS_PARALLEL_EXECUTE

39
Kiyamadigim birkaç tane daha ?

• Yeni VOSSTAT ve VSTATISTICS_LEVEL
  istatistikleri, ve bir çok ilginç yeni görüntü
  VSQL_CS_HISTOGRAM, VSQL_CS_SELECTIVITY,
  VSQL_CS_STATISTICS, VSQL_FEATURE, VSQL_HINT,
  VSQL_MONITOR, VSQL_PLAN_MONITOR, VIOSTAT_
• PL/SQL Bulk Insert ve APPEND_VALUES hint
• FORALL i IN l_tab.first .. l_tab.last
• INSERT / APPEND_VALUES / INTO forall_test
  VALUES l_tab(i)
• tkprof artik SQL ID degerini baslikta gösteriyor.
• DBMS_REDEFINITION online table redefinition
  bagimli nesneleri(PL/SQL, views, synonyms)
  INVALID durumuna artik düsürmüyor
• Baglantimdan olusan Trace dosyasi ne isimde
  artik çok kolay
• select value from vdiag_info where name
  'Default Trace File'
• VALUE
• --------------------------------------------------
  --------------------------
• /u01/app/oracle/diag/rdbms/tong/tong/trace/tong_or
  a_4402_IDENTIFIER.trc

40
Oku, dene, sorgula, gelistir, paylas ..

• Kaynakça
• Tim Hall's Oracle 11g Articles
  http//oracle-base.com/articles/11g/Articles11g.ph
  p
• Oracle 11g for Developers Lucas Jellema Alex
  Nuijten, AMIS
• 11g The Perfection of a Masterpiece Christo
  Kutrovsky, Pythian
• Top 10, no make that 11, things about Oracle
  Database 11g
• Thomas Kyte, http//asktom.oracle.com
• Oracle Database 11g For Developers
• Thomas Kyte, http//asktom.oracle.com
• New Features Documentation _at_ http//tahiti.oracle.
  com
• Oracle Database New Features Guide 11g Release 1
  (11.1) http//download.oracle.com/docs/cd/B28359_
  01/server.111/b28279/toc.htm
• Oracle Database New Features Guide 11g Release 2
  (11.2) http//download.oracle.com/docs/cd/E11882_
  01/server.112/e17128/toc.htm
• Daniel Morgans Oracle Cheatsheet Library
  http//www.morganslibrary.org/library.html
• Arup Nanda's Oracle Database 11g The Top New
  Features for DBAs and Developers
• OTN Series http//www.oracle.com/technetwork/arti
  cles/sql/index-099021.html

41
TTECH Oracle Türkiyeye destekleri için çok
tesekkürler
?
?
?
?
?
Sorulariniz ve/veya yorumlariniz
?
?
?
?
H.Tonguç Yilmaz - tonguc.yilmaz_at_gmail.com
http//friendfeed.com/TongucY
42
TROUG _at_ Oracle Day

• TROUG Stand
• DBA'ler için Oracle veritabani 11g yeni
  özellikleri Zekeriya Besiroglu, Bilginç Akademi 
• Veritabani gelistiricileri için Oracle veritabani
  11g yeni özellikleri H.Tonguç Yilmaz, ACE,
  Turkcell Teknoloji 
• Veri ambari ve Exadata Oracle veritabani 11g yeni
  özellikleri Hüsnü Sensoy, ACE Director, Global
  Maksimum 
• Panel TROUG Roundtable