Home » RDBMS Server » Server Administration » one Tablespace having two users (Oracle 9i)
one Tablespace having two users [message #420332] Fri, 28 August 2009 06:49 Go to next message
manoj12
Messages: 185
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 #420334 is a reply to message #420332] Fri, 28 August 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation between tablespace, audit and your question?
This is far from clear.
Explain it, for instance give examples.

Regards
Michel
Re: one Tablespace having two users [message #420355 is a reply to message #420332] Fri, 28 August 2009 09:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4615
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 #420413 is a reply to message #420332] Sat, 29 August 2009 05:24 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

Sir !

you have to grant privilege to users2 to access your users1 objects from the tablespace
Best regards,
Malik
Re: one Tablespace having two users [message #420546 is a reply to message #420413] Mon, 31 August 2009 04:49 Go to previous messageGo to next message
manoj12
Messages: 185
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 #420548 is a reply to message #420546] Mon, 31 August 2009 04:54 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

This export is also not going to work because it will not export objects from user1 schema

Appreciate your help on the above reply?

Regards
Re: one Tablespace having two users [message #420567 is a reply to message #420546] Mon, 31 August 2009 08:08 Go to previous message
joy_division
Messages: 4615
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;
Previous Topic: index problem
Next Topic: **URGENT** Error !!! cannot attach to oracle 7 -- When start DB from using remote desktop client XP
Goto Forum:
  


Current Time: Sat Sep 24 19:46:43 CDT 2016

Total time taken to generate the page: 0.07733 seconds