Home » RDBMS Server » Server Utilities » How to export a user without data of few tables (Oracle 10g)
How to export a user without data of few tables [message #468384] Thu, 29 July 2010 23:21 Go to next message
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

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 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
> I dont want to export data of Audit tables with AU prefix.
about how many tables need to be excluded?
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 Go to previous messageGo to next message
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)

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 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ROWS=Y export data rows (Y)
ROWS=N export Scructure only.
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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%';


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 Go to previous messageGo to next message
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?
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 Go to previous message
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







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


Current Time: Mon Oct 20 23:14:35 CDT 2014

Total time taken to generate the page: 0.05283 seconds