根据源库表空间实际使用创建表空间脚本
经常迁移数据库,创建表空间还需要继续创建几个文件,今写个脚本方便以后操作。
经常迁移数据库,创建表空间还需要继续创建几个文件,今写个脚本方便以后操作。
点击(此处)折叠或打开
- declare
- ---根据表空间实际使用自动生成创建语句;
- ---如需要修改数据文件创建路径,修改v_path参数即可;
- --2017.4.14 19:18
- ---Edit by Well
- v_path varchar2(100) := '''/data/ciq/';
- i integer;
- j integer;
- v_tbname varchar2(50);
- v_tbused varchar2(50);
- cursor c_tb is
- SELECT d.tablespace_name,
- ceil(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 / 1024) as usedgb
- FROM sys.dba_tablespaces d,
- (select tablespace_name, sum(bytes) bytes
- from dba_data_files
- group by tablespace_name) a,
- (select tablespace_name, sum(bytes) bytes
- from dba_free_space
- group by tablespace_name) f
- WHERE d.tablespace_name = a.tablespace_name(+)
- AND d.tablespace_name = f.tablespace_name(+)
- AND NOT (d.extent_management like 'LOCAL' AND
- d.contents like 'TEMPORARY')
- and d.TABLESPACE_NAME not in
- ('SYSAUX', 'UNDOTBS1', 'USERS', 'SYSTEM');
- begin
- for v_tb in c_tb loop
- v_tbname := v_tb.tablespace_name;
- v_tbused := v_tb.usedgb;
- i := ceil(v_tbused / 30);
- DBMS_OUTPUT.ENABLE(buffer_size => null);
- j := 1;
- dbms_output.put_line('create tablespace ' || v_tbname || ' datafile ' ||
- v_path || v_tbname || '_' || j ||
- '.dbf size 100M autoextend on next 1024M'';');
- for j in 2 .. i loop
- dbms_output.put_line('alter tablespace ' || v_tbname ||
- ' add datafile ' || v_path || v_tbname || '_' || j ||
- '.dbf size 100M autoextend on next 1024M'';');
- end loop;
- end loop;
- dbms_output.put_line('---for end---');
- end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-2137313/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29519108/viewspace-2137313/