Analysis of Oracle export file errors and DMP file structure, EXP-00008: encountered Oracle error 904 ORA-00904: "maxsize": Invalid IDENT

EXP-00008: encountered Oracle error 904 ORA-00904: "maxsize": Invalid identifier

Cause: the Oracle version is different.

Run

C:/> exp PLX/PLX @ orcl file = C:/PLX. dmp tables = (S, SC, c)

Result Error

Export: Release 11.1.0.6.0-production on Sunday July 26 12:42:19 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options
The zhs16gbk and utf8 nchar character sets have been exported.
The server uses the utf8 character set (possible Character Set conversion)

To export the specified table through the regular path...
... Exporting table s...
EXP-00008: Oracle error 904
ORA-00904: "maxsize": Invalid identifier
... Exporting the SC table
EXP-00008: Oracle error 1003
ORA-01003: no statement parsed
... Exporting Table C...
EXP-00008: Oracle error 904
ORA-00904: "maxsize": Invalid identifier
EXP-00008: Oracle error 942
ORA-00942: Table or view does not exist
EXP-00024: Export view not installed, please notify your DBA
EXP-00000: Export termination failed

It's strange. I have never seen any errors. How can I report errors without fields.

So Google (Baidu is junk in terms of technical materials ),

One of my friends said that the data dictionary was lost. I was surprised that I exported the data dictionary under Oracle 10g client a few days ago. How could I lose the data dictionary? I was still skeptical about how to execute it.

Run

Oracle @ linux-lrn6: ~> Sqlplus sys/sys @ orcl as sysdba

SQL> @/opt/Oracle/product/10.2/db_1/rdbms/admin/catexp. SQL

To reconstruct the data dictionary.

After creation, execute the export again.

But I can execute the same statement on the server.

So I realized that it could be an EXP version problem, so I tested it.

Run an SQL statement on a 11 GB machine to check the maxsize column.

SQL> select distinct table_name from user_col_comments where column_name = 'maxsize ';

Table_name
--------------------
Exu9stou
Exu9plb
Exu9pdsu
Exu9lbcpu
Exu9pps
Exu9ixcp
Cpool $
Exu9tbcpu
Exu9tbcp
Exu9sto
Exu9ixcpu

Table_name
--------------------
Dba_cpool_info
Exu9tbs
Exu9plbu
Exu9lbcp

Then, run the same statement on the 10 Gb server without returning rows. It can be seen that maxsize is a newly added item of 11 GB in the data dictionary, and 10 Gb does not exist.

#10g
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the partitioning, OLAP and Data Mining options

SQL> DESC SYS. exu9tbs
Is the name empty? Type
---------------------------
Id not null number
Owner char (6)
Name not null varchar2 (30)
Isonline varchar2 (7)
Content varchar2 (9)
Iniext not null number
Sext not null number
Pctinc not null number
Minext not null number
Maxext not null number
Minlen number
Deflog not null number
Ext_mgt not null number
Alloc_type not null number
Blocksize not null number

#11g
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0-Production
With the partitioning, OLAP and real application testing options

SQL> DESC SYS. exu9tbs
Is the name empty? Type
--------------------------
Id not null number
Owner char (6)
Name not null varchar2 (30)
Isonline varchar2 (7)
Content varchar2 (9)
Iniext not null number
Sext not null number
Pctinc not null number
Minext not null number
Maxext not null number
Minlen number
Deflog not null number
Ext_mgt not null number
Alloc_type not null number
Blocksize not null number
Maxsize number ---> here is the problem

Therefore, when importing and exporting data, try to unify the version as much as possible. 10 Gb is imported to 10 Gb, and 11 GB is imported to 11 GB to avoid unnecessary troubles.

Now let's get down to the point and analyze the DMP format.

What is the strange DMP format? The format of the backup file exported by the exp tool in Oracle.

A brain fever suddenly opened DMP with gvim and found that it was some SQL, which surprised me very much. So I found some strange ways to modify the backup, that is, to directly modify the DMP file.

Next I will open a newly exported backup file and analyze the DMP structure.

First, open DMP, with the following lines at the top:

Dplx
Rusers
8192
0
20
0

Do not care about the numbers below. For some database parameters, dplx indicates the exported user name, D indicates the identity, PLX indicates the user name, eusers indicates the default tablespace, e indicates the identity, and users indicates the tablespace.

Continue to see the following statement:

SYS. dbms_logrep_imp.instantiate_schema (Schema_name => sys_context ('userenv', 'current _ scheme'), export_db_name => 'orcl. regress. RDBMS. dev. us. oracle. com ', inst_scn => '000000 ′);

This is a schema-based statement. In principle, each user is a schema.

We recommend that you do not modify the above items. If you change the configuration, the import will not work and will fail. Although DMP is mostly plain-text SQL, some of them are binary, if this parameter is changed, verification is affected.

Later, we can see the table creation statement,

Create Table "C" ("CNO" number (10, 0) not null enable, "cname" varchar2 (20) Not
Null enable, "cpno" number (10, 0), "ccredit" number (3, 0) not null enable) pctf
REE 10 pctused 40 initrans 1 maxtrans 255 storage (initial 65536 freelists 1 free
List groups 1 buffer_pool default) tablespace "users" Logging nocompress

Sometimes the exported tablespace is messy, you can change it in the yellow background color and replace it with the expected tablespace.

Then, the statement for inserting data is as follows:

Insert into "C" ("CNO", "cname", "cpno", "ccredit") values (: 1,: 2,: 3,: 4)
The next step is a bunch of binary data, that is, the data to be inserted, which cannot be changed. Oracle has no public format.

However, knowing this is enough to solve many import problems, especially the tablespace confusion.

A large aircraft database is very messy. An ARJ user model (I did not disclose it to the public) contains tables from tablespaces such as ARJ, M3, users, and system, the method is incorrect during import. Then I used the method of revoking permissions in the previous Oracle log to export some tables. This is an Oracle bug, but after I modified it, the tablespace to which you want to import data.

This method is used to modify the export files of two databases of a large aircraft and import them to the same user mode. The tablespaces are all uniform, and the data is not lost. Therefore, the import process is useless.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/johnstrive/archive/2010/06/10/5660729.aspx