Home » RDBMS Server » Server Administration » export with wildcard characters and dependant objects
export with wildcard characters and dependant objects [message #211251] Wed, 27 December 2006 10:41 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I need to take export backup for transaction tables starts with 'T%' associated with one of the tablespace test. There are other tables like start with 'H%' associated for the same tablespaces.


exp jtest/jtest file='/u01/app/oracle/oradata/test.dmp' log='/u01/app/oracle/oradata/test.log' consistent=y tables=t%

I can take simply export backup using 'T%' but I wanted to take export backup for all other assocated objects like index,view,triggers,functions etc. at the same time.

I can go for user-wise export but I wanted to take backup for 'T%' tables and other objects as mentioned in the dump file.

Can you suggest me the best way to perform this job?

Re: export with wildcard characters and dependant objects [message #211256 is a reply to message #211251] Wed, 27 December 2006 11:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No direct method in 9i (not sure about 10g).
Do this
>>exp jtest/jtest file='/u01/app/oracle/oradata/test.dmp' log='/u01/app/oracle/oradata/test.log' consistent=y tables=t%
Followed by
>>I can go for user-wise export
Just add rows=n

Import the first dump.
Import the second dump
drop all the tables that is not like H%
or
>> view,triggers,functions etc
Just extract the ddl.
You can extract it from the dumpfile itself
or
Use dbms_metadata/any gui tool (sql*developer/toad)
Re: export with wildcard characters and dependant objects [message #211259 is a reply to message #211256] Wed, 27 December 2006 11:47 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Just extract the ddl.
You can extract it from the dumpfile itself
or
Use dbms_metadata/any gui tool (sql*developer/toad)

Can you please send me the procedure for above mentioned thing how to perform technically?

I am using ORacle 8.1.7 for this task.
Re: export with wildcard characters and dependant objects [message #211260 is a reply to message #211259] Wed, 27 December 2006 11:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
dbms_metadata works only after 9i.
>>You can extract it from the dumpfile itself
just use show=y during the import.
Something like this

imp user/pass file=mydump.dmp log=somefile.sql show=y

Using show=y will not actually do any import.
Instead the text ddl will dumped into somefile.sql
It is not pretty. But you get the ddl.
Much simple option is to use GUI tools.

>>Can you please send me the procedure for above mentioned thing how to perform technically?
Please search the forum. We have answered it a zillion times before

[Updated on: Wed, 27 December 2006 12:07]

Report message to a moderator

Re: export with wildcard characters and dependant objects [message #211261 is a reply to message #211260] Wed, 27 December 2006 11:56 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/128570/42800/?srch=show%3Dy#msg_128570

Other elegant options are discussed here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1464804639878

Several tools are available on net. Just google
http://www.ddlwizard.com/


You no need to go through this if you can do
>>Import the first dump.
>>Import the second dump
>>drop all the tables that is not like H%

[Updated on: Wed, 27 December 2006 11:58]

Report message to a moderator

Previous Topic: Migration issue
Next Topic: Oracle different edition after upgrade
Goto Forum:
  


Current Time: Sun Dec 11 07:55:37 CST 2016

Total time taken to generate the page: 0.10339 seconds