set serverout on size 999999

set linesize 155

declare
     begin
     dbms_output.put_line(' ');
     dbms_output.put_line('------------- Start report for waiting sessions with current SQL ---------------');
     for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> 'BACKGROUND'
     and vsw.event NOT IN ('rdbms ipc message'
     ,'smon timer'
     ,'pmon timer'
     ,'SQL-Net message from client'
     ,'lock manager wait for remote message'
     ,'ges remote message'
     ,'gcs remote message'
     ,'gcs for action'
     ,'client message'
     ,'pipe get'
     ,'Null event'
     ,'PX Idle Wait'
     ,'single-task message'
     ,'PX Deq: Execution Msg'
     ,'KXFQ: kxfqdeq - normal deqeue'
     ,'listen endpoint status'
     ,'slave wait'
     ,'wakeup time manager'))
     loop
begin
     dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
     dbms_output.put_line('------------------------- -------------------- ------ ----------- ---------- -------------');
     dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
     '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(' SQLText ');
     dbms_output.put_line('----------------------------------------------------------------');
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line('-------------- End report for sessions waiting with current SQL ----------------');
     dbms_output.put_line(' ');
end;

SELECT * FROM product_component_version ;

SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;
SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;

SELECT * FROM V$SESSION ;

SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS,
  PCT_INCREASE, MIN_EXTLEN
FROM DBA_TABLESPACES;

SELECT TABLESPACE_NAME, CONTENTS, STATUS
FROM DBA_TABLESPACES;

SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
       MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;

SELECT FILE#,T1.NAME,STATUS,ENABLED,BYTES,CREATE_BYTES,T2.NAME
FROM   V$DATAFILE T1, V$TABLESPACE T2
WHERE  T1.TS# = T2.TS# ;

SELECT tablespace_name,COUNT(*) AS fragments,
   SUM(bytes) AS total,
   MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;

SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NUMBER ;

SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;

SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;

SELECT segment_name,segment_type,
       max_extents, extents
FROM dba_segments
WHERE extents+5 > max_extents
AND segment_type<>'CACHE';

SELECT s.segment_name,s.segment_type,s.tablespace_name,s.next_extent
FROM dba_segments s
WHERE NOT EXISTS (SELECT 1
   FROM dba_free_space f
   WHERE s.tablespace_name=f.tablespace_name
   HAVING max(f.bytes) > s.next_extent);

SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;

SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;

SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;

SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;

/* General Rollback Segment Information */

SELECT t1.name, t2.extents, t2.rssize, t2.optsize, t2.hwmsize, t2.xacts, t2.status
FROM   v$rollname t1, v$rollstat t2
WHERE  t2.usn = t1.usn ;

/* Rollback Segment Information - Active Sessions */

select t2.username, t1.xidusn, t1.ubafil, t1.ubablk, t2.used_ublk
from v$session t2, v$transaction t1
where t2.saddr = t1.ses_addr 

SELECT T1.NAME, T2.EXTENTS, T2.RSSIZE, T2.OPTSIZE, T2.HWMSIZE,
          T2.XACTS, T2.STATUS
FROM   V$ROLLNAME T1, V$ROLLSTAT T2
WHERE  T1.USN = T2.USN AND
       T1.NAME LIKE '%RBS%';

SELECT s.sid, s.serial#
 FROM v$session s
 WHERE s.saddr in
  (SELECT t.ses_addr
    FROM V$transaction t, dba_rollback_segs r
    WHERE t.xidusn=r.segment_id
    AND r.tablespace_name='RBS');

SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;

SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
  (SELECT constraint_name
   FROM dba_constraints
   WHERE table_name = TNAME
    AND constraint_type in ('P','U')) ;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1','COMPANY');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1');



ANALYZE TABLE COMPANY COMPUTE STATISTICS ;

set pagesize 0;

select 'TABLE:',table_name,'current' from user_tables 
    union
select 'SYNONYM:',synonym_name,table_owner from user_synonyms  
order by 1,2 ;

SELECT constraint_name,table_name, column_name
FROM dba_cons_columns
WHERE table_name = TNAME
ORDER BY table_name, constraint_name, position
END IF;

SELECT constraint_name, table_name,
     constraint_type, validated, status
FROM dba_constraints;

select 
    b.uniqueness, a.index_name, a.table_name, a.column_name 
from user_ind_columns a, user_indexes b
where a.index_name=b.index_name 
order by a.table_name, a.index_name, a.column_position;

SELECT trigger_name, status
 FROM dba_triggers ;

SELECT    SUM(PINS) EXECS,
          SUM(RELOADS)MISSES,
          SUM(RELOADS)/SUM(PINS) HITRATIO
FROM      V$LIBRARYCACHE ;

SELECT    SUM(GETS) HITS,
          SUM(GETMISSES) LIBMISS,
          SUM(GETMISSES)/SUM(GETS) RCRATIO
FROM      V$ROWCACHE ;

SELECT NAME, VALUE
FROM   V$SYSSTAT WHERE NAME IN
   ('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS');

SELECT NAME, VALUE
FROM   V$SYSTAT
WHERE NAME LIKE '%SORT%';

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME='RECURSIVE CALLS' ;

SELECT CLASS,COUNT
FROM V$WAITSTAT
WHERE CLASS LIKE '%UNDO%' ;

SELECT NAME, PHYRDS,PHYWRTS
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE#=FS.FILE# ;

/* Kill a session, specified by the returned sess-id / serial number */

SELECT sid, serial#, username from v$session

ALTER SYSTEM KILL SESSION 'sessid,ser#'

/* Status of Archive Log Subsystem */

ARCHIVE LOG LIST


/* log mode of databases */

SELECT name, log_mode FROM v$database;


/* log mode of instance */

SELECT archiver FROM v$instance;

/* diagnose data file problem */
select * from v$recover_file ;

/* diagnose data file problem, by displaying tablespace info */
select file_id, file_name, tablespace_name, status
from dba_data_files ;

/* find archive log files */
select * from v$recovery_log ;


/* incomplete recovery #1 */

svrmgrl> shutdown abort

[[ In Unix copy data files from backup area to data directory(s). ]]

svrmgrl> connect;
svrmgrl> startup;

/* incomplete recovery #2 */

svrmgrl> shutdown abort;
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> alter database rename file '/data2/ts05.dbf' to '/backups/ts05.dbf'
svrmgrl> alter database open;


/* incomplete recovery #3, for user error (i.e. drop table ) */
Note:  archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

 [[ backup all files ]]
 [[ restore required data file(s), using OS commands ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database until time '2002-03-04:15:00:00' ;
svrmgrl> alter database open resetlogs;





/* complete recovery #1, for major recovery operations, closed instance */
Note:  archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

 [[ backup all files ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database ;
  < or >
svrmgrl> recover datafile '/data4/ts03.dbf'
svrmgrl> startup open;


/* complete recovery #2, for major/minor recovery operations, open instance */
Note:  archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

 [[ backup all files ]]
 [[ restore corrupted data files, using OS commands ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> set autorecovery on ;
svrmgrl> recover tablespace ts03 ;
  < or >
svrmgrl> recover datafile 4 ;
svrmgrl> startup open;

select group#, member, status from v$logfile ;

select group#,thread#,archived,status from v$log ;

--=================================================
--
-- proc_ora_monitor
--
-- parm1:  username to count
-- parm2:  number of loops, 5 sec duration
--
--
--=================================================

set serveroutput on ;


create or replace procedure 
  proc_ora_monitor ( user1  in  varchar, reps1 in integer )
is

i            number ;
usercount1   number ;
memory1      number ;
date1        varchar(20) ;
msg          varchar(99) ;

begin

i := 0 ;

while ( i < reps1 )
 loop
 msg := '=> ' || to_char(SYSDATE, 'HH:MM:SS PM');

 select count(1) 
 into usercount1
 from sys.v_$session
 where username = user1 ; 

 msg := msg || ', ' || user1 || ': ' ||  usercount1 ;

 select round(sum(bytes)/1024/1024 ,2)
 into memory1
 from sys.v_$sgastat
 where  pool = 'shared pool' and
        name = 'free memory' ;

 msg := msg || ', free mb = ' || memory1 ;

 select round(sum(bytes)/1024/1024 ,2)
 into memory1
 from sys.v_$sgastat
 where  pool = 'shared pool' and
        name = 'processes' ;

 msg := msg || ', processes mb = ' || memory1 ;

 dbms_output.put_line(msg) ;

 dbms_lock.sleep(5) ;

 i := i + 1 ;
end loop ;

end;
/

show errors ;

execute proc_ora_monitor('SILVERUSER',2) ;

exit

-------------------------------------------------------
ORA-01034: ORACLE not available
-------------------------------------------------------
TNS-12564: TNS:connection refused
-------------------------------------------------------
TNS-12530: Unable to start a dedicated server process
-------------------------------------------------------

1)  Check your environment ;  verify the variables depicted below are set.
    ( NT: check the registry )
    The example below details a Solaris/CSH environment.  
    Note the TWO_TASK setting ... 

setenv ORACLE_BASE /apps/oracle
setenv ORACLE_HOME ${ORACLE_BASE}
setenv ORACLE_SID db22
setenv TWO_TASK $ORACLE_SID
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib/X11
setenv ORACLE_PATH $ORACLE_HOME/bin:/usr/bin:/usr/local/bin
setenv ORA_CLIENT_LIB shared
set path = ($ORACLE_HOME/bin /bin /usr/bin /usr/local/bin /sbin /usr/sbin /usr/bin/X11 .)


2) Try to ping the instance:

   tnsping db22

   If there's an error, check $ORACLE_HOME/network/admin/tnsnames.ora


3) Restart the TNS service.

   Solaris:   
       1) kill the process, running the tnslsnr binary
       2) nohup $ORACLE_HOME/bin/tnslsnr start &
   NT:
       1) restart the service, in the control panel


4) SQL-Plus / ServerMgr

   Try using this syntax:     sqlplus user/password@instance



5) Solaris, shell change
  
  Try switching the oracle user to the Bourne or Csh shell ;  make a script for SQL-Plus
  as follow:

#!/usr/bin/csh

setenv ORACLE_BASE /apps/oracle
setenv ORACLE_HOME ${ORACLE_BASE}
setenv ORACLE_SID db22
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib/X11
setenv ORACLE_PATH $ORACLE_HOME/bin:/usr/bin:/usr/local/bin
setenv ORA_CLIENT_LIB shared
setenv TWO_TASK $ORACLE_SID
set path = ($ORACLE_HOME/bin /bin /usr/bin /usr/local/bin /sbin /usr/sbin /usr/bin/X11 .)

sqlplus $1 $2 $3

# OR 
#sqlplus $1@$ORACLE_SID


Also verify the oracle user owns the oracle directory tree.



6) Check the pfile, verify the settings detailed below.  For this example,
   the machine should have at least 512mb of memory, to handle the OS and
   other processes.   
   

        # 100 MB shared pool memory
        shared_pool_size = 104857600

        # 65 processes need 130 MB of additional memory

        processes = 65
        sessions = 65


Solaris:  check the "shared memory" and "semaphores" settings 
          also, in the /etc/system file.



7) Look at sqlnet.log ;  also check the alert log in 
   $ORACLE_HOME/admin/$ORACLE_SID/bdump



8) Verify the Oracle version, SQLNet version, and patched OS are all compatible.



9) If the problem is still a mystery, the server may need to be restarted.

delete from plan_table
 where statement_id = '9999';
commit;

COL operation   FORMAT A30
COL options     FORMAT A15
COL object_name FORMAT A20


/* ------ Your SQL here ------*/

EXPLAIN PLAN set statement_id = '9999' for
select count(1) from asia_monthly_pricing_data where order_id > 5000
/

/*----------------------------*/



select operation, options, object_name
  from plan_table
 where statement_id = '9999'
start with id = 0
connect by prior id=parent_id and prior statement_id = statement_id;


exit
/





 












Back to top page











email ... 
js99@rocket99.com





Copyright 1998-2002 © Citisoft, Inc. All Rights Reserved.