[20190510]快速建立執行指令碼.txt

[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