Renaming the column name
Alter table emp rename column emp_no to emp_code;
Author: Admin
segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used)
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)” »
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;