Differences

This shows you the differences between two versions of the page.

--- usefulscripts [2019/06/05 08:25] – z0hpvk
+++ usefulscripts [2025/08/07 09:11] (current) – z0hpvk
@@ Line 62: / Line 62: @@
          df.file_name;
 </code>
+<code SQL>
+SET linesize 140
+COL owner FORMAT a15
+COL segment_name FORMAT a35
+SELECT owner, segment_name, round(sum(bytes)/1024/1024,2) "SIZE MB",
+round(sum(bytes)/1024/1024/1024,2) "SIZE GB"
+FROM dba_segments
+GROUP BY owner, segment_name
+HAVING sum(bytes) > 102400000
+ORDER BY bytes DESC;
+</code>
 <code SQL>
 SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
@@ Line 95: / Line 109: @@
 ) b
 ORDER BY PROGRAM, OSUSER;
+</code>
+<code SQL>
+select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION
+  from V$RESOURCE_LIMIT
+ where RESOURCE_NAME in ('processes','sessions');
+</code>
+<code SQL>
+set lines 180
+col opname format a30
+col target format a40
+col start_time format a20
+col last_update_time format a20
+alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
+select inst_id, sid, serial#, opname, target, start_time, last_update_time, time_remaining, elapsed_seconds
+from gv$session_longops
+where to_char(start_time, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
+and target is not null
+and time_remaining > 0
+order by elapsed_seconds DESC, start_time;
 </code>
@@ Line 113: / Line 150: @@
 from v$sgastat
 where name = 'free memory';
+</code>
+====Scheduler Jobs====
+<code SQL>
+SET LINESIZE 200
+COLUMN owner FORMAT A20
+COLUMN job_name FORMAT A30
+COLUMN job_class FORMAT A30
+COLUMN next_run_date FORMAT A25
+SELECT owner, job_name, run_count, failure_count, to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS') next_run_date
+FROM   dba_scheduler_jobs
+where enabled = 'TRUE' and owner not in ('SYS','ORACLE_OCM','EXFSYS')
+ORDER BY owner, job_name;
+</code>
+<code>
+exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
+exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
+select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;
+</code>
+====Optimiser Statistics====
+<code SQL>
+COLUMN client_name FORMAT A32
+COLUMN window_name FORMAT A20
+COLUMN job_start_time FORMAT A30
+COLUMN job_duration FORMAT A20
+COLUMN job_status FORMAT A10
+SELECT client_name,
+       window_name,
+       to_char(job_start_time, 'DD-MON-YYYY HH24:MI:SS') job_start_time,
+       job_duration,
+       job_status
+FROM   dba_autotask_job_history where client_name like '%optimizer%'
+and job_start_time > sysdate -3
+ORDER BY job_start_time;
 </code>
@@ Line 269: / Line 344: @@
 </code>
-====Patching====
+====Patching and Registry====
 <code SQL>
 SET LINESIZE 120
@@ Line 278: / Line 353: @@
 </code>
+<code SQL>
+SET LINESIZE 120
+COL COMP_NAME FORMAT A40
+COL VERSION FORMAT A20
+select COMP_NAME, VERSION, STATUS from DBA_REGISTRY;
+</code>
+====Endian Format====
+<code SQL>
+select * from v$TRANSPORTABLE_PLATFORM order by platform_id;
+select tp.platform_name, tp.endian_format
+  from v$database d, v$transportable_platform tp
+ where d.platform_name = tp.platform_name;
+</code>
 ====Grants====
 The below 3 scripts will detail what privileges have been granted to standard users.\\
 It needs to be run as a user with DBA privilege.\\
-Also before running check the table sys.user$ to see what value is needed to user#\\
 <code SQL>
 select
@@ Line 290: / Line 380: @@
 END
 from dba_sys_privs
-where grantee in (select name from sys.user$
+where grantee in (select username from dba_users where oracle_maintained = 'N');
-                  where user# > 47);
 </code>
 <code SQL>
@@ Line 301: / Line 390: @@
 END
 from dba_tab_privs
-where grantee in (select name from sys.user$
+where grantee in (select username from dba_users where oracle_maintained = 'N');
-                  where user# > 47);
 </code>
 <code SQL>
@@ Line 312: / Line 400: @@
 END
 from dba_role_privs
-where grantee in (select name from sys.user$
+where grantee in (select username from dba_users where oracle_maintained = 'N');</code>
-                  where user# > 47);</code>
 ====Dummy Data====