Feb 26, 2011

imp partition table in different schema

Query I used in par file



file='/oracle/export/tab/tab3a.dmp'
log='/oracle/export/tab/tab3a.log'
tables=esam.summary
query="where TID IN ('010003050000','010003080000','010003090000') AND SUMMARY_PERIOD BETWEEN '01-oct-2010' AND '31-dec-2010'"


exp “'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log'

Export log


. . exporting table SAM_SUMMARY
. . exporting partition SAM_SUMMARY_04_2002 0 rows exported
. . exporting partition SAM_SUMMARY_05_2002 0 rows exported
. . exporting partition SAM_SUMMARY_06_2002 8000 rows exported


Import into different schema on different server



imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y


Import log


Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 0 PCTUSED 95 INITRANS 5 MAXTRANS
255 STORAGE(INITIAL 8388608 FREELISTS 3 FREELIST GROUPS 3 BUFFER_POOL DEFAULT) TABLESPACE "SAM_SUMM_DBF_2010" NOLOG...
 . importing ESAM's objects into NMAPP
Import terminated successfully with warnings.

Cause: Import utility verifies the syntax of all SQL statements in the dump file. Import utility uses default buffer size is 64k, if the SQL statement in the dump file exceeds the default buffer size, then import will be failed and raise 31 & 8 error..



Solution: increase the buffer size

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000


Import log


IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'SAM_SUMM_DBF' does not exist
Import terminated successfully with warnings.

My partition table having 50 partitions in prod and each partition in different tablespace but those tablespace doesn’t exist in QA.


Solution: Here we have 2 options to done this job.

1. Create missing tablespace in target, and then run import with buffer size.


2. Reff metalink document (ID: 372992.1) to import partition tables in different tablespace


                     a. Run the import utility with show=y or indexes=filename.sql
                     b. Save the ddl to a script file
                     c. Run the script in target db..

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000



this time partition tablespace are imported successfull without any mistake..
:-) feel happy now

------------------------------->>>>>>><<<<<<<<<<<<<<<<<<<---------------------------------------

9 comments:

oracle real scenarios said...

nice one. i was looking for

thanks, muhammed

Muhammad Abdul Halim said...

Nice one .


Thanks&Regards
Muhammad Abdul Halim
http://halimdba.blogspot.com/

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

[url=http://is.gd/EcKOCp][img]http://stomsk.ru/pics/spymobile.ol1[/img][/url]
[url=http://archive.org/details/sumasetos]Telechargement De Telephone Cellulaire Espion Gratuit[/url] [url=http://archive.org/details/paddahysi]Download Kostenlos Mobile Spy-Software Fur Mobiltelefone[/url] http://archive.org/details/chandisystza Download chave de rede sem fios espiao beste Androide monitoring software spy software for nokia mobile phones
Cell Phone Bugging
Basta Mobila Spion Programvara I Indien
SMS-bericht Te Zeggen Dat Ik Mis Je
http://otowndave.featuredblog.com/?p=10#comment-8729 http://www.scientologyopinions.com/User-aaSymnMand http://timothyblee.com/2010/08/01/chat-tomorrow-with-special-guest-reihan-salam/comment-page-1/#comment-534265 adres uit mobiele telefoon nummer Australie vinden mobile spy du detecter mon iphone software de monitoreo de PC para mac Numero de telephone cellulaire GPS tracker gratuit free spy software download mobile phones
[url=http://archive.org/details/inipscandie] Celular Espiao Software Cnet [/url] [url=http://archive.org/details/limisctemo] Blaue Stealth Bluetooth Handy Spion Software Kostenloser Download [/url] [url=http://archive.org/details/neradened] Telefoon Tracker Spy Gadget Iphone App [/url] http://spymobile9331lmxm.carbonmade.com/projects/4789059 Spy sunglasses cooper venda sms spion programvara for blackberry film complet de Spy kids 2001 sms gratuit logiciel android espion visao noturna sem fio mini espiao baba camera cam

Anonymous said...

http://mdriver.info/index.php?os=android&type=soft&filename=Magic-Hour-Camera-Android-App
[url="http://mdriver.info/index.php?os=android&type=soft&filename=Android-Tablet-Apps-and-Games"]Android Tablet Apps And Games apk jar[/url]
Angry Birds Space Premium V1 0 1 Danger Zone Unlocked
http://mdriver.info/index.php?os=android&type=soft&filename=Magic-Paper-v11-5-13-Paid-apk-android-szoheb
[url="http://mdriver.info/index.php?os=android&type=soft&filename=Android-Titanium-Backup-4-6-5-Activator"]Android Titanium Backup 4 6 5 Activator apk jar[/url]
Angry Birds Space Premium V1 1 0 Apk Danger Zone Unlocked
http://mdriver.info/index.php?os=android&type=soft&filename=Maildroid-Pro-v2-53-Android-P2P-BLOVES
[url="http://mdriver.info/index.php?os=android&type=soft&filename=Android-USB-Driver-rar"]Android USB Driver Rar apk jar[/url]
Angry Birds Space Premium V1 2 2 Final By Bobiras2009
http://mdriver.info/index.php?os=android&type=soft&filename=Manga-Watcher-v0-5-18-Android
[url="http://mdriver.info/index.php?os=android&type=soft&filename=Android-apps-games"]Android Apps Games apk jar[/url]
mamoleptino123 Angry Birds Space V1 0 0 Apk