Home » RDBMS Server » Server Utilities » Export only part of data.............
Export only part of data............. [message #263321] Wed, 29 August 2007 13:03 Go to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi,

I have created a table by name "Student".

SQL> select * from Student;

ID City
---------- ----------
301 Bangalore
302 Mangalore
303 Bangalore
304 Mysore
305 Bangalore


I am using Parameter file and i am trying to export only who are living in "Bangalore" to a dump file and the parameter file contains following parameters:-

File=F:\Backup_Venkat\Extonly.dmp
tables=nav.descrp
query=select * from Student where City='Bangalore'


But when i type the following command i am getting errors:-

C:\>exp nav/aaa parfile=F:\Backup_Venkat\Extonly.dmp
LRM-00112: multiple values not allowed for parameter 'query'
LRM-00113: error when processing file 'F:\Backup_Venkat\Extonly.dmp'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully


Please guide me.

Regards,
Naveen
Re: Export only part of data............. [message #263349 is a reply to message #263321] Wed, 29 August 2007 15:37 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read OraFAQ Forum Guide; in there you'll find how to properly ask a question (for example, Oracle database version would be useful information). Also, in there you'll find link to documentation; it will show you how to use QUERY parameter in EXP command.

Finally, it would (on my 10g) look like this:
C:\>exp scott/tiger tables=emp query='where deptno = 10'

Export: Release 10.2.0.1.0 - Production on Sri Kol 29 22:31:21 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character  set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP          3 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

C:\>


By the way, I hope you understand that using "tables=nav.descrp" will make EXP fail if 'nav.descrp' table doesn't contain 'city' column; if you ask me, I'd use "tables=student" instead.
Re: Export only part of data............. [message #263391 is a reply to message #263321] Wed, 29 August 2007 23:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

In addition to LittleFoot,
query=\"where City='Bangalore'\"
Re: Export only part of data............. [message #263641 is a reply to message #263349] Thu, 30 August 2007 10:17 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
In addition to Arju
Quote:
query=select * from Student where City='Bangalore'


Replace query clause.
Re: Export only part of data............. [message #264197 is a reply to message #263321] Sat, 01 September 2007 23:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
query=select * from Student where City='Bangalore'


Dream, query itself a select clause. You can't add another select inside query clause.
Re: Export only part of data............. [message #264200 is a reply to message #263391] Sat, 01 September 2007 23:38 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi Arju,
query=\"where City='Bangalore'\"


Can you please explain why we need
1) "(Double Code)
2) \(Backslash)

3) '(Single Code) :- character literal means we need to close it in single codes.


Regards,
Naveen
Re: Export only part of data............. [message #264202 is a reply to message #263321] Sat, 01 September 2007 23:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

1) Because the values of the query parameter is String.
2)And about /

It is
Operating system reserved character. So,it need to be preceded by an escape character.

3) Yes.
Re: Export only part of data............. [message #264205 is a reply to message #263321] Sun, 02 September 2007 00:38 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
Operating system reserved character.

I am saying about UNIX OS.
Previous Topic: imp utility
Next Topic: Strategic question export/import of large amount of data
Goto Forum:
  


Current Time: Fri Dec 02 18:29:55 CST 2016

Total time taken to generate the page: 0.45872 seconds