Database Support Engineer Oracle数据库支持工程师手册

ALLSTARS_ORACLE

Database Support Engineer Oracle数据库支持工程师手册


TROUBLESHOOTING STEPS

1) High memory usage/memory leak related issue

            set pages 999
            clear col
            spool pga.out
            select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
   
            PROMPT
            PROMPT V$SESSSTAT MEMORY INFO
            PROMPT -------------------------
   
            select p.spid, s.sid, substr(n.name,1,25) memory, s.value
            from v$sesstat s ,  v$statname n,v$process p,v$session vs
            where s.statistic# = n.statistic#
            and n.name like '%pga memory%' and s.sid=vs.sid
            and vs.paddr=p.addr  order by s.value asc;
   
   
            PROMPT
            PROMPT LARGEST PGA_ALLOC_MEM PROCESS NOT LIKE LGWR
            PROMPT -------------------------
   
            select pid,spid,substr(username,1,20) "USER" ,program,
            PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
            from v$process where pga_alloc_mem=
            (select max(pga_alloc_mem) from v$process
             where program not like '%LGWR%');
   
            PROMPT
            PROMPT SELECT SUM(PGA_ALLOC_MEM) FROM V$PROCESS
            PROMPT -------------------------
   
            select sum(pga_alloc_mem) from v$process;
   
            PROMPT
            PROMPT SELECT FROM V$PROCESS   www.askmaclean.com
            PROMPT -------------------------
   
            select spid, program,pga_alloc_mem from v$process
               order by pga_alloc_mem desc;
   
            PROMPT
            PROMPT SELECT * FROM V$PGASTAT
            PROMPT -------------------------
   
            select substr(name,1,30), value, unit from v$pgastat;
            
            show parameter
            
            spool off
            exit
Total memory used by Oracle.
--------------
select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory' );

Process memory
---------------
select a.sid,value/(1024*1024),program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');
If you have identified any specific session/process consuming high memory, then request you to gather the below information

select spid,pga_used_mem,pga_alloc_mem from v$process where addr=(select paddr from v$session where sid=<sid> and serial#=sl#);

oradebug setospid <pid of process>
oradebug dump heapdump 536870917
oradebug tracefile_name
Related article NOTE.233869.1 Diagnosing and Resolving ORA-4030 error



2) Invalid objects related issues

set pages 1000
set lines 120
col owner format a30
col object_name format a30
col object_type format a30
col comp_id format a20
col comp_name format a40
col version format a10
col status format a15
col dbname format a15
spool INVALID_OBJECTS_AND_REGISTRY_INFO.lst
PROMPT DATABASE NAME
PROMPT =============
select sys_context('USERENV','DB_NAME') DBNAME from dual;
PROMPT COUNT OF INVALID OBJECTS
PROMPT ========================
select count(*) from dba_objects where status='INVALID';
PROMPT INVALID OBJECTS GROUPED BY OBJECT TYPE AND OWNER
PROMPT ================================================
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
PROMPT DBA_REGISTRY CONTENTS (VIEW DOES NOT EXISIT IN VERSIONS < 9.2.0)
PROMPT ================================================================
select comp_id,comp_name,version,status from dba_registry;
spool off
spool INVALID_OBJECTS.lst
PROMPT LIST OF INVALID OBJECTS
PROMPT =======================
select owner,object_name,object_type from dba_objects where status='INVALID';
spool off
Related article NOTE.300056.1 Debug and Validate Invalid Objects
3)  Hanganalyze and systemstate

$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
wait 90 seconds
oradebug hanganalyze 3
exit
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
exit
Use level 266 where appropriate         
Related articles
Note 61552.1 Diagnosing Database Hanging Issues
Note.215858.1 Interpreting HANGANALYZE trace files to diagnose hanging and performance problems
Note.423153.1 Understanding and Reading Systemstates
     
         
4) Status of JVM

-- script to determine the status of the Oracle JVM and required packages

connect / as sysdba
spool jvm_stat.log
set serveroutput on
set echo on
set pagesize500
set linesize 100
column comp_name format a40
select comp_name, version, status from dba_registry;
select owner, status, count(*) from all_objects
where object_type like '%JAVA%' group by owner, status;
select owner, object_type, count(*) from all_objects
where object_type like '%JAVA%' and status <> 'VALID' group by owner, object_type;
select owner, status, object_type, object_name from all_objects
where object_name like'%DBMS_JAVA%';
select owner, status, object_type, object_name from all_objects
where object_name like'%INITJVMAUX%';
select * from v$sgastat where POOL = 'java pool' or NAME = 'free memory';
show parameter pool_size
show parameter sga
select owner, object_type, status, dbms_java.longname(object_name) from all_objects
where object_type like '%JAVA%' and status <> 'VALID';
spool off
/
Related article Note 842449.1 Troubleshooting Oracle JVM
5) Scheduler related issues

set ver off
set term off
set page 0
set markup html on spool on
spool scheduler.html
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SS AM TZR'
/
alter session set NLS_DATE_FORMAT='DD-MON-RR HH.MI.SS AM'
/
select WINDOW_NAME,WINDOW_PRIORITY,ENABLED,RESOURCE_PLAN,NEXT_START_DATE,
DURATION from DBA_SCHEDULER_WINDOWS
/
select LOG_DATE,WINDOW_NAME,REQ_START_DATE,ACTUAL_START_DATE,WINDOW_DURATION,
ACTUAL_DURATION,ADDITIONAL_INFO from DBA_SCHEDULER_WINDOW_DETAILS
where rownum<20
order by LOG_DATE
/
select LOG_DATE,STATUS,WINDOW_NAME, OPERATION from DBA_SCHEDULER_WINDOW_LOG
order by LOG_DATE desc
/
select WINDOW_GROUP_NAME, ENABLED, NEXT_START_DATE from DBA_SCHEDULER_WINDOW
_GROUPS
/
select LOG_DATE, OWNER,JOB_NAME, STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,
ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS
where JOB_NAME in ('GATHER_ITEM_STATS_JOB')
order by LOG_DATE
/
show parameter job_queue
/
spool off
set markup html off spool off
Related article Note 783357.1 Troubleshooting DBMS_SCHEDULER and DBMS_JOB
6) Session genrating high redo

SQL> SELECT s.sid, s.serial#, s.username, s.program,
           i.block_changes
           FROM v$session s, v$sess_io i
           WHERE s.sid = i.sid
           ORDER BY 5 desc, 1, 2, 3, 4;
SQL> SELECT s.sid, s.serial#, s.username, s.program,
          t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
Related article NOTE.167492.1 How to Find Sessions Generating Lots of Redo
   
7) Enterprise manager tablespace alert issues
-- QUERY 1: The following query shows the outstanding alerts that the RDBMS is aware of:
SELECT REASON
, METRIC_VALUE
, MESSAGE_TYPE
, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS')
, HOST_ID
FROM SYS.DBA_OUTSTANDING_ALERTS;
-- QUERY 2: The following query shows the current thresholds settings for the RDBMS tablespace full metric:
SELECT METRICS_NAME
, WARNING_OPERATOR WARN_OP
, WARNING_VALUE WARN_VAL
, CRITICAL_OPERATOR CRIT_OP
, CRITICAL_VALUE CRIT_VAL
, OBJECT_TYPE OBJ_TYPE
, OBJECT_NAME OBJ_NAME
, STATUS
FROM SYS.DBA_THRESHOLDS
WHERE metrics_name LIKE ‘%Tablespace%’; -- Do not replace the string ‘Tablespace’
-- QUERY 3: The following query shows the current values for the Tablespace Used(%) metric from the perspective of the RDBMS:
SELECT TABLESPACE_NAME TBSP_NAME
, USED_SPACE
, TABLESPACE_SIZE TBSP_SIZE
, USED_PERCENT
FROM SYS.DBA_TABLESPACE_USAGE_METRICS;
Related article Note 403264.1  Troubleshooting a Database Tablespace Used(%) Alert proble
8) 10046 trace

To gather 10046 trace at the session level:
alter session set tracefile_identifier='10046';   
alter session set timed_statistics = true;  
alter session set statistics_level=all;  
alter session set max_dump_file_size = unlimited;  
alter session set events '10046 trace name context forever,level 12';
-- Execute the queries or operations to be traced here --  
select * from dual;  
exit;
If the session is not exited then the trace can be disabled using:
alter session set events '10046 trace name context off';
-----------------------------------------------------------------------------------------------------------
If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
The first step is to identify the session to be traced by some means:
For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
select p.PID,p.SPID,s.SID  from v$process p,v$session s  where s.paddr = p.addr  and s.sid = &SESSION_ID  /
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)
Once the OS process id for the process has been determined then the trace can be initialised as follows:
Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:
connect / as sysdba  
oradebug setospid 9834  
oradebug unlimit  
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual os pid.
Note that it is also possible to attach to a session via oradebug using the 'setorapid'.
In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:
connect / as sysdba  
oradebug setorapid 9834  
oradebug unlimit  
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual ora pid.
To disable oradebug tracing once tracing is finished:
oradebug event 10046 trace name context off
-----------------------------------------------------------------------------------------------------------
System-wide tracing can be enabled as follows:
alter system set events '10046 trace name context forever,level 12';
The setting can be disabled in all sessions by using the following command:
alter system set events '10046 trace name context off';
9) Errorstack

At system level for an particular error
SQL> alter system set events ‘30926 trace name errorstack level 3’;
To unset it after trace file is produced:
SQL> alter system set events ‘30926 trace name errorstack off’;
At session level for an particular error
SQL> alter session set events ‘30926 trace name errorstack level 3’;
To unset it after trace file is produced:
SQL> alter session set events ‘30926 trace name errorstack off’;
Remember to replace the example '30926' value with the actual ora error to be traced.
  
10) Oradebug for getting function stack of processes

For user process use query below query to identify process associated with session
SELECT P.PID ORAPID, P.SPID OSPID FROM V$SESSION S, V$PROCESS
WHERE  S.PADDR=P.ADDR AND
S.SID=&SID;
oradebug setorapid &orapid
oradebug dump errorstack 3;
oradebug tracefile_name
For background process
select p.spid from v$process p,v$bgprocess b
where p.addr=b.paddr
and name like '%SMON%');
oradebug setospid <smon os pid>
oradebug dump errorstack 3;
oradebug tracefile_name
11) How to Trace Unix System Calls for a Process

O/S             Trace Utility
-----------     ------------------------------------------------------
Sun Solaris truss, e.g.:
  $ truss -aefo <output file> <executable>
HP/UX tusc, eg :
$ tusc -afpo <output file> <pid> <executable>
IBM AIX          truss, e.g.:
                       $ truss -aefo <output file> <executable>
Linux                  strace, eg :
$ strace -fo <output file> <executable>
12) Shutdown immediate

Plan to shutdown again and gather some infomation. Before issuing the
shutdown immediate command set some events as follows:
www.askmaclean.com
  SQL> connect / as sysdba
  SQL> alter session set events '10046 trace name context forever,level 12';
  SQL> alter session set events '10400 trace name context forever, level 1';
  SQL> shutdown immediate;

  10046 turns on extended SQL_TRACE for the shutdown process.
  10400 dumps a systemstate every 5 minutes.
Related articles
Note.164504.1  How to Check Why Shutdown Immediate Hangs?
Note.375935.1 What To Do and Not To Do When 'shutdown immediate' Hangs
13) Hung connections

Linux:
strace -o /tmp/truss.out -aef sqlplus "/ as sysdba"

HP:
tusc -afpo /tmp/truss.out -aef sqlplus "/ as sysdba"

AIX/ Solaris:
truss -aefo (output file) (executable)

If you are able to connect as SYS user, then run the following query when there are hung connections:

select sid,event,seq#,p1,p2,p3 from V$session_wait where wait_time=0 and event not like '%message%';

select count(*), status from v$session group by status;

select * from v$rsrc_plan;

select * from v$pgastat;

select sum(PGA_USED_MEM)/(1024*1024), sum(PGA_ALLOC_MEM)
from v$process ;

select max(pga_used_mem) from v$process;
15) SYSTEM ERROR LOGS

SOLARIS
---------
System log file         /var/adm/messages


HP
---

System log file         /var/adm/syslog/syslog.log
Display system/error messages         /usr/sbin/dmesg


Linux
-----
System log file         /var/log/messages
Display system/error messages         dmesg
   
   
   
AIX       
----

System log file         /var/adm/ras/errlog   
Display system/error messages         /bin/errpt -a  

WINDOWS
=======
Event viewer. click on actions >> export list
16) Client sqlnet tracing

client sqlnet tracing ,append the following lines in the client side sqlnet.ora (located %TNS_ADMIN or %ORACLE_HOME/network/admin)
                TRACE_LEVEL_CLIENT = 16
                TRACE_FILE_CLIENT = Client
                TRACE_DIRECTORY_CLIENT= <valid directory path that exists>
                TRACE_TIMESTAMP_ CLIENT = ON
                TRACE_UNIQUE_CLIENT = ON
                #Following to be added only for 11g clients.
                DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1  How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
17)  Server sqlnet tracing

server sqlnet tracing ,append the following lines in the server side sqlnet.ora
                TRACE_LEVEL_SERVER = 16
                TRACE_FILE_SERVER =  Server
                TRACE_DIRECTORY_SERVER = <valid directory path that exists>
                TRACE_TIMESTAMP_SERVER=ON
                #Add following only for cyclic tracing - mostly needed only if   the issue happens after the connection.
                TRACE_FILELEN_SERVER=<IN KILOBYTES>
                TRACE_FILENO_SERVER=<NO OF FILES>
                #Add following only for 11g database servers
                DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1  How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
18) Listener tracing

listener tracing ,append the following lines in the listener.ora
                TRACE_FILE_<LISTENER NAME>=Listener
                TRACE_LEVEL_<LISTENER NAME>=SUPPORT
                TRACE_TIMESTAMP_<LISTENER NAME>=TRUE
                TRACE_DIRECTORY_<LISTENER NAME>=<valid directory path>
                #Add cyclic tracing only for issues which occur long after the  listener is started or for intermittent issues.
                TRACE_FILELEN_<LISTENER NAME>=<IN KILOBYTES>
                TRACE_FILENO_<LISTENER NAME>=<NO OF FILES>
                #Add following only when the listener version is of 11g.
                DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1  How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
19) ORA-01652

select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by MB DESC ;

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
from v$sort_segment;

Also, please set event for 1652 and upload the trace generated

sql> alter system set events '1652 trace name ERRORSTACK level 3';
   
wait until the error happens
   
sql> alter system set events '1652 trace name ERRORSTACK off; /*Disable the event*/

Upload any trace files generated in the user_dump_destination around the time of the error.
10G
SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

For 8.1.7 to 9.2:

SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;


A query of the view v$sort_segment checks the temporary tablespaces
which contain sort segments.

you could use the query:

SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
         free_extents, max_used_size FROM v$sort_segment;


If you are interested in the amount of space in the temporary segments
currently in use by the database-users, you can query the view
v$sort_usage and the view v$session. The view v$sort_usage shows
only information during the sort.

you could use the query:

SQL> SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
      FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr;
Related article Note.793380.1 ORA-1652 Error Troubleshooting
20) Backup Recovery


set pagesize 20000
set linesize 1000
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool RECOVER.lst
show parameter pfile;
archive log list;
select * from v$backup;
select file#, status, substr(name, 1, 70) from v$datafile;
select distinct checkpoint_change# from v$datafile_header;
--
select status,
       resetlogs_change#,
       resetlogs_time,
       checkpoint_change#,
       to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
       count(*)                                                              
  from v$datafile_header                                                
group by status, resetlogs_change#, resetlogs_time, checkpoint_change#,
checkpoint_time                  
order by status, checkpoint_change#, checkpoint_time ;                 
--
select substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#,
resetlogs_time
  from v$datafile_header;
select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#  from gv$
log;
select GROUP#,substr(member,1,60) from gv$logfile;
select * from gv$log_history;
select * from v$recover_file;
select * from v$recovery_log;
select HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity,
       FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;
select hxfil FileNo,FHSTA status, FHSCN SCN, FHRBA_SEQ Sequence from x$kcvfhall;

spool off
21) CSSCAN

We first need to check the current data so that there will not be any truncation/loss of data after the characterset conversion.
For this we need to run the csscan utility.

- Installing csscan

Run csminst.sql using these commands and SQL statements:

    cd $ORACLE_HOME/rdbms/admin
    set oracle_sid=<your SID>
    sqlplus "sys as sysdba"
    SQL>set TERMOUT ON
    SQL>set ECHO ON
    SQL>spool csminst.log
    SQL> START csminst.sql

  Check the csminst.log for errors.

More infoabout csscan is in Note 123670.1 Use Scanner Utility before Altering the Database Character Set

- Run csscan

Run it with the following syntax:

csscan FULL=Y FROMCHAR=AL32UTF8 TOCHAR=AL32UTF8 LOG=AL32TOALUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2

This will create 3 files :

AL32TOALUTF8.out a logging of the output of csscan
AL32TOALUTF8.txt a Database Scan Summary Report
AL32TOALUTF8.err a log file that normally should contain the rowid's of
                 the rows of the tables reported in AL32TOTOUTF8.txt

csscan FULL=Y FROMCHAR=AL32UTF8 TOCHAR=UTF8 LOG=AL32TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2

This will create 3 files :

AL32TOUTF8.out a logging of the output of csscan
AL32TOUTF8.txt a Database Scan Summary Report
AL32TOUTF8.err a log file that normally should contain the rowid's of
                 the rows of the tables reported in AL32TOUTF8.txt


Imp: Change characterset name as appropriate

Related articles
Note.123670.1  Use Scanner Utility before Altering the Database Character Set
Note.225938.1  Database Character Set Healthcheck
Note.225912.1  Changing the Database Character Set ( NLS_CHARACTERSET )  
Note.444701.1  Csscan output explained
22)Catalog/catproc/utlrp


SQL> connect / as sysdba
SQL> startup migrate
SQL> @$ORACLE_HOME/rdbms/admin/catlog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> shutdown immediate
SQL> startup
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
23) V$SESSION_WAIT

column sid format 990
    column seq# format 99990
    column wait_time heading 'WTime' format 99990
    column event format a30
    column p1 format 9999999990
    column p2 format 9999999990
    column p3 format 9990
    select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait
    where sid=<SID>
    order by sid;
SELECT s.sid, p.spid
    FROM v$session s, v$process p
    WHERE s.paddr = p.addr
    AND ... < p.spid = <os pid> or perhaps
    s.sid = <sid from v$session> >
   
    - What SQL statement is being executed?
   
    SELECT s.sid, s.status, q.sql_text
    FROM v$session s, v$sqltext q
    WHERE s.sql_hash_value = q.hash_value
    AND s.sql_address = q.address
    AND s.sid = <sid>
    order by q.piece;

24) Resource manager


     select * from v$rsrc_plan;
     select * from dba_rsrc_plans;
     select * from dba_rsrc_consumer_groups;
     select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4,
     PARALLEL_DEGREE_LIMIT_P1, status from dba_rsrc_plan_directives;
     select * from dba_rsrc_consumer_group_privs;
     
25) UNABLE TO EXTEND errors

Determine the largest contiguous space available for the tablespace with the error
SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name>';
Determine NEXT_EXTENT size
SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = <segment name> AND SEGMENT_TYPE = <segment type> AND OWNER = <owner>
Compute the NEXT EXTENT SIZE if the segment resides in a dictionary managed tablespace and has a PCT_INCREASE >0
SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name>';
Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZ
For Data Files
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name> ';
For Temp Files
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name='<tablespace name> ';
26) Underscore parameter value

  select  a.ksppinm  "Parameter",
               b.ksppstvl "Session Value",
               c.ksppstvl "Instance Value"
        from x$ksppi a, x$ksppcv b, x$ksppsv c
       where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '__large_pool_size';
27) Default tablespace for users

SQL > SELECT USERNAME,USER_ID,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME IN
('<username1>','<username2>') ;
28) DBMS_METADATA

To get DDL of Table and tablespace.

SQL> SET LONG 2000000
SQL> select dbms_metadata.get_ddl('TABLE',’<tablename>','<owner>') from dual;
Table, Tablename,Owner
SQL> select dbms_metadata.get_ddl('TABLESPACE',’<tablespace_name>’) from dual;
29) Sessions related problems

select username, count(*) from v$session group by username;

select machine, count(*) from v$session group by machine;
   
select program, count(*) from v$session group by program;
   
select status, count(*) from v$session group by status;
   
select count(*) from v$process where addr not in (select paddr from v$session);
   
select username, program,sid, serial# from v$session where status='INACTIVE';
30) Shared memory segments and semaphores:

Checking for shared memory and semaphores
$ ipcs -mt (if there is anything owned by oracle remove it)
$ ipcrm -m [ID] (to remove it)

Checking and removing semaphores
$ ipcs -sbt (if there is anything owned by oracle remove it)
$ ipcrm -s [ID] (to remove it)
31) AWR

SQL> @?/rdbms/admin/awrrtp.sql
32) Statspack

The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
e.g.
SQL> connect perfstat/perfstat
SQL> execute statspack.snap
To gather a STATSPACK report :
=======================
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport
33) Important events

Event:   10513
Text:    disable transaction recovery
-------------------------------------------------------------------------------
Explanation:
         This event disables deferred transaction recovery which is initiated
         by the SMON process.
Levels:
         level 2  = transaction recovery disabled
Example:
         To set event 10513 enter the following line into init.ora:
           event="10513 trace name context forever, level 2"
         and bounce the instance
Event:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
        Level:  <=5  trace instance recovery
        > 5  trace posting of SMON

To set event 10500:
For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
    event="10500 trace name context forever, level <value>"
c.  restart the database

wrx0815

好东西顶啊

noko_jj

好东西顶啊

licharles

漂亮！收藏！

w老六

收藏！收藏！

ziyoo0830

收藏。。。

WWJD_DBA

必须顶！

545459983

好东西，必须赞

fengbao

学习学习

545459983

好东西，必须赞

mikexu

精辟精辟

zhuzd0

很实用，很强大

lgang403

it's good!

lunar

强大，期待继续补充，O(∩_∩)O哈哈~

id_zgd

学习！！！

dxianghui

学习！！！

北柏

继续支持！

Leo

mark,找时间细看.

yaohongdi1987

强大 学习了 期待继续补充

diyindo

感谢共享

bryan_zhy

   mark,好好学习

aaronqlm

收藏，好东西，感谢分享

yfzsj

谢谢整理分享

elementkk

都是很实用的东西，感谢分享！

genweihua

使用，收藏，学习。

kobe24shou

好东西顶啊