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;
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;