Category: SQL scripts

13:01:01 SYS@AWRTEST:-> select lebsz from x$kccle; LEBSZ ——— 512 512 512 3 rows selected. or df -g on unix prompt.

Oracle Identifiers: =================== In order not to make using Oracle confusing, there are some identifiers whose meaning must be totally clear if one want to work on Oracle: SID (System Identifier): ======================== A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less;…

Read More “Oracle Identifiers” »

To obtain the shared memory id and semaphore id for 8.1.X you can do either of the following: $ORACLE_HOME/bin/sysresv IPC Resources for ORACLE_SID “V817” : Shared Memory: ID KEY 14851 0x8a85a74c Semaphores: ID KEY 11206656 0x4bd4814c Oracle Instance alive for sid “V817” OR %sqlplus internal SQL> oradebug ipc Information written to trace file. Trace file…

Read More “DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS” »

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…

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