- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: sql/shell looping question
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 12:30 PM
тАО06-17-2003 12:30 PM
sql/shell looping question
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 <
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 12:51 PM
тАО06-17-2003 12:51 PM
Re: sql/shell looping question
while read -r tspace datafile filesize
do
echo "Alter tablespace ..... " >>myscript.sql
done
sqlplus internal @myscript.sql
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 02:05 PM
тАО06-17-2003 02:05 PM
Re: sql/shell looping question
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 09:16 PM
тАО06-17-2003 09:16 PM
Re: sql/shell looping question
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 09:24 PM
тАО06-17-2003 09:24 PM
Re: sql/shell looping question
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-17-2003 09:26 PM
тАО06-17-2003 09:26 PM
Re: sql/shell looping question
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2003 01:48 AM
тАО06-19-2003 01:48 AM
Re: sql/shell looping question
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2003 07:29 AM
тАО06-19-2003 07:29 AM
Re: sql/shell looping question
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2003 02:51 PM
тАО06-19-2003 02:51 PM
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