Thursday, July 8, 2010

EXP-00008: ORACLE error 904 encountered ORA-00904: "MAXSIZE": invalid identifier ORA-01003: no statement parsed


EXP-00008: ORACLE error 904 encountered ORA-00904: "MAXSIZE": invalid identifier ORA-01003: no statement parsed

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

=============================================================
When I execute a export (EXP) in this version I found this result............................
----------------------------------------------------------------------------
---------------------------------------------------------------------------
==============================================================

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 8 13:00:12 2010

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

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\era-1>exp userid=basel2/basel2@test107 owner=basel2
file=D:\basel_dump\basel2_08072010_test107.dmp LOG=D:\basel_dump\basel2_08072010_test107.LOG

Export: Release 11.1.0.6.0 - Production on Thu Jul 8 13:00:19 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BASEL2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BASEL2
About to export BASEL2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BASEL2's tables via Conventional Path ...
. . exporting table AUDIT_SOURCE_HIST
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table AUDIT_TABLE_BASEL2
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table D_TABLE_OF_VIEW
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table LK_BANK_RATING
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table LK_CUS_RATING
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table LK_GL_ELI_CAPITAL
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
====================================================================================

Note: As I know this is a bug of 11.1.0.6 oracle version

Look at metalink note 741984.1.
Bug number is 5872788 - there exists patch as well.

solution for another oracle versions (not sure)
================================
If there been any changes made to the database. Like catexp.sql
then you should be re-running catalog.sql which will run catexp.sql.


=====================================================================================

========================================================
But When I execute same comman here I found this result............................
----------------------------------------------------------------------------
---------------------------------------------------------------------------
=========================================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 8 13:06:51 2010

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

SQL> conn stlbas/stlbas@test107
Connected.
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\era-1>
C:\Documents and Settings\era-1>exp userid=basel2/basel2@test107 owner=basel2
file=D:\basel_dump\basel2_08072010_test107.dmp LOG=D:\basel_dump\basel2_08072010_test107.LOG

Export: Release 10.2.0.1.0 - Production on Thu Jul 8 13:07:28 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BASEL2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BASEL2
About to export BASEL2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BASEL2's tables via Conventional Path ...
. . exporting table AUDIT_SOURCE_HIST 138789 rows exported
. . exporting table AUDIT_TABLE_BASEL2 457 rows exported
. . exporting table D_TABLE_OF_VIEW 0 rows exported
. . exporting table LK_BANK_RATING 193 rows exported
. . exporting table LK_CUS_RATING 27 rows exported
. . exporting table LK_GL_ELI_CAPITAL 18 rows exported
. . exporting table LK_GL_MKR 46 rows exported
. . exporting table LK_GL_OFF 1419 rows exported
. . exporting table LK_GL_OFF_1 33 rows exported
. . exporting table LK_GL_ON 504 rows exported
. . exporting table LK_GL_ON_10122009 18221 rows exported
. . exporting table LK_GL_ON_30062010_BCK 499 rows exported

how to show user profile and change its limit ?

how to show user profile and change its limit ?
==============================================
1. First see the profile which are using my user ;

SELECT *
FROM dba_users
WHERE USERNAME='ISLBAS';

2. For showing the specific profiles property

select * from dba_profiles
where profile='DEFAULT'

--profile attribute
select * from profile$

---profile names
select * from profname$

SELECT name, lcount
FROM user$
WHERE lcount <> 0;

3.In order to track password related profile limits, Oracle stores the history
of passwords for a user in user_history$.

select * from user_history$

4. To change profile limit use

ALTER PROFILE default limit
failed_login_attempts UNLIMITED;