DBA Hub

📋Steps in this guide1/2

Edition-Based Redefinition Enhancements in Oracle Database 12c Release 1 (12.1)

Learn how to create non-editionable objects in edition-enabled schema.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

CREATE object

Create a test user with editioning enabled. The statement for editionable object types has been amended to allow the editionable status of the object to be controlled explicitly. The following code creates three procedures. Notice that is the default when creating an editionable object.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CONN sys@pdb1 AS SYSDBA

CREATE USER edition_test1 IDENTIFIED BY edition_test1 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE PROCEDURE TO edition_test1;

ALTER USER edition_test1 ENABLE EDITIONS;

CONN edition_test1/edition_test1@pdb1

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE EDITIONABLE PROCEDURE proc2 AS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE NONEDITIONABLE PROCEDURE proc3 AS
BEGIN
  NULL;
END;
/

COLUMN object_name FORMAT A20
COLUMN editionable FORMAT A15

SELECT object_name, editionable
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          EDITIONABLE
-------------------- ---------------
PROC1                Y
PROC2                Y
PROC3                N

3 rows selected.

SQL>
2

ALTER object

The statement for editionable object types have be amended to allow their editionable status to be changed. This is only possible prior to editioning being enabled on the user. Once editioning has been enabled, the editionable status of the object is fixed. Create a test user that does not have editioning enabled. Create the three test procedures in this schema. Switch the editionable status of and . Enable editioning for the user. Trying to amend the editionable status of the objects now results in an error. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
CONN sys@pdb1 AS SYSDBA

CREATE USER edition_test2 IDENTIFIED BY edition_test2 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE PROCEDURE TO edition_test2;

CONN edition_test2/edition_test2@pdb1

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE EDITIONABLE PROCEDURE proc2 AS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE NONEDITIONABLE PROCEDURE proc3 AS
BEGIN
  NULL;
END;
/

COLUMN object_name FORMAT A20
COLUMN editionable FORMAT A15

SELECT object_name, editionable
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          EDITIONABLE
-------------------- ---------------
PROC1                Y
PROC2                Y
PROC3                N

3 rows selected.

SQL>

ALTER PROCEDURE proc2 NONEDITIONABLE;
ALTER PROCEDURE proc3 EDITIONABLE;

COLUMN object_name FORMAT A20
COLUMN editionable FORMAT A15

SELECT object_name, editionable
FROM   user_objects
ORDER BY object_name;

OBJECT_NAME          EDITIONABLE
-------------------- ---------------
PROC1                Y
PROC2                N
PROC3                Y

3 rows selected.

SQL>

CONN sys@pdb1 AS SYSDBA

ALTER USER edition_test2 ENABLE EDITIONS;

SQL> CONN edition_test2/edition_test2@pdb1
Connected.
SQL> ALTER PROCEDURE proc2 EDITIONABLE;
ALTER PROCEDURE proc2 EDITIONABLE
*
ERROR at line 1:
ORA-38825: The EDITIONABLE property of an editioned object cannot be altered.


SQL> ALTER PROCEDURE proc3 NONEDITIONABLE;
ALTER PROCEDURE proc3 NONEDITIONABLE
*
ERROR at line 1:
ORA-38825: The EDITIONABLE property of an editioned object cannot be altered.


SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!