No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7200
3 B. Vroman 6200
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3200
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h7
Act. users last hour0
Registered user hits last week192
Registered user hits last month1051
Go up

Packages in memory
Next thread: ORA-12547: TNS:lost contact Error when try to connect using sysdba
Prev thread: For-loop with dynamic SQL

Message Score Author Date
I have a common package that has its usage across ...... Nishant Kariya Feb 20, 2019, 13:24
Only package variables are private to each sessi...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Feb 20, 2019, 13:32
Thanks Michel for the quick response. I got your p...... Nishant Kariya Feb 20, 2019, 13:40
you need to use the synonym method, otherwise you ...... David Johnson Feb 20, 2019, 13:42
>>> <i> This sharing could only happen if schema...... Michel Cadot Feb 20, 2019, 14:06
I'm not familiar with 12c yet, in 11g My library...... David Johnson Feb 20, 2019, 14:44
What is the reason to separate libschema fro app...... Michel Cadot Feb 20, 2019, 15:33

Follow up by mail Click here


Subject: Packages in memory
Author: Nishant Kariya, India
Date: Feb 20, 2019, 13:24, 91 days ago
Oracle info: 11g/12c
Message: I have a common package that has its usage across schemas. 1 way is to create a package in some emulation schema and create synonym of that package in all schemas using that package. Another way is to create the same package individually in each schema.

We know that complete package comes in to memory when we refer any object inside it. Suppose we go with 1st method of deploying that package in emulation user and creating synonymns.

When one schema refers that package that package will be loaded in memory, Right? Now if second schema comes in and refers same package, will that package be accessed from memory or another copy of it will be loaded in to memory as it was a call from different schema?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: Michel Cadot, France
Date: Feb 20, 2019, 13:32, 91 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Only package variables are private to each session, so each one has its own copy in its UGA.
Code is shared.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: Nishant Kariya, India
Date: Feb 20, 2019, 13:40, 91 days ago
Message: Thanks Michel for the quick response. I got your point. Just want to summarize this as - all other components of a package will be shared across schema's and variables will be private to each schema.

So if we go and create synonym of a package in multiple schema's(instead of creating package in each schema) it would be a major performance and memory boost.

1 more thing. This sharing could only happen if schema 2 has privileges to access schema 1 objects, Right?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: David Johnson, United States
Date: Feb 20, 2019, 13:42, 91 days ago
Message: you need to use the synonym method, otherwise you will have multiple copies of the code in your sessions if multiple schemas are invoked.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: Michel Cadot, France
Date: Feb 20, 2019, 14:06, 91 days ago
Message:
>>> This sharing could only happen if schema 2 has privileges to access schema 1 objects, Right?

If schema 2 is the owner of the package and schema 1 the owner of the objects accessed by the package, this is correct in the general case.
There are subtleties using "AUTHID CURRENT_USER" option and, in 12c, "ACCESSIBLE BY" clause and "INHERIT PRIVILEGES" privilege.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: David Johnson, United States
Date: Feb 20, 2019, 14:44, 91 days ago
Message: I'm not familiar with 12c yet, in 11g
My library schema (libschema) packages are mostly done using

authid definer

grant execute on libshema.pkgname to appschema ;
create synonym appschema.pkgname for libschema.pkgname ;

I like to use private synonyms for this case to easily find
which objects in a schema depend on my libschema objects.




Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Packages in memory
Author: Michel Cadot, France
Date: Feb 20, 2019, 15:33, 91 days ago
Message:
What is the reason to separate libschema fro appschema (assuming libschema contains the procedural objects and appschema the other application objects like tables)? And where are views then?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here