saeed anwar
asked on
Linux shell script for deleting tables
Hi,
I am a newbie in shell scripting!
I need to create a shell script that connects to the oracle 11g database and list all the tables that ends with _tmp (both lower case or upper case) and deletes them if it's equal or greater than 30 days. I should be able to pass the variable parameters for database name, instance name and the number of days. Also, it should skip SYS and SYSTEM schema tables but checks for all other schema tables in the database.
Please help me ASAP.
Appreciate it very much and thanks in advance.
I am a newbie in shell scripting!
I need to create a shell script that connects to the oracle 11g database and list all the tables that ends with _tmp (both lower case or upper case) and deletes them if it's equal or greater than 30 days. I should be able to pass the variable parameters for database name, instance name and the number of days. Also, it should skip SYS and SYSTEM schema tables but checks for all other schema tables in the database.
Please help me ASAP.
Appreciate it very much and thanks in advance.
ASKER
if the table is there for more than 30 days that ends with _tmp then delete the table(s). For example if the table xyz_tmp is created equal or more than 30 days ago then delete it
ASKER
drop the table(s) and I want to schedule it through Crontab and should be able to run it on any Linux server for any database name and instance. I want to pass the database name/instance name as variable. For example, drop_tmp_tables.sh <database name> <instance name>
ASKER
does that clarify your questions?
ASKER
need a shell script. Please help me ASAP!
Thanks!
Thanks!
First: You have a really bad Oracle design. Oracle normally doesn't need TMP tables like you are describing. It has something called a Global Temporary Table that you create once and never drop.
If you have some app that is creating a LOT of TMP tables, it is probably a bad app.
That said, I'm not on Unix where I can test anything.
>>For example, drop_tmp_tables.sh <database name> <instance name>
Database name and instance name are pretty much the same thing in this case. If you connect to an instance and drop a table, it is removed from the database.
I can state that the PL/SQL block should work the way you want.
My SHELL is rusty but there are MANY examples out there but I think I'm close:
If you have some app that is creating a LOT of TMP tables, it is probably a bad app.
That said, I'm not on Unix where I can test anything.
>>For example, drop_tmp_tables.sh <database name> <instance name>
Database name and instance name are pretty much the same thing in this case. If you connect to an instance and drop a table, it is removed from the database.
I can state that the PL/SQL block should work the way you want.
My SHELL is rusty but there are MANY examples out there but I think I'm close:
#!/usr/bin/ksh
sqlplus -s /nolog <<EOF
conn username/password@$1
begin
for i in (select object_name from user_objects where object_type='TABLE' and object_name like '%\_TMP' and created < trunc(sysdate-30) escape '\') loop
execute immediate 'drop table ' || i.object_name;
end loop;
end;
/
exit;
EOF
I agree with slightwv's comments. Basically, Linux shell scripts don't delete or drop Oracle tables (or do anything else inside an Oracle database). You need SQL commands to do tasks inside an Oracle database.
A shell script on a database server can launch SQL*Plus, and SQL*Plus can then do what ever tasks you have to do inside the database by executing SQL and/or PL\SQL commands. Yes, it is possible to write these SQL or PL\SQL commands inside a shell script that also launches SQL*Plus and connects to the database, then executes the SQL or PL\SQL commands, but personally, I never use that approach. I write SQL or PL\SQL commands into *.SQL files, that can be tested in the database. Then, if I need to run those commands from Linux, I write a shell script that launches SQL*Plus, then runs the *.SQL file.
A shell script on a database server can launch SQL*Plus, and SQL*Plus can then do what ever tasks you have to do inside the database by executing SQL and/or PL\SQL commands. Yes, it is possible to write these SQL or PL\SQL commands inside a shell script that also launches SQL*Plus and connects to the database, then executes the SQL or PL\SQL commands, but personally, I never use that approach. I write SQL or PL\SQL commands into *.SQL files, that can be tested in the database. Then, if I need to run those commands from Linux, I write a shell script that launches SQL*Plus, then runs the *.SQL file.
A word of warning. While saving things as SQL scripts and then running from a shell script is possible, I don't like doing it. Simply because things like quotes get lost and what was one parameter becomes two (or other fun things that are equally painful to debug). Unless you want to deal with nesting quotes which can get messy and people won't remember.
Take the example script (test_sh.sh):
Versus this script (test_sql.sh):
Now run the 2 scripts:
test_sh.sh "a b" c d
test_sql.sh "a b" c d
While those both appear to be the same, they aren't.
In this particular example where object names are being passed around, it shouldn't be an issue, but it is always possible.
Take the example script (test_sh.sh):
#!/bin/sh
sqlplus user/pass << EOF
select '$1', '$2', '$3' from dual;
exit
EOF
Versus this script (test_sql.sh):
#!/bin/sh
sqlplus user/pass << EOF
@test.sql $1 $2 $3
exit
EOF
test.sql contains:select '&1', '&2', '&3' from dual;
Now run the 2 scripts:
test_sh.sh "a b" c d
test_sql.sh "a b" c d
While those both appear to be the same, they aren't.
In this particular example where object names are being passed around, it shouldn't be an issue, but it is always possible.
ASKER
Thanks.
Mark - I was looking for a shell script that can use .sql file or launches sqlplus and have the flexibility to pass database name on the command line. For example: drop_tmp.sh oracle1
Mark - I was looking for a shell script that can use .sql file or launches sqlplus and have the flexibility to pass database name on the command line. For example: drop_tmp.sh oracle1
Did what I posted not get you there? I thought I posted that... If it didn't work, what was the error?
I agree. What slightwv posted should work. At least as a starting point.
If it was my script, I would add more robust parameter processing, as you typically don't specify arguments that way to a shell script. You would normally use a name and a value, but what he has posted should certainly work.
drop_tmp.sh -d oracle1
or
drop_tmp.sh --database oracle1
These are pretty easily done using calls to getopt.
There was also mention of running through cron. Extra code would have to be added to set up the environment as cron does not run any profile scripts.
If it was my script, I would add more robust parameter processing, as you typically don't specify arguments that way to a shell script. You would normally use a name and a value, but what he has posted should certainly work.
drop_tmp.sh -d oracle1
or
drop_tmp.sh --database oracle1
These are pretty easily done using calls to getopt.
There was also mention of running through cron. Extra code would have to be added to set up the environment as cron does not run any profile scripts.
>>There was also mention of running through cron.
100% agree!
Am I to assume you want us to write your script for you? That isn't really what this site is about. We will gladly help you help yourself. If you want someone to do all the work, I suggest a Gig: https://www.experts-exchange.com/gigs/
100% agree!
Am I to assume you want us to write your script for you? That isn't really what this site is about. We will gladly help you help yourself. If you want someone to do all the work, I suggest a Gig: https://www.experts-exchange.com/gigs/
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
If what is greater than 30 days?
When you say "delete" are you wanting to delete certain rows from them or drop the table itself?
Do you need a shell script? It seems a sqlplus script will work and your lack of shell scripting isn't an issue.