oracle schema and tablespace for liferay portal setup

prompt 'creating tablespace COMPETENCY_DATAand COMPETENCY_IDX'

 CREATE TABLESPACE "COMPETENCY_DATA" DATAFILE 

  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\COMPETENCY_DATA01.DBF' SIZE 5242880

  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

  

  CREATE TABLESPACE "COMPETENCY_IDX" DATAFILE 

  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\COMPETENCY_IDX01.DBF' SIZE 5242880

  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

prompt 'creating LPORTAL schema'     

drop user lportal cascade;

accept l_portalpwd prompt "LPORTAL Password : "

create user lportal identified by &l_portalpwd;

prompt 'granting privileges to the LPORTAL schema'

grant resource, connect, dba to lportal;

MOVING TABLESPACE:

declare

l_sqltext varchar2(500);

begin

for i in (select owner, table_name, tablespace_name from dba_tables where owner in  ('LPORTAL') and tablespace_name ='USERS') loop

l_sqltext := 'ALTER TABLE '||i.OWNER||'.'|| i.TABLE_NAME|| ' MOVE TABLESPACE COMPETENCY_DATA';

execute immediate l_sqltext;

end loop;

end;

/

declare

l_sqltext varchar2(500);

begin

for i in (select owner, index_name from dba_indexes where TABLE_OWNER in ('LPORTAL') AND index_type = 'NORMAL'  AND TABLESPACE_NAME = 'USERS') loop

l_sqltext := 'ALTER INDEX '||i.OWNER||'.'|| i.INDEX_NAME|| ' REBUILD TABLESPACE COMPETENCY_IDX';

execute immediate l_sqltext;

end loop;

end;

/