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

分类:操作系统 | 标签: |

相关日志

评论被关闭!