accept usrnm prompt “Enter The User Name -> ” accept tblnm prompt “Enter The Table Name -> ” select index_name, substr(column_name,1,15) “Colm Name”, column_position “Position” from dba_ind_columns where table_name = upper(‘&tblnm’) and table_owner = upper(‘&usrid’) order by index_name, column_position;
select table_name, tablespace_name from dba_tables where owner = upper(‘&usrnm’) order by table_name /
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…
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;
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 =…
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…
/* 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…
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 ||…
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…
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 */” »
