catall.sh
#!/bin/ksh for fname in `ls` do print “”; print “/* File Name : $fname */”; print “”; cat $fname done
before_trunc.sql Before Truncate table needs to execute following:
/* before_trunc.sql This script gives disble constrints script before truncating table */ accept tblname prompt ‘Enter the Table Name You want to truncate -> ‘ column head heading “Execute Following Statements To Truncate &tblname, as &tblname is referenced by following foreign keys” select ‘alter table ‘ || lpad(b.table_name, 30) || ‘ disable constraint ‘ ||…
Read More “before_trunc.sql Before Truncate table needs to execute following:” »
TRUNCATE table and disabling referential constraints.
When you truncate table A, and Table A has some child table that contain data. TRUNCATE fails. For that you need to find table A’s all child tables and diable that referential integrity. /* before_trunc.sql This script gives disble constrints script before truncating table */ accept tblname prompt ‘Enter the Table Name You want to…
Read More “TRUNCATE table and disabling referential constraints.” »
Rownum with Order by
Above version 8.0, you can select rows for specified range. select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum = MIN_ROWS /
tblwopk.sql tablewopk.sql
select a.table_name from user_tables a where not exists ( select ‘x’ from user_constraints b where b.table_name = a.table_name and b.constraint_type = ‘P’);
fkwoind.sql fkwoindex.sql
set pagesize 300 set linesize 205 set head on set echo off spool fkwoind accept usrid prompt ‘Enter User Name : ‘ col “Cons Name” format a30 heading “Cons Name” word_wrapped col status format a10 heading status col “Cons Type” format a12 heading “Cons Type” col “Column Name” format a30 heading “Column Name” col “Cons…
usnsql.sql Displays information about UNDO segments with sql statements
select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status, b.start_date, b.status tx_status, c.sid, c.sql_id, d.sql_text from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d where a.usn = b. xidusn and a.usn = a1.usn and b.ses_addr = c.saddr and c.sql_id = d.sql_id;
Drop tempfiles from database
(1) alter tablespace temp add tempfile ‘/db7/oradata/WEBARCH/temp01.dbf’ size 1024M; (2) alter database tempfile ‘/db2/oradata/WEBARCH/temp_001.dbf’ drop including datafiles;
sid_wise_cursor.sql find open cursor basis on username or SID
accept u_name prompt ‘Enter User Name (Enter NONE if want SID specific query): ‘ accept s_id prompt ‘Enter SID (Enter 0 for Userspecific Query): ‘ select a.sid, b.sql_text, a.sql_id, c.spid, b.user_name from v$session a, v$open_cursor b, v$process c where a.sql_id = b.sql_id and (a.username = upper(‘&u_name’) or a.sid = &s_id ) and a.paddr = c.addr…
Read More “sid_wise_cursor.sql find open cursor basis on username or SID” »
find_open_cur.sql Find open cursorts per session
set lines 132 set pages 300 column numopencursors format d8 column machine format a30 column osuser format a20 column username format a20 SELECT s.sid, v.value as numopencursors ,s.machine ,s.osuser,s.username FROM V$SESSTAT v, V$SESSION s WHERE v.statistic# = 3 and v.sid = s.sid ;