如何从不同的数据库复制或更新表的索引和约束？

Question

我们有一个生产Oracle 11g数据库，该数据库被一个试图集成到内部web应用程序的开发人员丢弃。他说他忘记了他连接到生产数据库，并运行他的命令php artisan migrate:fresh，我搜索它，它删除所有表并重新创建它。

然后，他尝试使用flashback命令恢复所有表。所有内容都已恢复，但索引、键和约束被重命名。

是否可以将备份数据库的表结构(索引、键、约束)复制/更新到受影响的数据库？

Answer 1

我认为没有直接的方法来获得所需的约束和索引的名称，但是的，您可以通过一些手动工作来实现，如下所示。

首先，您可以使用以下命令更改约束名称：

alter table <table_name> rename constraint <constarint_name> to <new_constarint_name>;

同样，您可以使用以下命令更改索引名称：

ALTER INDEX <index_name> RENAME TO <new_index_name>;

在您的例子中，您需要一些方法来链接不好的(约束/索引)名称和正确的名称。然后用正确的名称重命名约束/索引。要实现此目的，您可以尝试以下步骤。(我给出了约束的例子，你也可以对索引实现同样的效果)

• Step 1

使用以下查询从production DB获取约束名称及其数据，并使用这些数据在Backup DB中创建一个表。(您可以对其使用SQL Loader或External table。)将此表的名称指定为PROD_DB_CONS

SELECT
    C.TABLE_NAME,
    C.CONSTRAINT_NAME,
    C.CONSTRAINT_TYPE,
    LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP(
            ORDER BY
                COLUMN_NAME
        ) CONS_COLUMNS
FROM
    USER_CONSTRAINTS C
    JOIN USER_CONS_COLUMNS CC ON ( C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME )
GROUP BY
    C.TABLE_NAME,
    C.CONSTRAINT_NAME,
    C.CONSTRAINT_TYPE;

• Step 2

使用以下查询在backup DB中创建相同类型的表：

CREATE TABLE BACKUP_DB_CONS AS
SELECT
    C.TABLE_NAME,
    C.CONSTRAINT_NAME,
    C.CONSTRAINT_TYPE,
    LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP(
            ORDER BY
                COLUMN_NAME
        ) CONS_COLUMNS
FROM
    USER_CONSTRAINTS C
    JOIN USER_CONS_COLUMNS CC ON ( C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME )
GROUP BY
    C.TABLE_NAME,
    C.CONSTRAINT_NAME,
    C.CONSTRAINT_TYPE;

• Step 3

在Backup DB中执行以下查询，该查询将生成一系列命令来更改生产数据库的所有约束的名称

SELECT
    'ALTER TABLE '
    || B.TABLE_NAME
    || ' RENAME CONSTRAINT '
    || P.CONSTRAINT_NAME
    || ' TO '
    || B.CONSTRAINT_NAME
    || ';'
FROM
    BACKUP_DB_CONS B
    JOIN PROD_DB_CONS P ON ( B.TABLE_NAME = P.TABLE_NAME
                             AND B.CONS_COLUMNS = P.CONS_COLUMNS );

• Step 4

复制所有命令并在production DB中执行所有命令。

干杯！！