Oracle AWR snap id

Snap id
snapshot id
awr snapshot information
awr snapshot info


awr_snapid.sql

Check the current AWR interval time and retention period

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;



NOTE : Retention and interval both use value in minutes .

8 Days = 8*24*60 = 11520 minutes
7 DAYS = 7*24*60= 10080 minutes
6 DAYS = 6*24*60 = 8640 minutes



set linesize 300
 select snapshot_Interval,retention_interval_minute,retention_interval_minute/60 retention_interval_hour,(retention_interval_minute/60)/24 retention_interval_day
 from (SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval_minute 
FROM dba_hist_wr_control
)
 

SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval_minute,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
,(extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention))/24/60 days
FROM dba_hist_wr_control;  




SQL> execute  dbms_workload_repository.modify_snapshot_settings (topnsql => 'MAXIMUM');

PL/SQL procedure successfully completed.
                                                                            15 min          30 days
SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 15, retention =>43200);

PL/SQL procedure successfully completed.

SQL> 

SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval_minute,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
,(extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention))/24/60 days
FROM dba_hist_wr_control;  




SNAPSHOT_INTERVAL_MINUTE RETENTION_INTERVAL       DAYS
------------------------ ------------------ ----------
                      15              43200         30






SET HEADING off PAGESIZE 0 linesize 200
COLUMN sort_ord NOPRINT
SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE')
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
ORDER BY snap_week, sort_ord , hour_of_day
/




 20160410 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160410 04/10/16 04/11/16 04/12/16 04/13/16 04/14/16 04/15/16 04/16/16
20160410 --------- --------- --------- --------- --------- --------- --------- ---------
20160410 00:00 4296 4363 4398 4422
20160410 01:00 4297 4369 4399 4423
20160410 02:00 4298 4375 4400 4424
20160410 03:00 4299 4377 4401 4425
20160410 04:00 4300 4378 4402 4426
20160410 05:00 4301 4379 4403 4427
20160410 06:00 4302 4380 4404 4428
20160410 07:00 4303 4381 4405 4429
20160410 08:00 4304 4382 4406 4430
20160410 09:00 4305 4383 4407 4431
20160410 10:00 4306 4384 4408 4432
20160410 11:00 4307 4385 4409 4433
20160410 12:00 4308 4386 4410 4434
20160410 13:00 4309 4387 4411 4435
20160410 14:00 4310 4388 4412 4436
20160410 15:00 4311 4389 4413 4437
20160410 16:00 4315 4390 4414 4438
20160410 17:00 4321 4391 4415 4439
20160410 18:00 4327 4392 4416 4440
20160410 19:00 4333 4393 4417 4441
20160410 20:00 4339 4394 4418 4442
20160410 21:00 4293 4345 4395 4419 4443
20160410 22:00 4294 4351 4396 4420 4444
20160410 23:00 4295 4357 4397 4421 4445
20160410
20160417 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160417 04/17/16 04/18/16 04/19/16 04/20/16 04/21/16
20160417 --------- --------- --------- --------- --------- --------- --------- ---------
20160417 00:00 4446 4470 4494 4518 4542
20160417 01:00 4447 4471 4495 4519 4543
20160417 02:00 4448 4472 4496 4520 4544
20160417 03:00 4449 4473 4497 4521 4545
20160417 04:00 4450 4474 4498 4522 4546
20160417 05:00 4451 4475 4499 4523 4547
20160417 06:00 4452 4476 4500 4524 4548
20160417 07:00 4453 4477 4501 4525
20160417 08:00 4454 4478 4502 4526
20160417 09:00 4455 4479 4503 4527
20160417 10:00 4456 4480 4504 4528
20160417 11:00 4457 4481 4505 4529
20160417 12:00 4458 4482 4506 4530
20160417 13:00 4459 4483 4507 4531
20160417 14:00 4460 4484 4508 4532
20160417 15:00 4461 4485 4509 4533
20160417 16:00 4462 4486 4510 4534
20160417 17:00 4463 4487 4511 4535
20160417 18:00 4464 4488 4512 4536
20160417 19:00 4465 4489 4513 4537
20160417 20:00 4466 4490 4514 4538
20160417 21:00 4467 4491 4515 4539
20160417 22:00 4468 4492 4516 4540
20160417 23:00 4469 4493 4517 4541
20160417

56 rows selected.

 

====

define p_inst=1
define p_days =3


set linesize 400 pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;

===

define p_days=2
set linesize 200
set pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	--and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;

set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhdi.DBID,
dhs.snap_id,
to_char(dhs.begin_interval_time,'dd/MM/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'DD/MM/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= sysdate -2
order by db_name, instance_name, snap_id;

define  num_days     = 2;
define  db_name      = 'RAC';
define  dbid         = 1222414252;
define  begin_snap   = 10319;
define  end_snap     = 10320;
define  report_type  = 'html';
define  instance_numbers_or_ALL = 'ALL'
define  report_name  =  awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type

@?/rdbms/admin/awrgrpti

====

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

col  Max_SAMPLE_TIME  for a27

col Min_SAMPLE_TIME for a27

select min(SAMPLE_TIME) Min_SAMPLE_TIME,max(SAMPLE_TIME) Max_SAMPLE_TIME  from  gv$active_session_history

where 1=1

and IS_AWR_SAMPLE='N'

/

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 200 pagesize 200

col BEGIN_INTERVAL_TIME for a28

col END_INTERVAL_TIME for a28

select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot 

where 1=1

-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017' 

and begin_interval_time> sysdate -1 ;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 200 pagesize 200

col min_BEGIN_INTERVAL_TIME for a28

col MAX_BEGIN_INTERVAL_TIME for a28

select min(snap_id),min(begin_interval_time) min_BEGIN_INTERVAL_TIME,max(snap_id),max(begin_interval_time) MAX_BEGIN_INTERVAL_TIME from dba_hist_snapshot 

where 1=1

-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017' 

and begin_interval_time> sysdate -1 ;

====

from web awr_os_stat.sql

define days_history=1

define inst=1

set ver off pages 50000 lines 140 tab off  linesize 300  pages 9999

col end_snap_time format a30

col load        format 990.00           heading "OS|Load"

col num_cpus    format 9999             heading "CPU"

col mem         format 999990.00        heading "Memory|(GB)"

col oscpupct    format 990              heading "OS|CPU%"

col oscpuusr    format 990              heading "USR%"

col oscpusys    format 990              heading "SYS%"

col oscpuio     format 990              heading "IO%"

BREAK ON instance_number SKIP 1

WITH

  base_line AS

  (

SELECT

*

FROM

(

SELECT

  snp.instance_number,

  snp.end_interval_time ,

  sst.snap_id,

  sst.stat_name,

  sst.value

FROM

  dba_hist_snapshot snp,

  dba_hist_osstat sst

WHERE

  sst.instance_number = snp.instance_number

AND sst.snap_id       = snp.snap_id

AND snp.instance_number = decode(&inst,0,snp.instance_number,&inst)

AND snp.begin_interval_time >= TRUNC(sysdate)- &days_history

   )

  pivot (SUM(value) FOR (stat_name) IN (

  'LOAD'    AS LOAD,

  'NUM_CPUS'    AS NUM_CPUS,

  'PHYSICAL_MEMORY_BYTES'                      AS PHYSICAL_MEMORY_BYTES, 

  'BUSY_TIME'               AS BUSY_TIME,

  'USER_TIME'                                  AS USER_TIME,

  'SYS_TIME'                                   AS SYS_TIME,

  'IOWAIT_TIME'                                AS IOWAIT_TIME))

  )

SELECT

    b2.instance_number,

to_char(b2.end_interval_time,'MM/DD/YY HH24:MI:SS') end_snap_time,

b2.NUM_CPUS,

round(b2.LOAD,1) LOAD,

round(b2.PHYSICAL_MEMORY_BYTES/1024/1024/1024,0) mem,

(((b2.busy_time - b1.busy_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 

                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 

                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 

                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpupct,

(((b2.user_time - b1.user_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 

                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 

                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 

                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuusr,

    (((b2.sys_time - b1.sys_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 

                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 

                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 

                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpusys,

    (((b2.iowait_time - b1.iowait_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 

                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 

                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 

                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuio

FROM

  base_line b1,

  base_line b2

WHERE

     b1.instance_number = b2.instance_number

AND  b1.snap_id + 1         = b2.snap_id

ORDER BY 

  1,2 

  ;

                                                          OS     Memory   OS

INSTANCE_NUMBER END_SNAP_TIME                    CPU    Load       (GB) CPU% USR% SYS%  IO%

--------------- ------------------------------ ----- ------- ---------- ---- ---- ---- ----

              1 03/23/23 02:00:01                224   16.20     754.00   17   17    1    0

                03/23/23 03:00:08                224   27.50     754.00   16   15    1    0

                03/23/23 04:00:17                224   30.40     754.00   19   18    1    0

                03/23/23 05:00:59                224   94.80     754.00   27   24    3    2

   

from 

https://github.com/eclaro/oracle_scripts/blob/master/awrsnaps.sql

col SNAP_00 for a6 just right head "00H "

col SNAP_01 for a6 just right head "01H "

col SNAP_02 for a6 just right head "02H "

col SNAP_03 for a6 just right head "03H "

col SNAP_04 for a6 just right head "04H "

col SNAP_05 for a6 just right head "05H "

col SNAP_06 for a6 just right head "06H "

col SNAP_07 for a6 just right head "07H "

col SNAP_08 for a6 just right head "08H "

col SNAP_09 for a6 just right head "09H "

col SNAP_10 for a6 just right head "10H "

col SNAP_11 for a6 just right head "11H "

col SNAP_12 for a6 just right head "12H "

col SNAP_13 for a6 just right head "13H "

col SNAP_14 for a6 just right head "14H "

col SNAP_15 for a6 just right head "15H "

col SNAP_16 for a6 just right head "16H "

col SNAP_17 for a6 just right head "17H "

col SNAP_18 for a6 just right head "18H "

col SNAP_19 for a6 just right head "19H "

col SNAP_20 for a6 just right head "20H "

col SNAP_21 for a6 just right head "21H "

col SNAP_22 for a6 just right head "22H "

col SNAP_23 for a6 just right head "23H "

col dt head "DATE"

col DATE for a10

col INST for 9999

WITH x as (

SELECT 

trunc(end_interval_time) DT, INSTANCE_NUMBER,

min(case when extract(hour from end_interval_time) =  0 then snap_id else NULL end) SN00,

min(case when extract(hour from end_interval_time) =  1 then snap_id else NULL end) SN01,

min(case when extract(hour from end_interval_time) =  2 then snap_id else NULL end) SN02,

min(case when extract(hour from end_interval_time) =  3 then snap_id else NULL end) SN03,

min(case when extract(hour from end_interval_time) =  4 then snap_id else NULL end) SN04,

min(case when extract(hour from end_interval_time) =  5 then snap_id else NULL end) SN05,

min(case when extract(hour from end_interval_time) =  6 then snap_id else NULL end) SN06,

min(case when extract(hour from end_interval_time) =  7 then snap_id else NULL end) SN07,

min(case when extract(hour from end_interval_time) =  8 then snap_id else NULL end) SN08,

min(case when extract(hour from end_interval_time) =  9 then snap_id else NULL end) SN09,

min(case when extract(hour from end_interval_time) = 10 then snap_id else NULL end) SN10,

min(case when extract(hour from end_interval_time) = 11 then snap_id else NULL end) SN11,

min(case when extract(hour from end_interval_time) = 12 then snap_id else NULL end) SN12,

min(case when extract(hour from end_interval_time) = 13 then snap_id else NULL end) SN13,

min(case when extract(hour from end_interval_time) = 14 then snap_id else NULL end) SN14,

min(case when extract(hour from end_interval_time) = 15 then snap_id else NULL end) SN15,

min(case when extract(hour from end_interval_time) = 16 then snap_id else NULL end) SN16,

min(case when extract(hour from end_interval_time) = 17 then snap_id else NULL end) SN17,

min(case when extract(hour from end_interval_time) = 18 then snap_id else NULL end) SN18,

min(case when extract(hour from end_interval_time) = 19 then snap_id else NULL end) SN19,

min(case when extract(hour from end_interval_time) = 20 then snap_id else NULL end) SN20,

min(case when extract(hour from end_interval_time) = 21 then snap_id else NULL end) SN21,

min(case when extract(hour from end_interval_time) = 22 then snap_id else NULL end) SN22,

min(case when extract(hour from end_interval_time) = 23 then snap_id else NULL end) SN23,

max(case when extract(hour from end_interval_time) =  0 then startup_time else NULL end) ST00,

max(case when extract(hour from end_interval_time) =  1 then startup_time else NULL end) ST01,

max(case when extract(hour from end_interval_time) =  2 then startup_time else NULL end) ST02,

max(case when extract(hour from end_interval_time) =  3 then startup_time else NULL end) ST03,

max(case when extract(hour from end_interval_time) =  4 then startup_time else NULL end) ST04,

max(case when extract(hour from end_interval_time) =  5 then startup_time else NULL end) ST05,

max(case when extract(hour from end_interval_time) =  6 then startup_time else NULL end) ST06,

max(case when extract(hour from end_interval_time) =  7 then startup_time else NULL end) ST07,

max(case when extract(hour from end_interval_time) =  8 then startup_time else NULL end) ST08,

max(case when extract(hour from end_interval_time) =  9 then startup_time else NULL end) ST09,

max(case when extract(hour from end_interval_time) = 10 then startup_time else NULL end) ST10,

max(case when extract(hour from end_interval_time) = 11 then startup_time else NULL end) ST11,

max(case when extract(hour from end_interval_time) = 12 then startup_time else NULL end) ST12,

max(case when extract(hour from end_interval_time) = 13 then startup_time else NULL end) ST13,

max(case when extract(hour from end_interval_time) = 14 then startup_time else NULL end) ST14,

max(case when extract(hour from end_interval_time) = 15 then startup_time else NULL end) ST15,

max(case when extract(hour from end_interval_time) = 16 then startup_time else NULL end) ST16,

max(case when extract(hour from end_interval_time) = 17 then startup_time else NULL end) ST17,

max(case when extract(hour from end_interval_time) = 18 then startup_time else NULL end) ST18,

max(case when extract(hour from end_interval_time) = 19 then startup_time else NULL end) ST19,

max(case when extract(hour from end_interval_time) = 20 then startup_time else NULL end) ST20,

max(case when extract(hour from end_interval_time) = 21 then startup_time else NULL end) ST21,

max(case when extract(hour from end_interval_time) = 22 then startup_time else NULL end) ST22,

max(case when extract(hour from end_interval_time) = 23 then startup_time else NULL end) ST23

FROM dba_hist_snapshot

JOIN v$database using (DBID)

group by trunc(end_interval_time), INSTANCE_NUMBER

)

select 

to_char(DT,'YYYY-MM-DD') DT, INSTANCE_NUMBER as INST,

lpad(SN00 || case when ST00 is NOT NULL and ST00 <> lag(ST23) over (ORDER BY DT) then '*' else ' ' end,6,' ') as SNAP_00,

lpad(SN01 || case when ST01 is NOT NULL and ST01 <> ST00 then '*' else ' ' end                        ,6,' ') as SNAP_01,

lpad(SN02 || case when ST02 is NOT NULL and ST02 <> ST01 then '*' else ' ' end                        ,6,' ') as SNAP_02,

lpad(SN03 || case when ST03 is NOT NULL and ST03 <> ST02 then '*' else ' ' end                        ,6,' ') as SNAP_03,

lpad(SN04 || case when ST04 is NOT NULL and ST04 <> ST03 then '*' else ' ' end                        ,6,' ') as SNAP_04,

lpad(SN05 || case when ST05 is NOT NULL and ST05 <> ST04 then '*' else ' ' end                        ,6,' ') as SNAP_05,

lpad(SN06 || case when ST06 is NOT NULL and ST06 <> ST05 then '*' else ' ' end                        ,6,' ') as SNAP_06,

lpad(SN07 || case when ST07 is NOT NULL and ST07 <> ST06 then '*' else ' ' end                        ,6,' ') as SNAP_07,

lpad(SN08 || case when ST08 is NOT NULL and ST08 <> ST07 then '*' else ' ' end                        ,6,' ') as SNAP_08,

lpad(SN09 || case when ST09 is NOT NULL and ST09 <> ST08 then '*' else ' ' end                        ,6,' ') as SNAP_09,

lpad(SN10 || case when ST10 is NOT NULL and ST10 <> ST09 then '*' else ' ' end                        ,6,' ') as SNAP_10,

lpad(SN11 || case when ST11 is NOT NULL and ST11 <> ST10 then '*' else ' ' end                        ,6,' ') as SNAP_11,

lpad(SN12 || case when ST12 is NOT NULL and ST12 <> ST11 then '*' else ' ' end                        ,6,' ') as SNAP_12,

lpad(SN13 || case when ST13 is NOT NULL and ST13 <> ST12 then '*' else ' ' end                        ,6,' ') as SNAP_13,

lpad(SN14 || case when ST14 is NOT NULL and ST14 <> ST13 then '*' else ' ' end                        ,6,' ') as SNAP_14,

lpad(SN15 || case when ST15 is NOT NULL and ST15 <> ST14 then '*' else ' ' end                        ,6,' ') as SNAP_15,

lpad(SN16 || case when ST16 is NOT NULL and ST16 <> ST15 then '*' else ' ' end                        ,6,' ') as SNAP_16,

lpad(SN17 || case when ST17 is NOT NULL and ST17 <> ST16 then '*' else ' ' end                        ,6,' ') as SNAP_17,

lpad(SN18 || case when ST18 is NOT NULL and ST18 <> ST17 then '*' else ' ' end                        ,6,' ') as SNAP_18,

lpad(SN19 || case when ST19 is NOT NULL and ST19 <> ST18 then '*' else ' ' end                        ,6,' ') as SNAP_19,

lpad(SN20 || case when ST20 is NOT NULL and ST20 <> ST19 then '*' else ' ' end                        ,6,' ') as SNAP_20,

lpad(SN21 || case when ST21 is NOT NULL and ST21 <> ST20 then '*' else ' ' end                        ,6,' ') as SNAP_21,

lpad(SN22 || case when ST22 is NOT NULL and ST22 <> ST21 then '*' else ' ' end                        ,6,' ') as SNAP_22,

lpad(SN23 || case when ST23 is NOT NULL and ST23 <> ST22 then '*' else ' ' end                        ,6,' ') as SNAP_23

from x

order by INSTANCE_NUMBER, DT

;

DATE        INST   00H    01H    02H    03H    04H    05H    06H    07H    08H    09H    10H    11H    12H    13H    14H    15H    16H  17H    18H    19H    20H    21H    22H    23H

---------- ----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------

2020-10-24     1 105336 105339 105343 105347 105351 105355 105359 105363 105367 105371 105375 105379 105383 105387 105391 105395 105399 105403 105407 105411 105415 105419 105423 105427

Oracle table , col and index Statistics

Script for table , col and index Statistics
Script table col index

table stat
index stat
col stat

prompt table_info.sql

-- Author : Daniel W. Fink OptimalDBA.com
--- table ,col and index Statistics




COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT


SELECT TO_NUMBER(t.degree) tab_degree,
t.partitioned tab_partitioned,
t.num_rows tab_num_rows,
t.blocks tab_alloc_blocks,
(t.blocks - t.empty_blocks) tab_hwm_blocks,
TO_CHAR(t.last_analyzed, 'MM/DD/YYYY hh24:mi') tab_last_analyzed_time,
ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2) tab_analyzed_pct,
t.avg_space tab_avg_space,
t.avg_row_len tab_avg_row_length,
t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
-- WHERE t.table_name = UPPER('owner_table')
ORDER BY t.table_name
/




PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

SELECT i.index_name ind_name,
i.status ind_status,
DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique,
i.blevel ind_blevel,
i.leaf_blocks ind_leafblocks,
i.num_rows ind_numrows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, 'MM/DD/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
ORDER BY i.uniqueness DESC, i.index_name
/

COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'

BREAK ON index_name NODUP

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT

WITH col_hi_lo_vals AS
( select tc.column_name
, tc.data_type
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
from dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position
/


COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A20 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A10 HEADING 'Nullable?'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.99999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A16 HEADING 'Histogram'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

SELECT tc.column_name tab_column_name,
tc.data_type tab_column_datatype,
DECODE(tc.nullable, 'N', 'NOT NULL', NULL) tab_column_nullable,
tc.num_distinct tab_column_numdistinct,
tc.density tab_column_density,
tc.num_nulls tab_column_numnulls,
DECODE(tc.histogram,'NONE', NULL, tc.histogram) tab_column_histogram,
TO_NUMBER(DECODE(tc.num_buckets,1,NULL,tc.num_buckets)) tab_column_numbuckets
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
ORDER BY tc.column_id
/


undefine owner_table



*********************************************************************************
Enter value for owner_table: scott.emp
Table Statistics for scott.emp
*********************************************************************************

old 12: WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
new 12: WHERE t.owner||'.'||t.table_name = UPPER('scott.emp')

Allocated HWM Analyze Avg Block Avg Row
Deg Prtn Rows Blocks Blocks Analyzed Date Pct Free Space Length Mntr
----- ---- ------------ ------------ ------------ ----------------- ------- ---------- ------- ----
1 NO 14 4 4 10/05/2011 22:01 100.00 0 38 YES



*********************************************************************************
Index Statistics for scott.emp
*********************************************************************************

old 11: WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
new 11: WHERE i.table_owner||'.'||i.table_name = UPPER('scott.emp')

IND_NAME IND_STAT I IND_BLEVEL IND_LEAFBLOCKS IND_NUMROWS IND_DISTINCTKEYS IND_CLUFAC LAST_ANALYZED_TI
------------------------------ -------- - ---------- -------------- ----------- ---------------- ---------- ----------------
SYS_C0022543 VALID Y 0 1 14 14 1 10/05/2011 22:01



*********************************************************************************
Index Columns for scott.emp
*********************************************************************************

old 9: WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
new 9: WHERE tc.owner||'.'||tc.table_name = UPPER('scott.emp')
old 82: WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
new 82: WHERE ic.table_owner||'.'||ic.table_name = UPPER('scott.emp')

Index Name Column Name Low Value High Value
------------------------------ ------------------------------ -------------------- --------------------
SYS_C0022543 EMPNO 7369 7934



*********************************************************************************
Column Statistics for scott.emp
*********************************************************************************

old 10: WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
new 10: WHERE tc.owner||'.'||tc.table_name = UPPER('scott.emp')

Distinct Number
Column Name Datatype Nullable? Values Density of Nulls Histogram Buckets
------------------------------ -------------------- ---------- ------------ -------- ------------ ---------------- --------
EMPNO NUMBER NOT NULL 14 .07143 0
ENAME VARCHAR2 14 .07143 0
JOB VARCHAR2 5 .20000 0
MGR NUMBER 6 .16667 1
HIREDATE DATE 13 .07692 0
SAL NUMBER 12 .03571 0 FREQUENCY 12
COMM NUMBER 4 .25000 10
DEPTNO NUMBER 3 .33333 0

8 rows selected.

Oracle wait info

Oracle wait info .sql


wait.sql



REM Author : Daniel W. Fink OptimalDBA.com



wait.sql


SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/

Oracle create user script  ....

I am creating script to create scott user .

edit user_script.sql script as per your requirement .

create user script

user_script.sql

SQL> !cat user_script.sql
--accept dbaconnect char default '/' -
-- prompt 'Enter DBA username/password [ default / ] : '
set verify off scan on pagesize 0 feedback off recsep off echo off pause off
spool create_SCOTT.sql
select 'create user SCOTT identified by SCOTT' || chr(10) ||'default tablespace ' || default_tablespace || chr(10) ||'temporary tablespace ' || temporary_tablespace || chr(10) ||'profile ' || profile || chr(10) ||'/'
from dba_users
where username = upper('SCOTT')
/
select 'alter user SCOTT' || chr(10) ||'quota ' || decode(sign(nvl(max_bytes, -1)),-1, 'unlimited ',to_char(max_bytes / 1024) || ' K ')|| 'on ' || tablespace_name || chr(10) ||'/'
from dba_ts_quotas
where username = upper('SCOTT')
and max_bytes != 0
/
select 'grant ' || privilege || ' to SCOTT' || chr(10) ||
decode(admin_option, 'YES', 'with admin option;', '/')
from dba_sys_privs
where grantee = upper('SCOTT')
/
--
-- Non default roles are not handled by the procedure
--
select 'grant ' || granted_role || ' to SCOTT' || chr(10) ||decode(admin_option, 'YES', 'with admin option;', '/')
from dba_role_privs
where grantee = upper('SCOTT')
/
column dummy noprint
select grantor dummy,1 dummy,'connect SCOTT;dbaconnect' || chr(10) ||'@@become ' || grantor
from (select grantor from dba_tab_privs
where grantee = upper('SCOTT')
union
select grantor from dba_col_privs
where grantee = upper('SCOTT'))
union
select grantor, 2,'grant ' || privilege || ' on ' || owner || '.' || table_name|| chr(10) ||'to SCOTT' || decode(grantable, 'YES', ' with grant option;', ';')
from dba_tab_privs
where grantee = upper('SCOTT')
union
select grantor, 3,'grant ' || privilege || ' on ' || owner || '.' || table_name || '(' || column_name || ')' || chr(10) ||'to SCOTT' || decode(grantable, 'YES', ' with grant option;', ';')
from dba_col_privs
where grantee = upper('SCOTT')
order by 1, 2
/
--
-- Reconnect as the original DBA
--
--select 'connect SCOTT;dbaconnect' || chr(10) ||'@@become ' || USER
--from dual
/
spool off
set feedback on
-- start create_SCOTT




SQL> @user_script.sql
create user SCOTT identified by SCOTT
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
/
grant UNLIMITED TABLESPACE to SCOTT
/
grant RESOURCE to SCOTT
/
grant CONNECT to SCOTT
/



SQL> !cat create_SCOTT.sql
create user SCOTT identified by SCOTT
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
/
grant UNLIMITED TABLESPACE to SCOTT
/
grant RESOURCE to SCOTT
/
grant CONNECT to SCOTT
/

Oracle insert into Loop

Oracle insert through Loop




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR del_record_cur IS
SELECT rowid
FROM .
WHERE

BEGIN
FOR rec IN del_record_cur LOOP
DELETE FROM .
WHERE rowid = rec.rowid;

total := total + 1;
count := count + 1;

IF (count >= 1000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from ..');
END;
/





DECLARE


cnt NUMBER := 0 ;
total NUMBER := 0 ;

CURSOR copy_record_cur IS
SELECT * from emp_anuj ;


BEGIN

FOR rec IN copy_record_cur LOOP
insert into scott.emp_temp
(EMPNO ,
ENAME ,
JOB ,
HIREDATE ,
SAL ,
COMM ,
DEPTNO )
values (
rec.EMPNO ,
rec.ENAME ,
rec.JOB ,
rec.HIREDATE ,
rec.SAL ,
rec.COMM ,
rec.DEPTNO
) ;



total := total + 1;
cnt := cnt + 1;

IF (cnt >= 5000) THEN
COMMIT;

cnt := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from scott.emp_temp');
END;
/




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR copy_record_cur IS
SELECT *
FROM cccprod1.DM_AUDITTRAIL_S_backup
WHERE TIME_STAMP >'07-DEC-08';
-- rec.


BEGIN

FOR rec IN copy_record_cur LOOP
insert into cccprod1.DM_AUDITTRAIL_S (
R_OBJECT_ID ,
EVENT_NAME ,
EVENT_SOURCE ,
R_GEN_SOURCE ,
USER_NAME ,
AUDITED_OBJ_ID ,
TIME_STAMP ,
STRING_1 ,
STRING_2 ,
STRING_3 ,
STRING_4 ,
STRING_5 ,
ID_1 ,
ID_2 ,
ID_3 ,
ID_4 ,
ID_5 ,
I_IS_REPLICA ,
I_VSTAMP ,
CHRONICLE_ID ,
OBJECT_NAME ,
VERSION_LABEL ,
OBJECT_TYPE ,
EVENT_DESCRIPTION ,
POLICY_ID ,
CURRENT_STATE ,
WORKFLOW_ID ,
SESSION_ID ,
USER_ID ,
OWNER_NAME ,
ACL_NAME ,
ACL_DOMAIN ,
APPLICATION_CODE ,
CONTROLLING_APP ,
ATTRIBUTE_LIST ,
ATTRIBUTE_LIST_ID ,
AUDIT_SIGNATURE ,
AUDIT_VERSION ,
HOST_NAME ,
TIME_STAMP_UTC ,
I_AUDITED_OBJ_CLASS ,
REGISTRY_ID ,
I_IS_ARCHIVED ,
AUDITED_OBJ_VSTAMP )
values
(
rec.R_OBJECT_ID ,
rec.EVENT_NAME ,
rec.EVENT_SOURCE ,
rec.R_GEN_SOURCE ,
rec.USER_NAME ,
rec.AUDITED_OBJ_ID ,
rec.TIME_STAMP ,
rec.STRING_1 ,
rec.STRING_2 ,
rec.STRING_3 ,
rec.STRING_4 ,
rec.STRING_5 ,
rec.ID_1 ,
rec.ID_2 ,
rec.ID_3 ,
rec.ID_4 ,
rec.ID_5 ,
rec.I_IS_REPLICA ,
rec.I_VSTAMP ,
rec.CHRONICLE_ID ,
rec.OBJECT_NAME ,
rec.VERSION_LABEL ,
rec.OBJECT_TYPE ,
rec.EVENT_DESCRIPTION ,
rec.POLICY_ID ,
rec.CURRENT_STATE ,
rec.WORKFLOW_ID ,
rec.SESSION_ID ,
rec.USER_ID ,
rec.OWNER_NAME ,
rec.ACL_NAME ,
rec.ACL_DOMAIN ,
rec.APPLICATION_CODE ,
rec.CONTROLLING_APP ,
rec.ATTRIBUTE_LIST ,
rec.ATTRIBUTE_LIST_ID ,
rec.AUDIT_SIGNATURE ,
rec.AUDIT_VERSION ,
rec.HOST_NAME ,
rec.TIME_STAMP_UTC ,
rec.I_AUDITED_OBJ_CLASS ,
rec.REGISTRY_ID ,
rec.I_IS_ARCHIVED ,
rec.AUDITED_OBJ_VSTAMP
) ;

total := total + 1;
count := count + 1;

IF (count >= 5000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from CCCPROD1.DM_AUDITTRAIL_S');
END;
/

Oracle Sql col to row

Oracle sqlplus Change Row into Column?
sqlplus Change Row into Column?



CREATE or replace TYPE strings1 AS TABLE OF VARCHAR2(100);
/

CREATE or replace Function CONCAT_LIST1 ( lst IN strings1 )
RETURN VARCHAR2 AS
ret varchar2(1000);
BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || lst(j)||',';
END LOOP;
RETURN ret;
END;
/



SELECT dbid,CONCAT_LIST1 (CAST( COLLECT(snap_id||'->'||to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr') AS strings1 )) snap
FROM dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
group by dbid
;


DBID
----------
SNAP
----------------------------------------------------------------------------------------------------
92516760
33324->08-oct-11 10Hr,33325->08-oct-11 11Hr,33326->08-oct-11 12Hr,33328->08-oct-11 14Hr,33330->08-oc
t-11 16Hr,33332->08-oct-11 18Hr,33334->08-oct-11 20Hr,33336->08-oct-11 22Hr,33338->09-oct-11 00Hr,33
323->08-oct-11 09Hr,33345->09-oct-11 07Hr,33344->09-oct-11 06Hr,33343->09-oct-11 05Hr,33342->09-oct-
11 04Hr,33341->09-oct-11 03Hr,33340->09-oct-11 02Hr,33339->09-oct-11 01Hr,33337->08-oct-11 23Hr,3333
5->08-oct-11 21Hr,33333->08-oct-11 19Hr,33331->08-oct-11 17Hr,33329->08-oct-11 15Hr,33327->08-oct-11
13Hr,





SELECT dbid, snap_id,to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr' begin_time from dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
order by 2
/

DBID SNAP_ID BEGIN_TIME
---------- ---------- -----------------------
92516760 33323 08-oct-11 09Hr
92516760 33324 08-oct-11 10Hr
92516760 33325 08-oct-11 11Hr
92516760 33326 08-oct-11 12Hr
92516760 33327 08-oct-11 13Hr
92516760 33328 08-oct-11 14Hr
92516760 33329 08-oct-11 15Hr
92516760 33330 08-oct-11 16Hr
92516760 33331 08-oct-11 17Hr
92516760 33332 08-oct-11 18Hr
92516760 33333 08-oct-11 19Hr
92516760 33334 08-oct-11 20Hr
92516760 33335 08-oct-11 21Hr
92516760 33336 08-oct-11 22Hr
92516760 33337 08-oct-11 23Hr
92516760 33338 09-oct-11 00Hr
92516760 33339 09-oct-11 01Hr
92516760 33340 09-oct-11 02Hr
92516760 33341 09-oct-11 03Hr
92516760 33342 09-oct-11 04Hr
92516760 33343 09-oct-11 05Hr
92516760 33344 09-oct-11 06Hr
92516760 33345 09-oct-11 07Hr

23 rows selected.


==


select t,max(sys_connect_by_path(object_name, ' ' )) obj_name
from (select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
)
start with rn = 1
connect by prior rn = rn-1
and prior t = t
group by t;




T obj_name
------------------- ----------------------------------------------------------------------------------------------------
PROCEDURE SHOW_FKEYS
INDEX IDX_EMP_ANUJ NORMAL_T_BIT_BMX PARK_ACRES PARK_DESCR_PHONE PARK_NAME PK_DEPT PK_EMP
TABLE ADDRESSES BONUS DEPT EMP EMP_ANUJ EMP_DUP PARK SALGRADE T TEST T_BIT T_FETCH_FIRST_ROWS XXX


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

select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
/