[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;

--//T&1..dbf 注意2个点.

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