人工移动ORACLE 19C 调优集到另一个数据库

郑全

人工移动ORACLE 19C 调优集到另一个数据库


create directory d1 as '/home/oracle/scripts';
grant read,write on  directory d1 to public;
create user apps identified by Apps1234;
grant dba ,ADMINISTER ANY SQL TUNING SET to apps;

conn apps/Apps1234

create table apps.bigtab as select * from dba_objects;

create table apps.smalltab as select * from all_tables;

DECLARE
     n NUMBER;
BEGIN
     FOR n IN 1..100
     LOOP
         INSERT INTO bigtab SELECT rownum as "id", a.* FROM all_objects a;
         COMMIT;
     END LOOP;
END;

DECLARE
    v_var number;
BEGIN
    FOR n IN 1..6
    LOOP
        select count(*) into v_var from bigtab b, smalltab a;
    END LOOP;
END;


BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name  => 'T_11G_STAGING_TABLE',   schema_name => 'APPS',   db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION);
END;



BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (sqlset_name=> 'sts-003',sqlset_owner=> 'SYS', staging_table_name=> 'T_11G_STAGING_TABLE',staging_schema_owner => 'APPS',db_version=> DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION);
END;


expdp apps/apps1234@db11g DIRECTORY=d1 DUMPFILE=appsst.dmp schemas=apps

[oracle@host01 ~]$ expdp apps/apps1234 DIRECTORY=d1 DUMPFILE=appsst.dmp schemas=apps

Export: Release 11.2.0.4.0 - Production on Mon Jul 3 18:58:16 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "APPS"."SYS_EXPORT_SCHEMA_01":  apps/******** DIRECTORY=d1 DUMPFILE=appsst.dmp schemas=apps
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 820.6 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "APPS"."BIGTAB"                             707.8 MB 6900016 rows
. . exported "APPS"."T_11G_STAGING_TABLE"                48.24 KB       1 rows
. . exported "APPS"."SMALLTAB"                           52.10 KB     104 rows
Master table "APPS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for APPS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scripts/appsst.dmp
Job "APPS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jul 3 18:58:35 2023 elapsed 0 00:00:18



create user apps identified by Apps1234;

grant dba ,ADMINISTER ANY SQL TUNING SET to apps;
create directory dir_sts as '/home/oracle/scripts';
grant read,write on  directory dir_sts to public;


impdp apps/apps1234@pdbprod1 DIRECTORY=dir_sts DUMPFILE=appsst.dmp


[oracle@host01 scripts]$ impdp apps/Apps1234@pdbprod1 DIRECTORY=dir_sts DUMPFILE=appsst.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 3 19:28:50 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "APPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "APPS"."SYS_IMPORT_FULL_01":  apps/********@pdbprod1 DIRECTORY=dir_sts DUMPFILE=appsst.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"APPS" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "APPS"."BIGTAB"                             707.8 MB 6900016 rows
. . imported "APPS"."T_11G_STAGING_TABLE"                48.24 KB       1 rows
. . imported "APPS"."SMALLTAB"                           52.10 KB     104 rows
Job "APPS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Jul 3 19:29:27 2023 elapsed 0 00:00:35


在测试数据库上，执行将 UNPACK_STGTAB_SQLSETSQL 调整集从中转表复制到数据库中的过程。


PROCEDURE UNPACK_STGTAB_SQLSET
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME                    VARCHAR2                IN     DEFAULT
SQLSET_OWNER                   VARCHAR2                IN     DEFAULT
REPLACE                        BOOLEAN                 IN
STAGING_TABLE_NAME             VARCHAR2                IN
STAGING_SCHEMA_OWNER           VARCHAR2                IN     DEFAULT



conn apps/Apps1234@pdbprod1 as sysdba
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (sqlset_name=> '%',replace=> true,staging_table_name => 'T_11G_STAGING_TABLE',STAGING_SCHEMA_OWNER=>'APPS');
END;


SYS@pdbprod1> BEGIN
  2    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (sqlset_name=> '%',replace=> true,staging_table_name => 'T_11G_STAGING_TABLE',STAGING_SCHEMA_OWNER=>'APPS');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYS@pdbprod1>

参考 Doc ID 751068.1