1. ran query:
select VIEW_NAME from ALL_VIEWS WHERE (view_name like ‘V_$%’ or view_name like ‘V$%’) order by view_name; and saved to a text file (/tmp/vdollar.txt)
2. make a directory to work in:
mkdir VDOLLAR
3. change to that directory:
cd VDOLLAR
4. use shell to create scripts to login and run
DESC for each line in above file and save to an individual file:
for i in `cat /tmp/vdollar.txt `; do (echo “sqlplus / as sysdba << EOF”; echo desc ‘V\$’${i}’;'; echo “EOF”) > $i.sh; done
5. Now execute all of the scripts:
for i in `ls *.sh`; do sh $i > $i.txt; done
6. used perl to parse out the sqlplus header and footer in each of the files, then catt`ed everything together with the #### separators:
#!/usr/bin/perl
@files=`ls *.sh.txt`;
foreach $file (@files){
chomp $file;
#print “FILE: $file\n”;
($vdollar,undef,undef)=split(’\.’,$file);
open(IN,”$file”) or die “unable to open $file: $!\n”;
open(OUT,”>$vdollar.txt”) or die “unable to open $vdollar.txt: $!\n”;
$inside=’no’;
$lnum=1;
print OUT “V\$$vdollar\n”;
while(<IN>){
#print “File; $file: LINE: $lnum\n”;
$l = $_;
if ( $l =~ /———/ ){
$inside = ‘yes’;
}
elsif ( $l =~ /^$/ && $inside eq ‘yes’ ){
$inside = ‘no’; last;
}
elsif ( $inside eq ‘yes’ ){
print OUT $l;
$lnum++;
}
}
close OUT;
close IN;
# delete all of the zero line files
if ( $lnum == 1 ){
system (”rm -f $vdollar.txt”);
}
}
7. now remove all of the .sh.txt files (used by the perl program):
rm -f *.sh.txt
8. Cat everything together:
for i in `ls *.txt`; do cat $i; echo; echo ‘#######################################################’; echo; done > /tmp/all.txt
