Oracle outstanding alerts

Oracle outstanding alerts



set linesize   200
set pagesize  1000
set trimout     on
set trimspool   on
Set Feedback   off
set timing     off
set verify     off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   Outstanding Alert                                                     ---

prompt -- ----------------------------------------------------------------------- ---

prompt


column ct                          format a18              heading  "Creation Time"
column instance_name               format a8               heading  "Instance|Name"
column object_type                 format a14              heading  "Object|Type"
column message_type                format a9               heading  "Message|Type"
column message_level               format 9999             heading  "Mess.|Lev."
column reason                      format a30              heading  "Reason"
column suggested_action            format a75              heading  "Suggested|Action"

Select  
       To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
     , instance_name          
     , object_type            
     , message_type         
     , message_level       
     , reason             
     , suggested_action  
  From  dba_outstanding_alerts
 Order  By Creation_Time
;


Prompt

           



                  Instance Object         Message   Mess.                                Suggested
Creation Time      Name     Type           Type       Lev. Reason                         Action
------------------ -------- -------------- --------- ----- ------------------------------ ---------------------------------------------------------------------------
30-06-2011 13:20   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_INDEX] Add space to the tablespace
                                                            is [85 percent] full

13-09-2011 16:45   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_DOCBAS Add space to the tablespace
                                                           E] is [85 percent] full

17-10-2011 15:51   db    EVENT_CLASS    Warning       5 Metrics "Database Time Spent W Run ADDM to get more performance analysis about your system.
                                                           aiting (%)" is at 33.97673 for
                                                            event class "Network"


===========================

## Alerts database ##
set lines 250 pages 1000
col "Alter_from" for a20
col creation_time for a20
col reason for a120
select 'Alert_outstanding' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_outstanding_alerts
union
select 'History' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_alert_history 
order by 1 desc,creation_time;




## Alert log ##
set linesize 160 pagesize 0
col time for a20
col message_text for a120 head Message
SELECT to_char(originating_timestamp,'DD/MM/YYYY HH24:MI:SS') time, message_text FROM v$diag_alert_ext 
where originating_timestamp>sysdate-8/24
order by RECORD_ID ;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170
 
SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;


set linesize 254
col originating_timestamp format a50
col message_text format a70 wrap
 
select inst_id, originating_timestamp, message_text
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE  1=1   
				      --and originating_timestamp > (SYSDATE - 10/1440)
					  and originating_timestamp > (SYSDATE - 1)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' 
						 --or MESSAGE_TEXT LIKE '%TNS-%'
						 )
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;

Oracle Scheduler info schedule.sql

@schedule.sql



set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Scheduler Attribute ---

prompt -- ----------------------------------------------------------------------- ---

prompt

Set linesize 180
Set Pagesize 50


column an format a28 word_wrapped heading "Attribute|Name"
column v format a28 word_wrapped heading "Value"

select
attribute_name an
, value v
from
Dba_Scheduler_Global_Attribute
Order
By attribute_name
;


prompt

prompt -- Schedule jobs state (users only)

prompt -- ----------------------------------------------------------------------- ---


column status format a120 wrap heading "Status"


Set Heading Off
Set Feedback Off


Select status_01||' | '||status_02 status
From
(Select ' Schedule Ok '||Lpad(count(*),16) status_01 from Dba_Scheduler_Jobs where state <> 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))
, (Select ' Schedule Disabled '||Lpad(count(*),13) status_02 from Dba_Scheduler_Jobs where state = 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))
;


Prompt

Prompt

prompt -- Schedule jobs state (full)

prompt -- ----------------------------------------------------------------------- ---

set head on

column st format a10 word_wrapped heading "State"
column o format a10 word_wrapped heading "Owner"
column en format a10 word_wrapped heading "Enabled"
column ct format 999999 heading "Count"

clear breaks
break on st -
skip 1
compute Sum of ct on st

select state st
, enabled en
, owner o
, count(*) ct
from Dba_Scheduler_Jobs
Group
By state
, enabled
, owner
Order By state
, enabled
, owner
;


prompt -- Schedule jobs details

prompt -- ----------------------------------------------------------------------- ---

column o format a10 word_wrapped heading "Owner"
column jn format a30 word_wrapped heading "Job Name|(Subname - Creator)"
column jt format a17 word_wrapped heading "Job Type"
column ja format a38 heading "Job Action"
column st format a9 word_wrapped heading "State"
column lsd format a16 word_wrapped heading "Last Start Date"
column nrd format a16 word_wrapped heading "Next Run Date"
column fc format 9999 heading "Fail.|Ct."
column rc format 999999 heading "Run|Ct."
column en format a6 word_wrapped heading "Enab."
column ri format a30 word_wrapped heading "Rep.|Int."
column sosn format a24 word_wrapped heading "Sched. |Owner|Sched. Name"
column sowc format a5 word_wrapped heading "Stop|On|Wind.|Close"
column ad format a5 word_wrapped heading "Auto|Drop"

clear breaks
break on o -
skip 1

select
owner o
, job_name||'('||decode(job_subname,null,'',job_subname||' - ') ||job_creator||')' jn
, job_type jt
, replace(Substr(job_action,1,38),chr(10),' ') ja
, state st
, to_char(last_start_date, 'DD-MM-YYYY HH24:MI') lsd
, to_char(next_run_date, 'DD-MM-YYYY HH24:MI') nrd
-- , substr(repeat_interval,1,30) ri
, failure_count fc
, run_count rc
, enabled en
-- , decode(schedule_owner,null,'',schedule_owner||' - ')||schedule_name sosn
, stop_on_window_close sowc
, auto_drop ad
from
Dba_Scheduler_Jobs
Order
By owner
, state
, enabled
, job_name
;

clear breaks



--======================

SQL> @schedule.sql

-- ----------------------------------------------------------------------- --
-- Scheduler Attribute --
-- ----------------------------------------------------------------------- --

Any Key...;

Attribute
Name Value
---------------------------- ----------------------------
CURRENT_OPEN_WINDOW
DEFAULT_TIMEZONE GMT
EMAIL_SENDER
EMAIL_SERVER
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT 0
LAST_OBSERVED_EVENT
LOG_HISTORY 30
MAX_JOB_SLAVE_PROCESSES

-- Schedule jobs state (users only)
-- ----------------------------------------------------------------------- --
Any Key...;

Schedule Ok 4 | Schedule Disabled 0


-- Schedule jobs state (full)
-- ----------------------------------------------------------------------- --
Any Key...;

State Enabled Owner Count
---------- ---------- ---------- -------
DISABLED FALSE SYS 4
********** -------
sum 4

SCHEDULED TRUE APEX_04000 4
0

TRUE EXFSYS 2
TRUE ORACLE_OCM 2
TRUE SYS 6
********** -------
sum 14

-- Schedule jobs details
-- ----------------------------------------------------------------------- --
Any Key...;

Stop
On
Job Name Fail. Run Wind. Auto
Owner (Subname - Creator) Job Type Job Action State Last Start Date Next Run Date Ct. Ct. Enab. Close Drop
---------- ------------------------------ ----------------- -------------------------------------- --------- ---------------- ---------------- ----- ------- ------ ----- -----
APEX_04000 ORACLE_APEX_DAILY_MAINTENANCE( STORED_PROCEDURE WWV_FLOW_MAINT.DAILY_MAINTENANCE SCHEDULED 17-10-2011 01:00 18-10-2011 01:00 0 126 TRUE FALSE FALSE
0 SYS)

ORACLE_APEX_MAIL_QUEUE(SYS) STORED_PROCEDURE WWV_FLOW_MAIL.PUSH_QUEUE SCHEDULED 17-10-2011 15:35 17-10-2011 15:40 0 33653 TRUE FALSE FALSE
ORACLE_APEX_PURGE_SESSIONS(SYS STORED_PROCEDURE WWV_FLOW_CACHE.PURGE_SESSIONS SCHEDULED 17-10-2011 15:00 17-10-2011 16:00 0 2818 TRUE FALSE FALSE
)

ORACLE_APEX_WS_NOTIFICATIONS(S STORED_PROCEDURE WWV_FLOW_WORKSHEET_API.DO_NOTIFY SCHEDULED 17-10-2011 15:30 17-10-2011 16:00 0 5622 TRUE FALSE FALSE
YS)


EXFSYS RLM$EVTCLEANUP(SYS) PLSQL_BLOCK begin dbms_rlmgr_dr.cleanup_events; en SCHEDULED 17-10-2011 08:31 17-10-2011 09:31 0 5005 TRUE FALSE FALSE
RLM$SCHDNEGACTION(SYS) PLSQL_BLOCK begin dbms_rlmgr_dr.execschdactions('R SCHEDULED 17-10-2011 15:24 17-10-2011 16:21 0 5207 TRUE FALSE FALSE

ORACLE_OCM MGMT_CONFIG_JOB(SYS) STORED_PROCEDURE ORACLE_OCM.MGMT_CONFIG.collect_config SCHEDULED 15-10-2011 23:00 0 212 TRUE FALSE FALSE
MGMT_STATS_CONFIG_JOB(SYS) STORED_PROCEDURE ORACLE_OCM.MGMT_CONFIG.collect_stats SCHEDULED 03-10-2011 08:17 01-11-2011 01:01 0 10 TRUE FALSE FALSE

SYS FGR$AUTOPURGE_JOB(SYS) PLSQL_BLOCK sys.dbms_file_group.purge_file_group(N DISABLED 0 0 FALSE FALSE TRUE
FILE_WATCHER(SYS) DISABLED 0 0 FALSE FALSE FALSE
HM_CREATE_OFFLINE_DICTIONARY(S STORED_PROCEDURE dbms_hm.create_offline_dictionary DISABLED 0 0 FALSE FALSE FALSE
YS)

XMLDB_NFS_CLEANUP_JOB(SYS) STORED_PROCEDURE xdb.dbms_xdbutil_int.cleanup_expired_n DISABLED 0 0 FALSE FALSE TRUE
BSLN_MAINTAIN_STATS_JOB(SYS) SCHEDULED 16-10-2011 00:00 23-10-2011 00:00 0 37 TRUE FALSE FALSE
DRA_REEVALUATE_OPEN_FAILURES(S STORED_PROCEDURE dbms_ir.reevaluateopenfailures SCHEDULED 15-10-2011 23:00 0 212 TRUE FALSE FALSE
YS)

ORA$AUTOTASK_CLEAN(SYS) SCHEDULED 17-10-2011 03:00 18-10-2011 03:00 0 225 TRUE FALSE FALSE
PURGE_LOG(SYS) SCHEDULED 17-10-2011 03:00 18-10-2011 03:00 0 225 TRUE FALSE FALSE
RSE$CLEAN_RECOVERABLE_SCRIPT(S PLSQL_BLOCK sys.dbms_streams_auto_int.clean_recove SCHEDULED 17-10-2011 00:00 18-10-2011 00:00 0 222 TRUE FALSE TRUE
YS)

SM$CLEAN_AUTO_SPLIT_MERGE(SYS) PLSQL_BLOCK sys.dbms_streams_auto_int.clean_auto_s SCHEDULED 17-10-2011 00:00 18-10-2011 00:00 0 222 TRUE FALSE TRUE

Oracle REVERSE fuction

SQL> select reverse('Anuj Kumar Singh') from dual ;
Any Key...;

REVERSE('ANUJKUM
----------------
hgniS ramuK junA

Oracle Segment advisor

Segment advisor

Oracle Table shrink


SQL> SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;

Any Key...;

TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE Allocated, Mb Used, Mb Reclaimable, Mb
------------------------------ ------------------------------ ------------------ ------------- ---------- ---------------
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERS GGS_DDL_HIST TABLE 264.95 251.95 12.99
Enable row movement of the table GGATE.GGS_DDL_HIST and perform shrink, estimated savings is 13625759 bytes.
alter table "GGATE"."GGS_DDL_HIST" shrink space
alter table "GGATE"."GGS_DDL_HIST" shrink space COMPACT
alter table "GGATE"."GGS_DDL_HIST" enable row movement



SQL> alter table "GGATE"."GGS_DDL_HIST" enable row movement;

Table altered.

SQL> alter table "GGATE"."GGS_DDL_HIST" shrink space COMPACT;

Table altered.

SQL> alter table "GGATE"."GGS_DDL_HIST" disable row movement ;

Table altered.



alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;





SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id 2 3 4 5 6 7 8 9
10 /
Any Key...;

SEGMENT_NAME ALLOC_MB USED_MB RECLAIM_MB PCTSAVE
------------------------------ ---------- ---------- ---------- ----------
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TASK_ID EXECUTION
---------- ---------
GGS_DDL_HIST 264.9 252 13 5
Enable row movement of the table GGATE.GGS_DDL_HIST and perform shrink, estimated savings is 13625759 bytes.
5442 16-OCT-11

Oracle Tablespace IO detail

IO time by tablespace

column name format a30
SELECT
ts.name,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,4,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI4,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,8,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI8,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,16,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI16,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,32,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI32,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,64,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI64,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,128,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI128,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,256,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI256,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,512,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI512,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1024,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1024,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2048,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2048,
round (100*sum(FH.SINGLEBLKRDS)/sum(sum(FH.SINGLEBLKRDS)) over(),2) AS total
FROM V$FILE_HISTOGRAM fh
join v$datafile df on df.file#=fh.file# and df.ENABLED='READ WRITE'
join v$tablespace ts on TS.TS#=df.ts#
GROUP BY ts.name ORDER BY total;



NAME MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 TOTAL
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ANUJTEST 0 0 0 0 0 0 100 0 0 0 0 0 0
DROP1 0 0 0 0 0 0 0 100 0 0 0 0 0
EXAMPLE 0 0 20 20 40 20 0 0 0 0 0 0 0
PERFSTAT 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXF 0 0 0 0 0 0 0 100 0 0 0 0 0
RMAN 0 0 0 0 0 0 0 100 0 0 0 0 0
TEST 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXU 8.696 0 13.043 21.739 43.478 4.348 4.348 4.348 0 0 0 0 .02
UNDOTBS1 59.375 0 3.125 6.25 15.625 12.5 0 3.125 0 0 0 0 .03
SYSAUX 60.946 1.789 2.288 7.128 13.123 10.906 3.139 .573 .1 .004 0 0 23.62
SYSTEM 80.917 .588 .908 3.911 6.091 4.106 1.798 1.25 .386 .033 .011 0 26.68
USERS 89.227 .883 1.867 2.821 2.661 1.887 .565 .071 .018 0 0 0 49.64

Oracle Top IO objects user wise

Top IO objects Top 20 IO OBJECTS by owner [code]

set linesize 200 pagesize 100
select * from (
select t.* from (
select owner,object_name,
round(100*(sum(decode( statistic_name, 'logical reads', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as l_reads,
round(100*(sum(decode( statistic_name, 'physical reads', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads,
round(100*(sum(decode( statistic_name, 'physical reads direct', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads_d,
round(100*(sum(decode( statistic_name, 'physical writes', VALUE,'physical writes direct', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_writes,
round(100*sum(value)/sum(sum(value)) over(),2) AS total
from gv$segment_statistics
where object_type in ('TABLE', 'INDEX')
--and owner='OWNER'
and owner not in ('SYS', 'SYSTEM')
and statistic_name in ('logical reads','physical reads', 'physical reads direct', 'physical writes', 'physical writes direct')
group by object_name,owner
order by sum(value) desc )t 
where rownum < 21)
/






set linesize 300 pagesize 300
col object_name 		for a25
col owner 				for a20
col statistic_name  	for a20

SELECT ROWNUM AS RANK, Seg_Lio.*
FROM ( SELECT 
       -- St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'LIO' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'logical reads'
		ORDER BY St.VALUE DESC) Seg_Lio
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_r.* 
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'PIO Reads' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical reads'
		ORDER BY St.VALUE DESC) Seq_Pio_r
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
        St.Obj#,
        St.Object_Type,
        St.Object_Name,
        St.VALUE,
        'PIO Writes' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical writes'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
        'PIO READS Direct' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical reads direct'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'DB Block changes' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'db block changes'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20;
		




define statistic_name='physical writes'

set lines 140 pages 100 
col owner format A12
col object_name format A30
col statistic_name format A30 
col object_type format A10
col value format 99999999999
col perc format 99.99
undef statistic_name
break on statistic_name
with  segstats as (
       select * from (
         select inst_id, owner, object_name, object_type , value , 	rank() over (partition by  inst_id, statistic_name order by value desc ) rnk , statistic_name from gv$segment_statistics 
          where value >0  
       and statistic_name like '%'||'&&statistic_name' ||'%'
        ) where rnk <31
       )  , 
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id) 
 select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type,a.value,(a.value/b.sum_value)*100 perc
    from segstats a ,   sumstats b 
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name, a.value desc
/





with  segstats as (
    select * from (
       select  inst_id, owner, object_name, object_type ,  statistic_name , value,    rank ()  over (partition by  inst_id, statistic_name order by value desc )  rnk
       from (
         select inst_id, owner, object_name, object_type ,  statistic_name , sum(value) value  from gv$segment_statistics
          where value >0 
         and statistic_name like '%'||'&&statistic_name' ||'%'
         group by inst_id, owner, object_name, object_type, statistic_name
        )
     ) where rnk <31
    ),
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
 select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type,a.value,(a.value/b.sum_value)*100 perc
    from segstats a ,   sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name,  a.inst_id, a.value desc
/

Oracle desc table

desc.sql


!cat desc1.sql

spool /tmp/&&1.txt
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SELECT 'SELECT ' FROM DUAL;
select chr(9)||lower(column_name)||',' from USER_tab_columns
where table_Name = UPPER('&&1');
SELECT ' FROM &1 WHERE ROWNUM <2' FROM DUAL;
SET HEADING ON
UNDEFINE 1
SET VERIFY ON
SET FEEDBACK ON
SPOOL OFF









desc.sql


SET PAUSE ON
SET PAUSE 'Any Key...;'
SET PAGES 24
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
break on tab_own skip 1 on tab_name skip 1
spool tab_desc.lst
select
owner Tab_Own
,table_name Tab_Name
,column_name Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
,'NUMBER','('
||decode(to_char(data_precision)
,null,'38'
, to_char(data_precision)||
decode(data_scale,null,''
, ','||data_scale)
)
||')'
,'DATE',null
,'LONG',null
,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
) col_def
from dba_tab_columns
where table_name like upper (nvl('EMP','ANUJ')||'%')
order by 1,2,column_id,3,4
/
spool off
clear col








SQL> @desc emp
Any Key...;


TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
ANUJ EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)


HR EMPLOYEES EMPLOYEE_ID Y NUMBER(6,0)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
EMAIL Y VARCHAR2(25)
PHONE_NUMBER N VARCHAR2(20)
HIRE_DATE Y DATE
JOB_ID Y VARCHAR2(10)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
Any Key...;

TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------

HR EMP_DETAILS_VIEW EMPLOYEE_ID Y NUMBER(6,0)
JOB_ID Y VARCHAR2(10)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
LOCATION_ID N NUMBER(4,0)
COUNTRY_ID N CHAR(2)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
DEPARTMENT_NAME Y VARCHAR2(30)
JOB_TITLE Y VARCHAR2(35)
CITY Y VARCHAR2(30)
STATE_PROVINCE N VARCHAR2(25)
COUNTRY_NAME N VARCHAR2(40)
REGION_NAME N VARCHAR2(25)


SCOTT EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
Any Key...;

TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
SCOTT EMP JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)


SYS EMP EMPNO N NUMBER(38)



44 rows selected.

Oracle Sqlplus variable declare

sqlplus var declare
sqlplus variable 
sqlplus bind variable 




SQL> var h varchar2(10)
SQL> begin :h := 'abcdf'; end;
 /


PL/SQL procedure successfully completed.


SQL> print :h

H
--------------------------------
abcdf






SQL> var myvar varchar2(30)
SQL> exec :myvar := 'Anuj'

PL/SQL procedure successfully completed.


SQL> print myvar

MYVAR
----------------------------
Anuj

OR !!!

SQL> print :myvar

MYVAR
----------------------
Anuj




To unset variable 

exec :myvar := '' or exec :myvar := NULL



You cannot undefine bind variables in sqlplus


================

VARIABLE btime VARCHAR2(30)
VARIABLE etime VARCHAR2(30)
exec :btime:='2022-01-26 12:00:00';
exec :etime:='2022-01-28 12:00:00';

select
 count(*) count
--distinct a.machine,a.program,a.module
from dba_hist_active_sess_history a
where 1=1
and sample_time between to_date(:btime,'YYYY-MM-DD HH24:MI:SS') and to_date(:etime,'YYYY-MM-DD HH24:MI:SS')
;

    COUNT
----------
     41627


===

for spool 

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;


column awr new_val X
select to_char(:BgnSnap||'_'||:EndSnap) awr from dual;
spool awr_&X._file.txt

--- select 
spool off 

===

in Oracle 23c 

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>
 var h varchar2(8)
 begin :h := '123456789'; end;
 /

PL/SQL procedure successfully completed.

SQL> print :h

H
--------------------------------
12345678

SQL>