Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

I ♥ PL/SQL

Slide 3

Slide 3 text

How the @#!% do we get it live?!

Slide 4

Slide 4 text

Zero-Downtime PL/SQL Changes with Edition-Based Redefinition [email protected] @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicofSQL Chris Saxon, Oracle Developer Advocate

Slide 5

Slide 5 text

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor

Slide 6

Slide 6 text

alter package … compile;

Slide 7

Slide 7 text

alter package … compile; Errors: check compiler log

Slide 8

Slide 8 text

PRO start release create table … update … commit; PRO release complete ORA-04068 PLS-00905 ORA-04061

Slide 9

Slide 9 text

alter package … compile;

Slide 10

Slide 10 text

alter package … compile;

Slide 11

Slide 11 text

alter package … compile;

Slide 12

Slide 12 text

alter package … compile; No users executing!

Slide 13

Slide 13 text

Rolling Release?

Slide 14

Slide 14 text

Rolling Release?

Slide 15

Slide 15 text

Rolling Release?

Slide 16

Slide 16 text

Rolling Release?

Slide 17

Slide 17 text

shutdown immediate

Slide 18

Slide 18 text

Emergency Rollback?

Slide 19

Slide 19 text

Solutions pre 11g Release 2

Slide 20

Slide 20 text

PROCEDURE_3 VIEW_2 Rename Your Objects! PROCEDURE PROCEDURE_2 PACKAGE PACKAGE_2 VIEW

Slide 21

Slide 21 text

VIEW_2 Dependency Hell PROC PROC_2 PACK PACK_2 PACK_3 VIEW VIEW_3 PROC_3a PACK_3a VIEW_3b PROC_4 VIEW_2a

Slide 22

Slide 22 text

PROCEDURE_3 VIEW_2 Rename Your Objects! PROCEDURE PROCEDURE_2 PACKAGE PACKAGE_2 PACKAGE_3 VIEW VIEW_3

Slide 23

Slide 23 text

Multiple PL/SQL Schemas DATA PL/SQL 1 PL/SQL 2

Slide 24

Slide 24 text

Replicate Your Database $$$ &

Slide 25

Slide 25 text

Copyright © 2019 Oracle and/or its affiliates. No Database Compiled PL/SQL!!

Slide 26

Slide 26 text

EBR to the Rescue!

Slide 27

Slide 27 text

PROCEDURE VIEW EDITION_1 EDITION_2 EDITION_3 EBR to the Rescue! PROCEDURE PACKAGE VIEW

Slide 28

Slide 28 text

How do I use EBR?

Slide 29

Slide 29 text

DEMO

Slide 30

Slide 30 text

create edition … alter session set edition … @do_your_release @recompile_all grant use on edition to …

Slide 31

Slide 31 text

That was too easy! Surely there must be a catch?

Slide 32

Slide 32 text

alter user … enable editions;

Slide 33

Slide 33 text

You can’t editions disable a user

Slide 34

Slide 34 text

Not Everything is Editionable MY_PACKAGE FUNCTION-BASED INDEX MATERIALIZED VIEW Addressed in 12c!

Slide 35

Slide 35 text

create noneditionable function … as begin … end; /

Slide 36

Slide 36 text

alter table t add … as ( your_func ) evaluate using current edition

Slide 37

Slide 37 text

Many editions >> risk of missteaks

Slide 38

Slide 38 text

Specify edition in connect string Ethernet plug grey / Elembis / CC-BY-SA-3.0

Slide 39

Slide 39 text

oracle.jdbc.editionName export ORA_EDITION = … conn usr/pwd edition = …

Slide 40

Slide 40 text

alter database default edition = …

Slide 41

Slide 41 text

Minimize Live Editions Image by tswedensky from Pixabay

Slide 42

Slide 42 text

revoke use on edition release_001 from ebr_user;

Slide 43

Slide 43 text

conn ebr_user alter session set edition = release_001; ORA-38802: edition does not exist

Slide 44

Slide 44 text

Wait, There’s More! EBR

Slide 45

Slide 45 text

Further Reading 11gR2 Documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS020 12cR1 Documentation: http://docs.oracle.com/database/121/ADFNS/adfns_editions.htm Oracle-BASE article: http://oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php Tom Kyte’s Oracle Magazine article, Edition-Based Redefinition, Part 1: http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html Edition-based redefinition whitepaper, Bryn Llewellyn July 2009: http://www.oracle.com/technetwork/database/features/availability/edition-based- redefinition-1-133045.pdf Oren Nakdimon's EBR blog post series

Slide 46

Slide 46 text

Summary Use EBR to deploy PL/SQL with zero downtime …or just shrink outage windows Requires 11g Release 2+; Easier from 12c

Slide 47

Slide 47 text

asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography