CTAS Method to Create Table

##Shell Script##


export ORACLE_SID=joshi
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4

sqlplus -s '/ as sysdba' << EOF

set lines 200
set pages 50
Set echo on
set serveroutput on
set timing on
set time on
spool /home/oracle/CTAS_TABLE.log

alter session set db_file_multiblock_read_count=128;
alter session set sort_multiblock_read_count=128;
alter session set parallel_force_local=true;

exec dbms_application_info.set_module ('CTAS_TABLE_TMP',null);

CREATE TABLE SCOTT.TMP AS SELECT * FROM HR.DEPT;


spool off
exit;
EOF


#########


#Sample CTAS statement:
CREATE TABLE emp2 AS SELECT * FROM emp;

#Specifying a tablespace:
CREATE TABLE emp3 TABLESPACE users AS SELECT * FROM emp;

#Parallel CTAS with nologging for faster performance
CREATE TABLE emp4 NOLOGGING PARALLEL 4 AS SELECT * FROM emp;