[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

Windows Batch script to drop, create database

Hi Experts,
 I have the following SQL commands which i want to put it in a batch file, however getting errors while doing so,

Appreciate your kind and quick help.
@ echo off
echo Make Sure to kill the Java Process
echo dropping Database Schema Owner
sqlplus system/password drop user db_user cascade;
echo dropping application owner user
sqlplus system/password drop user app_user cascade;

Open in new window

0
itsme_asif
Asked:
itsme_asif
  • 6
  • 5
  • 2
1 Solution
 
rbrookerCommented:
what errors do you get?
0
 
itsme_asifAuthor Commented:
Not really a error
It works until the following
@ echo off
echo Make Sure to kill the Java Process
echo dropping Database Schema Owner
 and fails in this statement
sqlplus system/password drop user db_user cascade;
Not sure if the syntax for the above command is right

0
 
rbrookerCommented:
easiest way to do that is to create a sql file called drop_user.sql and put in it :
drop user &1 cascade;

and then call it by
sqlplus system/password @drop_user.sql db_user

you cannot have the commands you want to run on the sqlplus line.

in unix you do this :
sqlplus /nolog <<!
connect system/password
drop user db_user cascade;
exit;
!

where everything between <<! and ! is what you want to run, i do not know how to do this in windows..
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
MikeOM_DBACommented:
Try something like this:


@ echo off
echo Make Sure to kill the Java Process
(
echo prompt ++++ dropping Database Schema Owner ++++
echo drop user db_user cascade;
echo prompt ++++ dropping application owner user ++++
echo drop user app_user cascade;
echo quit
) | sqlplus system/password

Open in new window

0
 
itsme_asifAuthor Commented:
Hi Mike,
 the script you provided just works fine, however it stops after the first drop command, in other words after executing the first user it kind of comes back to the SQL> command line, can you please verify
0
 
MikeOM_DBACommented:

Yes, maybe if you remove the prompts...
0
 
itsme_asifAuthor Commented:
Works great, but the script is not quitting out of the SQL command prompt, i have the following

@ echo off
echo Make Sure to kill the Java Process
(
echo prompt ++++ dropping Database Schema Owner ++++
echo drop user db_user cascade;
echo prompt ++++ dropping application owner user ++++
echo drop user app_user cascade;
echo quit
) | sqlplus system/password
echo quit (also tried with just quit)
0
 
MikeOM_DBACommented:
Try this one:

@ echo off
echo Make Sure to kill the Java Process
echo ++++ dropping Database/Application Schema Owners ++++
(
echo drop user db_user cascade;
echo drop user app_user cascade;
echo exit 0
) | sqlplus system/password

Open in new window

0
 
itsme_asifAuthor Commented:
Tried, but having the same problem doesnt exit from the SQL command prompt
echo entering SQL
sqlplus system/password
echo exit 0

Open in new window

0
 
MikeOM_DBACommented:
The above does not work!
Try this test:




(
echo select sysdate from dual;
echo exit;
) | sqlplus system/password

Open in new window

0
 
itsme_asifAuthor Commented:
Here is the complete script. your suggestion above just works fine, however the same thing, that is exiting of the SQL command line does not happen for the second set of SQL statements, can you please take a look
@ echo off
 
echo Make Sure to kill the Java Process
(
echo prompt dropping Database Schema Owner
echo drop user orm_user cascade;
echo prompt dropping Application Owner
echo drop user orm_owner cascade;
echo prompt dropping tablespace
echo drop tablespace db_DATA including contents;
echo drop tablespace db_UNDO including contents;
echo drop tablespace db_TEMP including contents;
echo drop tablespace db_INDEX including contents;
echo exit 0;
) | sqlplus system/password
echo Deleting the DB files
cd C:\oracle\product\10.2.0\oradata\orcl
sleep 120
del orm* 
:: Works Until here
echo ++++ Creating TableSpace ++++ 
cd C:\dbHome\samples\sqlscripts\oracle
sqlplus system/password @create-tablespace.sql C:\oracle\product\10.2.0\oradata\orcl C:\oracle\product\10.2.0\oradata\orcl C:\oracle\product\10.2.0\oradata\orcl
sqlplus system/password @create-schema-owner.sql db_user dbuser
sqlplus system/password @create-app-user.sql app_owner appowner
sqlplus "sys/password as sysdba" @transaction-support.sql app_owner
echo exit 0;

Open in new window

0
 
MikeOM_DBACommented:
Either add an 'exit' statement at th end of each "@create-xxx.sql" script or do this:

--- ETC ---
:: Works Until here
echo ++++ Creating TableSpace ++++ 
cd C:\dbHome\samples\sqlscripts\oracle
(
echo @create-tablespace.sql C:\oracle\product\10.2.0\oradata\orcl C:\oracle\product\10.2.0\oradata\orcl C:\oracle\product\10.2.0\oradata\orcl
echo @create-schema-owner.sql db_user dbuser
echo @create-app-user.sql app_owner appowner
echo conn sys/password as sysdba
echo @transaction-support.sql app_owner
echo exit 0;
) | sqlplus system/password 

Open in new window

0
 
itsme_asifAuthor Commented:
That works, thanks much
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.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now