SQL> variable komal number SQL> begin 2 :komal := add_me(45,12); 3 end; 4 / PL/SQL procedure successfully completed. SQL> print komal KOMAL ———- 57 SQL> variable tejal number; SQL> execute :tejal := add_me(10,20) PL/SQL procedure successfully completed. SQL> print tejal TEJAL ———- 30 SQL>
How to execute any DML statement in Parallel mode?? (1) ALTER SESSION ENABLE PARALLEL DML; (2) Apply Parallel(table_name, parallel_degree) in hint. You can see three new clumns (TQ, IN-OUT, PQ Distribut) in execution plan of “select * from table(dbms_xplan.display())”. These new columns will specify the query is running in parallel mode.
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…
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 /
