Server Utilities :: Exporting / Importing Partitioned Table

Sep 2, 2013

I am trying to export a partition of a table and import it to another database. I get the below error when I try to import.

ORA-14400: inserted partition key does not map to any partition

If I export the table(for that particular partition) and import the table(after dropping the table) in destination, the partitions and sub partitions are created without any problem.

The table is Range Partitioned and Sub partitioned in List. So I had to perform the below operation if I want to retain other data in the Destination table.

1. Drop the existing partition
2. Create the partition and sub partition, same as source
3. Execute imp

In fact I had to perform step#2, as if I split the partition also, the sub partition gets replicated in the new partition, which again throws the same error. Is there better way of managing the partitions and subpartition in destination with exp/imp utility, so that I need not perform step#1 and step#2 manually.

View 11 Replies


ADVERTISEMENT

Server Utilities :: Data Pump For Exporting And Importing Extremely Large Data Files

Sep 24, 2010

I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?

View 4 Replies View Related

Server Utilities :: Exporting Table Structure From A Particular DB?

Nov 9, 2010

know the process of exporting only the table structure of a Database without the actual content of it.

Note:: I don't know how many tables are present in the DB.

View 1 Replies View Related

Server Utilities :: Exporting A Table That Is 3 GB In Size

Mar 22, 2011

I am exporting a table that is 3 GB in size and also Partitioned with option NOCOMPRESS specified.

Now when i export it with COMPRESS=N option of exp utility then it should take 3 Gb in target server but will exporting it with COMPRESS=Y will save some storage during import or once NOCOMPRESS option specified on partition has no impact on exp utility COMPRESS=Y option and it will take 3 GB space in both cases

Is this true that whether u specify COMPRESS=N|Y during export it does not matter the size will be 3 GB always after import?

View 6 Replies View Related

Server Utilities :: Importing Table From 9i To 10g

May 31, 2012

I want to import my oracle 9i dump file into oracle 10g version. What should i do ???

View 2 Replies View Related

Export/Import/SQL Loader :: Importing Data In A Partitioned Table

Sep 18, 2012

I have a query regarding importing data in a partitioned table. let me make myself more clear with an example:

I have 1 month table that contains 30 partitions single partition for a single day on one machine say machine A. on another machine say machine B i create the same table with the same script which is on machine A for the same table. i loaded data till 1-15th of a month in Machine A table and rest of 15 -30 Days data into table on machine B at the end i want to import the data on partitioned table on machine B that is from 15th -30th to machine A table. I just want to know whether data is properly imported or not not or i need to specify something

I take export partition wise (15 -30th) 15 partitions dumps and imported into Machine A table. Is it possible that i can import day wise partition from 15th to 30th into a partitioned table which already contains data from 1st -15th partition.

I know this is possible

View 2 Replies View Related

Server Utilities :: Imp-00033 Table Not Found While Importing

Aug 5, 2010

while importing dump file into database,i received the following error.

imp -00033 table demo not found.

But i checked in dump file ,the table exist .

Below is the command i executed in unix box.

imp system/manager file=data.dmp log=data.log tables=DEMO feedback=100 buffer=1000 fromuser=TOM touser=maddy

View 3 Replies View Related

Server Utilities :: Importing Table Data Is Not Coming?

May 10, 2010

i take export of one table (export complet successfully without warnings) when i am going to import into prduction databae the data in the table no coming i past the table structure and import command and logfile for import.

import command

imp user/user@database file=C:Tempuserdrop_id10g _id_rac_ra_headertables.dmp log=C:Tempuserdrop_id10g _id_rac_ra_headertablesimp.log tables=t_id_rac_ra_header fromuser=user touser=user statistics=none ignore=yes

my table structure in production database is

CREATE TABLE t_id_rac_ra_header
(ra_company VARCHAR2(10) NOT NULL,
ra_key NUMBER NOT NULL,
ra_doc_type VARCHAR2(50) NOT NULL,
ra_doc_number VARCHAR2(25) NOT NULL,
ra_doc_date DATE DEFAULT SYSDATE NOT NULL,
ra_reserve_key NUMBER,

[code].....

View 4 Replies View Related

Server Utilities :: Exporting Database?

May 31, 2010

I am Trying to Export my database and whenever I try to login it is giving Ora-Error 1017 Invalid Username/Password.

If I Login as System and Manager it is accepting but I am not able to Export all my Database.

View 9 Replies View Related

Server Utilities :: Exporting Metadata Backup

Jul 19, 2011

i am using a schema which i need to take a backup of meta data only i am using exp utility

exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n

but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

View 3 Replies View Related

Server Utilities :: Exporting From Physical Standby

Aug 3, 2012

I would like to export few tables from the physical standby which is in read only mode.

I have tried both the exp and expdp methods and could successfully export and import the tables from physical standby using exp unfortunately the expdp does not allow this process from a read only database.

Does this mean that we still have to use the exp feature instead of expdp ?

Note : I would expect a proper response from experts and no unwanted comments like "Contact Oracle support" or "Paste the entire command here" or "Read the Manuals" or "Why i am exporting from Standby and not from Primary" etc.

View 1 Replies View Related

Server Utilities :: Exporting Huge Amount Of Data?

Jul 25, 2011

extract a huge amount of data from a couple of views... the problem is that they want it in TXT files with fixed record length. There will be like 6 files, for a total amount of about 10GB.

export those tables in the fastest possible way? If I'm not mistaken exp and expdp can't create txt files, so do I really need to use utl_file or spool?

View 1 Replies View Related

Server Utilities :: Exporting Schema Using Consistent Parameter

Aug 5, 2012

I taking export using consistent parameter. Theoretically i can understand . practically i couldn't understand how it works.

for ex

I am updating tab1 table under sams user. table having one lakh records.
while updating the query using consistent=y and consistent=n. i mean

exp sams/sams file=cons.dmp owner=sams consistent=y
exp sams/sams file=cons2.dmp owner=sams consistent=n

then both files imported to separate user(sam ,san).
Updated info not visible in san and sam user.

I want to know practically how it works. I need perfect example. while using consistent=y and consistent=n

View 2 Replies View Related

Server Utilities :: Exporting Schema Using Filesize Parameter?

Aug 6, 2012

Export /Import
==============

While exporting schema's

i couldn't export dump file to exact location i mean see following query : -

QUERY
=====

exp file=ackupfile1.dmp,ackupfile2.dmp,ackupfile3.dmp
owner=(order,purchase) filesize=5m as os level ,

I fould those dump files files home directory.

-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp
[oracle@localhost ~]$ pwd
/home/oracle

when i listing

rw-r--r-- 1 oracle oinstall 72 Jun 20 21:17 afiedt.buf
drwxr-xr-x 3 oracle oinstall 4096 Jun 17 10:07 Desktop
-rw-r--r-- 1 oracle oinstall 71 Jun 19 20:42 ed.hup
drwxr-xr-x 2 oracle oinstall 4096 Aug 6 19:38 backup
-rw-r--r-- 1 oracle oinstall 2826240 Aug 6 19:39 expdat.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp

Dump file goes to home path even if i mentioned appropriate location.

View 7 Replies View Related

Server Utilities :: Syntax For Exporting Only Procedures Of Particular User

Mar 7, 2011

What is the syntax for exporting only the procedures of a particular user.

View 1 Replies View Related

Server Utilities :: Exporting Database Schema / Tables Without Data

Sep 22, 2010

I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).

For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?

Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.

View 1 Replies View Related

Server Utilities :: Primary Keys Are Not Exporting When Export Using EXP Command

Dec 27, 2011

I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.

how will i take backup including with primary keys?

View 7 Replies View Related

Server Utilities :: Export Hangs At Exporting Cluster Definitions?

Dec 22, 2010

Sunddenly my exports hangs at 'exporting cluster definitions'. I had been using this database since last 4 years and it never cause a problem or hangs at this level. here i'm pasting my screen details. it is my production db.

[oracle1@wbh_as1 smbshare]$ exp wb/wb

Export: Release 9.2.0.1.0 - Production on Thu Dec 23 00:02:44 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > wb

(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WB
About to export WB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions

View 11 Replies View Related

Server Utilities :: Importing 9i Dump Into 10g?

Jun 22, 2011

how can i import the oracle 9i dump file into 10g database, while iporting i get following error imp-00002 fail to open dump file

View 4 Replies View Related

Server Utilities :: Importing Data From 10G To 8i

Nov 9, 2010

I would like to know if a dump file taken from 10G could be imported into to an Oracle 8I database.

View 1 Replies View Related

Server Utilities :: Exported Data Of One User Importing Into Another Schema At Another Server

Sep 15, 2010

I have exported data of one user an importing into another schema at another server. when i am trying to imoport it is working fine for quite no of imports into tables, but after some time it starts giving me below mention error...

IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<‏ے
IMP-00008: unrecognized statement in the export file:
+A
IMP-00008: unrecognized statement in the export file:
[code]...

View 6 Replies View Related

Server Utilities :: Importing Text Files?

Sep 16, 2011

I have a rather complicated process to import text files into my DB.I'm given thousands of files every day, separated by "," and with 80 fields each. With a bash script, I take the 45 fields I need and then split each file into x number of files grouping the rows by three fields.Then I use SQL Loader to insert them into de DB.

The problem is that now I must insert on two tables and the "WHEN" clause doesn't allow the use of > and <.

To make things a litle clearer take this text file (already splited and grouped and ready to be inserted):
...
1,1,135,1900,0,12,114,2011/08/25 17:19:00,135,...
1,1,135,1900,0,13,119,2011/08/25 17:19:00,136,...
1,1,135,1900,0,14,117,2011/08/25 17:19:00,137,...
1,1,135,1900,0,15,113,2011/08/25 17:19:00,138,...
1,1,135,1900,0,16,119,2011/08/25 17:19:00,139,...
...
When field 6 is higher or equal to 14, it must go to table a.When field 6 is lower than 14, it must go to table b.I can't use external tables as I'm in a different server.

View 1 Replies View Related

Server Utilities :: Character Set Error While Importing

Jun 9, 2011

I have oracle 10.2.0.4.0 installed on Window server 2008 [Machine A] and oracle 10.2.0.1.0 on windowx xp [Machine b]. Now I have taken the export of database on windows server 2k8 [Machine a] by puting the entry in the tnsname.ora file of Windox XP [Machine b].

Now when I am importing on the same machine I am getting the below mentioned error:

C:Documents and Settingsdsharma>IMP FROMUSER=SYSTEM TOUSER=ESCDBO FILE='D:sharevcc53_0106.dmp' LOG='D:sharevcc53_0106_IMP.LOG' ignore=y

Import: Release 10.2.0.1.0 - Production on Thu Jun 9 20:08:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production..With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
Import terminated successfully without warnings.

View 14 Replies View Related

Server Utilities :: Importing Full Database With Different Name And Sid

Oct 20, 2011

Source:Oracle 11g 11.1.0.2 32 bit on Windows 2003,Destination: Oracle 11g 11.1.0.2 64 bit on Windows 2008

In our Source Database, we are using following to create full export

Source database_name=dbfour,sid=dbfour1

I am creating full export by using following

expdp system/psswd full=y dumpfile=expdp_%date:~0,2%-%date:~3,2%-%date:~6,4%.dmp logfile=explog_%date:~0,2%-%date:~3,2%-%date:~6,4%.log FLASHBACK_TIME="to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')"

In my destination DB with database_name=dbfive and sid=dbfive1, i am trying to import whole database by using the file created above and use following

impdp system/pwd@dbfive DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_20-10-2011.DMP

Process started but after sometime it gave 1200 errors. Is it due to the Different Database name or Is it because i did not create table space in destination database.

View 3 Replies View Related

Server Utilities :: Tablespace For Importing Objects

Sep 20, 2010

Two different says in different documentation as below.

1) Import required same tablespace to fit the objects, otherwise it throws error.

2) Import by default uses the default tablespace to fit the objects, but some times it uses SYSTEM tablespace also.

confirm with the correct one.

View 8 Replies View Related

Server Utilities :: Getting Errors During Importing Data

Aug 14, 2012

I am getting following errors while importing data.

SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 2, column 1:
[code]......

View 6 Replies View Related

Server Utilities :: Importing Into Lower Version Using (exp / Imp)

Jan 27, 2011

For importing higher version database exp dump e.g. 10g exp dump into 9i database it is suggested at many places "Always use a version of the EXPORT utility that is equal to the lowest version of either the source or the target database"

I understand from this is, I will be using 9i exp utility for exporting 10g database

I would like to know the ways it can be achieved.

1) Installing oracle 9i client on 10g database Do I still need to execute 9i catexp.sql after that? If answer to above is 'yes', I assume catexp.sql of 9i wont be created after 9i client is installed as such I will need to copy it from 9i database server and execute on 10g database server. Correct? For above question I have assumed catexp.sql has different contents for 9i and 10g

2) After configuring tns file in 9i database, can I use?

exp user/password@10gdbconnstring ....

(of course for small amount of data)

Is there any other option for achieving the same?

Also I understand export and import between any 9.* into any 9.* wont be issue e.g. import from 9.2 into 9.1 Similarly any 10.* to another 10.* wont be issue.

View 6 Replies View Related

Server Utilities :: Importing From DMP File Taken From Different Edition

Mar 2, 2010

i m having full export .dmp file taken from oracle 10.2.0.1 Enterprise Edition.

i need to reinstall 10.2.0.1 standard edition and want to create database using the dump file which is taken from the Enterprise edition.

View 3 Replies View Related

Server Utilities :: Importing Other Schema Object

Sep 19, 2010

I have 2 users (scott and Krishnan).

Krishnan wants to import the table, which was exported by scott.

1) but we should not run the script file of CATALOG.SQL.
2) we can't give the rights of imp_full_database from outside.

Also how user Krishnan can import that table to another user's schema?

View 3 Replies View Related

Server Utilities :: Importing Data From SQL Server To Oracle?

Mar 7, 2011

I'm importing data from SQL Server to Oracle. I used BCP to export the data from SQL Server. Below is the 1st record of table trlc from the csv file.

trlc.CSV

11032|100|Wman| | |2008-02-08| |

Using SQL Loader to import into Oracle:

TRLC table in Oracle database:

est_no varchar2(10) default ' '
right_no number(4) default 0
maj_auth varchar2(15) default ' '
weight varchar2(10) default ' '
idm_ht varchar2(8) default ' '
c_date date
P_tkt varchar2(5) default ' '

sqlldr user/pwd@db02 control=trlc.ctl log=trlc.log

trlc.ctL:

load data
infile 'trlc.csv'
replace
into table trlc
fields TERMINATED BY '|'
TRAILING NULLCOLS
(est_no,right_no,maj_auth,weight,idm_ht,c_date,P_tkt)

The rows get inserted successfully. But the result sets are different, for example: When I do a select in SQL Server,'select len(weight) from trlc;' , I get the length as 0. But when I do a select in oracle database, I get the length as 1. Also, the result set varies for the query below:

select * from trlc where weight=' ';

(SQL Server returns 1 row but Oracle returns no rows)

Do I need to mention any conversion code for the weight field to accept ' ' value?

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved