Hybris95's questions

我有一个用户负责在自己的模式之外的其他模式上部署 DDL。
该用户可以在这些模式上创建一个表，它还可以在这些模式上创建序列。

但是当我尝试在这些模式上创建一个带有 GENERATED IDENTITY 列的表时，
用户会遇到一个ORA-01031: privileges insuffisants

当这个用户在自己的模式上尝试相同的创建表时，他可以正确地做到这一点。

该用户被授予以下权限：

GRANT CREATE ANY SEQUENCE TO ddl_role;
GRANT CREATE ANY TABLE TO ddl_role;

这是执行的查询示例：

CREATE TABLE "sch1"."tab1"
   (        "col1" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE)
TABLESPACE "sch1_data";

以下查询有效：

CREATE TABLE "sch1"."tab1"
    (        "col1" NUMBER )
TABLESPACE "sch1_data";

CREATE SEQUENCE "sch1"."seq1";

参考：
https ://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

我有 2 个登录名：

• LoginA：角色 dbcreator + 连接任何数据库
• LoginB：角色系统管理员

我有 2 个数据库：

• dbA：每个人都拥有它的所有权利
• dbB：只有 LoginB 对其拥有权限，LoginA 只是该数据库的所有者，但没有更多

我想知道是否可以从 dbA 的存储过程在 dbB 中创建一个用户（链接到 LoginA）。
考虑到这个存储过程必须由 LoginA 执行。
也许某种“EXECUTE AS”机制可以提供帮助？
我也有关于如何在存储过程中使用“USE [dbB]”的问题。

该解决方案可以进行一些权限修改，
但这些不能赋予 securityadmin 或 sysadmin 角色。

这是目前所做的，但显然还有很多调试和功能要做，但我想做的精神在这里：

CREATE PROCEDURE [dbB_UserAfterRestore] 
AS
BEGIN
    SET NOCOUNT ON;

    USE [dbB]
    CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA]
    ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA]
    ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA]
END
GO

我有一个相对简单的查询，它在表中选择数据。

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

这会生成以下计划：

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

在这张表上，我有一个包含所有这 3 列的 UNIQUE INDEX。
但是优化器更喜欢在表上进行 FULL SCAN 而不是使用 INDEX。

事情变得奇怪的地方是，当我在没有绑定但直接替换查询中的值的情况下尝试此查询时。
当然，SQL_ID 发生了变化，但这次优化器决定使用 INDEX 并从 1104 变为 6（这当然是一个很好的优化）。

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

统计数据是最新的，此表中没有直方图。

有什么方法可以让优化器在不修改查询的情况下使用该索引？

我认为优化器认为有 3 个 NULL 的可能性可能会发生，所以这使他无法使用索引，对吗？

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)