sql/shell looping question

[Roger Baptiste]

Hi,

I am trying to script the method of adding data files to a tablespace using shell script and sql. for eg:
while read -r tspace datafile filesize
do
sqlplus internal < Alter tablespace .....
exit
EOF
done
The problem with this method is for every data file added, the script will login to sqlplus. This will be inefficient when lot of datafiles are added. Is there a way where i can keep the looping method but have only one sql login connection?

thanks
Roger

[James A. Donovan]

how about....

while read -r tspace datafile filesize
do
echo "Alter tablespace ..... " >>myscript.sql
done
sqlplus internal @myscript.sql

[Brian Crabtree]

Just a thought, but you might be better off keeping it as a single datafile rather than scripting the entire thing at once. The overhead is high, but it would give you a change to verify that there is enough space to add the datafile to the disk as well, which might cut-down on unexpected errors.

Brian

[Yogeeraj_1]

hi roger,

a few ideas i can think of:
try
===============================
#!/bin/sh
TBS=tbs
DF=/u01/oracle/oradata/file
sqlplus yd/yd@dev <
declare
st varchar2(100);
begin
for c1 in (select rownum from all_objects where rownum < 5) loop
st:='alter tablespace $TBS'||c1.rownum||' add datafile $DF'||c1.rownum;
dbms_output.put_line(st);
execute immediate st;
dbms_output.put_line('Modified.');
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
eof
==============================================


hope this helps!

regards
Yogeeraj

[Yogeeraj_1]

sorry for the garbled output. attached the demo script.

regards
Yogeeraj

[twang]

Totally agree with Jim, in my opinion, I will build the sql script first, and then batch process it.

[JJ_4]

Have your script "addem" :

while read -r tspace datafile filesize
do
echo "Alter tablespace ..... "
done

then run your script and pipe the output :

sh addem | sqlplus internal

(I don't know sqlplus at all but most other db vendor command line utilities take stuff froms stdin.

[bob hollis]

I guess my question would be "why do you want to do this?"
It's really not good to have a large number of datafiles - there are limits and each one will need a process to connect to the database.

The way I maintain my databases is a bit different. For one I have largefile systems enabled so I can go above 2GB. I just extend the tablespaces using the following script.
Once it runs, I have a script with all datafiles listed - it's just a matter of changing the sizes for those tablespaces that need to grow. Running the script "alters" all the datafiles - but only the changed ones actually grow. We use OFA - so all my datafiles are in one tree - adjust the find statement as needed.

Hope this helps

echo "set echo on" > alter_datafiles_all.sql
echo "spool alter_datafiles_all.log" >> alter_datafiles_all.sql
find /oracle/DATA -type f -exec ls -s {} \; |grep -v "+"|grep -v "/ctrl"|sort -k2|awk '{printf("%s\047%s\047\t%s%d%s\n","alter database datafile ",$2," resize ",$1/1024/2,"M;")}'>> alter_datafiles_all.sql
echo "exit" >> alter_datafiles_all.sql

[Roger Baptiste]

Thanks for all the responses.

My question on using looping through sql script in a shell script was not specifically to adding datafiles; The same process can be applied to other repetitive tasks too like creating tablespaces etc.

I know little bit of sql but Yogiraj's script seems intresting to try. Jim's solution seems simple.

Bob's suggestion is fine too, but the only issue is when we are creating new VG's/DG's we would need to create new datafiles. But it's a handy way to extend existing files.

Any further suggestions are also welcome. Some of the answers deserved a 8-10, but am holding the rabbit until i try them succesfully.

Roger