#!/usr/bin/perl
open(FL,"1")||die "can't open file";
while(<FL>){
s/$_/;/ if /tablespace/i;
print $_;
}
print "\n";
2.以/tablespace/开头行
...
s/$_/;/ if /^tablespace/i;
...
3.tablespace大小写并且行前含有空格情况
#!/usr/bin/perl
open(FL,"1")||die "can't open file";
while(<FL>){
s/$_/;/ if /\s*tablespace/i;
print $_;
}
close(FS,"1");
print "\n";
---------------------------------------------------------------------
功能:遇到create table 情况将后面的tablespace替换成指定的表空间名,遇到create index也是如些;
#!/usr/bin/perl
open(FL,"3")||die "can't open the file\n";
while(<FL>)
{
if($flag==0) {
if(/create\s+\w*\.*table/i) {
s/tablespace\s+\w+/TABLESPACE data/; # tablespace 替换成指定DATA
if(/;/) {$flag=0;} #如果遇到分号 $flag=0,
else {$flag=1;} # 否则$flag=1;
print $_; } #输出$_;
elsif(/create\s+\w*\.*index/i) { #否则/create index/ tablespace 替换成指定IND
s/tablespace\s+\w+/TABLESPACE ind/; #如果遇到分号$flag=0,
if(/;/){$flag=0;} #否则$flag=2;
else{$flag=2;}
print $_; } #输出$_;
else {print $_;}
}
elsif($flag==1){
s/tablespace\s+\w+/TABLESPACE data/i;
if(/;/){$flag=0;}
else{$flag=1;}
print $_; }
elsif($flag==2){
s/tablespace\s+\w+/TABLESPACE ind/i;
if(/;/){$flag=0;}
else{$flag=2;}
print $_;
}
}
close(FL);
------------------------将文本中的DDL语句的属主要表空间抽出,用perl正则表式2句话就能搞定了,牛---
perl -ne'print "OWNER:".$1."\n" if /create .* (\w+)\./i;' 3 |sort -u
perl -ne 'print "TABLESPACE:".$1."\n" if /\s*tablespace\s+(\w+)\s*;/i' 3 |sort -u
------------------------根据表/索引,属主,来替换相应的表空间
[oracle@localhost ~]$ cat t4
#!/usr/bin/perl
$/=';';
open(FL,"4");
my @state=<FL>;
close(FL);
foreach $field (@state){
if ($field =~/create.*index/ig){
if ($field =~ /NGCRM_COMM\./ig) {
$field=~s/tablespace\s+\w+/TABLESPACE COMM_IND/ig;
}
elsif($field =~ /NGCRM_FS\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE XX_CRM_IND/ig;
}
elsif($field =~ /GDHSC\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE GD_HSC_IND/ig;
}
elsif($field =~ /FSHSC\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_HSC_IND/ig;
}
elsif($field =~ /FSIB\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_IB_IND/ig;
}
elsif($field =~ /FSIBHIS\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_IBHIS_IND/ig;
}
}
elsif ($field =~ /\s*create\s+table/ig)
{
if ($field =~ /NGCRM_COMM\./ig) {
$field=~s/tablespace\s+\w+/TABLESPACE COMM_DATA/ig;
}
elsif($field =~ /NGCRM_FS\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE XX_CRM_DATA/ig;
}
elsif($field =~ /GDHSC\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE GD_HSC_DATA/ig;
}
elsif($field =~ /FSHSC\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_HSC_DATA/ig;
}
elsif($field =~ /FSIB\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_IB_DATA/ig;
}
elsif($field =~ /FSIBHIS\./ig){
$field=~s/tablespace\s+\w+/TABLESPACE FS_IBHIS_DATA/ig;
}
}
print $field."\n";
}
[oracle@localhost ~]$
-----------------------------------------------------