Search This Blog

Sunday, 21 October 2018

AWR baseline:

SYS@TESH996P SQL> select min(snap_id), min(sample_time), max(snap_id),max(sample_time) from dba_hist_active_sess_history where sample_time < to_date('20-JUL-18','DD-MON-YY');

MIN(SNAP_ID) MIN(SAMPLE_TIME)
------------ ---------------------------------------------------------------------------
MAX(SNAP_ID) MAX(SAMPLE_TIME)
------------ ---------------------------------------------------------------------------
       72277 18-JUL-18 11.00.39.789 PM
       72326 19-JUL-18 11.59.48.043 PM


1 row selected.

To know the retenetion:
------------------------------------
SYS@TESH996P SQL> select DBID,RETENTION,SNAP_INTERVAL from dba_hist_wr_control;

      DBID RETENTION                                SNAP_INTERVAL
---------- ---------------------------------------- --------------------------------------------------
1857388495 +00090 00:00:00.0                        +00000 00:30:00.0

1 row selected.

Cretae baseline:
---------------------

exec DBMS_WORKLOAD_REPOSITORY.create_baseline(72277,72326,'Test Baseline');

PL/SQL procedure successfully completed.


To view the baseline:
-------------------------


SYS@TESH996P SQL> SELECT baseline_id,baseline_name,baseline_type,start_snap_id,end_snap_id FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                BASELINE_TYPE                           START_SNAP_ID END_SNAP_ID
----------- ------------------------------------------------------------ --------------------------------------- ------------- -----------
          8 Test Baseline                                                STATIC                                          76691       76738
          1 EOD BASELINE 13/AUG/2018 MON                                 STATIC                                          73479       73492


Delete baseline:
-------------------

SYS@TESH996P SQL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline(BASELINE_NAME => 'Test Baseline');

PL/SQL procedure successfully completed.

SYS@TESH996P SQL> SELECT baseline_id,baseline_name,baseline_type,start_snap_id,end_snap_id FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                BASELINE_TYPE                           START_SNAP_ID END_SNAP_ID
----------- ------------------------------------------------------------ --------------------------------------- ------------- -----------
          1 EOD BASELINE 13/AUG/2018 MON                                 STATIC                                          73479       73492

Friday, 19 October 2018

tablespace details

pct_free.sql
==================================
set echo off
set lines 999 pages 999;
col tablespace for a22;
col "Used % " for a10;

select a.tablespace_name "Tablespace",
        a.total_size_mb   "Total_Size_MB",
        (a.total_size_mb - b.free_size_mb) Used_Size_MB,
         b.free_size_mb "Free_Size_MB",
         round(((a.total_size_mb - b.free_size_mb)*100/a.total_size_mb), 2)        "Pct_Used",
      round(free_size_mb*100/total_size_mb, 2) "Pct_Free",
          round(b.max_free, 2) "Max_Free",
      round(c.max_next, 2) "Max_Next",
        d.status,
      TO_CHAR(NVL((a.bytes - NVL(b.bytes, 0)) / a.maxbytes * 100, 0), '990.00')  "Used % "
      from   ( select tablespace_name, round(sum(bytes)/1024/1024, 2) total_size_mb,
    decode(sum(decode(autoextensible,'YES',1,0)),0,'NO','YES') auto_extension,sum(bytes) bytes,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
                 from   dba_data_files group  by tablespace_name ) a, ( select tablespace_name, round(sum(bytes)/1024/1024, 2) free_size_mb,
                                                     round (max( bytes) / ( 1024 * 1024 ), 2 )max_free,sum(bytes) bytes from   dba_free_space
                group  by tablespace_name ) b, ( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next from   dba_segments
                                                 group  by tablespace_name ) c, dba_tablespaces d
where  d.tablespace_name = a.tablespace_name(+)
   and    d.tablespace_name = b.tablespace_name(+)
and    d.tablespace_name = c.tablespace_name(+)
   and    a.tablespace_name is not null
order by 5;

==============================================================
tbs.sql
------------------------------------
set echo off
set line 200
col file_name for a100
select file_id, file_name, bytes/1024/1024 "mb" from dba_data_files where tablespace_name='&tbs';


prompt >>>>> Please check mount point size before resize datafile
prompt
prompt >>>>> HINTS for resize datafile command
prompt =======================================================================
prompt alter database datafile <file_id> resize ...m;
prompt alter tablespace tablespace_name add datafile '...' size ...m;
prompt

Monday, 1 October 2018

Database SID based on OS Process ID


Database SID based on OS Process ID


col SPID for a20;
col OSUSER for a30;
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

=============================================
col SPID for a20;
col OSUSER for a30;
col sid format 999999
col username format a20
col CLIENTPROGRAM for a20;
col MODULE for a30;
col MACHINE for a30;

select b.spid,a.sid, a.serial#,a.username, a.osuser,
a.PROGRAM clientprogram, a.module, a.machine
from v$session a, v$process b
where a.paddr= b.addr
and b.spid in ='&spid'
order by b.spid;