1.问题
在做在线重定义的时候,出现如下提示
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-12090: cannot online redefine table "usernam1"."test1"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 1
2.查看错误原因
通过排除非,最后定位在FGA/RLS;
$oerr ora 12090
12090, 00000, "cannot online redefine table \"%s\".\"%s\""
// *Cause: An attempt was made to online redefine a table that is either a
// clustered table, AQ table, temporary table, IOT overflow table
// or table with FGA/RLS enabled.
// *Action: Do not attempt to online redefine a table that is a
// clustered table, AQ table, temporary table, IOT overflow table
// or table with FGA/RLS enabled.
3.解决
步骤:
1.
select * from dba_policies
where object_name='test1'
2.保存VPD的定义
3.DROP
begin
DBMS_RLS.DROP_POLICY (
object_schema =>'usernam1',
object_name =>'',
policy_name =>'KFCPOLY');
end;
/
3.重定义完成后
再建回去--类似如下
begin
DBMS_RLS.ADD_POLICY (
object_schema =>'usernam1',
object_name =>'test1',
policy_name =>'KFCPOLY',
function_schema =>'usernam1',
policy_function =>'Fn_GetPolicy_areacode',
statement_types=>'',
update_check =>true,
enable =>true,
static_policy=>false,
policy_type =>dbms_rls.CONTEXT_SENSITIVE);
end;
4.解决
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
在做在线重定义的时候,出现如下提示
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-12090: cannot online redefine table "usernam1"."test1"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 1
2.查看错误原因
通过排除非,最后定位在FGA/RLS;
$oerr ora 12090
12090, 00000, "cannot online redefine table \"%s\".\"%s\""
// *Cause: An attempt was made to online redefine a table that is either a
// clustered table, AQ table, temporary table, IOT overflow table
// or table with FGA/RLS enabled.
// *Action: Do not attempt to online redefine a table that is a
// clustered table, AQ table, temporary table, IOT overflow table
// or table with FGA/RLS enabled.
3.解决
步骤:
1.
select * from dba_policies
where object_name='test1'
2.保存VPD的定义
3.DROP
begin
DBMS_RLS.DROP_POLICY (
object_schema =>'usernam1',
object_name =>'',
policy_name =>'KFCPOLY');
end;
/
3.重定义完成后
再建回去--类似如下
begin
DBMS_RLS.ADD_POLICY (
object_schema =>'usernam1',
object_name =>'test1',
policy_name =>'KFCPOLY',
function_schema =>'usernam1',
policy_function =>'Fn_GetPolicy_areacode',
statement_types=>'',
update_check =>true,
enable =>true,
static_policy=>false,
policy_type =>dbms_rls.CONTEXT_SENSITIVE);
end;
4.解决
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('usernam1', 'test1', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.