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....

How do I connect to a PDB?   This is a common question among new DBAs, and a valid one indeed!  We are all used to connect to a database with the “/ as sysdba” option, and this is not really working for a PDB. Or is it now? Let’s find out!  Did you know that a PDB is exposed to us as a service, as a service name. When you create a new PDB, and you open it, a service with the same name as the PDB is also created.  This service name will register with the listener, and it will show in the listings of lsnrctl status, or lsnrctl service. You will use this service name of the PDB to create aliases in the tnsnames.ora file, and connect to the PDB. Let’s see the ways we can connect to a PDB.  1) The most common way of connecting to a PDB, if you are on a database server, is to connect first to the CDB, and from there to the PDB. This way you do not need to remember passwords for the admin accounts. Let’s say you have the following container database: PRODCDB, whi...

  The other day, I had an interesting challenge to look at. The maximum number of processes in the database instance was reached, and I had to review if this is was an isolated case, or something that occurred often. There is a data dictionary view that keeps track of the number of processes in the instance for each hourly interval, or however often you take AWR snapshots. Since this view is a   DBA _HIST view, and it is part of AWR, you need to have appropriate licensing in place, before you query it. You have been warned! I personally was not aware of this view! I won’t keep it from you any longer! The view name is:   DBA _HIST_RESOURCE_LIMIT . This view contains snapshots of   V$RESOURCE_LIMIT . The current value, the maximum utilization and the init parameter value for the resource are captured, along with other information. Knowing about this view, makes it easy to track the usage of processes over time, especially if your retention for AWR data is greater than ...