ORA-01919:

ORA-01919:

This example, tested on Oracle 10g , demonstrates the ORA-01919 error message, which some people find misleading. First create a user called ANAND, a role called newrole and another user called ANOTHER_USER

SQL> conn / as sysdba

Connected.

SQL> grant create session to anand

  2  identified by anand

  3  /

Grant succeeded.

SQL> create role newrole

  2  /

Role created.

SQL> create user another_user

  2  identified by another_user

  3  /

User created.

SQL>

Connect as ANAND then try to grant role NEWROLE to ANOTHER_USER. This fails but the error message is not helpful. Role NEWROLE  does exist but it has not been granted to ANAND yet:

SQL> conn anand/anand

Connected.

SQL> grant NEWROLE to another_user

  2  /

grant newrole to another_user

*

ERROR at line 1:

ORA-01919: role 'newrole' does not exist

SQL>

Grant the role NEWROLE to ANAND:

SQL> conn / as sysdba

Connected.

SQL> grant newrole to anand

  2  /

Grant succeeded.

SQL>

Then login as ANAND and try to grant NEWROLE to ANOTHER_USER again. This time the error message gives a better explanation:

SQL> conn anand/anand

Connected.

SQL> grant newrole to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01932: ADMIN option not granted for role 'NEWROLE’

SQL>

Now grant the role to ANAND with the ADMIN option:

SQL> conn / as sysdba

Connected.

SQL> grant newrole  to anand with admin option

  2  /

Grant succeeded.

SQL>

Login as ANAND again. This time the GRANT statement works:

SQL> conn anand/anand

Connected.

SQL> grant newrole to another_user

  2  /

Grant succeeded.

SQL>

IMPORT THE DATA WITHOUT EXPORTING ,DIRECTLY IMPORTING BY USING IMPDP

1   )   Importing the data from remotely without taking the export by database link :

Steps:

   Remote side :

·         Create a new user name called <prod>

Sql> Create user prod identified by prod;

·         Grant connect, resource ,dba   , imp_full_database,  exp_full_database to user prod;

·         Now log into user which we created

Conn prod/prod

·         Now create a db link

Create database link  <newlink>  connect scott identified by tiger using ‘remotedb’;

·         Now execute the impdb comment

$ ] Impdp prod/prod@testdb network_link=newlink schemas=scott remap_schema=scott:prod

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

2)    Exporting data by expdp :

Expdp  username/password  dumpfile=<filename>   logfile=<filename> schemas=<schema name>  directory=<directory name>

For Example:

Expdp  scott/tiger   dumpfile=scott.dmp logfile=scott.log schemas=scott  directory=data

3)    Importing data by impdb:

Impdp username/username dumpfile=<filename> logfile=<filename> remap_schema=<from schema: to schema> directory=<directory name>

            For Example:

          Impdp  prod/prod  dumpfile=scott.dmp logfile=scott1.log remap_schema=scott:prod    schemas=scott