Solved

Windows Batch script to drop, create database

Posted on 2008-06-16
13
1,894 Views
Last Modified: 2012-05-05
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
Comment
Question by:itsme_asif
  • 6
  • 5
  • 2
13 Comments
 
LVL 18

Expert Comment

by:rbrooker
ID: 21796638
what errors do you get?
0
 

Author Comment

by:itsme_asif
ID: 21796733
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
 
LVL 18

Expert Comment

by:rbrooker
ID: 21796776
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21796800
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
 

Author Comment

by:itsme_asif
ID: 21797083
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21797202

Yes, maybe if you remove the prompts...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:itsme_asif
ID: 21798185
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21803975
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
 

Author Comment

by:itsme_asif
ID: 21805913
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21806507
The above does not work!
Try this test:





(

echo select sysdate from dual;

echo exit;

) | sqlplus system/password

Open in new window

0
 

Author Comment

by:itsme_asif
ID: 21806714
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
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 21807389
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
 

Author Comment

by:itsme_asif
ID: 21808737
That works, thanks much
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now