Search This Blog

Total Pageviews

Monday 17 October 2011

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>

Oracle DBA

anuj blog Archive