Ok seems attachements don't make it..
Put this in a .sql file and fire it up. Haven't checked if it need 9i
upgrading but I think it should be
Be of value.
rem pins.sql - Lists all packages and in-memory objects.
Brian Spears ... See below
spool c:\dba\lst\pins.txt
column executions format 999,999,999;
column Mem_used format 999,999,999;
col name format a30 wrap
col KEPT_YN format a12 wrap;
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PROMPT Column Numbers to Sort on: Most common is 4 - Executions
PROMPT
PROMPT . 2 3 4 5 6
PROMPT . TYPE Owner Executions Mem_used Kept? =20
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
select substr(owner,1,10) Owner
,substr(type,1,12) Type
,substr(name,1,60) Name
,executions
,sharable_mem Mem_used
,substr(' '||kept||' ',1,12) KEPT_YN
from V$DB_OBJECT_CACHE
where type in ('TRIGGER'
,'PROCEDURE'
,'PACKAGE BODY'
,'PACKAGE'
,'SEQUENCE')
order by &sort_by_column
/
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
PROMPT Useful sorts: Executions <--- Best in memory
PROMPT Mem_used <--- Large should be
PROMPT Kept? <--- Missing any ?
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
spool off
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Wednesday, July 14, 2004 12:01 PM
To: oracle-l@(protected)
Subject: RE:
ORA-04031=20
Here is my handy dandy script to help you quickly see which packages you
could pin... Cursor_sharing=3Dsimilar...often vastly reduces the amount =
of
memory used as well.
Brian Spears
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Wednesday, July 14, 2004 11:18 AM
To: 'oracle-l@(protected)'
Subject: RE:
ORA-04031=20
>> It is currently set at 4400 - how much lower would I need to go????
>> <<
From experience I think the answer depends on how large the requests
that are failing happen to be. If you routinely get errors trying to
find small chunks then your shared pool then it is badly fragmented and
you should look at large pl/sql and cursors that have reloads. Pinning
one or two large objects can have a very beneficial effect when you
suffer from 04031 errors. If the errors normally occur on chunks larger
than 4400 then you need more reserved pool and/or again to pin large
pl/sql packages.
We have worked a half-dozen 04031 related iTARs that support acknowledge
trace to Oracle memory management bugs . Pinning packages, reducing the
size of objects considered large, and increasing the space reserved for
large objects is the best you can do if your application follows the
code paths that hit these bugs.
Then you have poor application design in relation to how Oracle works,
namely, failure to use reusage SQL. The cursor_sharing =3D similar =
might
be an option. Force produced unacceptable side effects for us on 8.1.7+
so we have not tried it on 9.2. If you have SQL reuse issues then 9+
option might be available to you.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Paula_Stankus@(protected)
Sent: Wednesday, July 14, 2004 10:28 AM
To: oracle-l@(protected)
Subject: RE:
ORA-04031=20
Platform:
Oracle 9.2.0.5
Solaris 2.9
ArcSDE 8.3
I am running a DSS - geodatabase with 30 concurrent users. I am getting
=3D
ORA-04031 errors.
I have verified that last_failure_size > shared_pool_reserved_min_alloc.
=3D According to Note: 146599.1 it states that I should increase the =
=3D
hidden parameter "_shared_pool_reserved_min_alloc" to lower the number =
=3D
of objects being cached. =3D20
It is currently set at 4400 - how much lower would I need to go????
It also states I should consider increasing the =3D
shared_pool_reserved_size and shared_pool_size but these parameters seem
=3D adequate to me:
40M for shared_pool_size
4M for shared_pool_reserved_size
I have gone through NOTE: 1012046.6 "Calculating Shared Pool size" and
=3D based on that my shared_pool_size is more than adequate.
How can I more specifically size the shared pool, shared reserved pool =
=3D
as I know that if I size too large then I can start incurring overhead.
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Wednesday, July 14, 2004 8:41 AM
To: oracle-l@(protected)
Subject: RE: rman nocatalog - point in time recovery
Sometimes I find it is just expedient to use sqlplus to open the =3D
database. Sometimes, there is no other way. Rman is still a work in
progress and =3D it has been seriously improved since the 8.0.5... days
but when you are =3D doing incomplete recovery and it seems to want a =
redo
log instead of an =3D archived log, then I have found, sqlplus recover
the way you did it is the =3D easiest way. Even Oracle support has told
me this.
HTH,
Ruth
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Tuesday, July 13, 2004 4:44 PM
To: oracle-l@(protected)
Subject: RE: rman nocatalog - point in time recovery
This may not be the cleanest solution, but it worked:
rman
run {
set until logseq=3D3D1235 thread =3D3D1;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
restore database;
}
exit;
sqlplus internal
sqlplus> recover database until cancel using backup controlfile; alter
sqlplus> database open resetlogs; exit;
Is there a better way to do this?
thanks,
Peter Schauss
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Tuesday, July 13, 2004 4:12 PM
To: Oracle-L (E-mail)
Subject: rman nocatalog - point in time recovery
Enviroment: Oracle 8.1.7.4 / AIX 5.2
Scenario:
- I have an rman backup (nocatalog) and a separate control file backup
done at 11:00 pm.
- The database is in archivelog mode.
- At 11:00 am the next morning I have a failure which causes loss of the
entire database.
- Archivelogs through 10:45 are intact. The last archive log is =3D
sequence 1234.
- I have the backup files created from the 11:00 pm rman backup in the
directory to which they were backed up.
- I want to restore the database to the state it was in at 10:45 am by
applying the redo logs through number 1234.
I restore my control files by copying them to the appropriate =3D
directories on the disk (e.g. /ora1/oradata/sid/control01.ctl ...
/ora2/... =3D /ora3/...).
I run the following commands in rman
run {
set until logseq=3D3D1234 thread=3D3D1;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
restore database;
recover database;
alter database open resetlogs;
}
Oracle says:
RMAN-03002: failure during compilation of command
RMAN-03013: command type: set
RMAN-06003: ORACLE error from target database: RMAN-20206: log sequence
=3D not found in the recovery catalog
What am I missing here?
Thanks,
Peter Schauss
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------
Teach CanIt if this mail (ID 4586007) is spam:
Spam: =3D
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Ds&i=3D3D4586007&m=3D3=
Ddd09
e=3D
d4dec9a
Not spam: =3D
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Dn&i=3D3D4586007&m=3D3=
Ddd09
e=3D
d4dec9a
Forget vote: =3D
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3D3Df&i=3D3D4586007&m=3D3=
Ddd09
e=3D
d4dec9a
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: pins.sql
-- Desc: pins.sql
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
