Cursor related queries.
--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
--total cursors open, by username and machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
--HIGHEST_OPEN_CUR and MAX_OPEN_CUR
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count' ;
select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid ;
SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
alter system set events = '1000 trace name ERRORSTACK level 3'
#LOCKED_OBJECT
select oracle_username, object_name,decode(a.locked_mode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.locked_mode)) mode_held
from V$LOCKED_OBJECT a,DBA_OBJECTS b where a.object_id = b.object_id;
#Get Invalid Object
The first thing to do is to recompile all of your invalid objects. Sign on to the database as SYS or SYSTEM and run the ORACLE_HOME/rdbms/admin/utlrp.sql script. This will recompile all objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects.
The following example identifies and recompile invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END; /
Get Inactive sessions
SELECT username,SID,serial#,schemaname, osuser, process, machine, PROGRAM, state, seconds_in_wait,service_name, status FROM v$session WHERE status ='INACTIVE'
ALTER SYSTEM KILL SESSION 'SID, SER#';
ALTER SYSTEM KILL SESSION '236, 386';
SELECT A.username,A.SID,A.process ,b.spid,to_char(A.logon_time,'dd:mm:yyyy hh24:mi')
"logon", A.status,A.osuser,A.machine,A.PROGRAM FROM v$session A, v$process b
WHERE A.paddr=b.addr AND A.username IS NOT NULL AND status='INACTIVE'
SELECT username,status,server,machine,count(*) FROM v$session
WHERE status='INACTIVE' GROUP BY username,status,server,machine;
How Import a Oracle DB schema in new Database.
1. Copy DB dump to some directory on DB server, for example "/u02/myschema.dmp"
2. Execute below command to create Dir path for SQL and grant permission to system user.
SQL> create directory data as '/u02';
SQL> grant read,write on directory data to system;
3. Create a tablespace for new schema and define size according to DB dump size.
SQL> create tablespace ts_soadb_data datafile '/u01/newapp/11GSOASuite/oradata/SOA11G/ts_soadb_data01.dbf' size 6000M
4.execute below command to import dump file.
bash$ impdb system schemas=myschema directory=data dumpfile=myschema.dmp logfile=imprt.log;
How to resize/increase/decrease Datafile size on physical machine by SQL prompt.
ALTER DATABASE DATAFILE '/u01/app/oracle/product/OID/UAT2OIDDBFiles/UAT2OID/undotbs01.dbf' RESIZE 400M;
Change /increase sga_max_size
alter system set sga_max_size=700m scope=spfile;
shutdown immediate;
startup;
##increase sga_target
alter system set sga_target=650m;
No comments:
Post a Comment