drop tablespace tbssga including contents and datafiles;

create tablespace tbssga datafile 'tbssga12.dbf' size 20m;

drop tablespace mytemp including contents and datafiles;

create temporary tablespace mytemp tempfile 'temp12.f' size 40m reuse;

drop user amm cascade;

create user amm identified by amm default tablespace tbssga temporary tablespace mytemp;

grant connect,resource,dba to amm;

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

connect amm/amm

drop table tabsga purge;

create table tabsga(a number, b number) tablespace tbssga;

begin
  for i in 1..100000 loop
    insert into tabsga values (i, i);
  end loop;
end;
/

commit;

alter table tabsga parallel 64;

create or replace procedure testpga( psize number ) as
begin
declare
  TYPE nAllotment_tabtyp    IS TABLE OF char(2048) INDEX BY BINARY_INTEGER;
  myarray nAllotment_tabtyp;
begin
  for i in 1..psize loop
    myarray(i) := to_char(i);
  end loop;
end;
end;
/

show errors

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

connect / as sysdba

alter system set "_PX_use_large_pool" = TRUE SCOPE=SPFILE;

alter system set "_memory_broker_stat_interval" = 5 SCOPE=SPFILE;

alter system set "_memory_management_tracing" = 31 SCOPE=SPFILE;

alter system set "parallel_execution_message_size" = 36864 SCOPE=SPFILE;

alter system set "parallel_max_servers" = 200 SCOPE=SPFILE;

alter system set "parallel_adaptive_multi_user" = FALSE SCOPE=SPFILE;

alter system set "processes" = 200 SCOPE=SPFILE;

alter system set "pga_aggregate_target" = 0 SCOPE=SPFILE;

alter system set "sga_target" = 0 SCOPE=SPFILE;

alter system set "memory_target" = 280M SCOPE=SPFILE;

shutdown immediate;

startup;