block_ident.sql
select /*+ all_rows */ distinct owner, segment_name, segment_type from dba_extents where file_id = &filid and &blkid between block_id and block_id+blocks-1;
Author: Admin
eplan.sql
truncate table plan_table; EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR select distinct owner, segment_name, segment_type from dba_extents where file_id = &filid and &blkid between block_id and block_id+blocks-1; — syntax for viewing information from plan_table SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM plan_table START WITH id =…
refre.sql
REM Author : Paresh Mehta REM Created : June 4, 2004 REM Purpose : Change the look of sql prompt (for 9i). set echo off set numwidth 9 set linesize 100 set pagesize 24 Set head off set time on set timing on define _PVMUSRNM=”” column USERNAME new_value _PVMUSRNM; column name new_value _PVMDBSTR; select username…
ext#.sql
/* USER_EXTENTS describes the extents comprising the segments owned by the current user’s objects. This view does not display the OWNER, FILE_ID, BLOCK_ID, or RELATIVE_FNO columns. Column Datatype NULL Description ————————————————————————————————————— OWNER VARCHAR2(30) Owner of the segment associated with the extent SEGMENT_NAME VARCHAR2(81) Name of the segment associated with the extent PARTITION_NAME VARCHAR2(30) Object Partition…
sesswait.sql
set pages 300 set lines 151 column event format a30 column p1_detail format a25 column p2_detail format a15 column p3_detail format a5 column sid format a15 column wait_time format 999999 column seconds_in_wait format 999999999 column state format a30 word_wrapped select A.SID ||’,’ || A.seq# sid , A.EVENT, A.P1TEXT || ‘=’ || A.P1 p1_detail, A.P2TEXT ||…
Wait time tuning research
http://www.dba-oracle.com/art_otn_waits.htm v$session.paddr = v$process.addr v$session.sid = v$session_wait.sid v$session.sql_address = v$sqlarea.address v$session.sql_hash_value = v$sqlarea.hash_value v$session.sql_id = v$sqlares.sql_id v$session.server shows Dedicated or MTS configuration. The fields module and action of v$session can be set with dbms_application_info.set_module. The field client_info can be set with dbms_application_info.set_client_info Join sid with v$sesstat if you want to get some statistical information for…
crtgr.sql /* For creating trigger from data dictionary */
SET LONG 1000000 UNDEFINE trigger_name SET PAGES 0 SET FEEDBACK OFF SET LINES 132 set echo off set numwidth 9 Set head off set verify off set time off set timing off COLUMN text FORMAT A302 word_wrapped COLUMN spool_name NEW_VALUE spool_name NOPRINT SELECT lower(trigger_name||’.ctr’) spool_name FROM dba_triggers WHERE trigger_NAME = UPPER(‘&&trigger_name’); SPOOL &spool_name select ‘CREATE…
Read More “crtgr.sql /* For creating trigger from data dictionary */” »
eplan9i.sql
set pages 300 set lines 100 truncate table plan_table; EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR SELECT * FROM emp; SELECT rpad(cardinality, 10, ‘ ‘) || ‘ ‘ || rpad(cost, 10, ‘ ‘) || ‘ ‘ || lpad(‘ ‘,level-1)||operation||’ ‘|| options||’ ‘||object_name “Rows Cost Plan” FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior…
Rename Oracle Instance Name
Renaming oracle instance (from UOCTEST to UAS) Note : Remember to rename datafile, logfile, controlfile, parameterfile and passwordfile. Aslo need to change listener. ******** Parameter File *************************** (1) check whether instance is using SPFIlE. using “show parameter spfile” (2) SQL> connect / as sysdba SQL> “create pfile from spfile” in 10g by default it will…
PLSQL Table Syntax 2
DECLARE TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_tab EmpTabTyp; BEGIN /* Retrieve employee record. */ SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7468; END;