Differences

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

--- usefulscripts [2019/02/04 09:37] – [ASM] z0hpvk
+++ usefulscripts [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 =====Scripts=====
-====Tablespaces and Data Files====
+====Tablespaces / Data Files / Blocks====
 <code SQL>
 SET PAGESIZE 140 LINESIZE 200
 COLUMN used_pct FORMAT A11
-COLUMN size_mb FORMAT 999,999
+COLUMN size_mb FORMAT 9,999,999
-COLUMN free_mb FORMAT 999,999
+COLUMN free_mb FORMAT 9,999,999
-COLUMN max_size_mb FORMAT 999,999
+COLUMN max_size_mb FORMAT 9,999,999
-COLUMN max_free_mb FORMAT 999,999
+COLUMN max_free_mb FORMAT 9,999,999
 SELECT tablespace_name,
@@ Line 61: / Line 61: @@
 ORDER BY df.tablespace_name,
          df.file_name;
+</code>
+<code SQL>
+SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
+WHERE FILE_ID = <FILE#>
+AND BLOCK_ID BETWEEN <BLOCK#> AND (<BLOCK#> + BLOCKS);
+</code>
+====Sessions====
+<code SQL>
+set linesize 200 pages 100
+col username format a20
+col osuser format a20
+col machine format a30
+col program format a40
+col logon_time format a10
+select SID, SERIAL#, USERNAME, OSUSER, MACHINE,
+       PROGRAM, STATUS, LOGON_TIME, SPID,
+       WAIT_EVENT, LOCKS_BLOCKING from (
+          select a.*
+          , (select event from v$session_wait where sid = a.sid) wait_event
+          , (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking from (
+                select s.status, s.saddr
+                , (select spid from v$process where addr = paddr) spid
+                , s.sid, decode(s.username, null, 'Server process', s.username) username
+                , osuser, machine, terminal, program
+                , logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server
+                , process, s.serial#, type, s.module, s.action, schemaname
+    from
+    v$session s, V$SESS_IO
+    where V$SESS_IO.sid (+)= s.sid
+  ) a
+) 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>
+====Memory====
+<code SQL>
+col component format a30
+SELECT  component,
+        ROUND(current_size/1024/1024) AS current_size_mb,
+        ROUND(min_size/1024/1024) AS min_size_mb,
+        ROUND(max_size/1024/1024) AS max_size_mb
+FROM    v$memory_dynamic_components
+WHERE   current_size != 0
+ORDER BY component;
+</code>
+<code SQL>
+select pool, round(bytes/1024/1024,0) FREE_MB
+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>
+====Initialisation Parameters====
+<code SQL>
+set lines 140 pages 40
+col name format a40
+col value format a50
+select name, value, isdefault, ismodified from v$parameter where isdefault = 'FALSE' order by 1;
+</code>
+===Hidden Parameters===
+<code SQL>
+SET LINESIZE 170 PAGESIZE 100
+COL PARAMETER FORMAT A50
+COL DESCRIPTION FORMAT A60 TRUNCATED
+COL SESSION_VALUE FORMAT A25
+COL INSTANCE_VALUE FORMAT A25
+SELECT a.ksppinm PARAMETER, a.ksppdesc DESCRIPTION,
+b.ksppstvl SESSION_VALUE, c.ksppstvl INSTANCE_VALUE
+FROM x$ksppi a, x$ksppcv b, x$ksppsv c
+WHERE a.indx = b.indx
+AND a.indx = c.indx
+AND a.ksppinm LIKE '/_%' escape '/'
+ORDER BY 1;
+</code>
+====Licensing====
+<code SQL>
+col name format a60
+col detected_usages format 999999
+SELECT u1.name, u1.detected_usages, u1.currently_used, u1.version
+FROM   dba_feature_usage_statistics u1
+WHERE  u1.version = (SELECT MAX(u2.version)
+                     FROM   dba_feature_usage_statistics u2
+                     WHERE  u2.name = u1.name)
+AND    u1.detected_usages > 0
+AND    u1.dbid = (SELECT dbid FROM v$database)
+ORDER BY name;
 </code>
 ====ASM====
 <code SQL>
+set echo off
 set wrap off
 set lines 155 pages 9999
@@ Line 177: / Line 330: @@
 </code>
+====Patching and Registry====
+<code SQL>
+SET LINESIZE 120
+COL ACTION_TIME FORMAT A30
+COL DESCRIPTION FORMAT A70
+select PATCH_ID, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;
+</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 189: / Line 366: @@
 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 200: / Line 376: @@
 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 211: / Line 386: @@
 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====
@@ Line 226: / Line 400: @@
 from dual
 connect by level <= 1000;
-</code>
-====Hidden Parameters====
-<code SQL>
-SET LINESIZE 170 PAGESIZE 100
-COL PARAMETER FORMAT A50
-COL DESCRIPTION FORMAT A60 TRUNCATED
-COL SESSION_VALUE FORMAT A25
-COL INSTANCE_VALUE FORMAT A25
-SELECT a.ksppinm PARAMETER, a.ksppdesc DESCRIPTION,
-b.ksppstvl SESSION_VALUE, c.ksppstvl INSTANCE_VALUE
-FROM x$ksppi a, x$ksppcv b, x$ksppsv c
-WHERE a.indx = b.indx
-AND a.indx = c.indx
-AND a.ksppinm LIKE '/_%' escape '/'
-ORDER BY 1;
 </code>