No quota on USERS tablespace? Then no EXPDP.

When i tried to export objects from a schema using EXPDP using the userid and passwd as that of the schema name, it failed.

my [DR1]$ expdp SCHEMAS=dprd PARFILE=exp_include.par
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 28 January, 2009 14:47:15
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: DPRD
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "DPRD.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'

Well, the error message clearly says “no privileges on tablespace ‘USERS’“.

Yes, oracle will create some temporary tables whilst the expdp.So, no quota on users? then no expdp. Then i granted some quota on tablespace USERS to user DPRD.

SQL>alter user DPRD quota 10M on USERS;

User altered.

While the expdp was in process, i found the below objects created on tablespace USERS

SQL>select segment_name,segment_type,bytes from dba_segments where owner='DBKPRD' and tablespace_name='USERS';
SEGMENT_NAME                   SEGMENT_TYPE                        BYTES
------------------------------ ------------------------------ ----------
SYS_LOB0000154588C00039$$      LOBSEGMENT                          65536
SYS_MTABLE_000025BDC_IND_1     INDEX                              131072
SYS_C00119124                  INDEX                               65536
SYS_IL0000154588C00039$$       LOBINDEX                            65536
SYS_EXPORT_SCHEMA_01           TABLE                              131072
SQL>

Do we have any control on the tablespace name ?
yet to find it out 😦

Advertisements

One thought on “No quota on USERS tablespace? Then no EXPDP.

  1. Pingback: Index « My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s