oracle自动创建表空间tablespace
2014-04-29
为了让oracle自动创建表空间,我们可以使用计划任务,前提是要保证硬盘有足够的空间。
linux实例:
1、添加计划任务
crontab -e
0 4 * * * /home/oracle/create_ts.sh
2、创建脚本
vi /home/oracle/create_ts.sh
#!/bin/bash
. ~/.bash_profile
today=`date +%Y%m%d`
tomorrow=`date -d '1 day' +%Y%m%d`
gpsdata_src=GPSDATA_TS
gpsdata_dst=GPSDATA_TS_${today}
gpsdata_ts1=/data1/oracle/gpsdata_ts_${tomorrow}_1.dbf
gpsdata_ts2=/data1/oracle/gpsdata_ts_${tomorrow}_2.dbf
sqlplus test/abcd123456 << EOF
alter tablespace $gpsdata_src rename to $gpsdata_dst;
CREATE TABLESPACE $gpsdata_src DATAFILE '$gpsdata_ts1' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;
alter TABLESPACE $gpsdata_src add DATAFILE '$gpsdata_ts2' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;
quit;
EOF
windows实例:
1、控制面板添加计划任务
2、notepad create_ts.bat
@echo off
set /a today=%date:~0,4%%date:~5,2%%date:~8,2%
set /a tomorrow=%date:~0,4%%date:~5,2%%date:~8,2%+1
set gpsdata_src=GPSDATA_TS
set gpsdata_dst=GPSDATA_TS_%today%
set gpsdata_ts1=e:\data1\oracle\gpsdata_ts_${tomorrow}_1.dbf
set gpsdata_ts2=e:\data1\oracle\gpsdata_ts_${tomorrow}_2.dbf
echo alter tablespace %gpsdata_src% rename to %gpsdata_dst%;|sqlplus user/123456
echo CREATE TABLESPACE %gpsdata_src% DATAFILE '%gpsdata_ts1%' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;|sqlplus user/123456
echo alter TABLESPACE %gpsdata_src% add DATAFILE '%gpsdata_ts2%' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;|sqlplus user/123456
分类:操作系统 | 标签: shell |
相关日志