Running 11g on AIX Power 7
I'm refreshing a schema and before I import the data I drop the data.dbf and index.dbf files and create new blank .dbf files.
This is the way the dba before me did things, and I've not yet found a different way.
Sometimes this decreases the space used on the disk but not always. Am I missing a step after dropping the tablespace or is there a better way?
this is the script I use to drop the tablespace....
. /home/oracle/.profile
#jobname="${fromschema}_TO_${toschema}_$(date +%m%d%Y%T)"
#logname="${fromschema}_TO_${toschema}_$(date +%m%d%Y%T).log"
#change the name of the database in 2 lines
for tblspace_up in TEST
do
tblspace_lower=$(echo $tblspace_up | tr '[:upper:]' '[:lower:]')
sqlplus / as sysdba << EOF #>
spool /home/oracle/scripts/drop_test_tablespace.log
drop tablespace "${tblspace_up}_DATA" including contents and datafiles cascade constraints;
drop tablespace "${tblspace_up}_INDEX" including contents and datafiles cascade constraints;
spool off
quit
EOF
done