How to change the ABAP schema in Oracle 11g

[Former Member]

Hi,

I have completed the Import process on target system.but later found that by mistake i have selected the sap default ABAP schema i.e. SAPSR3 on target server rather than changing the ABAP schema to SAPBW1 in sync with source system.

Is it possible to change the ABAP schema on oracle for BI 7.0(ABAP ) as i have done it for MS sql server earlier through scripts provided in an SAP note but not sure about oracle.

please provide any hints or links which suggests the same.

Regards,

Dheeraj

[Former Member]

Hello Dheeraj

I think you are talking of owner of schema to be changed from sapsr3 to sapbw1 I hope.

If that is the case you can change it first you need to drop the user sapsr3 then create the sapbw1 user granting all the permissions required.

Regards

Vivek

Edited by: vivekwali on May 5, 2011 8:48 PM

[Former Member]

Hello Vivek,

Thanks,

Can you pls let me know the exact steps to follow for changing the schema user and table spaces and the SQL commands to execute as i am not able to find the same.

Regards,

Dheeraj

[JPReyes]

Until 10g renaming a schema was not an option the only way to change a schema was to do a homogeneous system copy and import the system with the desired schema.

As far as I read it should be possible with 11g but I haven't had the need to do it and I can't find any reliable documentation for it.

My recommendation will be to stick to the supported way and do a HSC.

Regards

Juan

[Former Member]

Hello Dheeraj

Have a look at this note 400241 I hope it will help you in fixing your issue of owner change.

Regards

Vivek

[JPReyes]

@Vivek,

Note 400241 is about the ops$ users, its not related to the question at all.

Juan

[former_member208104]

Hi,

I belive only option is at db level export schema level, drop schema, create schema with new name, import schema level with remap option.

-Sanjay

[Former Member]

Hello Deeraj

I mentioned that note as that gives you how to verify who is the user who owns the schema.

Again always ABAP schema is owned SAPSR3 and SAPSR3DB owns java.

select owner from dba_tables where table_name = 'T000'; this query which is explained will give you who owns the SAP DB in your case. As t000 address the client setting table.

In your case it will be SAPSR3.

First thing should be you need to drop this user then create it providing appropriate password along with default tablespace where it has to write along with temporary tablespace to be used and quota for that user should be unlimited.

Then grant the privileges of connecting and creating.

I would suggest you to work with a DBA if you are not comfortable executing SQL statements.

Regards

Vivek

[JPReyes]

@vivek

That info is misleading, the question in this thread is about how to change the SCHEMA. Nothing to do with the owner/OPS$ user.

Regards

Juan

[Former Member]

@ Juan

In reply to my reply earlier Dheeraj mentioned this.

" Can you pls let me know the exact steps to follow for changing the schema user and table spaces and the SQL commands to execute as i am not able to find the same"

I think this confuses whether its schema or user who owns the ABAP schema.

If its a Schema then what ever you suggested is ideal method of HSC.

Regards

Vivek