oracle表空间(tablespace)的增删改查(create/drop/rename,move/select)

2013-03-05

oracle维护中经常会对表空间进行操作,整理了一下tablespace的增删改查操作(create/drop/rename,move/select)

1、create tablespace
#!/bin/bash

dts_src=DATA_TS

data_ts1=/data1/oracle/data_201303_ts1.dbf
data_ts2=/data1/oracle/data_201303_ts2.dbf

sqlplus user01/pw123456 << EOF
CREATE TABLESPACE $dts_src DATAFILE '$data_ts1' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED BLOCKSIZE 16k;
alter TABLESPACE $dts_src add DATAFILE '$data_ts2' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;
quit;
EOF

2、drop tablespace

如果tablespace中包含table,需要先进行drop table,为了drop table ,你需要查询tablespace中包含哪些table:
col SEGMENT_NAME format a30
select distinct SEGMENT_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='DATA_TS' order by SEGMENT_NAME;
假如tablename是：tablename_201202
你也可以先truncate table，再drop table.

#!/bin/bash

. ~/.bash_profile

#create drop sql
#select 'drop table '||tname||' purge;' from tab where tname like '%20110%' order by tname;

ym=201202

sqlplus user01/pw123456 << EOF
truncate table tablename_$ym;
drop table tablename_$ym purge;
DROP TABLESPACE DATA_TS_$ym INCLUDING CONTENTS AND DATAFILES;
quit;
EOF

3、重命名表空间

#!/bin/bash

dts_src=DATA_TS
dts_dst=DATA_TS_201302
data_ts1=/data1/oracle/data_201303_ts1.dbf
data_ts2=/data1/oracle/data_201303_ts2.dbf

sqlplus user01/pw123456 << EOF
alter tablespace $dts_src rename to $dts_dst;
quit;
EOF

4、改变表空间数据文件存放位置

#!/bin/bash

. ~/.bash_profile

ts=DATA_TS_201207

src1=/data3/oracle/data_ts_201207_1.dbf
dst1=/datac3/oracle/data_ts_201207_1.dbf

src2=/data3/oracle/data_ts_201207_2.dbf
dst2=/datac3/oracle/data_ts_201207_2.dbf

sqlplus user01/pw123456 << EOF
set hea off
alter tablespace $ts read only;
alter tablespace $ts offline;
!mv $src1 $dst1
!mv $src2 $dst2
alter database rename file '$src1' to '$dst1';
alter database rename file '$src2' to '$dst2';
alter tablespace $ts online;
alter tablespace $ts read write;
quit;
EOF

5、改变数据文件存储参数

alter database datafile '/data2/oracle/view_data02.dbf' AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;

6、查询表空间信息

col name format a50
set linesize 200
select a.name,b.name from v$tablespace a ,v$datafile b where a.TS#=b.TS#;
col file_name format a50
select TABLESPACE_NAME,FILE_NAME from dba_data_files order by TABLESPACE_NAME;

get tablespace_name
select distinct(TABLESPACE_NAME) from dba_data_files order by tablespace_name;

tablespace use space
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';

分类：数据库 | 标签： |

相关日志

评论被关闭！