Hello.
I try use Edition-Base Redifinition (EBR) and I want set column unused as described in EBR documentation when executed DML operation in other session.
When I execute alter table ... set unused collumn ... online (Oracle 12c) then recive error code ora-04023.
The Database documentation to alter table describe
ONLINE
Specify ONLINE to indicate that DML operations on the table will be allowed while marking the column or columns UNUSED.
Do You know a solution for resolve ora-04023 error or it's oracle bug?
Thanks.
Test example bellow.
SELECT banner FROM v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
CREATE TABLE test_table
( col_1 NUMBER(2)
, col_2 VARCHAR2(10)
);
CREATE OR REPLACE EDITIONING VIEW v#test_table_edition
AS
SELECT col_1
, col_2
FROM test_table;
CREATE OR REPLACE NONEDITIONABLE VIEW v#test_table_nonedition
AS
SELECT col_1
, col_2
FROM test_table;
INSERT INTO v#test_table_edition
VALUES (1, 'Test 1');
COMMIT;
INSERT INTO v#test_table_nonedition
VALUES (2, 'Test 2');
COMMIT;
SELECT * FROM v#test_table_edition;
COL_1 COL_2
---------- ----------
1 Test 1
2 Test 2
SELECT * FROM v#test_table_nonedition;
COL_1 COL_2
---------- ----------
1 Test 1
2 Test 2
ALTER TABLE test_table ADD (col_3 NUMBER);
ALTER TABLE test_table ADD (col_4 NUMBER);
SELECT t.column_name, t.data_type
FROM user_tab_columns t
WHERE t.TABLE_NAME = 'TEST_TABLE'
ORDER BY t.COLUMN_ID;
COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------
COL_1 NUMBER
COL_2 VARCHAR2
COL_3 NUMBER
COL_4 NUMBER
ALTER TABLE test_table SET UNUSED COLUMN col_4;
COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------
COL_1 NUMBER
COL_2 VARCHAR2
COL_3 NUMBER
SELECT * FROM v#test_table_edition;
COL_1 COL_2
---------- ----------
1 Test 1
2 Test 2
SELECT * FROM v#test_table_nonedition;
COL_1 COL_2
---------- ----------
1 Test 1
2 Test 2
ALTER TABLE test_table SET UNUSED COLUMN col_3 INVALIDATE ONLINE;
Table altered.
SELECT t.column_name, t.data_type
FROM user_tab_columns t
WHERE t.TABLE_NAME = 'TEST_TABLE'
ORDER BY t.COLUMN_ID;
COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------
COL_1 NUMBER
COL_2 VARCHAR2
SELECT * FROM v#test_table_edition;
ERROR at line 1:
ORA-04023: Object SELECT * FROM v#test_table_edition could not be validated or authorized
SELECT * FROM v#test_table_nonedition;
ERROR at line 1:
ORA-04023: Object SELECT * FROM v#test_table_nonedition could not be validated or authorized
SELECT object_name, object_type
FROM user_objects
WHERE status='INVALID';
no rows selected