OPTIMAL UNDO size

Let’s determine the optimal size for UNDO: col “ACTUAL UNDO SIZE [MByte]” format 999999 col “UNDO RETENTION [Sec]” format 99999 col “OPTIMAL UNDO RETENTION [Sec]” format 999999 SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,        SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,        ROUND((d.undo_size / (to_number(f.value) *        g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”   FROM (        SELECT SUM(a.bytes)…

How to check character set in Oracle

SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ; SELECT * FROM NLS_DATABASE_PARAMETERS select owner, table_name, column_name      from dba_tab_columns     where (data_type = ‘NCHAR’ or             data_type = ‘NVARCHAR2’ or             data_type = ‘NCLOB’) and             owner != ‘SYS’ and             owner != ‘SYSTEM’;

List all tablespaces size

SELECT /* + RULE */  df.tablespace_name “Tablespace”,  df.bytes / (1024 * 1024) “Size (MB)”,          from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size        SUM(fs.bytes) / (1024 * 1024) “Free (MB)”, Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”, Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”   FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes          …

GET PID for SID

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 405; col “SID/SERIAL” format a10 col username format a15 col osuser format a15 col program format a40 select     s.sid || ‘,’ || s.serial# “SID/SERIAL” ,     s.username ,     s.osuser ,     p.spid “OS PID” ,     s.program from     v$session s ,     v$process…

ora-27086 unable to lock file already in use netapp nfs

So….the server crashed and after reboot I got something like this: Wed DEC 04 22:51:55 EST 2013 ORA-00210: cannot OPEN the specified control file ORA-00202: control file: ‘/path/control01.ctl’ ORA-27086: unable TO LOCK file – already IN USE Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 Wed DEC 04 22:51:55 EST 2013 ORA-205 signalled during:…

Session locks

set serveroutput on BEGIN    dbms_output.enable(1000000);   for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,    b.object_name object_name, b.object_type object_type   FROM v$locked_object a, dba_objects b     WHERE xidsqn != 0     and b.object_id = a.object_id)    loop    dbms_output.put_line(‘.’);   dbms_output.put_line(‘Blocking Session : ‘||do_loop.session_id);   dbms_output.put_line(‘Object (Owner/Name): ‘||do_loop.owner||’.’||do_loop.object_name);   dbms_output.put_line(‘Object Type :…

EM12c java.lang.OutOfMemoryError: Java heap space

-rw-r—– 1 oracle oinstall 6571 Oct 22 07:32 emd.properties located in path like this: /u01/app/oracle/product/12.1.0.2/agent_inst/sysman/config/emd.properties increase the Xmx memory limit. Before this operation shutdown agent (emctl stop agent). And start him again after change. # # These are the optional Java flags for the agent # agentJavaDefines=-Xmx140M -XX:MaxPermSize=96M and increase Xmx parameter, like: agentJavaDefines=-Xmx256M -XX:MaxPermSize=96M