Solved

script shell impdp

Posted on 2011-09-14
19
1,877 Views
Last Modified: 2012-05-12
Hello,

I search a shell script for doing an impdp for a schema or a table with parameters.

Thanks

bibi
0
Comment
Question by:bibi92
  • 10
  • 9
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36535760
What part are you having problems with?

What parameters will you be passing into the sript?

Do you have the impdp command you need?

The script itself is pretty easy:

#/usr/bin/ksh

export ORACLE_SID=ORCL
export ORAENV_ASK=NO
. oraenv
impdp ............ the rest of the command
0
 

Author Comment

by:bibi92
ID: 36535981
The script itself is pretty easy:
if schema :
nohup impdp \"/ as sysdba\"  remap_schema=$schema1:$schema2 network_link=$dblink buffer=100000 COMMIT=Y recordlength=65535 LOGFILE=logname.log

if table
nohup impdp \"/ as sysdba\" TABLES=$tablename  network_link=$dblink buffer=100000 COMMIT=N recordlength=65535 LOGFILE=logname.log

if dataonly
nohup impdp \"/ as sysdba\" TABLES=$tablename network_link=$dblink  CONTENT=DATA_ONLY COMMIT=Y buffer=100000  recordlength=65535  LOGFILE=logname.log

Thanks

bibi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36535994
I do not understand the 'if' parts.

Are you wanting to pass in a single parameter to the script that will select one of those types of import?

Will you have already exported the 'schema' and/or 'tablename' variables or will you be passing them in as well?
0
 

Author Comment

by:bibi92
ID: 36536285
if $table or if the user want to import the structure and the datas.
if $schema or if the user want to import the schema.
iif $dataonly or if the user want to import only the datas.

I transfer the datas with network_link=$dblink

Thanks

bibi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36536536
That doesn't really answer my question.

Are you wanting them to be passed in as parameters to the shell script or are you going to be setting them externally before you call the script?
0
 

Author Comment

by:bibi92
ID: 36539466
Yes, I want them to be passed in as parameters. Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36539505
I need the requirements.

What are the parameters to look like?  How is it to work?

For example what is the first parameter to be?  If it is just some 'word' how do I know if it is a table or a schema?

It sounds like you don't have complete requirements or a design in mind.  Once you have that we can help.

It is hard for us to help with the design since we do don't understand your system or requirements.
0
 

Author Comment

by:bibi92
ID: 36541741
1 : Instance
2 : Table or schema
3 : Data_only or structure

Thanks

Bibi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36542489
I need a more descriptive explanation.

"1 : Instance" doesn't tell me anything.

This is even further confusing when you add an 'or' like in #2 since the impdp commands are different.

I would suggest you hire a local consultant to come in and work on this with you.  They would be able to better understand your requirements if they were onsite with you.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bibi92
ID: 36542558
1 : Instance = Oracle instance name
This is even further confusing when you add an 'or' like in #2 since the impdp commands are different :
Or is used for explain the parameter :
If $table or if the user want to import the structure and the datas.
if $schema or if the user want to import the schema.
iif $dataonly or if the user want to import only the datas.
Thanks
bibi

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36542747
>>Or is used for explain the parameter

I understand you want to use parameters.  I'm not unserstanding the specifics.

Say your script is called 'myscript.ksh'.

Give me some examples of how you are wanting to call it using the parameters.

For example:

for a table I would like to do:
myscript.ksh ??? ??? ???

for a schema:
myscript.ksh ??? ??? ???



where ??? are the parameters you are thinking about.
0
 

Author Comment

by:bibi92
ID: 36550314
for a table I would like to do:
myscript.ksh $tablename
or
myscript.ksh $tablename $data_only
for a schema:
myscript.ksh $schema_source $schema_cible

Thanks

bibi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550336
>>for a table I would like to do:

Given:
myscript.ksh $tablename $data_only

before you call the script you will do:
export tablename='some_value'

and for
myscript.ksh $schema_source $schema_cible

export schema_source='some_value'

Please clarify.

If so, there is no way to do this because once inside the script how will you be able to tell the first parameter is a 'table', or a 'schema'?
0
 

Author Comment

by:bibi92
ID: 36560428
maybe can I use case in the script for do an import for a table or a schema. Thanks bibi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36560471
You are not understanding what I am saying.

Once inside the script, how do you know if the first parameter is the name of a table or the name of a schema?

By using the '$', $tablename and $schema_source appear to be environment variables in Unix.  They resolve to some 'string'.

I was trying to get you to understand your requirements and what you are really wanting to do.



You need some way to tell the script 'what' you are doing.

For example, the first parameter could be the 'what':

myscript.ksh TABLE $tablename $data_only
myscript.ksh SCHEMA $schema_source $schema_cible

Then you can use a case statement or simple if-then-else.
0
 

Author Comment

by:bibi92
ID: 36565749
Example of a part of script I do for export schema :
if [ $COMPRESSION = "Y" -o $COMPRESSION = "y" ]
then
      mknod 1>/dev/null 2>&1
      if [ "$?" -eq 2 ]
      then
            mknod $GBL_ADM/tmp/${INSTANCE}_${SCHEMA}_$DATE2.dmp p
            gzip -c < $GBL_ADM/tmp/${INSTANCE}_${SCHEMA}_$DATE2.dmp > $CHEMIN_EXP/${INSTANCE}_${SCHEMA}_$DATE2.dmp.gz &
            exp userid="'/ as sysdba'" file=$GBL_ADM/tmp/${INSTANCE}_${SCHEMA}_$DATE2.dmp $TYPE statistics=none >>$LOG_EXP 2>&1
            rm -f $GBL_ADM/tmp/${INSTANCE}_${SCHEMA}_$DATE2.dmp
      else
            ECHOLOG ": fin: echec: The command mknod does not exist"
            exit 3
      fi
else
      exp userid="'/ as sysdba'" file=$CHEMIN_EXP/${INSTANCE}_${SCHEMA}_$DATE2.dmp $TYPE statistics=none >>$LOG_EXP 2>&1
fi
bibi
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36566809
>>Example of a part of script I do for export schema :

How does that help with this question?

I understand "WHAT" you are wanting to do.  I'm just not understanding the "HOW" you are thinking it will work.

Once inside the script you want to write you still need a method to check what type of export you want to do.

I don't understand how you will take the first parameter passed into the script and decide if it is a table or a schema.

For example:
script command line arguments are provided a numeric offset.  So the first parameter passed in is $1.  The second $2 etc...

http://www.well.ox.ac.uk/~johnb/comp/unix/ksh.html#commandlineargs

So given the test script:
----------------------------
#/usr/bin/ksh

print "I will export:  $1"
---------------------------

Then you call it:
---------------------
export tablename="Fred"
export schema_source="Fred"

myscript.ksh TABLE $tablename
myscript.ksh SCHEMA $schema_source


What will be printed?

Unless there is a typo the output from both runs will be:
I will export: Fred

How do you plan on knowing which 'Fred' is a table and which is a schema?
0
 

Author Comment

by:bibi92
ID: 36567663
Hello,
I think I have to close this post and I have to modify the exp command by impdp and change the variables like  file=$CHEMIN_EXP/${INSTANCE}_${SCHEMA}_$DATE2.dmp $TYPE .

exp userid="'/ as sysdba'" file=$CHEMIN_EXP/${INSTANCE}_${SCHEMA}_$DATE2.dmp $TYPE.

Thanks

bibi
0
 

Author Closing Comment

by:bibi92
ID: 36572072
Thanks bibi
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

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

16 Experts available now in Live!

Get 1:1 Help Now