OraFAQ Forum: Server Utilities » How to export a user without data of few tables

Home » RDBMS Server » Server Utilities » How to export a user without data of few tables (Oracle 10g)

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

How to export a user without data of few tables [message #468384]

Thu, 29 July 2010 23:21

hari_bk
Messages: 110
Registered: March 2006

Senior Member

Hi

I need to export a user with all the tables. But I need to export data into only few tables, need to omit the data of few tables.

Ex I dont want to export data of Audit tables with AU prefix.

Thanks
Hari

Report message to a moderator

Re: How to export a user without data of few tables [message #468385 is a reply to message #468384]

Thu, 29 July 2010 23:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I dont want to export data of Audit tables with AU prefix.
about how many tables need to be excluded?

Report message to a moderator

Re: How to export a user without data of few tables [message #468386 is a reply to message #468384]

Thu, 29 July 2010 23:25

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Check Import Export FAQ on Ora FAQ

In your case select the table which to export for example emp,dept


exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

Report message to a moderator

Re: How to export a user without data of few tables [message #468387 is a reply to message #468386]

Thu, 29 July 2010 23:28

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

ROWS=Y export data rows (Y)
ROWS=N export Scructure only.

Report message to a moderator

Re: How to export a user without data of few tables [message #468388 is a reply to message #468385]

Thu, 29 July 2010 23:40

hari_bk
Messages: 110
Registered: March 2006

Senior Member

I need to export approx 600 tables and for 150 tables of AU prefix and 75 tables of TEMP prefix, I dont want to export data. But I need data for other tables.

Thanks

Report message to a moderator

Re: How to export a user without data of few tables [message #468393 is a reply to message #468388]

Thu, 29 July 2010 23:50

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

filter the names

select Table_Name from user_tables where Table_Name Not Like 'AU%';

select Table_Name from user_tables where Table_Name Not Like 'TEMP%';

Report message to a moderator

Re: How to export a user without data of few tables [message #468395 is a reply to message #468384]

Fri, 30 July 2010 00:04

hari_bk
Messages: 110
Registered: March 2006

Senior Member

Thanks Rahul...But where I can pass this query to conditionally select/ Not select rows? Can you give me example?

Report message to a moderator

Re: How to export a user without data of few tables [message #468413 is a reply to message #468395]

Fri, 30 July 2010 01:11

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Dont pass ther quey just get the list of tables you and put
it in the braket [tables=( t,t,2... )]for which you want export the data

exp scott/tiger file=emp.dmp log=emp.log tables=( T1,T2,T3,...) rows=yes indexes=no

without data rows=no

        exp scott/tiger file=emp.dmp log=emp.log tables=( T1,T2,T3,...) rows=no indexes=no

Report message to a moderator

Previous Topic:	Data Pump Error
Next Topic:	Oracle export and import performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 14:25:48 CST 2024