Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

sql/shell looping question

Roger Baptiste
Honored Contributor

sql/shell looping question

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
Take it easy.
8 REPLIES
James A. Donovan
Honored Contributor

Re: sql/shell looping question

how about....

while read -r tspace datafile filesize
do
echo "Alter tablespace ..... " >>myscript.sql
done
sqlplus internal @myscript.sql
Remember, wherever you go, there you are...
Brian Crabtree
Honored Contributor

Re: sql/shell looping question

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
Honored Contributor

Re: sql/shell looping question

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: sql/shell looping question

sorry for the garbled output. attached the demo script.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
twang
Honored Contributor

Re: sql/shell looping question

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

Re: sql/shell looping question

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.
Not enough Zappa makes you sad.
bob hollis
Frequent Advisor

Re: sql/shell looping question

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
Honored Contributor

Re: sql/shell looping question


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
Take it easy.