ITPub博客

首页 > 数据库 > Oracle > [20190510]快速建立执行脚本.txt

[20190510]快速建立执行脚本.txt

原创 Oracle 作者:lfree 时间:2019-05-10 11:55:52 0 删除 编辑

[20190510]快速建立执行脚本.txt


--//上午在测试建立表空间备份时,浪费一点点时间.脚本如下:

$ cat d10.sql

drop tablespace t01 including contents and datafiles;

drop tablespace t02 including contents and datafiles;

drop tablespace t03 including contents and datafiles;

drop tablespace t04 including contents and datafiles;

drop tablespace t05 including contents and datafiles;

drop tablespace t06 including contents and datafiles;

drop tablespace t07 including contents and datafiles;

drop tablespace t08 including contents and datafiles;

drop tablespace t09 including contents and datafiles;

drop tablespace t10 including contents and datafiles;


CREATE TABLESPACE T01 DATAFILE '/mnt/ramdisk/book/T01.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T02 DATAFILE '/mnt/ramdisk/book/T02.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T03 DATAFILE '/mnt/ramdisk/book/T03.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T04 DATAFILE '/mnt/ramdisk/book/T04.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T05 DATAFILE '/mnt/ramdisk/book/T05.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T06 DATAFILE '/mnt/ramdisk/book/T06.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T07 DATAFILE '/mnt/ramdisk/book/T07.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T08 DATAFILE '/mnt/ramdisk/book/T08.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T09 DATAFILE '/mnt/ramdisk/book/T09.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

CREATE TABLESPACE T10 DATAFILE '/mnt/ramdisk/book/T10.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;


create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;

create table t02 tablespace t02 as select rownum id ,to_char(rownum,'FM000000')||lpad('B',26,'B') name from dual connect by level<=1e5;

create table t03 tablespace t03 as select rownum id ,to_char(rownum,'FM000000')||lpad('C',26,'C') name from dual connect by level<=1e5;

create table t04 tablespace t04 as select rownum id ,to_char(rownum,'FM000000')||lpad('D',26,'D') name from dual connect by level<=1e5;

create table t05 tablespace t05 as select rownum id ,to_char(rownum,'FM000000')||lpad('E',26,'E') name from dual connect by level<=1e5;

create table t06 tablespace t06 as select rownum id ,to_char(rownum,'FM000000')||lpad('F',26,'F') name from dual connect by level<=1e5;

create table t07 tablespace t07 as select rownum id ,to_char(rownum,'FM000000')||lpad('G',26,'G') name from dual connect by level<=1e5;

create table t08 tablespace t08 as select rownum id ,to_char(rownum,'FM000000')||lpad('H',26,'H') name from dual connect by level<=1e5;

create table t09 tablespace t09 as select rownum id ,to_char(rownum,'FM000000')||lpad('I',26,'I') name from dual connect by level<=1e5;

create table t10 tablespace t10 as select rownum id ,to_char(rownum,'FM000000')||lpad('J',26,'J') name from dual connect by level<=1e5;


alter system checkpoint;

alter system checkpoint;

alter system checkpoint;


--//实际上脚本很有规律.实际上单独写1个,然后替换参数就ok了.

--//我开始copy and paste,一些地方忘记修改了.浪费许多时间.


$ cat dx.sql

drop tablespace t&1 including contents and datafiles;

CREATE TABLESPACE T&1 DATAFILE '/mnt/ramdisk/book/T&1.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

create table t&1 tablespace t&1 as select rownum id ,to_char(rownum,'FM000000')||lpad('&&2',26,'&&2') name from dual connect by level<=1e5;


SCOTT@book> @ dx 01 A

old   1: drop tablespace t&1 including contents and datafiles

new   1: drop tablespace t01 including contents and datafiles

Tablespace dropped.


old   1: CREATE TABLESPACE T&1 DATAFILE '/mnt/ramdisk/book/T&1.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON

new   1: CREATE TABLESPACE T01 DATAFILE '/mnt/ramdisk/book/T01dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON

Tablespace created.


old   1: create table t&1 tablespace t&1 as select rownum id ,to_char(rownum,'FM000000')||lpad('&&2',26,'&&2') name from dual connect by level<=1e5

new   1: create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5

Table created.


--//这样又快有简单,不容易出错.


--//如何建立1到10个呢?


$ paste -d " " <(seq -f "%02g" 10 ) <((echo A B C D E F G H I J | tr ' ' '\n' )) | xargs -IQ echo @ dx Q

@ dx 01 A

@ dx 02 B

@ dx 03 C

@ dx 04 D

@ dx 05 E

@ dx 06 F

@ dx 07 G

@ dx 08 H

@ dx 09 I

@ dx 10 J


--//执行如下就可以了.

$ paste -d " " <(seq -f "%02g" 10 ) <((echo A B C D E F G H I J | tr ' ' '\n' )) | xargs -IQ echo @ dx Q | sqlplus -s -l scott/book


--//还有一点奇怪的地方是

$ echo $BASH_VERSION

3.2.25(1)-release


$ echo {A..F}| tr ' ' '\n'

A

B

C

D

E

F


--//可以发现可以每行输出1个.

$ paste -d " " <(seq -f "%02g" 6 ) <(echo {A..F}| tr ' ' '\n' )

01 A B C D E F

02

03

04

05

06

--//感觉这个是BUG(我的测试Oracle Linux Server release 5.9,我感觉这个版本bash的问题多多),我在rhel 7 测试没有问题.

--//这样写就没有问题.不知道为什么.

$ paste -d " " <(seq -f "%02g" 6 ) <(echo A B C D E F| tr ' ' '\n' )

01 A

02 B

03 C

04 D

05 E

06 F


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2643900/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2435
  • 访问量
    6215019