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

Privileges in EXP_FULL_DATABASE and IMP_FULL_DATABASE

What are the Privileges exist for EXP_FULL_DATABASE and IMP_FULL_DATABASE ?

Here we go,


FOR EXP_FULL_DATABASE


BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
READ ANY FILE GROUP

FOR IMP_FULL_DATABASE

CREATE TABLESPACE
DROP TABLESPACE
CREATE USER
BECOME USER
DROP USER
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
CREATE ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
DROP ANY INDEX
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE ANY VIEW
DROP ANY VIEW
CREATE ANY SEQUENCE
DROP ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
AUDIT ANY
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
DROP PROFILE
ANALYZE ANY
CREATE ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
DROP ANY DIRECTORY
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
CREATE ANY DIRECTORY
CREATE ANY LIBRARY
DROP ANY LIBRARY
CREATE ANY OPERATOR
DROP ANY OPERATOR
CREATE ANY INDEXTYPE
DROP ANY INDEXTYPE
GLOBAL QUERY REWRITE
CREATE ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
RESUMABLE
DROP ANY SQL PROFILE
CREATE ANY SQL PROFILE


have a nice day.......................
The art of being wise is knowing what to overlook.
William James

EASY STEPS : TOP 5 SQL QUERY

A performance tuning issue:

AS a DBA , you may need to find out top five sql query from your database server based on a specific time interval.
Try the following query,


sql> select sql_text,cpu_time,rownum from (SELECT SQL_TEXT,a.CPU_TIME FROM DBA_HIST_SQLTEXT b,
(SELECT c.SQL_ID SQL_ID,SUM(c.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT c WHERE c.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/15/2008','MM/DD/YYYY')AND END_INTERVAL_TIME<=TO_DATE('09/16/2008','MM/DD/YYYY')) GROUP BY c.SQL_ID) a WHERE a.SQL_ID=b.SQL_ID ORDER BY a.CPU_TIME DESC) where rownum<6;>

Note: if u require more information about a sql statement execution please try with the following views:

dba_hist_sqltext
dba_hist_sqlstat
dba_hist_snapshot
v$sql
v$sqlstats
v$sqltext


thanks , have a nice day.............

Winning isn't everything, but wanting to win is.
Vince Lombardi

Extract DDL from database objects

Easy steps to extract ddl from database objects:


In order to extract ddl we have to use GET_DDL function of DBMS_METADATA package
sample syntax

DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMA')

Note: all values should be in varchar2 (means accepted as character,so mention it in singe quotes(' ') and upper case) and schema is by default current schema(means if no schema is mentioned,it will run in current schema)

Check some examples:


a) Extract DDL from all the tables of a schema:

sql> SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner)
FROM DBA_TABLES u WHERE u.owner='&owner';


It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT



b) Extract DDL from all the Indexes of a schema:

sql> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner)
FROM DBA_indexeS u WHERE u.owner='&owner';


It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT



c) Extract DDL from a specific table of a schema:

sql> SELECT DBMS_METADATA.GET_DDL('TABLE','&table_name','&owner') from dual;

It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT
Enter value for table_name: EMP



d) Or we can try the following to Extract DDL from all the tables of current schema:

sql>SELECT DBMS_METADATA.GET_DDL('TABLE',a.table_name)
FROM USER_ALL_TABLES a
WHERE a.nested='NO' AND (a.iot_type is null or a.iot_type='IOT') ;
Note: I have excluded nested tables and overflow segments here


e) Extract DDL for all the system privilege granted to scott


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

OUTPUT:

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
----------------------------------------------------------------------------
GRANT CREATE ANY DIRECTORY TO "SCOTT"
GRANT CREATE SYNONYM TO "SCOTT"


f) Extract DDL from all the Procedures of a schema

sql>select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name,u.owner)
from dba_objects u where u.object_type = 'PROCEDURE' and u.owner='&owner';

g) Extract DDL from all the Function of a schema

sql>SELECT DBMS_METADATA.GET_DDL('FUNCTION',u.object_name,u.owner)
FROM DBA_objects u WHERE u.object_type='FUNCTION' and u.owner='&owner';


h) Extract DDL from all the Synonym of a schema


sql>SELECT DBMS_METADATA.GET_DDL('SYNONYM',u.SYNONYM_name,u.owner)
FROM DBA_synonyms u WHERE u.owner='&owner';


i) Extract DDL from all the Sequence of a schema

sql> SELECT DBMS_METADATA.GET_DDL('SEQUENCE',u.Sequence_name,u.sequence_owner)
FROM DBA_sequences u WHERE u.sequence_owner='&owner';





j) MORE:

For a Role:
sql> select DBMS_METADATA.GET_DDL('ROLE','ROLLNAME') FROM DUAL;

For a View:
sql> SELECT DBMS_METADATA.GET_DDL('VIEW','VIEWNAME','SCHEMA') FROM DUAL;

For a Synonym:
sql>SELECT DBMS_METADATA.GET_DDL('SYNONYM','NAME','SCHEMA') FROM DUAL;



SOME OTHER OBJECT TYPE WHAT WE CAN USE:


AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
DB_LINK
DEFAULT_ROLE
DIRECTORY
FUNCTION
INDEX_STATISTICS
INDEX_TYPE
OBJECT_GRANT
PACKAGE
PROCEDURE
PROFILE
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLE_DATA
TABLE_STATISTICS
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
USER
VIEW



Have A Nice Day.......................

A wise man will make more opportunities than he finds.
Francis Bacon

Welcome to my world

hi , i welcome you to the world of oracle and redhat
i will try to share my day to day problems with an expected solution on hand
be helpful to us is the moto
thanks
ershad