JD Edwards CNC

  Monitor space used in ASM Disk Groups SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name' COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size' COLUMN block_size FORMAT 99,999 HEAD 'Block|Size' COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size' COLUMN state FORMAT a11 HEAD 'State' COLUMN type FORMAT a6 HEAD 'Type' COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)' COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used' break on report on disk_group_name skip 1 compute sum label "Grand Total: " of total_mb used_mb on report SELECT name ...

  Locked Sessions and Locked Objects PROMPT Blocked and Blocker Sessions col blocker_sid format 99999999999 col blocked_sid format 99999999999 col min_blocked format 99999999999 col request format 9999999 select /*+ ORDERED */ blocker.sid blocker_sid , blocked.sid blocked_sid , TRUNC(blocked.ctime/60) min_blocked , blocked.request from (select * from v$lock where block != 0 and type = 'TX') blocker , v$lock blocked where blocked.type='TX' and blocked.block = 0 and blocked.id1 = blocker.id1; prompt blocked objects from V$LOCK and SYS.OBJ$ set lines 132 col BLOCKED_OBJ format a35 trunc select /*+ ORDERED */ l.sid , l.lmode , TRUNC(l.ctime/60) min_blocked , u.name||'.'||o.NAME blocked_obj from (select * from v$lock where type='TM' and sid in (select sid from v$lock where block!=0)) l , sys.obj$ o , sys.user$ u where o.obj# = l.ID1 and o.OWNER# = u.user# / prompt blocked sessions from V$LOCK select /*+ ORDERED */ blocker.sid b...

  Identify active transactions in undo and rollback segments col o format a10 col u format a10 select osuser o, username u, sid, segment_name s, substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) And substr(sa.sql_text,1,200) is not null order by 3; col name format a8 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti 'Active transactions' select username, osuser, t.start_time, r.name, t.used_ublk "ROLLB BLKS", decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status) )) status from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s where t.xidusn = r.usn and t.ses_addr = s.saddr /

  Find SQL being executed by a OS Process ID (PID) prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID;

  Script – Database structure and file location set pagesize 500 set linesize 130 Prompt Prompt Control Files Location >>>> col name format a60 heading "Control Files" select name from sys.v_$controlfile / Prompt Prompt Redo Log File Locations >>>> Prompt col Grp format 9999 col member format a50 heading "Online REDO Logs" col File# format 9999 col name format a50 heading "Online REDO Logs" break on Grp select group#,member from sys.v_$logfile / Prompt Data Files Locations >>>> col Tspace format a25 col status format a3 heading Sta col Id format 9999 col Mbyte format 999999999 col name format a50 heading "Database Data Files" col Reads format 99,999,999 col Writes format 99,999,999 break on report compute sum label 'Total(MB)' of Mbyte on report select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte, ...

Use the below query to find table space usage  set pages 10000 set linesize 250 col tablespace_name format a15 heading 'TableSpace|Name' col AvailSpace format 9999990.90 heading 'Total Size|(in Mb)' col FreeSpace format 9999990.90 heading 'FreeSpace |(in Mb)' col UsedSpace format 9999990.90 heading 'UsedSpace |(in Mb)' SELECT  dts.tablespace_name,  (ddf.bytes / 1024 / 1024) "AvailSpace",  (ddf.bytes - (dfs.bytes))/1024/1024 "UsedSpace",  (dfs.bytes / 1024 / 1024) "FreeSpace",  TO_CHAR(((ddf.bytes - (dfs.bytes)) / ddf.bytes * 100),'990.00') "Used %"  FROM  sys.dba_tablespaces dts,  (select tablespace_name, sum(bytes) bytes   from dba_data_files group by tablespace_name) ddf,    (select tablespace_name, sum(bytes) bytes     from dba_free_space group by tablespace_name) dfs     WHERE      dts.tablespace_name = ddf.tablespace_name(+)     AND dts.tablespace_name = dfs....