OraFAQ Forum: Unix » scripting advise or guidance

Home » Infrastructure » Unix » scripting advise or guidance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

scripting advise or guidance [message #174226]

Fri, 26 May 2006 11:12

Bruce Carson
Messages: 38
Registered: February 2005
Location: Nova Scotia, Canada

Member

Hi folks,

I am fairly new at the batch command line scripting and am trying to automate as much as possible. I have a requirement to script our next implementation which will be a combination of sql commands and imports/exports. I will also need to pass parameters as much as possible to try and make it generic.

Right now I have a script as follows. It is very crude but I am just getting started. I can also script this in dos or unix (whatever is easier).

/*
icmr3ft1 build
*/

/*
Backup existing schema
Note I am using the host command to execute the exp command from within the command window in pl/sql developer.
*/
host C:\oracle\product\10.1.0\Client_2\BIN\exp userid=carsonbc@gns52 file=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.exp log=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.exp.log owner=icmr3ft1 feedback=100

/*
drop existing schema
*/
drop user &&schemaname cascade;

/*
Re-create schema_owner
*/
-- drop user
drop user &&schemaname cascade;
-- Create the user
create user &&schemaname
identified by &&password
default tablespace ICMFM_D1
temporary tablespace TEMP
profile SCHEMA_OWNERS;
-- Grant/Revoke role privileges
grant connect to &&schemaname;
grant resource to &&schemaname;
-- Grant/Revoke system privileges
grant unlimited tablespace to &&schemaname;

*/
This runs the initial build structure only as the schema owner
*/
conn &&username/&&password@gns52
@C:\PVCS\ICM_RELEASE_2.2\Database\ICMFM\scripts\icmfmdv_icmr3ft1.pdc

/*
Import from a previous schema export to load system tables only
This step is completed yet so do not read too much into the text here. Just trying to capture the flow.
*/
host C:\oracle\product\10.1.0\Client_2\BIN\imp userid=carsonbc@gns52 file=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.exp log=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.imp.log fromuser=icmr3ft1 touser=icmr3ft1 tables=(system1,system2,system3) feedback=100

/*
sequences will have to be adjusted after conversion for tables that already have
data such as case_players_seq
*/

/*
bkp new schema (new baseline)
*/
host C:\oracle\product\10.1.0\Client_2\BIN\exp userid=carsonbc@gns52 file=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.exp log=I:\ICM_PROJECT\db\project\icmr3.00\build\gns52.icmr3ft1.exp.log owner=icmr3ft1 feedback=100

The glaring questions I have right now are:
Can I run the host command for the imports and exports from within a .sql file. I do this now within command line pl/sql developer and sql*plus. My issue is I want to parms into the exp/imp statement to make it generic with things lke &&schemaname embedded in the export statement and maybe a date/time stamp on the files being written to the o/s. This leads me to believe it may be better to script this using unix shell script. Thoughts? If I do script this using unix shell script, how do I pass the parms such as &&schemaname and && password from sqlplus session to sqlplus session since the substitution variables will be cleared.

My Unix scripting is limited but am thinking I am going to have to use it to accomplish what I am looking for. If so, any advice would be appreciated. Any links to some unix shell scripting for oracle (for dummies) would be great as well. I am sure this isn't rocket science and has been done billions of times before.

whew .... enough typing. Hopefully I haven't confused everyone.

Bruce Carson

Report message to a moderator

Re: scripting advise or guidance [message #174258 is a reply to message #174226]

Fri, 26 May 2006 19:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>My Unix scripting is limited but am thinking I am going to have to use it to accomplish what I am looking for
HUH?
UNIX<>Windoze!
The use of "C:\" would indicate a Windoze environment.
On which OS and version is Oracle installed?
YOYO!

Report message to a moderator

Previous Topic:	remove log which are more than 2 months
Next Topic:	de-install oracle 8i and 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 13 23:27:44 CDT 2026