Oracle DBA Daily Experiences

set serveroutput on
BEGIN
for i in (select file_name,autoextensible from dba_data_files where autoextensible = ‘YES’) loop
dbms_output.put_line(‘File name is: ‘||i.file_name||’ and autoextensible is: ‘||i.autoextensible);
execute immediate ‘alter database datafile ”’||i.file_name||”’ autoextend off’;
end loop;
END;
/

the script below will help you to see how big auto allocation is set plus all the report for the current used and free space.

[spocoradb1.aeso.ca]/dbadmin/workspace2/alima/sql>cat db_tablespaces_report.sql
set linesize 200
set trimspool on
set pagesize 100
set feedback off
column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a30 heading “Tablespace Name”
column Kbytes_alloc format 999,999,999,999 heading “Alloc|KBytes”
column currently_used format 999,999,999,999 heading “Used|KBytes”
column kbytes_free format 999,999,999,999 heading “Free|KBytes”
column Kbytes_max format 999,999,999,999 heading “MaxPoss|Kbytes”
column pct_free format 999.99 heading “%Free”
column max_kbytes_free format 999,999,999,999 heading “MaxPoss|Free|KBytes”
column max_pct_used format 999.99 heading “%Max|Used”
column host_name format a30

spool $vfile_email

select TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) TODAY FROM dual
/
SELECT host_name, instance_name FROM v$instance
/
break on report
compute sum of Kbytes_alloc on report
compute sum of kbytes_free on report
compute sum of currently_used on report
compute sum of Kbytes_max on report
select decode(extent_management,’LOCAL’,'*’,”) ||
decode(segment_space_management,’AUTO’,'a ‘,’m ‘) ||
b.tablespace_name name,
Kbytes_alloc,
kbytes_max,
(Kbytes_alloc – NVL(kbytes_free, 0)) currently_used,
NVL(kbytes_free, 0) kbytes_free,
(NVL(kbytes_free, 0))*100/Kbytes_alloc pct_free,
(Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) max_kbytes_free,
(Kbytes_alloc – NVL(kbytes_free, 0))*100/kbytes_max max_pct_used
FROM
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(AUTOEXTENSIBLE, ‘NO’, bytes,
decode(sign(maxbytes – bytes), 1, maxbytes, -1, bytes, 0,maxbytes)))/1024 Kbytes_max,tablespace_name
from sys.dba_data_files
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name
AND c.tablespace_name = b.tablespace_name
AND b.tablespace_name NOT IN (select distinct TABLESPACE_NAME from
DBA_UNDO_EXTENTS)
— 5% free and less than 2GB
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max < 5
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) < (2*1024*1024)
ORDER BY (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max, b.tablespace_name
/
spool off

This script will format the new 11G crsctl status resource in a tabular format.

#!/usr/bin/ksh
#
# Sample 11g CRS resource status query script
#
# Description:
# – Returns formatted version of crsctl status resource, in tabular
# format, with the complete rsc names and filtering keywords
# – The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# – $ORA_CRS_HOME should be set in your environment
#
#
# HISTORY ##################################
#
# 28/06/2010 – Alex Lima – UPgraded to 11G commands.
#
################################################################
export CRS_HOME=/usr/grid/product/11.2.0

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk # if not available use /usr/xpg4/bin/awk

# Table header:echo “”
echo “”

$AWK \
‘BEGIN {printf “%-30s %-70s %-18s\n”, “HA Resource”, “State”, “Target”;
printf “%-30s %-70s %-18s\n”, “———–”, “—–”, “——”;}’

# Table body:
$CRS_HOME/bin/crsctl status resource | $AWK \
‘BEGIN { FS=”=”; state = 0; }
$1~/NAME/ && $2~/’$RSC_KEY’/ {appname = $2; state=1};
state == 0 {next;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
state == 3 {printf “%-30s %-70s %-18s\n”, appname, appstate, apptarget; state=0;}’
echo “”

I use this simple script to find bind variables with or without sql_id.

select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
– and sql_id=’974ju5zc5whfn’;

set serveroutput on
declare
row_cnt number;
BEGIN
for i in (select table_name from user_tables) loop
execute immediate ‘select count(*) from ‘|| i.table_name into row_cnt;
dbms_output.put_line(‘Table sum for ‘||i.table_name||’ is ‘ ||row_cnt);
end loop;
END;
/

Simple script to generate DDL for all database links in the database:

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) || ‘/’ FROM dba_db_links a
/

Here is a simple script to generate the DDL code for all indexes in a particular table.

set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘INDEX’,UPPER(‘&table_name’),UPPER(‘&schema_name’))
from dual
/
set heading on;
set echo on;

#!/bin/ksh
# Oracle Utilities
# dbv automation script
#
#
. oraenv
wlogfile=dbv.${ORACLE_SID}.log
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
$SQLPLUS -s system/xxxx>> $wlogfile < set echo off feedback off verify off pages 0 termout off linesize 150
spool dbv.${ORACLE_SID}.cmd
select ‘dbv file=’ || name || ‘ blocksize=’ || block_size || ‘ feedback=’ || round(blocks*.10,0) — 10 dots per file
from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF
#

SQL>SQLPLUS ‘/AS SYSDBA’

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_program sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_program
FROM sys.v_$session
WHERE audsid = USERENV(‘SESSIONID’)
AND audsid != 0 — Don’t Check SYS Connections
AND ROWNUM = 1; — Parallel processes will have the same AUDSID’s

IF UPPER(v_program ) LIKE ‘%TOAD%’ OR UPPER(v_prog) LIKE ‘%T.O.A.D%’ OR — Toad
UPPER(v_program ) LIKE ‘%SQLNAV%’ OR — SQL Navigator
UPPER(v_program ) LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
UPPER(v_program ) LIKE ‘%BUSOBJ%’ OR — Business Objects
UPPER(v_prog) LIKE ‘%EXCEL%’ — MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, ‘Development tools are not allowed here.’);
END IF;
END;
/
SHOW ERRORS

If you need to capture SQL statements to replay in another instance you can turn trace on and capture in the trace files located in the udump folder.

– turn timing on.
alter session set timed_statistics=true;

– set to unlimited otherwise you may get an incomplete trace file when dump file size limit is reached.

alter session set max_dump_file_size=unlimited;

alter session set events ’10046 trace name context forever, level 12′;

– ******* run all of your processing here *******

alter session set events ’10046 trace name context off’;