Server Utilities :: Exp / Imp Failed On Nested Table
Jun 18, 2013
I am trying to exp/imp database schema from one environmet to other in oracle 11g. I have a couple nested tables (tables with types datatype). Here are the errors I am getting:
ORA-31693: Table data object "IPAM"."BACKEND_POLICY_TYPES" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00600: internal error code, arguments: [kpudpxcs_getCol_ref_4], [Imgver=1 InputStrmVer=9 Name=POLICY], [], [], [], [], [], [], [], [], [], []
ORA-31693: Table data object "IPAM"."LB_POLICIES" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00600: internal error code, arguments: [kpudpxcs_getCol_ref_4], [Imgver=1 InputStrmVer=9 Name=POLICY], [], [], [], [], [], [], [], [], [], []
ORA-31694: master table "SYS"."SYS_IMPORT_FULL_02" failed to load/unload ORA-31640: unable to open dump file "D:oradataPSPRODDBdata_pump_dirpowerschool-fri.dmp" for read ORA-19505: failed to identify file "D:oradataPSPRODDBdata_pump_dirpowerschool-fri.dmp" ORA-27046: file size is not a multiple of logical block size OSD-04012: file size mismatch (OS 2192117862)
I want to export all db using data pump. I got this error when using it:
Export: Release 10.2.0.4.0 - Production on Thu Nov 25 11:46:48 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions EXP-00068: tablespace TISDATA is offline EXP-00008: ORACLE error 1187 encountered ORA-01187: cannot read from file 203 because it failed verification tests ORA-01110: data file 203: '/u03/app/oracle/product/10.2.0/oradata/unidev/TISTEMP01.dbf' EXP-00000: Export terminated unsuccessful
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE The new table SQL> desc "TESTINVENTORY"."TTRANSACTION" Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TRANSACTIONIDNOT NULL CHAR(26) BRANCHCODE NOT NULL CHAR(3) EXTERNALSYSTEM NOT NULL CHAR(3) EXTRACTSYSTEM NOT NULL CHAR(3) OWNERBRANCHCODE NOT NULL CHAR(3) TRADEREFERENCE NOT NULL CHAR(20) [code]...
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
Is Oracle will support Multi value storage ? In what way we can use Nested table? In real time application where we can use nested table . What is the usage of nested table in real time application.
We have a table in Oracle9i database with around 14 million records and we would like to import that table into 10g database with similar structure. We have exported the table from 9i database and would like to import the table into 10g database within same schema name with different table name as we already have the table with same name in 10g database in same schema. Is it possible to import a table with different table name?
We have a way around to import the table into 10g database in another schema and then push the data into our main table but want to know whether the above requirement is possible.
I am studing Multidimensional Nested table and have the below code:
DECLARE TYPE table_type1 IS TABLE OF INTEGER; TYPE table_type2 IS TABLE OF TABLE_TYPE1; table_tab1 table_type1 := table_type1(); table_tab2 table_type2 := table_type2(table_tab1); BEGIN FOR i IN 1 .. 2 LOOP table_tab2.extend; table_tab2(i) := table_type1();
[Code]...
exception when others then dbms_output.put_line(sqlerrm);END; This code is working fine as of now.But,If i comment below code(table_tab2 is also extended latter):
getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted. Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO 10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified
I am getting the below error when I import a table from Prod to Dev. I understand this error will be occured if length of the datatype is low. First I got the error when the datatype(length) which is 25 for the column PASSWORD column.Then I increased the length of this column to 45, then it was imported successfully.
why am facing the error when the datatye and length for this table is same in prod and dev? What are the possible ways to import the data without increasing the PASSWORD column length?
IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "ANEES"."SALSA_WEB_ACCESS"."PASSWORD" (actual: 28, maximum: 25) [code]....
from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove
INTO TABLE TEST_TITLE( TX_SID POSITION(1:3) CHAR, ID_TITLE "get_title_id(:TITLE_NAME)" ,
[code]...
from Control file, TEST_USER and TEST_ROLE is getting populated.
Here RD_ROLE_MASTER script
CREATE TABLE RD_ROLE_MASTER ( "ID_ROLE" NUMBER(38,0) NOT NULL ENABLE, "TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE
[code]...
Here is RD_TITLE_MASTER script CREATE TABLE RD_TITLE_MASTER( "ID_TITLE" NUMBER(38,0) NOT NULL ENABLE, "TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);
Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) values (7,'RED_LOB_ESCALATION_L1'); what is the problem?
I Have one csv file.i want to load to a table trough sql*loader.but in table 3 column is there.but in the csv file some record hav one semicolumn in last filed like this
I wanted to export a table "emp_production" from Production database then import it as "emp_datawarehouse" in Data warehouse database.Both tables has same structure. I have granted IMPORT FULL DATABASE & EXPORT FULL DATABASE privileges to both schema
I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.
SQL> desc EMPLOYEE Name Type EMP_ID NUMBER(10) -- PrimaryKey EMP_NAME VARCHAR2(30) DEPT_ID NUMBER(10) -- ForeignKey from DEPARTMENT
SQL> desc DEPARTMENT Name Type DEPT_ID NUMBER(10) DEPT_NAME VARCHAR2(30)
myFile.txt ------------ 1,Edward,Account 2,Andrew,Finance 3,Sam, IT
CONTROL FILE (SQLLOADER) ------------ load data infile myFile.txt append into table EMPLOYEE FIELDS TERMINATED BY ',' (EMP_ID, EMP_NAME, DEPT_ID ) <--- ?? What should do in here
what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME. If there any sql statement can be used in control file?
im trying to create an external table, and i load my data without no problem, and everything is fine, but i got some behavior with one column that i would like to know whats behind scenes, OK let's get the example:
[*] Sample Data Line 1:333 1111111112009100000000000080000000013450.33 Line 2:11111111111220091016000000004.48 Line 3:222222222 220091016000000004.48 Line 4:(This is a blank line left)
As you can see i can upload my table with no problem but i always get 3 lines counting last blank line if i try LOAD WHEN COL_A != BLANKS, i dont know if its a problem of the blank space left between fixed fields length, but if i do LOAD WHEN COL_B != BLANKS i get correct result 2 lines instead of 3, i want to know why (missing fields...) and (reject rows...) are not working...
Note: COL_A could be 9-11 length, if length its 9 then 2 spaces left before next one...
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?
i am trying to use exp/imp utility through cmd and exp/imp is done successfully as per message given at last. but data is not import in targeted user.
Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:UsersNeetesh>exp
Export: Release 10.2.0.1.0 - Production on Thu Jul 12 14:18:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: scott/tiger@localdb
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > d:/scott_data
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes > y
Compress extents (yes/no): yes > n
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
C:UsersNeetesh>imp
Import: Release 10.2.0.1.0 - Production on Thu Jul 12 14:20:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: localaepuser/flair22@localdb
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Import file: EXPDAT.DMP > d:/scott_data
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > y
Import entire export file (yes/no): no > y
importing SCOTT's objects into LOCALAEPUSER Import terminated successfully without warnings.
I'm not sure if this is so much a SQL Loader problem as it is a database understanding problem, but here it is. I am having trouble loading data into a table (using SQL Loader) due to the fact that I am trying to load data row by row, into corresponding columns.
TestFile.csv
testvalue1, 123445 testvalue2, test testvalue3, 455321 testvalue4, 65742 testvalue5, 5719
So, using the above data, I am trying to load the value for 'testvalue1' into a column defined as 'testvalue1'; the value for 'testvalue2' into a column defined as 'testvalue2' and so on. From my understanding, SQL loader loads by column not by row, so I am not even sure if this is possible.
