| one Tablespace having two users [message #420332] |
Fri, 28 August 2009 06:49  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
I have a tablespace name user.ora.In this user tablespace there are two users user1 and user2 which have been assigned user.ora tablespace .the main user is user1 having objects like procedure,function.The second user is empty.I have enabled audit trail in user2 schema.I wanted to get objects from user1 schema in user2.Could you please tell me how to do it?
Appreciate your help on the above?
Regards
|
|
|
|
|
|
| Re: one Tablespace having two users [message #420355 is a reply to message #420332] |
Fri, 28 August 2009 09:38  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | I have a tablespace name user.ora
|
If you do, you want to beat your dba with sticks. The only way to get a tablespace called user.ora would be to wrap Tablespace name in "" when you created it, and there's no excuse for that that I can think of.
| Quote: | The main user is user1 having objects like procedure,function.The second user is empty
|
Procedure and Functions don't exist in tablespaces - only tables and related objects like indexes, materialized views, etc exist in a tablespace.
| Quote: | I have enabled audit trail in user2 schema
|
Ok.
| Quote: | I wanted to get objects from user1 schema in user2.Could you please tell me how to do it?
|
If you want to access User1's objects from User2, without prefixing each object with 'USER1.' you want to look at CREATE SYNONYM
|
|
|
|
| Re: one Tablespace having two users [message #420375 is a reply to message #420355] |
Fri, 28 August 2009 13:05 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| JRowbottom wrote on Fri, 28 August 2009 10:38 | | Quote: | I have a tablespace name user.ora
|
If you want to access User1's objects from User2, without prefixing each object with 'USER1.' you want to look at CREATE SYNONYM
|
I think this person wants to actually create the objects in user2 that were in user1, but put them in a different tablespace, but with the tongue twister of a post like this, any one of us might be correct.
|
|
|
|
|
|
| Re: one Tablespace having two users [message #420546 is a reply to message #420413] |
Mon, 31 August 2009 04:49 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
I have a tablespace by the name user.ora.This tablespace all the system level and object level privelges are given like
CREATE TABLESPACE USER DATAFILE ' \DATAFILE\USER.ORA' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
I create two schema users as user1 and user2
create user user1 identified by user1 default tablespace user temporary tablespace temp;
create user user2 identified by user2 default tablespace user temporary tablespace temp;
Now I Import the dump in USER1 schema whereas USER2 SCHEMA IS EMPTY SCHEMA.
Now granted the system level privilges to both the schemas;
grant connect,resource,dba,exp_full_database,imp_full_database to user1;
grant connect,resource,dba,exp_full_database,imp_full_database to user2;
This privelges I am giving from user2 schema
grant select any table to user1;
grant insert any table to user1;
grant update any table to user1;
grant delete any table to user1;
grant execute any procedure to user1;
No Objects exist in USER2 SCHEMA WHEREAS OBJECT ARE THERE IN USER1 SCHEMA.Now i WANTED TO RETRIEVE DATA FROM USER1 SCHEMA THROUGH USER2 SCHEMA.
I can retrieve data through USER2 SCHEMA by giving privelege as USER1.OBJ_NAME.
Now since my USER2 SCHEMA IS EXPTY SCHEMA.How I should export the objects which are there in user1 schema through user2 schema.
exp user2/user2@test file=user2(20090827).dmp log=user2.log
As mention earlier have been said
you have to grant privilege to users2 to access your users1 objects from the tablespace?
Now the question comes here which other priveleges I should grant IF I wanted to access objects from user2 schema which are there in user1 schema.?
Appreciate your help on the above?
Regards
|
|
|
|
|
|
| Re: one Tablespace having two users [message #420567 is a reply to message #420546] |
Mon, 31 August 2009 08:08 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I still cannot understand what you are trying to say, but if you are asking how to run the export command from one schema to get the objects in another users schema, you cannot.
If you are asking how to select from tables in another schema, then preface the table name with the schema name as such.
select * from user1.table1; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
