Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table all_data
(
 kind varchar2(10)
,time_period date
,amount number
)
insert into all_data
values('peter',sysdate,1000)
1 rows affected

insert into all_data
values('peter',sysdate+1,2000)
1 rows affected

insert into all_data
values('maria',sysdate,1000)
1 rows affected

insert into all_data
values('eric',sysdate,1000)
1 rows affected

declare
  type array_t is table of varchar2(4000);
  array array_t := array_t();
  lo_stmt varchar2(2000);
begin
  select distinct 'create table '||kind||'_data '||
    'as '||
    'select kind,time_period,amount '||
    'from all_data '||
    'where kind = '''||kind||''''
    bulk collect into array
    from all_data;
  
  for i in 1..array.count loop
    dbms_output.put_line(array(i));
    execute immediate array(i);
  end loop;
end;
/
1 rows affected

dbms_output:
create table peter_data as select kind,time_period,amount from all_data where kind = 'peter'
create table maria_data as select kind,time_period,amount from all_data where kind = 'maria'
create table eric_data as select kind,time_period,amount from all_data where kind = 'eric'

select * from Peter_data


KIND
TIME_PERIOD
AMOUNT


peter
20-OCT-20
1000

peter
21-OCT-20
2000


select * from maria_data


KIND
TIME_PERIOD
AMOUNT


maria
20-OCT-20
1000


select * from eric_data


KIND
TIME_PERIOD
AMOUNT


eric
20-OCT-20
1000