Category: SQL scripts

spool seg1 set lines 141 set serveroutput on execute dbms_output.enable(1000000); declare tmp varchar2(1); v_tbl_name varchar2(100); v_tot_blocks number; v_tot_bytes number; v_unused_blocks number; v_unused_bytes number; tot_mb number; unused_mb number; v_fileid number; v_blockid number; v_block number; cursor c1 is select OWNER, segment_name, segment_type, bytes/(1024 *1024) USED_MB, (extents * initial_extent)/(1024 *1024) Allocated_MB, Tablespace_name, extents from dba_segments where owner in…

Read More “segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used)” »

/* 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:” »

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.” »

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 /

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’);

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…

Read More “fkwoind.sql fkwoindex.sql” »

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;

(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;

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” »

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 ;