Страницы

среда, 22 июля 2015 г.

Edition-Based redefinition: adjunct schemas

EBR (Edition-Based Redefinition) - one of the killer feature of Oracle Database 11g R2, as said Tom Kyte at the 2010.
But I don't think that this feature widely used by Oracle community.
Why? Probably, because application must be "edition-aware", as described by Tom Kyte in his excellent articles.
I have been using EBR (Edition-Based Redefinition) since 2012.
In this blog post I would like to describe one of the basic component of EBR: adjunct schemas.
All of this information is a pure speculations based on blog posts and Oracle whitepapers, and my experience with EBR feature.
All of the tests are run in Oracle Database version 12.1.0.2 under Solaris SPARC.
Here is a code that I have used for this demo:
def tns_alias=orcl

doc
  connect as DBA user
#
conn /@&tns_alias.

set echo on timi off ti off sqlp "SQL> "

drop edition e1 cascade;
drop edition e2 cascade;
drop user tc cascade;

doc
  Create editions-enabled schema
#
grant create procedure, create session to tc identified by tc;

alter user tc enable editions;

doc
  Enable SQL tracing
#

alter session set events 'sql_trace bind=true';

doc
  Create 2 editions
#

create edition e1;
create edition e2;

alter session set events 'sql_trace off';

select value from v$diag_info where name='Default Trace File';
You can see that I have created edition-enabled schema TC and 2 editions: E1 and E2.
I would to point your attention to the below recursive SQL executed during "CREATE EDITION" command:
PARSING IN CURSOR #18446744071422692152 len=288 dep=1 uid=0 oct=2 lid=0 tim=9324454212634 hv=556673006 ad='430c59cc0' sqlid='fws71mhhkw9zf'
insert into user$(user#, name, password, ctime, ptime,                      datats#, tempts#, type#, defrole, resource$, ltime,                      astatus, lcount, spare1, spare2, ext_user
name)    values(:1, :2, NULL, sysdate, null, :3, :4, 2, :5, :6, null, :7, 0, 16,           :8, :9)
END OF STMT
PARSE #18446744071422692152:c=1199,e=1198,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=9324454212629
BINDS #18446744071422692152:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=ffffffff77e77150  bln=22  avl=03  flg=05
  value=859
 Bind#1
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=171 siz=32 off=0
  kxsbbbfp=ffffffff7fff8688  bln=32  avl=30  flg=09
  value="SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=144 off=0
  kxsbbbfp=ffffffff77e770a8  bln=22  avl=02  flg=05
  value=3
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=ffffffff77e770c0  bln=22  avl=02  flg=01
  value=2
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=ffffffff77e770d8  bln=22  avl=01  flg=01
  value=0
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=72
  kxsbbbfp=ffffffff77e770f0  bln=22  avl=02  flg=01
  value=1
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=96
  kxsbbbfp=ffffffff77e77108  bln=22  avl=01  flg=01
  value=0
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=120
  kxsbbbfp=ffffffff77e77120  bln=22  avl=04  flg=01
  value=512690
 Bind#8
  oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=171 siz=32 off=0
  kxsbbbfp=ffffffff7fff86c4  bln=32  avl=02  flg=09
  value="TC"
Using Tom Kyte print_table procedure I retrieve this row in more readable format:
SQL> exec print_table(q'#select * from sys.user$ where name='SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF'#')
USER#                         : 859
NAME                          : SYS_DZAYDZXFEF1OQRKZUBTDF4UHRF
TYPE#                         : 2
PASSWORD                      :
DATATS#                       : 3
TEMPTS#                       : 2
CTIME                         : 22.07.2015 15:44:08
PTIME                         :
EXPTIME                       :
LTIME                         :
RESOURCE$                     : 1
AUDIT$                        :
DEFROLE                       : 0
DEFGRP#                       :
DEFGRP_SEQ#                   :
ASTATUS                       : 0
LCOUNT                        : 0
DEFSCHCLASS                   :
EXT_USERNAME                  : TC
SPARE1                        : 16
SPARE2                        : 512690
SPARE3                        :
SPARE4                        :
SPARE5                        :
SPARE6                        :
SPARE7                        :
SPARE8                        :
SPARE9                        :
SPARE10                       :
SPARE11                       :
-----------------
We can see that USER$ was populated with new row with obscure "NAME" SYS_%. New row has "TYPE#"=2.
According to a definition of SYS.USER$ table from @?/rdbms/admin/dcore.bsq this's "adjunct" schema:
create table user$                                             /* user table */
( user#         number not null,                   /* user identifier number */
  name          varchar2("M_IDEN") not null,                 /* name of user */
               /* 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym */
Row has EXT_USERNAME same as original schema:
EXT_USERNAME                  : TC
SPARE2 - references to edition object:
SPARE2                        : 512690

SQL> exec print_table('select * from dba_objects where object_id=512690')
OWNER                         : SYS
OBJECT_NAME                   : E1
SUBOBJECT_NAME                :
OBJECT_ID                     : 512690
DATA_OBJECT_ID                :
OBJECT_TYPE                   : EDITION
CREATED                       : 22.07.2015 15:44:08
LAST_DDL_TIME                 : 22.07.2015 15:44:08
TIMESTAMP                     : 2015-07-22:15:44:08
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 64
EDITION_NAME                  :
SHARING                       : NONE
EDITIONABLE                   :
ORACLE_MAINTAINED             : N
-----------------
Second adjunct schema points to E2 edition (some rows skipped for readability):
SQL> exec print_table(q'#select * from sys.user$ where name='SYS_D$8SPB$NEB3PDFCNYRJHBAV7MK'#')
USER#                         : 861
NAME                          : SYS_D$8SPB$NEB3PDFCNYRJHBAV7MK
TYPE#                         : 2
...
EXT_USERNAME                  : TC
SPARE1                        : 16
SPARE2                        : 512691
...

SQL> exec print_table('select * from dba_objects where object_id=512691')
OWNER                         : SYS
OBJECT_NAME                   : E2
SUBOBJECT_NAME                :
OBJECT_ID                     : 512691
DATA_OBJECT_ID                :
OBJECT_TYPE                   : EDITION
CREATED                       : 22.07.2015 15:44:08
LAST_DDL_TIME                 : 22.07.2015 15:44:08
TIMESTAMP                     : 2015-07-22:15:44:08
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 64
EDITION_NAME                  :
SHARING                       : NONE
EDITIONABLE                   :
ORACLE_MAINTAINED             : N
-----------------
Below is the listing of adjunct schemas in one of production database with one edition (we purge old editions periodically).
SQL> select name from sys.user$ where type#=2;

NAME
------------------------------
SYS_CTO$G3UFDV01YXT$0I2IIAM1FW
SYS_#65J#JRLAO7834YSNCQ09MJB0#
SYS_CFV3AQP2WPEDH4WG#BC_OY_529
SYS_MAEDFDRVL4UF6O0_K#2IKFG332
SYS_ZTM78VJZ1NRBT0OZ7CX3QQYWO#
SYS_PBOZ#4GB#AZAEGM2DQ62ODWO1Q
SYS_4OYT_40J6MSQ9HK4L$R5K8ZD8G
SYS_7DT8CD#ARM8HF8LE5K6T7#G91K
SYS_DAL9_OKM$3EL#MH_IA4IYDJ6V8
SYS_7W7SDDSF#H6QO#D8F_CFH6HL5A
SYS_WW9CZFBWQZH45YCIQW9Z8QXDPG
SYS_R0CCNSCA$$67F4F3JASWEJXZMS
SYS___IC1EVBW5MA9VL6BUY958MFEQ
SYS_JY31UD09PBTXAUV#YLDG8ND1X$
SYS_CMD9YMUP#4W3IRHEA99$OA_2$H
SYS_375#5FX937$H68GJ_ZSX5ROSP3
SYS_U5$O#5LBHUX78D5M6C421CM_F#
SYS_FFQSGVBBM97GZCOVA7ZZCXOE6N
SYS_DX#I9P0FLCROTW5_FUQ0TG4L$M
SYS_7CFP28DK1RX$CV#ES#WEKAJ1WR
Sometimes we have 5-6 editions in place. That results in 100-120 additional schemas used by EBR!
Now you know, if you will find a row in user$ with type#=2 and with a obscure name like 'SYS_%' - it's a normal.
At least if you are using Editions.