set pagesize 300 set linesize 205 set head on set echo off spool fkwoind accept usrid prompt ‘Enter User Name : ‘ col “Cons Name” format a30 heading “Cons Name” word_wrapped col status format a10 heading status col “Cons Type” format a12 heading “Cons Type” col “Column Name” format a30 heading “Column Name” col “Cons…
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…
