Oracle Database 12.2.0
RHEL 7.4
Hi Team,
Following the query from create tablespace for partitions , I have created a script with the following requirements:
1. create a tablespace for a specific month, for example JAN-2019
2. in the script I would alter the tablespace so that I add another datafile (OMF)
3. alter the current table so that it would have default tablespace to the newly created tablespace. Hence, January's data would be loaded in the new tablespace
This way it would also help prevent fragmentation.
d=$(date "+%d%m%Y")
read n < fileDATA.txt
echo $n
while [ ${n} -lt 08 ];
do
n=$(printf "%02d" "$((${n} + 1))")
sqlplus DWHMOBILE/AAA << EOF
create tablespace DW_DATA_2018_${n} datafile '+DATA/DWHDV2/DATAFILE/<OMF file>' size 1g autoextend on next 100m maxsize 31g;
alter tablespace DW_DATA_2018_${n} add datafile '+DATA/DWHDV2/DATAFILE/<OMF file>' size 1g autoextend on next 100m maxsize 31g
alter table mobile_data move tablespace DW_DATA_2018_${n};
exit;
EOF
echo ${n} > fileDATA.txt
done
When I execute the script, I am getting:
SQL> alter table mobile_data_test move tablespace DW_DATA_2018_01
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
Please help,
Joe