changing the schema owner

[Former Member]

hi everyone,

I am doing database refresh QAS with PRD. After restoring the PRD data into QAS DB how to change the schema owner?

[stefan_koehler]

Hi,

you cannot rename an oracle schema - there is an existing enhancement request by oracle to realize this feature. But at the moment it is not possible.

A quote from metalinknote #14013.1:

3.  Can you rename a user through Oracle?
Enhancement Request 158508.
Common workaround(s):
a - Do a user-level export of user A.
        create new user B.
        import dba/dba_passwd fromuser=A touser=B
        drop user A.
        Drawback(s) - time to do export/import
                      if you don't drop user A before importing into user B,
                      you will need enough space in the database to store
                      both users' tables.

So the only solution is to export/import the data.

But you can leave the "old" schema owner and only create a new external identified user, which is used to get the SAP schema user password.

Regards

Stefan

[former_member204746]

It wil lbe possible with Oracle 11.

until then, only way is to do a full export/import using Oracle or SAP tools... or live with it.

[fidel_vales]

Hi,

If what you want is to rename the schema SAPPRD to SAPQAS, it is not easily done.

The only possible method is to perform a system copy with R3load method.

If you perform a backup/restore, you keep the schema name

[Former Member]

Hi,

read following sap note

Note 400241 - Problems with ops$ or sapr3 connect to Oracle

regards,

kaushal