pvmehta.com

set echo off set head off set lines 130 set verify off accept v_owner prompt ‘Enter Owner Name : ‘ accept v_name prompt ‘Enter Object Name : ‘ accept v_type prompt ‘Enter Object Type : ‘ prompt Dependency tree for OWNER : &v_owner, OBJECT_NAME : &v_name OBJECT_TYPE : &v_type prompt ========================================================================================== SELECT rpad (rpad(‘*’, LEVEL*2,…

Read More “Find Multiple levels of object dependencies : depen.sql” »

Subject: Script To List Recursive Dependency Between Objects Doc ID: Note:139594.1 Type: BULLETIN Last Revision Date: 16-JUN-2004 Status: PUBLISHED PURPOSE ——- This script provides a listing of how objects depend on other given objects such as packages, package bodies or procedures recursively. SCOPE & APPLICATION ——————- You often get invalidated objects and would like to…

Read More “SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql” »

Sometimes when all the SQL statements are well tuned but the routine still takes noticeable time to execute. DBMS_PROFILER Oracle 8i provides a new tool called PL/SQL Profiler. This is a powerful tool to analyze a Program unit execution and determine the runtime behavior. It gives execution time for individual statemenets in procedure, functtion or…

Read More “DBMS_PROFILER for tuning PLSQL programs.” »

Alter table emp rename column emp_no to emp_code;

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