• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1138
  • Last Modified:

sql script to drop all tables from the user

My following script does not runit give an error like below:
test-omf-01> ./auto_del_lim_cpsstage.sh
ksh: ./auto_del_lim_cpsstage.sh: cannot execute

The script is as follow:
#!/usr/bin/ksh

. /opt/users/oracle/.profile_stage
$ORACLE_HOME/bin/sqlplus /nolog <<EOSSPLUS
connect lim/lim@stage
set heading off
set feedback off
set pause off
set pagesize 0
set linesize 132
prompt
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET ESCAPE \
SPOOL /opt/users/oracle/admin/bin/DELETEME.SQL
select 'drop table ', table_name, 'cascade constraints \;' from user_tables;
SPOOL OFF


thanks, for assisting .

0
gyans
Asked:
gyans
1 Solution
 
fmonroyCommented:
you need to concatenate the entire line:

select 'drop table '||table_name||' cascade constraints \;' from user_tables

I think the '\' is specific for linux, not sure...

not sure how to run it, in windows you do this:

connect <user>/<pass>@<alias>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the OLAP and Data Mining options

@<complete_path_to_scrip_file>

from your example I think:

@/opt/users/oracle/admin/bin/DELETEME

hope it helps.


FM
0
 
vishal68Commented:
Your shell file does not have execute permissions. Either give it execute permission by running chmod +x auto_del_lim_cpsstage.sh or run it as $ sh ./auto_del_lim_cpsstage.sh

Also your script does not have the EOSSPLUS at the end to stop execution

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
It is possible in UNIX and/or Linux to write a shell script that starts SQL*Plus, then executes one or more SQL commands that are in the same file, then exits SQL*Plus and returns control to the shell script.  Since most of my Oracle experience has been with Oracle on Windows until recently, I never mastered this technique.  I always used the two-file approach, and that works just as well on UNIX or Linux as it does on Windows.

Do you want just an *.SQL script that you can run from SQL*Plus, or do you want a shell script that both starts SQL*Plus, then does the SQL work all from one file?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gyansAuthor Commented:
I was looking for a shell script that would do both - start sql plus and do all the sql work from the same file .

thanks.
0
 
Mark GeerlingsDatabase AdministratorCommented:
If you are willing to use the two-file approach (a small shell script that starts SQL*PLus and passes it the name of the *.SQL file to run, plus the separate *.SQL file) I can help you.  If you definitely want the single-file approach, you will have to wait for someone else to help you with that.
0
 
vishal68Commented:
Your script is almost there

---------------------START OF SCRIPT --------------
#!/usr/bin/ksh

. /opt/users/oracle/.profile_stage
$ORACLE_HOME/bin/sqlplus /nolog <<EOSSPLUS
connect lim/lim@stage
set heading off
set feedback off
set pause off
set pagesize 0
set linesize 132
prompt
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET ESCAPE \
SPOOL /opt/users/oracle/admin/bin/DELETEME.SQL
select 'drop table '||table_name||' cascade constraints ;' from user_tables;
SPOOL OFF

EOSSPLUS

-------------------------------END OF SCRIPT------------

If you want to run the drop table script automatically at the end then add the line
@/opt/users/oracle/admin/bin/DELETEEME.SQL
after spool off

Once you have saved the file. Give it execute permissions using the command
chmod +x auto_del_lim_cpsstage.sh

Then you can run it.

HTH
Vishal
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now