surendra babu: DATABASE SCRIPTS

1) script to find free,used,total space in the tablespaces:

-------------------------------------------------------------------------------------------
-- SCRIPT: DF.SQL
-- PURPOSE: Show Oracle tablespace free space in Unix df style
-- AUTHOR: Tanel Poder [ http://www.tanelpoder.com ]
-- DATE: 2003-05-01
-------------------------------------------------------------------------------------------

SET echo OFF verify OFF lines 120 pages 100
COLUMN tablespace_name heading TABLESPACE format a30
CLEAR COLUMNS
CLEAR breaks
CLEAR computes
COLUMN tablespace_name FORMAT a30 HEADING "TABLESPACE_NAME"
COLUMN TotalMB FORMAT 999,999,999 HEADING "SIZE"
COLUMN UsedMB FORMAT 999,999,999 HEADING "Used"
COLUMN FreeMB FORMAT 999,999,999 HEADING "Free"
TTITLE ON
TTITLE CENTER 'TABLESPACE FREE SPACE REPORT '
break ON report
COMPUTE SUM LABEL "Totals: " OF TotalMB ON report
COMPUTE SUM LABEL "Totals: " OF UsedMB ON report
COMPUTE SUM LABEL "Totals: " OF FreeMB ON report
set timing on
TTITLE OFF

col "% Used" for a6
col "Used" for a22

select t.tablespace_name, t.mb "TotalMB", t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB"
,lpad(ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*100)||'%', 6) "% Used", t.ext "Ext",
'|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*20),'#'),20,' ')||'|' "Used"
from (
select tablespace_name, trunc(sum(bytes)/1048576) MB
from dba_free_space
group by tablespace_name
union all
select tablespace_name, trunc(sum(bytes_free)/1048576) MB
from v$temp_space_header
group by tablespace_name
) f, (
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
from dba_data_files
group by tablespace_name
union all
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
from dba_temp_files
group by tablespace_name
) t
where t.tablespace_name = f.tablespace_name (+)
order by t.tablespace_name;

CLEAR COLUMNS
CLEAR breaks
CLEAR computes

2) script to find table size :

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

accept ownname prompt "Enter owner name :"

accept tabname prompt "Enter table name :"

break ON segment_type SKIP 1

COLUMN SEGMENT_TYPE FORMAT A15

COLUMN SEGMENT_NAME FORMAT a30

SET LINES 150 PAGES 200 verify off

SELECT

ds.owner,

ds.segment_type,

ds.segment_name,

SUM(bytes)/(1024*1024) "Size(M)"

FROM DBA_SEGMENTS ds

WHERE ds.owner like upper('%&ownname%')

AND ds.segment_name

IN ( SELECT table_name FROM DBA_TABLES WHERE owner like upper('%&ownname%') AND table_name like upper('%&tabname%')

UNION

SELECT index_name FROM DBA_INDEXES WHERE owner like upper('%&ownname%') AND table_name like upper('%&tabname%')

UNION

SELECT segment_name FROM DBA_LOBS WHERE owner like upper('%&ownname%') AND table_name like upper('%&tabname%')

)

GROUP BY ds.owner, ds.segment_type, ds.segment_name

ORDER BY DECODE(segment_type, 'TABLE', 1, 'LOBSEGMENT',2,'LOBINDEX',3,'INDEX',4,NULL)

3) script to find user size :

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

set lines 150 pages 60

column machine format a30

column username format a30

column program format a35

column login_time format a20

ACCEPT usr default % prompt "username :(Press Enter for all users):"

select username||'('||sid||','||serial#||')' "USERNAME" ,

program,

machine,

seconds_in_wait,

status ,

to_char(logon_time,'dd/mm hh24:mi:ss') login_time

from v$session where username is not null and username like upper('%&usr%') order by seconds_in_wait desc,status

kkbuss@stpidb00 /dba/kkbuss/sql

$ cat us.sql

SET echo OFF verify OFF lines 120

COLUMN owner format a30

column size for a15

ACCEPT usr default % prompt "username :(Press Enter for all users):"

set heading on

SELECT du.username "SCHEMA" ,nvl2(ds.owner, to_char(round(sum(bytes)/(1024*1024),1),'9,999,999.9'),'N') "Size"

FROM dba_segments ds, dba_users du

where ds.owner(+) = du.username and nvl(owner,'%') like upper('%&usr%') group by du.username,ds.owner

order by nvl2(ds.owner, round(sum(bytes)/(1024*1024),1),0) desc

4) script to find locks:

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

SET linesize 200 pagesize 66
break ON KILL ON username ON terminal ON program
COLUMN KILL heading 'KillString' format a15
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The report generated by this script gives information on sessions
REM which are holding locks and gives the information needed to kill
REM using the ALTER SYSTEM KILL SESSION command.
REM ------------------------------------------------------------------------
REM Main text of script follows:

COLUMN res heading 'Resource Type' format 999
COLUMN id1 format 9999990
COLUMN id2 format 9999990
COLUMN lmode heading 'Lock Held' format a20
COLUMN request heading 'Lock Requested' format a20
COLUMN username format a15 heading "Username"
COLUMN terminal format a10
COLUMN TAB format a35 heading "Table Name"
COLUMN owner format a9
COLUMN program format a25
COLUMN Address format a18
SELECT NVL(S.USERNAME,'Internal') username,
NVL(S.MACHINE,'None') terminal, s.program,
L.SID||','||S.SERIAL# KILL,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) TAB,
DECODE(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',NULL) lmode,
DECODE(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',NULL) request
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER# AND SUBSTR(T1.NAME,1,20) not like 'ORA$BASE%'
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,3,4,5
/

5) script to show the user details:

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

ACCEPT usr default KKBUSS prompt "username (Default kkbuss) :"

SET ECHO OFF FEED OFF PAGES 0 verify off time off timing off

cl scr

SELECT

LPAD('USERNAME',30) ||' : ' || USERNAME ||CHR(10) ||

LPAD('PROFILE',30) ||' : ' || PROFILE ||CHR(10) ||

LPAD('STATUS',30) ||' : ' || ACCOUNT_STATUS ||CHR(10) ||

LPAD('CREATED',30) ||' : ' || ACCOUNT_STATUS ||CHR(10) ||

LPAD('DEF TS',30) ||' : ' || DEFAULT_TABLESPACE ||CHR(10) ||

LPAD('TMP TS',30) ||' : ' || TEMPORARY_TABLESPACE ||CHR(10) ||

LPAD('EXP DT',30) ||' : ' || EXPIRY_DATE ||CHR(10) "USER INFO"

FROM DBA_USERS WHERE USERNAME like upper('&usr')

SELECT LPAD('ROLES GRANTED:',32) FROM DUAL

Select LPAD(' ',32)||granted_role

from DBA_role_privs

where grantee like upper('&usr')

order by grantee

SELECT LPAD('SYSTEM PRIVILEGES GRANTED:',32) FROM DUAL

Select LPAD(' ',32)||PRIVILEGE||'('||ADMIN_OPTION||')' from DBA_sys_privs

where grantee like upper('&usr')

order by grantee

SELECT LPAD('TS QUOTA:',32) FROM DUAL

select LPAD(' ',32)||ROUND(MAX_BYTES/(1024*1024),1)||'M on ' || TABLESPACE_NAME

FROM dba_ts_quotas where username like upper('&usr')

set feed on

6) script to view session details:

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

ACCEPT usr default KKBUSS prompt "username (Default kkbuss) :"

SET ECHO OFF FEED OFF PAGES 0 verify off time off timing off

cl scr

SELECT

LPAD('USERNAME',30) ||' : ' || USERNAME ||CHR(10) ||

LPAD('PROFILE',30) ||' : ' || PROFILE ||CHR(10) ||

LPAD('STATUS',30) ||' : ' || ACCOUNT_STATUS ||CHR(10) ||

LPAD('CREATED',30) ||' : ' || ACCOUNT_STATUS ||CHR(10) ||

LPAD('DEF TS',30) ||' : ' || DEFAULT_TABLESPACE ||CHR(10) ||

LPAD('TMP TS',30) ||' : ' || TEMPORARY_TABLESPACE ||CHR(10) ||

LPAD('EXP DT',30) ||' : ' || EXPIRY_DATE ||CHR(10) "USER INFO"

FROM DBA_USERS WHERE USERNAME like upper('&usr')

SELECT LPAD('ROLES GRANTED:',32) FROM DUAL

Select LPAD(' ',32)||granted_role

from DBA_role_privs

where grantee like upper('&usr')

order by grantee

SELECT LPAD('SYSTEM PRIVILEGES GRANTED:',32) FROM DUAL

Select LPAD(' ',32)||PRIVILEGE||'('||ADMIN_OPTION||')' from DBA_sys_privs

where grantee like upper('&usr')

order by grantee

SELECT LPAD('TS QUOTA:',32) FROM DUAL

select LPAD(' ',32)||ROUND(MAX_BYTES/(1024*1024),1)||'M on ' || TABLESPACE_NAME

FROM dba_ts_quotas where username like upper('&usr')

set feed on

kkbuss@stpidb00 /dba/kkbuss/sql

$ cat sessions.sql

set lines 150 pages 60

column machine format a30

column username format a30

column program format a35

column login_time format a20

ACCEPT usr default % prompt "username :(Press Enter for all users):"

select username||'('||sid||','||serial#||')' "USERNAME" ,

program,

machine,

seconds_in_wait,

status ,

to_char(logon_time,'dd/mm hh24:mi:ss') login_time

from v$session where username is not null and username like upper('%&usr%') order by seconds_in_wait desc,status

/

7)script to view SGA details:
------------------------------

CLEAR breaks
CLEAR computes
break ON owner SKIP 2 ON report
COMPUTE SUM LABEL "Total" OF MB ON report

SELECT name, ROUND (SUM (mb), 1) mb, ROUND (SUM (inuse), 1) inuse
FROM (SELECT CASE
WHEN name = 'buffer_cache' THEN 'db_cache_size'
WHEN name = 'log_buffer' THEN 'log_buffer'
ELSE pool
END
name,
bytes / 1024 / 1024 mb,
CASE
WHEN name = 'buffer_cache'
THEN
(bytes
- (SELECT COUNT(*) FROM v$bh WHERE status = 'free')
* (SELECT VALUE FROM v$parameter WHERE name = 'db_block_size')
)/ 1024 / 1024
WHEN name <> 'free memory'
THEN
bytes / 1024 / 1024
END
inuse
FROM v$sgastat)
GROUP BY name
/
CLEAR breaks
CLEAR computes

show parameter memory_max_target
show parameter memory_target
show parameter sga_max_size
show parameter sga_target
show parameter pga_aggregate_target
show parameter processes

8) script to find long sessions details:
---------------------------------------
set lines 150
set pages 60
COLUMN userinfo FORMAT A60
COLUMN runtime heading "Elapsed/|Remaining" FORMAT A10
COLUMN message FORMAT A60
COLUMN "%Done" FORMAT 999.99
TTITLE LEFT "LONG RUNNING PROCESSES DETAILS"
SELECT s.username||'('||s.SID||','||s.serial#||') - '||s.machine||' - ' ||s.program userinfo,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) ||'//'||
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) runtime,
ROUND(sl.sofar/sl.totalwork*100, 2) "%Done",
sl.message
FROM v$session s,
v$session_longops sl
WHERE s.SID = sl.SID
AND s.serial# = sl.serial# and ( TRUNC(sl.time_remaining/60) > 0 or MOD(sl.time_remaining,60) > 0 )
order by "%Done"
/

TTITLE OFF

9) scritp to set auto trace for exp:
----------------------------------

set autot trace exp lines 160

10) script for wait event:
------------------------

select event, count(*) from v$session where username is not null group by event
/

11) script for 11g password reset:

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

set lines 180

select 'alter user ' || name || ' identified by values ''' || spare4||';'||password ||''';' from sys.user$ where name like upper('&user%')

12) script for asm disk group details:
--------------------------------------

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
WHERE
total_mb != 0
ORDER BY
name
/

13) script for asm disk details:
------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a25 HEAD 'Path'
COLUMN disk_file_name FORMAT a25 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a25 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (decode(b.free_mb,0,1,b.free_mb) / decode(b.total_mb,0,1,b.free_mb)))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/

14) script for active sql's:
--------------------------

SET lines 180 pages 100

COLUMN USER format a50
COLUMN event format a30
COLUMN wait_secs format 99999999
COLUMN SQL format a60 wrap ON

SELECT
SUBSTR(s.username,1,15)||'('||s.SID||','||s.serial# || ')-'||SUBSTR(s.machine,1,10)||'-'||SUBSTR(s.program,1,7) "USER",
s.event,
s.seconds_in_wait "WAIT_SECS" ,
sq.hash_value||' - '||sq.sql_text "SQL"
FROM v$session s, v$sql sq WHERE s.sql_hash_value=sq.hash_value AND s.username IS NOT NULL AND s.status='ACTIVE'
and s.sid != (select sid from v$mystat where rownum=1)
/

15) script for archive size:
----------------------------

/*
prompt **********************************************************
prompt Display the number of redo log switches per hour a day.
prompt Enter the date (dd-mon-yyyy format) from which to start.
prompt **********************************************************
*/

set head on
set pagesize 300
set linesize 180
-- ttitle center "Number of Redo-Log Switches Distributed by Hour" skip 1

column day format a5 heading day
column h00 format a4 heading 00
column h01 format a4 heading 01
column h02 format a4 heading 02
column h03 format a4 heading 03
column h04 format a4 heading 04
column h05 format a4 heading 05
column h06 format a4 heading 06
column h07 format a4 heading 07
column h08 format a4 heading 08
column h09 format a4 heading 09
column h10 format a4 heading 10
column h11 format a4 heading 11
column h12 format a4 heading 12
column h13 format a4 heading 13
column h14 format a4 heading 14
column h15 format a4 heading 15
column h16 format a4 heading 16
column h17 format a4 heading 17
column h18 format a4 heading 18
column h19 format a4 heading 19
column h20 format a4 heading 20
column h21 format a4 heading 21
column h22 format a4 heading 22
column h23 format a4 heading 23
column Total format a8 heading Total

prompt
select to_char(first_time,'mm/dd') day,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'24',0,round((blocks*block_size)/(1048576)))))||'/'||count(*) "Total",
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'00',round((blocks*block_size)/(1048576)),0))) h00,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'01',round((blocks*block_size)/(1048576)),0))) h01,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'02',round((blocks*block_size)/(1048576)),0))) h02,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'03',round((blocks*block_size)/(1048576)),0))) h03,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'04',round((blocks*block_size)/(1048576)),0))) h04,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'05',round((blocks*block_size)/(1048576)),0))) h05,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'06',round((blocks*block_size)/(1048576)),0))) h06,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'07',round((blocks*block_size)/(1048576)),0))) h07,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'08',round((blocks*block_size)/(1048576)),0))) h08,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'09',round((blocks*block_size)/(1048576)),0))) h09,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'10',round((blocks*block_size)/(1048576)),0))) h10,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'11',round((blocks*block_size)/(1048576)),0))) h11,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'12',round((blocks*block_size)/(1048576)),0))) h12,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'13',round((blocks*block_size)/(1048576)),0))) h13,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'14',round((blocks*block_size)/(1048576)),0))) h14,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'15',round((blocks*block_size)/(1048576)),0))) h15,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'16',round((blocks*block_size)/(1048576)),0))) h16,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'17',round((blocks*block_size)/(1048576)),0))) h17,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'18',round((blocks*block_size)/(1048576)),0))) h18,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'19',round((blocks*block_size)/(1048576)),0))) h19,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'20',round((blocks*block_size)/(1048576)),0))) h20,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'21',round((blocks*block_size)/(1048576)),0))) h21,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'22',round((blocks*block_size)/(1048576)),0))) h22,
to_char(sum(decode(substr(to_char(first_time,'hh24'),1,2),'23',round((blocks*block_size)/(1048576)),0))) h23
from v$archived_log
where first_time > sysdate-&NO_OF_DAYS and standby_dest='NO'
group by to_char(first_time,'mm/dd')
order by to_char(first_time,'mm/dd');

15) script for fra:
-----------------

show parameter db_recovery_file_dest

select * from v$flash_recovery_area_usage;

16) script to unlock the user :
------------------------------
accept uname prompt 'enter username to unlock:'
alter user &uname identified by "chang3m3!" account unlock password expire;

17) Show Oracle tablespace free space in Unix df style:

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

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

-- SCRIPT: DF.SQL

-- PURPOSE: Show Oracle tablespace free space in Unix df style

-- AUTHOR: Tanel Poder [ http://www.tanelpoder.com ]

-- DATE: 2003-05-01

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

col "% Used" for a6

col "Used" for a22

select t.tablespace_name, t.mb "TotalMB", t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB"

,lpad(ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*100)||'%', 6) "% Used", t.ext "Ext",

'|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*20),'#'),20,' ')||'|' "Used"

from (

select tablespace_name, trunc(sum(bytes)/1048576) MB

from dba_free_space

group by tablespace_name

union all

select tablespace_name, trunc(sum(bytes_free)/1048576) MB

from v$temp_space_header

group by tablespace_name

) f, (

select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext

from dba_data_files

group by tablespace_name

union all

select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext

from dba_temp_files

group by tablespace_name

) t

where t.tablespace_name = f.tablespace_name (+)

order by t.tablespace_name;

18) script for user status:
-------------------------
set pages 50 lines 200

select username , ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED from dba_users where username like upper('%&usernamea')
/

19) script for database details:
-------------------------------

set heading off feed off
cl scr
SELECT
LPAD('DBNAME',15) ||' : ' || RPAD(DB_UNIQUE_NAME||' ('||version||')',28) ||
LPAD('HOST',15) ||' : ' || HOST_NAME ||CHR(10) ||
LPAD('UP TIME',15)||' : ' ||
RPAD( round(SYSDATE-STARTUP_TIME) ||' Days (' ||TO_CHAR(STARTUP_TIME,'DD/MM/YY

HH24:MI')||')',28)||LPAD('PLATFORM',15) ||' : ' ||
PLATFORM_NAME||CHR(10) ||
LPAD('ARCH/FLASH',15) ||' : ' ||
RPAD(DECODE(LOG_MODE,'ARCHIVELOG','YES','NO')||' / '||FLASHBACK_ON,22)
from v$instance I, v$database D
where upper(i.instance_name)like upper(d.db_unique_name)||'%'
union all
SELECT LPAD('CHARACTERSET',15) ||' : ' || RPAD(VALUE,28) from NLS_DATABASE_PARAMETERS where

parameter='NLS_CHARACTERSET'
union all
SELECT LPAD('NCHAR_CHARACTERSET',15) ||' : ' || RPAD(VALUE,28) from NLS_DATABASE_PARAMETERS where

parameter='NLS_NCHAR_CHARACTERSET'
/

set heading on feed on

20) script for rman:
--------------------

-- from rman11g user
grant select on rman11g.RC_STORED_SCRIPT_LINE to kkbuss;

-- from your user
CREATE OR REPLACE FUNCTION get_script (d_key NUMBER, s_name varchar2)
RETURN VARCHAR2
IS
s_text VARCHAR2(4000) := NULL;
BEGIN
FOR cur_rec IN (SELECT line, text FROM rman11g.RC_STORED_SCRIPT_LINE WHERE db_key=d_key and

script_name=s_name)
LOOP
s_text := s_text || chr(10) || cur_rec.text; END LOOP;
RETURN s_text;
END;
/
SHOW ERRORS

SET MARKUP HTML ON
col script_name for a25
col db_name for a10
col text for a180
set lines 250
break on script_name on db_name skip 2
spool c:\tt.html

SELECT
db_name , s.script_name,
get_script(s.db_key,s.script_name) "text"
FROM rman11g.RC_DATABASE_INCARNATION i,
rman11g.RC_STORED_SCRIPT s
WHERE i.DB_KEY = s.DB_KEY
AND s.SCRIPT_NAME like '%arch%'
AND i.CURRENT_INCARNATION = 'YES'
and s.db_name in ('PREMD')
/

SET MARKUP HTML OFF

21) script for max process:
-----------------------------

select
to_char(a.begin_interval_time,'MON/YYYY') as Day,
b.resource_name,
max(b.current_utilization),
max(b.max_utilization)
from
dba_hist_resource_limit b,
dba_hist_snapshot a
where
a.snap_id = b.snap_id
and
b.resource_name = 'processes'
-- and b.current_utilization > 250
and to_char(a.begin_interval_time,'YYYY') >= 2011
group by
to_char(a.begin_interval_time,'MON/YYYY'),
b.resource_name
order by
to_date(to_char(a.begin_interval_time,'MON/YYYY'),'MON/YYYY') asc
/

22) script for undo:
-------------------

column sid_serial format a12
column orauser format a30
column program format a35
column Undo format a10
column undoseg format a30

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
order by t.used_ublk*TO_NUMBER(x.value) desc
/

23) script for failed login details:

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

SELECT *

FROM sys.dba_audit_trail

WHERE username = upper('&user') AND action_name = 'LOGON'

--GROUP BY TRUNC (timestamp)

ORDER BY 1

24) script for db growth:

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

set serveroutput on size 1000000

Declare

v_BaselineSize number(20);

v_CurrentSize number(20);

v_TotalGrowth number(20);

v_Space number(20);

cursor usageHist is

select a.snap_id,

SNAP_TIME,

sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum

from

(select SNAP_ID,

sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA

from DBA_HIST_SEG_STAT

group by SNAP_ID

having sum(SPACE_ALLOCATED_TOTAL) <> 0

order by 1 ) a,

(select distinct SNAP_ID,

to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME

from DBA_HIST_SNAPSHOT) b

where a.snap_id=b.snap_id;

Begin

select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;

select sum(bytes) into v_CurrentSize from dba_segments;

v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

dbms_output.put_line('SNAP_TIME Database Size(MB)');

for row in usageHist loop

v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);

dbms_output.put_line(row.SNAP_TIME || ' ' || to_char(v_Space) );

end loop;

end;

25) script for date:
-------------------
select
sysdate,
(to_date(sysdate) - to_date('01011970','ddmmyyyy')) * 24*60*60 SECONDS_EPOCH,
to_char((to_date(sysdate) - to_date('01011970','ddmmyyyy')) * 24*60*60, 'XXXXXXXX') SEC_HEX
from
dual
/

26) script to show locations of db files:
----------------------------------------

SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT NAME FROM V$TEMPFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT FILENAME FROM V$BLOCK_CHANGE_TRACKING
/

27) script to see plan:

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

set lines 150 pages 100

select plan_table_output from table(dbms_xplan.display('plan_table',&statement_id,'serial'));

28) report sql monitor:

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

-- File name: report_sql_monitor.sql

-- Purpose: Execute DBMS_SQLTUNE.REPORT_SQL_MONITOR function.

-- Usage: This scripts prompts for three values, all of which can be left blank.

-- If all three parameters are left blank, the last statement monitored

-- for the current session will be reported on.

-- If the SID is specified and the other two parameters are left blank,

-- the last statement executed by the specified SID will be reported.

-- If the SQL_ID is specified and the other two parameters are left blank,

-- the last execution of the specified statement by the current session

-- will be reported.

-- If the SID and the SQL_ID are specifie and the SQL_EXEC_ID is left

-- blank, the last execution of the specified statement by the specified

-- session will be reported.

-- If all three parameters are specified, the specified execution of the

-- specified statement by the specified session will by reported.

-- Note: If a match is not found - the header is printed with no data.

-- The most common cause for this is when you enter a SQL_ID and

-- leave the other parameters blank, but the current session has

-- not executed the specifid statement.

-- Note 2: The serial# is not prompted for, but is setup by the decode.

-- The serial# parameter is in here to ensure you don't get data

-- for the wrong session, but be aware that you may need to modify

-- this script to allow input of a specific serial#.

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

set long 999999999

set lines 280

col report for a279

accept sid prompt "Enter value for sid: "

select

DBMS_SQLTUNE.REPORT_SQL_MONITOR(

session_id=>nvl('&&sid',sys_context('userenv','sid')),

session_serial=>decode('&&sid',null,null,

sys_context('userenv','sid'),(select serial# from v$session where audsid = sys_context('userenv','sessionid')),

null),

sql_id=>'&sql_id',

sql_exec_id=>'&sql_exec_id',

report_level=>'ALL')

as report

from dual;

set lines 155

undef SID

29) login script:

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

set termout off

col user_name new_value user_name

col inst_name new_value inst_name

col host_name new_value host_name

select upper(user) user_name, upper(instance_name) inst_name, upper(host_name) host_name from v$instance;

set sqlprompt "&host_name@&inst_name:&user_name>>"

set termout on

set trimspool on

set trimout on

set serveroutput on size 1000000

set timing on

set lines 190

@@db

30) script for whoami:

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

col machine for a20

select username||'('||sid||','||serial#||')' "USERNAME" ,

program,

machine,

to_char(logon_time,'dd/mm hh24:mi:ss') login_time,

PREV_HASH_VALUE,

SCHEMANAME

from v$session where sid=sys_context('userenv','sid')

31) script for auto trace off:

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

set autot off

32) script for temp:

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

col username for a35

col machine for a25

col tablespace for a15

SELECT se.username||'('||se.SID||','||se.serial#||')' username,

se.status status, se.sql_hash_value,

se.prev_hash_value,se.machine machine,

su.TABLESPACE tablespace,su.segtype,

su.CONTENTS CONTENTS

FROM v$session se,

v$sort_usage su

WHERE se.saddr=su.session_addr;

33) asm details script:

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

set wrap off

set lines 120

set pages 999

col "Group Name" form a25

col "Disk Name" form a30

col "State" form a15

col "Type" form a7

col "Free GB" form 9,999

prompt

prompt ASM Disk Groups

prompt ===============

select group_number "Group"

, name "Group Name"

, state "State"

, type "Type"

, total_mb/1024 "Total GB"

, free_mb/1024 "Free GB"

from v$asm_diskgroup

prompt

prompt ASM Disks

prompt =========

col "Group" form 999

col "Disk" form 999

col "Header" form a9

col "Mode" form a8

col "Redundancy" form a10

col "Failure Group" form a10

col "Path" form a19

select group_number "Group"

, disk_number "Disk"

, header_status "Header"

, mode_status "Mode"

, state "State"

, redundancy "Redundancy"

, total_mb "Total MB"

, free_mb "Free MB"

, name "Disk Name"

, failgroup "Failure Group"

, path "Path"

from v$asm_disk

order by group_number

, disk_number

prompt

prompt Instances currently accessing these diskgroups

prompt ==============================================

col "Instance" form a8

select c.group_number "Group"

, g.name "Group Name"

, c.instance_name "Instance"

from v$asm_client c

, v$asm_diskgroup g

where g.group_number=c.group_number

prompt

prompt Current ASM disk operations

prompt ===========================

select *

from v$asm_operation

prompt

prompt free ASM disks and their paths

prompt ===========================

select header_status , mode_status, path from V$asm_disk

where header_status in ('FORMER','CANDIDATE')

clear columns

surendra babu

Pages

DATABASE SCRIPTS

4 comments: