Wednesday, September 17, 2008

Export errors ORA-31633 ORA-01031 ORA-01536 ORA-39171

Well , Let us have a discussion on export with data pump


step 1: I am going to create a new user to perform export with data pump .

SQL> show user
USER is "SYS"

SQL> create user samsu identified by samsu;
User created.

Let me give minimum privilege so that user samsu can log in

SQL> grant create session to samsu;
Grant succeeded.


SQL> conn samsu/samsu@tiger
Connected.
SQL> show user
USER is "SAMSU"

Let me check the privilege of samsu

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION


Note: first of all user samsu should create a directory to do export through data pump.
So , As sys user i will provide the privilege to samsu "CREATE ANY DIRECTORY"

SQL> show user
USER is "SYS"
SQL> grant create any directory to samsu;
Grant succeeded.

now let us try to create directory as samsu user.

SQL> show user
USER is "SAMSU"
SQL> create directory data_pump_directory as 'c:\test';
Directory created.

This is the directory what we will refer while export operation with data pump and ' c:\test ' is the directory where the dump file and log file will be created . so , plz ensure write permission on that directory/folder(linux/windows/or any other os) for everyone(safest way)


NOTE:
I am going to execute the command to export through data pump as samsu user.But the user is not having proper privilege to do so .So,errors are expected . Let us face the errors.
SQL> show user
USER is "SAMSU"
SQL> host expdp samsu/samsu@tiger dumpfile=file_name.dmp directory=data_pump_directory full=y


ERROR


Export: Release 10.2.0.1.0 - Production on Wednesday, 17 September, 2008 16:31:42
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges

step 2: We have to give export privilege to that user which is "EXP_FULL_DATABASE"

SQL> show user
USER is "SYS"
SQL> grant exp_full_database to samsu;
Grant succeeded.

now login as samsu and try once again the export query:

SQL> show user
USER is "SAMSU"
SQL> host expdp samsu/samsu@tiger dumpfile=file_name.dmp directory=data_pump_directory full=y

ERROR:


Export: Release 10.2.0.1.0 - Production on Wednesday, 17 September, 2008 16:55:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAMSU.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges


(reason: see ORA-31633 as when we try to export through data pump it needs to create a table under the user's schema . so, we need to provide the "create table " privilege now)

SQL> show user
USER is "SYS"
SQL> grant create table to samsu;
Grant succeeded.

Let us try once again

SQL> show user
USER is "SAMSU"
SQL> host expdp samsu/samsu@tiger dumpfile=file_name.dmp directory=data_pump_directory full=y




ERROR


Export: Release 10.2.0.1.0 - Production on Wednesday, 17 September, 2008 16:59:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'SYSTEM'


(reason: well last time it was ORA:01031 where user was not having the create table privilege,this time error is ORA:01950 which indicates user is not having any space allocated to him in his default tablespace (if not explicitly defined in the profile ,the user's default tablespace is system)

so next step is to provide space to the default tablepsace of user samsu which is system tablespace in my case


SQL> show user
USER is "SYS"

SQL> alter user samsu quota unlimited on system;
User altered.

let us try the export once again!!!

SQL> show user
USER is "SAMSU"
SQL> host expdp samsu/samsu@tiger dumpfile=file_name.dmp directory=data_pump_directory full=y


Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 9:30:12

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SAM"."SYS_EXPORT_FULL_01": sam/********@tiger dumpfile=file_name.dmp directory=data_pump_directory full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA



(As you see ,it has started)


Note: if the user is not having enough space when the export is running, export job may abort with the following error


Export: Release 10.2.0.1.0 - Production on Wednesday, 17 September, 2008 13:04:56
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SAMSU"."SYS_EXPORT_FULL_01": samsu/********@tiger dumpfile=fulltiger.dmp directory=dump full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.209 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
ORA-39171: Job is experiencing a resumable wait.
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
ORA-39171: Job is experiencing a resumable wait.
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
ORA-39171: Job is experiencing a resumable wait.
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
ORA-39171: Job is experiencing a resumable wait.
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
ORA-39171: Job is experiencing a resumable wait.



So, quota unlimited is better option if you dont know how much of space it will consume..


Thanks


have a nice day ....................

Years teach us more than books.

Berthold Auerbach

2 comments:

Anonymous said...

top [url=http://www.xgambling.org/]001casino.com[/url] hinder the latest [url=http://www.casinolasvegass.com/]free casino games[/url] unshackled no deposit perk at the best [url=http://www.baywatchcasino.com/]casino online
[/url].

Anonymous said...

Thanks Dude for step by step explanation !

Regards
Christo