sqlplus “/ as sysdba”
set line 1000
set pages 2000
col name for a60
col value for a40
col description for a60
select KSPPINM as name ,KSPPSTVL as value,KSPPDESC as description from x$ksppi a,x$ksppsv b
where a.INDX=b.INDX and KSPPINM like ‘\_%’ escape ‘\’;
http://www.idevelopment.info/data/Oracle/
DBA_scripts/OUTPUT_REPORTS/dba_query_hidden_parameters.lst
动态视图 FIXED TABLE ------------------------ ------------------------- GV$ACCESS x$ksuses,x$kglob,x$kgldp,x$kgllk GV$ACTIVE_INSTANCES x$ksimsi GV$ACTIVE_SESS_POOL_MTH x$kgskasp GV$AQ1 X$KWQSI GV$ACCESS x$ksuses,x$kglob,x$kgldp,x$kgllk GV$ACTIVE_INSTANCES x$ksimsi GV$ACTIVE_SESS_POOL_MTH x$kgskasp GV$AQ1 X$KWQSI GV$ARCHIVE x$kccle,x$kccdi GV$ARCHIVED_LOG x$kccal GV$ARCHIVE_DEST x$kcrrdest GV$ARCHIVE_DEST_STATUS x$kcrrdstat GV$ARCHIVE_GAP v$archived_log, from v$log_history, GV$ARCHIVE_PROCESSES x$kcrrarch GV$BACKUP x$kcvfhonl GV$BACKUP_ASYNC_IO x$ksfqp GV$BACKUP_CORRUPTION x$kccfc GV$BACKUP_DATAFILE x$kccbf GV$BACKUP_DEVICE x$ksfhdvnt GV$BACKUP_PIECE x$kccbp GV$BACKUP_REDOLOG x$kccbl GV$BACKUP_SET x$kccbs GV$BACKUP_SYNC_IO x$ksfqp GV$BGPROCESS x$ksbdp,x$ksbdd GV$BH x$bh,x$le GV$BSP x$kclcrst GV$BUFFER_POOL x$kcbwbpd GV$BUFFER_POOL_STATISTICS x$kcbwds,x$kcbwbpd GV$CIRCUIT x$kmcvc GV$CLASS_CACHE_TRANSFER x$class_stat GV$CLASS_PING x$class_stat GV$COMPATIBILITY x$kckty GV$COMPATSEG x$kckce GV$CONTEXT x$context GV$CONTROLFILE x$kcccf GV$CONTROLFILE_RECORD_SECTION x$kccrs GV$COPY_CORRUPTION x$kcccc GV$CR_BLOCK_SERVER x$kclcrst GV$DATABASE x$kccdi GV$DATAFILE x$kccfe,x$kccfn,x$kccfn, x$kcvfh GV$DATAFILE_COPY x$kccdc GV$DATAFILE_HEADER x$kcvfh GV$DBFILE x$kccfn GV$DBLINK x$uganco GV$DB_CACHE_ADVICE x$kcbsc,x$kcbwbpd GV$DB_OBJECT_CACHE x$kglob GV$DB_PIPES x$kglob GV$DELETED_OBJECT x$kccdl GV$DISPATCHER x$kmmdi GV$DISPATCHER_RATE x$kmmdi GV$DLM_ALL_LOCKS V$GES_ENQUEUE GV$DLM_CONVERT_LOCAL x$kjicvt GV$DLM_CONVERT_REMOTE x$kjicvt GV$DLM_LATCH V$LATCH GV$DLM_LOCKS V$GES_BLOCKING_ENQUEUE GV$DLM_MISC x$kjisft GV$DLM_RESS x$kjirft,x$kjbr GV$DLM_TRAFFIC_CONTROLLER x$kjitrft GV$ENABLEDPRIVS x$kzspr GV$ENQUEUE_LOCK x$ksqeq,x$ksuse,x$ksqrs GV$ENQUEUE_STAT X$KSQST GV$EVENT_NAME x$ksled GV$EXECUTION x$kstex GV$FAST_START_SERVERS x$ktprxrs GV$FAST_START_TRANSACTIONS x$ktprxrt GV$FILESTAT x$kcfio,x$kccfe GV$FILE_CACHE_TRANSFER x$kcfio,x$kccfe GV$FILE_PING x$kcfio,x$kccfe GV$FIXED_TABLE x$kqfta,x$kqfdt GV$FIXED_VIEW_DEFINITION x$kqfvi,x$kqfvt GV$GCSHVMASTER_INFO x$kjdrpcmhv GV$GCSPFMASTER_INFO x$kjdrpcmpf GV$GC_ELEMENT x$le GV$GC_ELEMENTS_WITH_COLLISIONS v$bh GV$GES_BLOCKING_ENQUEUE V$GES_ENQUEUE GV$GES_ENQUEUE x$kjilkft,x$kjbl GV$GLOBALCONTEXT x$globalcontext GV$GLOBAL_BLOCKED_LOCKS v$lock, v$dlm_locks GV$GLOBAL_TRANSACTION X$K2GTE2 GV$HS_AGENT X$HS_SESSION GV$HS_PARAMETER X$HS_SESSION,X$HOFP GV$HS_SESSION X$HS_SESSION GV$HVMASTER_INFO x$kjdrhv GV$INDEXED_FIXED_COLUMN x$kqfco,x$kqfta GV$INSTANCE x$ksuxsinst,x$kvit,x$quiesce GV$INSTANCE_RECOVERY X$KSUSGSTA,X$TARGETRBA,X$ESTIMATED_MTTR GV$LATCH x$kslld, x$ksllt GV$LATCHHOLDER x$ksuprlat GV$LATCHNAME x$kslld GV$LATCH_CHILDREN x$ksllt,x$kslld GV$LATCH_MISSES x$ksllw,x$kslwsc GV$LATCH_PARENT x$ksllt, x$kslld GV$LIBRARYCACHE x$kglst GV$LICENSE x$ksull GV$LOADCSTAT x$kllcnt GV$LOADISTAT x$klcie GV$LOADPSTAT x$klpt GV$LOADTSTAT x$klltab GV$LOCK v$_lock,x$ksuse,x$ksqrs GV$LOCKED_OBJECT x$ktcxb,x$ktadm, x$ksuse GV$LOCKS_WITH_COLLISIONS v$bh GV$LOCK_ACTIVITY x$le_stat GV$LOCK_ELEMENT x$le GV$LOG x$kccle, x$kccrt GV$LOGFILE x$kccfn GV$LOGHIST x$kcclh GV$LOGMNR_CALLBACK x$logmnr_callback GV$LOGMNR_CONTENTS x$logmnr_contents GV$LOGMNR_DICTIONARY x$logmnr_dictionary GV$LOGMNR_LOGFILE x$logmnr_logfile GV$LOGMNR_LOGS x$logmnr_logs GV$LOGMNR_PARAMETERS x$logmnr_parameters GV$LOGMNR_PROCESS x$logmnr_process, v$process GV$LOGMNR_REGION x$logmnr_region GV$LOGMNR_SESSION x$logmnr_session GV$LOGMNR_TRANSACTION x$logmnr_transaction GV$LOGSTDBY x$krvslv GV$LOGSTDBY_APPLY x$knstasl,v$session GV$LOGSTDBY_COORDINATOR x$knstacr,v$session GV$LOGSTDBY_STATS x$krvslvs GV$LOG_HISTORY x$kcclh GV$MANAGED_STANDBY x$kcrrms GV$MAX_ACTIVE_SESS_TARGET_MTH x$kgskasp GV$MVREFRESH x$knstmvr,v$session GV$MYSTAT x$ksumysta GV$NLS_PARAMETERS x$nls_parameters GV$NLS_VALID_VALUES x$ksulv GV$OBJECT_DEPENDENCY x$kglob,x$kgldp GV$OBSOLETE_PARAMETER x$ksppo GV$OFFLINE_RANGE x$kccor GV$OPEN_CURSOR x$kgllk GV$OPTION x$option GV$PARALLEL_DEGREE_LIMIT_MTH x$kgskdopp GV$PARAMETER x$ksppi, x$ksppcv GV$PARAMETER2 x$ksppi , x$ksppcv2 GV$PGASTAT X$QESMMSGA GV$PQ_SESSTAT x$kxfpsst GV$PQ_SLAVE x$kxfpdp GV$PQ_SYSSTAT x$kxfpys GV$PQ_TQSTAT x$kxfqsrow GV$PROCESS x$ksupr GV$PROXY_ARCHIVEDLOG x$kccpa GV$PROXY_DATAFILE x$kccpd GV$PWFILE_USERS x$kzsrt GV$PX_PROCESS x$kxfpdp, V$PROCESS, V$SESSION GV$PX_PROCESS_SYSSTAT x$kxfpns GV$PX_SESSION x$ksuse,x$kxfpdp GV$PX_SESSTAT x$ksuse, x$kxfpdp, x$ksusesta,x$ksusd GV$QUEUE x$kmcqs GV$QUEUEING_MTH x$kgskquep GV$RECOVERY_FILE_STATUS x$kcrmx, x$kccfn , x$kccfe, x$kcrmf GV$RECOVERY_LOG x$kcclh,x$kcvfhmrr,x$kccle GV$RECOVERY_PROGRESS V$SESSION_LONGOPS GV$RECOVERY_STATUS x$kcrmx, x$kcrfx GV$RECOVER_FILE x$kcvfhmrr GV$REPLPROP x$knstrpp,v$session GV$REPLQUEUE x$knstrqu GV$REQDIST x$kmmrd GV$RESERVED_WORDS x$kwddef GV$RESOURCE x$ksqrs GV$RESOURCE_LIMIT x$ksurlmt GV$RESUMABLE x$ktrso GV$RMAN_CONFIGURATION X$KCCRM GV$ROLLSTAT x$kturd GV$ROWCACHE x$kqrst GV$ROWCACHE_PARENT x$kqrfp GV$ROWCACHE_SUBORDINATE x$kqrfs GV$RSRC_CONSUMER_GROUP x$kgskcft GV$RSRC_CONSUMER_GROUP_CPU_MTH x$kgskcp GV$RSRC_PLAN x$kgskpft GV$RSRC_PLAN_CPU_MTH x$kgskpp GV$SESSION x$ksuse GV$SESSION_CONNECT_INFO x$ksusecon GV$SESSION_CURSOR_CACHE x$kgicc GV$SESSION_EVENT x$ksles , x$ksled GV$SESSION_LONGOPS x$ksulop GV$SESSION_OBJECT_CACHE x$kocst GV$SESSION_WAIT x$ksusecst, x$ksled GV$SESSTAT x$ksusesta GV$SESS_IO x$ksusio GV$SGA x$ksmsd GV$SGASTAT x$ksmfs,x$ksmss,x$ksmls,x$ksmjs GV$SHARED_POOL_RESERVED x$ksmspr, x$kghlu GV$SHARED_SERVER x$kmmsi GV$SHARED_SERVER_MONITOR x$kmmsg GV$SORT_SEGMENT x$ktstssd GV$SORT_USAGE x$ktsso, v$session GV$SPPARAMETER x$kspspfile GV$SQL x$kglcursor GV$SQLAREA x$kglcursor GV$SQLTEXT x$kglna GV$SQLTEXT_WITH_NEWLINES x$kglna1 GV$SQL_BIND_DATA x$kxsbd GV$SQL_BIND_METADATA x$kksbv GV$SQL_CURSOR x$kxscc GV$SQL_PLAN x$kqlfxpl, obj$ , user$ GV$SQL_REDIRECTION x$kglcursor , x$kkssrd GV$SQL_SHARED_CURSOR x$kkscs GV$SQL_SHARED_MEMORY x$kglcursor, x$ksmhp GV$SQL_WORKAREA GV$SQL_WORKAREA_ACTIVE x$qesmmiwt GV$STANDBY_LOG x$kccsl GV$STATNAME x$ksusd GV$SUBCACHE x$kqlset GV$SYSSTAT x$ksusgsta GV$SYSTEM_CURSOR_CACHE x$kgics GV$SYSTEM_EVENT x$kslei , x$ksled GV$SYSTEM_PARAMETER x$ksppi , x$ksppsv GV$SYSTEM_PARAMETER2 x$ksppi , x$ksppsv2 GV$TABLESPACE x$kccts GV$TEMPFILE x$kcctf, x$kccfn , x$kcvfhtmp GV$TEMPORARY_LOBS X$KDLT GV$TEMPSTAT x$kcftio ,x$kcctf GV$TEMP_CACHE_TRANSFER x$kcftio , x$kcctf GV$TEMP_EXTENT_MAP ts$, x$ktftme GV$TEMP_EXTENT_POOL ts$ , x$ktstfc GV$TEMP_PING x$kcftio , x$kcctf GV$TEMP_SPACE_HEADER ts$ , x$ktfthc GV$THREAD x$kccrt GV$TIMER x$ksutm GV$TIMEZONE_NAMES X$TIMEZONE_NAMES GV$TRANSACTION x$ktcxb GV$TRANSACTION_ENQUEUE x$ktcxb ,x$ksuse ,x$ksqrs GV$TYPE_SIZE x$kqfsz GV$UNDOSTAT X$KTUSMST GV$VERSION x$version GV$VPD_POLICY x$kglcursor, x$kzrtpd GV$WAITSTAT x$kcbwait GV$_LOCK v$_lock1,x$ktadm,x$ktcxb GV$_LOCK1 x$kdnssf, x$ksqeq GV$_SEQUENCES X$KGLOB V$ACCESS GV$ACCESS V$ACTIVE_INSTANCES GV$ACTIVE_INSTANCES V$ACTIVE_SESS_POOL_MTH gv$active_sess_pool_mth V$AQ1 GV$AQ1 V$ARCHIVE GV$ARCHIVE V$ARCHIVED_LOG GV$ARCHIVED_LOG V$ARCHIVE_DEST GV$ARCHIVE_DEST V$ARCHIVE_DEST_STATUS GV$ARCHIVE_DEST_STATUS V$ARCHIVE_GAP GV$ARCHIVE_GAP V$ARCHIVE_PROCESSES GV$ARCHIVE_PROCESSES V$BACKUP GV$BACKUP V$BACKUP_ASYNC_IO gv$backup_async_io V$BACKUP_CORRUPTION GV$BACKUP_CORRUPTION V$BACKUP_DATAFILE GV$BACKUP_DATAFILE V$BACKUP_DEVICE GV$BACKUP_DEVICE V$BACKUP_PIECE GV$BACKUP_PIECE V$BACKUP_REDOLOG GV$BACKUP_REDOLOG V$BACKUP_SET GV$BACKUP_SET V$BACKUP_SYNC_IO gv$backup_sync_io V$BGPROCESS gv$bgprocess V$BH gv$bh V$BSP gv$bsp V$BUFFER_POOL gv$buffer_pool V$BUFFER_POOL_STATISTICS gv$buffer_pool_statistics V$CIRCUIT GV$CIRCUIT V$CLASS_CACHE_TRANSFER gv$class_cache_transfer V$CLASS_PING gv$class_ping V$COMPATIBILITY GV$COMPATIBILITY V$COMPATSEG GV$COMPATSEG V$CONTEXT x$context V$CONTROLFILE GV$CONTROLFILE V$CONTROLFILE_RECORD_SECTION GV$CONTROLFILE_RECORD_SECTION V$COPY_CORRUPTION GV$COPY_CORRUPTION V$CR_BLOCK_SERVER gv$cr_block_server V$DATABASE GV$DATABASE V$DATAFILE GV$DATAFILE V$DATAFILE_COPY GV$DATAFILE_COPY V$DATAFILE_HEADER GV$DATAFILE_HEADER V$DBFILE GV$DBFILE V$DBLINK GV$DBLINK V$DB_CACHE_ADVICE gv$db_cache_advice V$DB_OBJECT_CACHE GV$DB_OBJECT_CACHE V$DB_PIPES GV$DB_PIPES V$DELETED_OBJECT GV$DELETED_OBJECT V$DISPATCHER GV$DISPATCHER V$DISPATCHER_RATE GV$DISPATCHER_RATE V$DLM_ALL_LOCKS GV$DLM_ALL_LOCKS V$DLM_CONVERT_LOCAL GV$DLM_CONVERT_LOCAL V$DLM_CONVERT_REMOTE GV$DLM_CONVERT_REMOTE V$DLM_LATCH GV$DLM_LATCH V$DLM_LOCKS GV$DLM_LOCKS V$DLM_MISC GV$DLM_MISC V$DLM_RESS GV$DLM_RESS V$DLM_TRAFFIC_CONTROLLER GV$DLM_TRAFFIC_CONTROLLER V$ENABLEDPRIVS GV$ENABLEDPRIVS V$ENQUEUE_LOCK GV$ENQUEUE_LOCK V$ENQUEUE_STAT GV$ENQUEUE_STAT V$EVENT_NAME gv$event_name V$EXECUTION GV$EXECUTION V$FAST_START_SERVERS GV$FAST_START_SERVERS V$FAST_START_TRANSACTIONS GV$FAST_START_TRANSACTIONS V$FILESTAT GV$FILESTAT V$FILE_CACHE_TRANSFER gv$file_cache_transfer V$FILE_PING gv$file_ping V$FIXED_TABLE GV$FIXED_TABLE V$FIXED_VIEW_DEFINITION GV$FIXED_VIEW_DEFINITION V$GCSHVMASTER_INFO GV$GCSHVMASTER_INFO V$GCSPFMASTER_INFO GV$GCSPFMASTER_INFO V$GC_ELEMENT gv$gc_element V$GC_ELEMENTS_WITH_COLLISIONS gv$gc_elements_with_collisions V$GES_BLOCKING_ENQUEUE GV$GES_BLOCKING_ENQUEUE V$GES_ENQUEUE GV$GES_ENQUEUE V$GLOBALCONTEXT gv$globalcontext V$GLOBAL_BLOCKED_LOCKS gv$global_blocked_locks V$GLOBAL_TRANSACTION GV$GLOBAL_TRANSACTION V$HS_AGENT GV$HS_AGENT V$HS_PARAMETER GV$HS_PARAMETER V$HS_SESSION GV$HS_SESSION V$HVMASTER_INFO GV$HVMASTER_INFO V$INDEXED_FIXED_COLUMN GV$INDEXED_FIXED_COLUMN V$INSTANCE GV$INSTANCE V$INSTANCE_RECOVERY GV$INSTANCE_RECOVERY V$LATCH gv$latch V$LATCHHOLDER GV$LATCHHOLDER V$LATCHNAME gv$latchname V$LATCH_CHILDREN GV$LATCH_CHILDREN V$LATCH_MISSES GV$LATCH_MISSES V$LATCH_PARENT GV$LATCH_PARENT V$LIBRARYCACHE GV$LIBRARYCACHE V$LICENSE gv$license V$LOADCSTAT GV$LOADCSTAT V$LOADISTAT GV$LOADISTAT V$LOADPSTAT GV$LOADPSTAT V$LOADTSTAT GV$LOADTSTAT V$LOCK GV$LOCK V$LOCKED_OBJECT gv$locked_object V$LOCKS_WITH_COLLISIONS v$bh V$LOCK_ACTIVITY GV$LOCK_ACTIVITY V$LOCK_ELEMENT gv$lock_element V$LOG GV$LOG V$LOGFILE GV$LOGFILE V$LOGHIST GV$LOGHIST V$LOGMNR_CALLBACK gv$logmnr_callback V$LOGMNR_CONTENTS GV$LOGMNR_CONTENTS V$LOGMNR_DICTIONARY GV$LOGMNR_DICTIONARY V$LOGMNR_LOGFILE gv$logmnr_logfile V$LOGMNR_LOGS GV$LOGMNR_LOGS V$LOGMNR_PARAMETERS GV$LOGMNR_PARAMETERS V$LOGMNR_PROCESS gv$logmnr_process V$LOGMNR_REGION gv$logmnr_region V$LOGMNR_SESSION GV$LOGMNR_SESSION V$LOGMNR_TRANSACTION gv$logmnr_transaction V$LOGSTDBY gv$logstdby V$LOGSTDBY_APPLY GV$LOGSTDBY_APPLY V$LOGSTDBY_COORDINATOR GV$LOGSTDBY_COORDINATOR V$LOGSTDBY_STATS gv$logstdby_stats V$LOG_HISTORY GV$LOG_HISTORY V$MANAGED_STANDBY GV$MANAGED_STANDBY V$MAX_ACTIVE_SESS_TARGET_MTH gv$max_active_sess_target_mth V$MVREFRESH GV$MVREFRESH V$MYSTAT GV$MYSTAT V$NLS_PARAMETERS GV$NLS_PARAMETERS V$NLS_VALID_VALUES GV$NLS_VALID_VALUES V$OBJECT_DEPENDENCY GV$OBJECT_DEPENDENCY V$OBSOLETE_PARAMETER GV$OBSOLETE_PARAMETER V$OFFLINE_RANGE GV$OFFLINE_RANGE V$OPEN_CURSOR GV$OPEN_CURSOR V$OPTION GV$OPTION V$PARALLEL_DEGREE_LIMIT_MTH gv$parallel_degree_limit_mth V$PARAMETER GV$PARAMETER V$PARAMETER2 GV$PARAMETER2 V$PGASTAT GV$PGASTAT V$PQ_SESSTAT GV$PQ_SESSTAT V$PQ_SLAVE GV$PQ_SLAVE V$PQ_SYSSTAT GV$PQ_SYSSTAT V$PQ_TQSTAT GV$PQ_TQSTAT V$PROCESS gv$process V$PROXY_ARCHIVEDLOG GV$PROXY_ARCHIVEDLOG V$PROXY_DATAFILE GV$PROXY_DATAFILE V$PWFILE_USERS GV$PWFILE_USERS V$PX_PROCESS GV$PX_PROCESS V$PX_PROCESS_SYSSTAT GV$PX_PROCESS_SYSSTAT V$PX_SESSION GV$PX_SESSION V$PX_SESSTAT GV$PX_SESSTAT V$QUEUE GV$QUEUE V$QUEUEING_MTH gv$queueing_mth V$RECOVERY_FILE_STATUS GV$RECOVERY_FILE_STATUS V$RECOVERY_LOG GV$RECOVERY_LOG V$RECOVERY_PROGRESS GV$RECOVERY_PROGRESS V$RECOVERY_STATUS GV$RECOVERY_STATUS V$RECOVER_FILE GV$RECOVER_FILE V$REPLPROP GV$REPLPROP V$REPLQUEUE GV$REPLQUEUE V$REQDIST GV$REQDIST V$RESERVED_WORDS GV$RESERVED_WORDS V$RESOURCE GV$RESOURCE V$RESOURCE_LIMIT GV$RESOURCE_LIMIT V$RESUMABLE GV$RESUMABLE V$RMAN_CONFIGURATION GV$RMAN_CONFIGURATION V$ROLLSTAT GV$ROLLSTAT V$ROWCACHE gv$rowcache V$ROWCACHE_PARENT gv$rowcache_parent V$ROWCACHE_SUBORDINATE gv$rowcache_subordinate V$RSRC_CONSUMER_GROUP gv$rsrc_consumer_group V$RSRC_CONSUMER_GROUP_CPU_MTH gv$rsrc_consumer_group_cpu_mth V$RSRC_PLAN gv$rsrc_plan V$RSRC_PLAN_CPU_MTH gv$rsrc_plan_cpu_mth VSESSION GV$SESSION V$SESSION_CONNECT_INFO gv$session_connect_info V$SESSION_CURSOR_CACHE GV$SESSION_CURSOR_CACHE V$SESSION_EVENT gv$session_event V$SESSION_LONGOPS GV$SESSION_LONGOPS V$SESSION_OBJECT_CACHE gv$session_object_cache V$SESSION_WAIT gv$session_wait V$SESSTAT GV$SESSTAT V$SESS_IO GV$SESS_IO V$SGA GV$SGA V$SGASTAT GV$SGASTAT V$SHARED_POOL_RESERVED GV$SHARED_POOL_RESERVED V$SHARED_SERVER GV$SHARED_SERVER V$SHARED_SERVER_MONITOR GV$SHARED_SERVER_MONITOR V$SORT_SEGMENT GV$SORT_SEGMENT V$SORT_USAGE GV$SORT_USAGE V$SPPARAMETER GV$SPPARAMETER V$SQL GV$SQL V$SQLAREA GV$SQLAREA V$SQLTEXT GV$SQLTEXT V$SQLTEXT_WITH_NEWLINES GV$SQLTEXT_WITH_NEWLINES V$SQL_BIND_DATA GV$SQL_BIND_DATA V$SQL_BIND_METADATA GV$SQL_BIND_METADATA V$SQL_CURSOR GV$SQL_CURSOR V$SQL_PLAN GV$SQL_PLAN V$SQL_REDIRECTION GV$SQL_REDIRECTION V$SQL_SHARED_CURSOR GV$SQL_SHARED_CURSOR V$SQL_SHARED_MEMORY GV$SQL_SHARED_MEMORY V$SQL_WORKAREA GV$SQL_WORKAREA V$SQL_WORKAREA_ACTIVE GV$SQL_WORKAREA_ACTIVE V$STANDBY_LOG GV$STANDBY_LOG V$STATNAME GV$STATNAME V$SUBCACHE GV$SUBCACHE V$SYSSTAT GV$SYSSTAT V$SYSTEM_CURSOR_CACHE GV$SYSTEM_CURSOR_CACHE V$SYSTEM_EVENT gv$system_event V$SYSTEM_PARAMETER GV$SYSTEM_PARAMETER V$SYSTEM_PARAMETER2 GV$SYSTEM_PARAMETER2 V$TABLESPACE GV$TABLESPACE V$TEMPFILE GV$TEMPFILE V$TEMPORARY_LOBS GV$TEMPORARY_LOBS V$TEMPSTAT GV$TEMPSTAT V$TEMP_CACHE_TRANSFER gv$temp_cache_transfer V$TEMP_EXTENT_MAP GV$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL GV$TEMP_EXTENT_POOL V$TEMP_PING gv$temp_ping V$TEMP_SPACE_HEADER GV$TEMP_SPACE_HEADER V$THREAD GV$THREAD V$TIMER GV$TIMER V$TIMEZONE_NAMES GV$TIMEZONE_NAMES V$TRANSACTION gv$transaction V$TRANSACTION_ENQUEUE GV$TRANSACTION_ENQUEUE V$TYPE_SIZE GV$TYPE_SIZE V$UNDOSTAT X$KTUSMST V$VERSION GV$VERSION V$VPD_POLICY GV$VPD_POLICY V$WAITSTAT gv$waitstat V$_LOCK GV$_LOCK V$_LOCK1 GV$_LOCK1 V$_SEQUENCES GV$_SEQUENCES
故障现象提供：小荷
解决方案提供：老熊
(1)现象：报ora-600,2662错误
Completed: ALTER DATABASE MOUNT
Thu Jan 22 13:05:08 2009
ALTER DATABASE OPEN
Thu Jan 22 13:05:09 2009
Beginning crash recovery of 1 threads
Thu Jan 22 13:05:09 2009
Started first pass scan
Thu Jan 22 13:05:09 2009
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Thu Jan 22 13:05:09 2009
Started recovery at
Thread 1: logseq 2, block 3, scn 0.43536037
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/ora9i/redo01.log
Thu Jan 22 13:05:09 2009
Ended recovery at
Thread 1: logseq 2, block 3, scn 0.43556038
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Thu Jan 22 13:05:10 2009
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
Current log# 2 seq# 3 mem# 0: /oracle/oradata/ora9i/redo02.log
Successful open of redo thread 1.
Thu Jan 22 13:05:10 2009
SMON: enabling cache recovery
Thu Jan 22 13:05:10 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_12968.trc:
ORA-00600: internal error code, arguments: [2662], [0], [43556042], [261], [2396789971], [4194729], [], []
SQL> startup
ORACLE instance started.
Total System Global Area 1076850392 bytes
Fixed Size 736984 bytes
Variable Size 536870912 bytes
Database Buffers 536870912 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
版本：9206
*._allow_resetlogs_corruption=TRUE
SQL> startup nomount pfile=’?/dbs/initora9i.ora’
ORACLE instance started.
Total System Global Area 1076850392 bytes
Fixed Size 736984 bytes
Variable Size 536870912 bytes
Database Buffers 536870912 bytes
Redo Buffers 2371584 bytes
SQL> alter database mount;
Database altered.
(2)采用adjust scn的方法：
SQL> alter session set events ’10015 trace name ADJUST_SCN level 1047′;
Session altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Thu Jan 22 13:27:56 2009
SMON: enabling cache recovery
Thu Jan 22 13:27:56 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_13322.trc:
ORA-00600: internal error code, arguments: [2662], [0], [43576046], [261], [2396789971], [4194729], [], []
Thu Jan 22 13:28:37 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_13322.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [43576046], [261], [2396789971], [4194729], [], []
Thu Jan 22 13:28:37 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 13322
ORA-1092 signalled during: alter database open…
*._allow_error_simulation=TRUE
———————————————————————————————–
(3)老熊的历史案例提供：
SQL> alter system set job_queue_processes=0;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;
SQL> alter system set “_allow_read_only_corruption”=true scope=spfile;
SQL> alter system set “_allow_terminal_recovery_corruption”=true scope=spfile;
再尝试以resetlogs方式打开数据库：
SQL> alter database open resetlogs;
Database altered.
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
检查日志发现由于[2662]错误导致数据库实例中止。
调整SCN：
SQL> alter session set events ’10015 trace name adjust_scn level 7681′;
再次打开数据库：
SQL> alter database open;
————————————————————————————————-
(4)照猫画熊
*._minimum_giga_scn=1047
SQL> startup mount pfile=’?/dbs/initora9i.ora’
ORACLE instance started.
Total System Global Area 1076850392 bytes
Fixed Size 736984 bytes
Variable Size 536870912 bytes
Database Buffers 536870912 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL>
Successfully onlined Undo Tablespace 15.
Dictionary check beginning
File #20 is offline, but is part of an online tablespace.
data file 20: ‘/opt/oracle/UNDO_001.dbf’
Dictionary check complete
Thu Jan 22 13:55:33 2009
SMON: enabling tx recovery
Thu Jan 22 13:55:33 2009
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Undo Segment 1 Onlined
Completed: alter database open
–数据库启动成功
看看现在的scn是多少？
SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
1.1242E+12
(5)老熊讲解
_minimum_giga_scn，就相当于adjust_scn，那个参数意思就是系统的最小scn为多少。g为单位(约为10亿)
SQL> select ksppinm,ksppdesc from x$ksppi where
2 ksppinm=’_minimum_giga_scn’;
KSPPINM
———————————————————
KSPPDESC
———————————————————
_minimum_giga_scn
Minimum SCN to start with in 2^30 units （g=2^30=1,073,741,824=10亿）
SCN构成：
ORA-00600: internal error code, arguments: [2662], [0], [43616053], [261], [2396789971], [4194729], [], []
warp=261
base=2396789971
261–2396789971
高2字节就是261
低4字节就是2396789971
scn= wrap….base
就是
0xffff.ffffffff
（base最大是ffffffff，那么加1就进位上去到wrap了。
其实就是说base的1就相当于base中的ffffffff+1了。 ）
wrap中的1其实就是2的32次方 （2^32=4294967296=2^30 *4）
_minimum_giga_scn，是我们要调整的，是以G为单位，就是2的30次方啊
就是说261那个1就相当于4个2的30次方啊
261*4+3=1047，我们去1057，比1047大一些，也就是比target大一些
(6)取消相关参数，重启数据库：
*.user_dump_dest=’/oracle/admin/ora9i/udump’
#*._allow_resetlogs_corruption=TRUE
#*._allow_error_simulation=TRUE
#*._minimum_giga_scn=1047
“initora9i.ora” 47 lines, 1467 characters
$
$
$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.1.0 – Production on Thu Jan 22 14:32:13 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1076850392 bytes
Fixed Size 736984 bytes
Variable Size 536870912 bytes
Database Buffers 536870912 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL>
当然如果是生产系统，最好是exp再重建库，imp进去
注：对于10个g，缺省情况_ALLOW_ERROR_SIMULATION是FALSE，这会阻止ADJUST_SCN，所以要做ADJUST_SCN必须设置这个隐含参数为TRUE
HTML clipboard使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库后,我们说很多时候你会遇到ORA-00600 2662号错误，这个错误的含义是:
A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
stored in a UGA variable.
If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
internal error.
在测试中,很容易模拟这个错误:
Thu Oct 20 10:38:27 2005 SMON: enabling cache recovery Thu Oct 20 10:38:27 2005 Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc: ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], [] Thu Oct 20 10:38:28 2005 Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc: ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []
如果SCN相差不多，可以通过多次重起数据库解决。
也可以通过内部事件:
alter session set events ‘IMMEDIATE trace name ADJUST_SCN level 1′;
来解决。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
通过正常方式启动数据库时，从alert文件中，我们可以看到ora-00600 2662号错误。
Sun Dec 11 18:02:25 2005
Errors in file /opt/oracle/admin/conner/udump/conner_ora_13349.trc:
ORA-00600: internal error code, arguments: [2662], [0], [547743994], [0], [898092653],
[8388617], [], []
Sun Dec 11 18:02:27 2005
Errors in file /opt/oracle/admin/conner/udump/conner_ora_13349.trc:
ORA-00600: internal error code, arguments: [2662], [0], [547743994], [0], [898092653],
[8388617], [], []
Sun Dec 11 18:02:27 2005
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
此时我们可以通过Oracle的内部事件来调整SCN:
增进SCN有两种常用方法:
1.通过immediate trace name方式(在数据库Open状态下)
alter session set events ‘IMMEDIATE trace name ADJUST_SCN level x’;
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events ’10015 trace name adjust_scn level x’;
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。
本例由于数据库无法打开，只能使用的二种方法。
| [oracle@jumper dbs]$ sqlplus “/ as sysdba”SQL*Plus: Release 9.2.0.4.0 – Production on Sun Dec 11 18:26:18 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startup mount pfile=initconner.ora ORACLE instance started.Total System Global Area 97588504 bytes Fixed Size 451864 bytes Variable Size 33554432 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted.SQL> alter session set events ’10015 trace name adjust_scn level 10′;Session altered.SQL> alter database open;Database altered. |
注意,由于我使用了10015事件，使得SCN增进了10 billion，稍后我们可以验证。
| [oracle@jumper dbs]$ sqlplus “/ as sysdba”SQL*Plus: Release 9.2.0.4.0 – Production on Sun Dec 11 18:26:18 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startup mount pfile=initconner.ora ORACLE instance started.Total System Global Area 97588504 bytes Fixed Size 451864 bytes Variable Size 33554432 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted.SQL> alter session set events ’10015 trace name adjust_scn level 10′;Session altered.SQL> alter database open;Database altered. |
此时数据库可以打开，从alert文件中我们可以看到如下提示:
| Sun Dec 11 18:27:04 2005 SMON: enabling cache recovery Sun Dec 11 18:27:05 2005 Debugging event used to advance scn to 10737418240 |
SCN被增进了10 billion,即 10 * (1024*1024*1024) = 10737418240,正好是日志里记录的数量。
我们从数据库内部看一下检查点的增进情况：
| SQL> select open_mode from v$database;OPEN_MODE ———- READ WRITESQL> select file#,CHECKPOINT_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# ———- —————— 1 547783998 2 547783998 3 547783998SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 97588504 bytes Fixed Size 451864 bytes Variable Size 33554432 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> select file#,CHECKPOINT_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# ———- —————— 1 1.0737E+10 2 1.0737E+10 3 1.0737E+10SQL> col CHECKPOINT_CHANGE# for 99999999999999999 SQL> select file#,CHECKPOINT_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# ———- —————— 1 10737418447 2 10737418447 3 10737418447 |
我们看到CHECKPOINT_CHANGE# 最终被增进了10 Billion.这要是账户上的钱该有多好：）
ref:http://www.eygle.com/archives/2005/12/20/
通常我们对于ORA-600 [2662]错误的解决是通过10015 ADJUST_SCN事件来增进current SCN以达到比数据文件中最大的SCN还要大的目的，这样才可能启动数据库。
eygle的例子中报错信息如下：
ORA-00600: internal error code, arguments: [2662], [0], [547743994], [0], [898092653], [8388617], [], []
这个报错参数的含义在metalink中如此描述的：
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
为了存储更大的SCN值，当SCN BASE到足够大并开始重置的时候，SCN WRAP将加1。
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
也就是Arg [d] 的值是从哪个block中找到的，通常是一个data block address。
通过这几个参数根据一定的规则可以计算出我们需要的level。计算规则如下：
1. Arg [c] *4得出一个数值，假设为V_Wrap
2. 如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
仍旧看eygle的案例。
Arg [c] *4 = 0 * 4 = 0
Arg [d] = 898092653 < 1073741824
所以level = 0+1 = 1
因此其实eygle不需要增进level 10，level 1就应该足够了。
看另外一个例子的报错信息，我们再来计算一次。
ORA-00600: internal error code, arguments: [2662], [0], [2179133], [8656], [70114056], [33855201], [], []
Arg [c] *4 = 8656 * 4 = 34624
Arg [d] = 70114056 < 1073741824
所以level = 34624 + 1 = 34625
因此在这个例子中我们应该执行
假如聚簇因子过大，那么重建索引可能会有好处，聚簇因子应该接近块的数量，而非行的数量。
B-tree索引：
·索引会随着时间的增加而变的不平衡；
·删除的索引空间不会被重用；
·随着索引层数的增加，索引将会变得无效并需要重建；
·聚簇因子差，索引需要重建；
·为了提高性能，索引需要经常重建；
索引基础
·一个更新由一个删除和一个插入组成；
·页块由索引条目（row header(2/3B)|length(1B)|indexed data value(nB)|length(1B)|RowID(6B)）和相应的rowid组成；
·每个页块包含两个指针分别前面的页块和后面页块；
Treedump
alter session set events ‘immediate trace name treedump level index_object_id’;
—– begin tree dump
branch: 0×424362 4342626 (0: nrow: 2, level: 1)
leaf: 0×424363 4342627 (-1: nrow: 540 rrow: 540)
leaf: 0×424364 4342628 (0: nrow: 461 rrow: 461)
—– end tree dump
以上dump包含的信息如下：
块类型：branch（分支块）；leaf（页块）；
块地址：0×424362 4342626；
nrow：索引条目的数量；
rrow：当前块中的索引条目数量；
level：分支块等级（页块隐示为0）；
Block Dump
alter system dump datafile X block X;
alter system dump datafile X block min X1 block max X2
Start dump data blocks tsn: 0 file#: 1 minblk 148538 maxblk 148538
buffer tsn: 0 rdba: 0x0042443a (1/148538)
scn: 0×0000.00162a95 seq: 0×01 flg: 0×04 tail: 0x2a950601
frmt: 0×02 chkval: 0x8b5c type: 0×06=trans data
Block header dump: 0x0042443a
Object id on Block? Y
seg/obj: 0xd1fe csc: 0×00.162a95 itc: 2 flg: O typ: 2 – INDEX
fsl: 0 fnx: 0x42443b ver: 0×01
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0005.02a.00000332 0x008005cb.020e.01 CB– 0 scn 0×0000.00162a92
0×02 0×0008.011.00000346 0x008002e6.0163.03 C— 0 scn 0×0000.00162a93
该dump包含的信息如下：
rdba：分支块的相对数据库块地址（文件号/块号）；
scn：块最后改变的SCN号；
type：块类型；
seq：块改变的数量；
seg/obj: 16进制对象ID；
typ：段类型；
Itl：相关的事务槽（页块默认为2），包括槽ID，事务ID，撤销块地址，标记，锁信息，和事务SCN；
通过rba确定数据文件号和块号：
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(rba),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(rba)
from dual;
通用的索引块头
header address 153168988=0x9212c5c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 1
kdxcoopc 0×89: opcode=9: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 254
kdxcofbo 544=0×220
kdxcofeo 4482=0×1182
kdxcoavs 3938
kdxcolev：索引级别（0代表页块）；
kdxcolok：标示结构块事块是否发生；
kdxcoopc：内部操作码；
kdxconco：索引列数量，包括ROWID；
kdxcosdc：块中索引结构改变的数量；
kdxconro：索引条目的数量，不包括kdxbrlmc指针；
kdxcofbo：块中空闲空间的开始位置；
kdxcofeo：块中空闲空间的结束位置；
kdxcoavs：块中的可用空间数量（kdxcofbo-kdxcofeo）；
分支头区域
kdxbrlmc 8388627=0×800013
kdxbrsno 92
kdxbrbksz 8060
kdxbrlmc：如果索引值小于第一个值（row#0），则为该索引值所在的块地址；
kdxbrsno：最后更改的索引条目；
kdxbrbksz：可使用的块空间；
叶块头区域
kdxlespl 0
kdxlende 127
kdxlenxt 4342843=0x42443b
kdxleprv 4342845=0x42443d
kdxledsz 0
kdxlebksz 8036
kdxlespl：块拆分时被清除的未提交数据的字节数；
kdxlende：被删除的条目数；
kdxlenxt：下一个页块的RBA；
kdxleprv：上一个页块的RBA；
kdxlebksz：可使用的块空间（默认小于分支的可用空间）；
分支条目
row#0[7898] dba: 4342821=0×424425
col 0; len 3; (3): c2 61 03
col 1; TERM
row#1[7214] dba: 4342873=0×424459
col 0; len 4; (4): c3 04 02 17
col 1; TERM
行号，[块中的起始位置] dba；
列号，列长度，列值；
brach中的每个entry有2个columns:
一个是child blocks中的最大值，另一个是指向的下一层block的address’
但是某些时候可能会有一些比较奇怪的结果：
row#0[7025] dba: 4342908=0x42447c
col 0; len 1024; (1024):
41 20 20 20 …20
col 1; len 4; (4): 00 42 44 73
—– end of branch block dump —–
叶条目
row#38[5014] flag: —-S-, lock: 2, len=14
col 0; len 4; (4): c3 04 61 55
col 1; len 6; (6): 00 42 43 db 00 a1
row#39[5028] flag: —DS-, lock: 2, len=14
行号[在块中的开始位置] 各种标记（锁信息，删除信息）；
索引列号，长度，值。其中6个字节的为ROWID号，将其转换为二进制，算法结果为：
前10 bit代表了file_id
中22 bit代表了block_id
后16 bit代表了row_id；
通过文件号和块号算出的结果为创建该索引的表的块。
奇怪的是,为什么索引中的rowid不能直接找到obj_id?
因为索引段对应的数据段在 一开始就知道，因为是先知道数据段才找到索引段，然后
根据索引段内容去搜索数据段内容，所以索引段中 rowid 不必包含 data_object_id 信息。
如果索引是建立在非分区表上，或者是分区表上的 LOCAL 索引，使用的是6 bytes的 Restricted ROWID。如果索引是建立在分区表上的 GLOBAL index，则使用 10bytes 的 Extended ROWID，这样可以区分索引指向哪个分区表。
更新/重用索引条目
当更新了索引条目后，DUMP如下：
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 8006=0x1f46
kdxcoavs 7966
kdxlespl 0
kdxlende 1
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: —D-, lock: 2 => deleted index entry
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 00 80 05 0a 00 00
row#1[8006] flag: —–, lock: 2
col 0; len 5; (5): 5a 49 47 47 59 => new index entry
col 1; len 6; (6): 00 80 05 0a 00 00
更新后，将包含一个删除的条目，一个新的条目。在随后的插入中，如果新插入的索引条目能够放到被删除的索引条目的位置上，就会直接重用这个条目。根据索引值来决定。
所谓重用，是对row 的重用，而不是对row所在物理存储（或说物理位置）的重用。索引是按照indexed value对row进行排序的。有新的row被插入，首先按照value排序，将他放在合适的row list中，如果他的位置正好原来有个row被删掉了，则重用这个row在row list中的位置。至于物理存储上，则可能根据版本不同会有不同。在10.2中，我做的测试并没有向下开辟空间。
结论:
·到叶块中的任何插入都将移除所有被删除的条目；
·删除的空间在随后的写中被清除；
·删除的空间在延迟块清除中被清除；
·全空块被放在空闲列表，可以重用；
索引统计
·dba_indexes
·dbms_stats
·index_stats
– analyze index index_name validate structure;
–分析资源，锁；
·v$segment_statistics
statistics_level = typical (or all)
注意事项：
blevel (dba_indexes) vs. height (index_stats)
blocks allocated，但未必使用；
lf_rows_len包含行负载（单列索引12个字节）
pct_used索引结构中当前使用的空间：(used_space/btree_space)*100
绝大多数索引统计包含删除的条目：
non-deleted rows = lf_rows – del_lf_rows
pct_used by non-deleted rows = ((used_space – del_lf_rows_len) / btree_space) * 100
（1）删除（Delete）流程
·Oracle读Block(数据块)到Buffer Cache（缓冲区）(如果该Block在Buffer中不存在)；
·在Redo Log Buffer（重做日志缓冲区）中记录Delete操作的细节；
·在相应回滚段段头的事物表中创建一个Undo（回滚）条目；
·把将要删除的记录创建前镜像，存放到Undo Block（回滚块）中；
·在Buffer Cache中的相应数据块上删除记录，并且标记相应的数据块为Dirty（脏）。
（2）提交(Commit)流程
·Oracle产生一个SCN；
·在回滚段事物表中标记该事物状态为Commited；
·LGWR（日志读写进程） Flush Log Buffer到日志文件；
·如果此时数据块仍然在Buffer Cache中，那么SCN将被记录到Block Header上，这被称为快速提交；
·如果Dirty Block已经被写回到磁盘，那么下一个访问这个Block的进程将会自回滚段中获取该事物的状态，确认该事物被提交。然后这个进程获得提交SCN并写回到Block Header上，这被称为延迟块清除。
January 2001 |
I have written this note purely for entertainment value. It highlights the differences that appear when a block is subject ot delayed block cleanout rather than fast commit cleanout. The experiments in this note were run against 8.1.5 only.
When an Oracle process changes a data block it keeps track of the block and, at commit time, will revisit the block and mark the change as committed – but only if the block has not been flushed from the db_block_buffer in the interim. There is also a restriction that if the process has changed too many blocks then only the first few will be revisited (I believe the limit is currently 10% of the db_block_buffers).
If a block has not been fixed up by the fast commit then the next time it is read, it will be cleaned out by the reader process. There are two variations on this delayed block cleanout: the reader may be able to determine the exact SCN that was in effect at commit time because the state of the rollback segments is still sufficiently fresh, in this case the block will be marked with the correct SCN; alternatively, if too much time has passed and the exact SCN can no longer be retrieved, the block will be marked with a ‘best guess’ SCN or upper bound commit number.
To find out if there were any differences between blocks that fall into these three states, I did the following:
Create a table with one row per block – which can be achieved by using the rpad() function to make one column as long as needed, and setting pctfree very high to make it impossible for Oracle to put more than one row in each block. For the purposes of the test I used a small db_block_buffer and made the table a little larger than the buffer. For convenience, each row has a built-in row number so that it is easy to identify
I then did three tests:
a) Update the first row in the table, commit, and dump the block.
b) Update the rest of the rows, commit, and dump the second block
c) Thrash the rollback segment to recycle and lose the history of my update, then dump another block.
In the dumps below, the significant changes are highlighted.
SQL to generate the table
create table junk1
nologging
pctfree 99
pctused 1
as
select rownum n1, rpad(rownum,200) v1
from all_objects
where rownum <= 1000
;
Fast cleanout – the first block after a single row update and commit.
The block SCN matches the transaction SCN (and the transaction SCN entry is still claiming to be a Free Space Count entry anyway), and the block flag is 2. The transaction flag is –U-, but we are still apparently indicating that this transaction is locking one row. The row has its lock byte set to 1 to identify the relevant item in the interested transaction list (ITL).
Start dump data blocks tsn: 1 file#: 2 minblk 27225 maxblk 27225
buffer tsn: 1 rdba: 0x00806a59 (2/27225)
scn: 0x0000.0067694d seq: 0x01 flg: 0x02 tail: 0x694d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00806a59
Object id on Block? Y
seg/obj: 0x8693 csc: 0x00.676937 itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.008.00001902 uba: 0x00c071b9.041b.0e --U- 1 fsc 0x0000.0067694d
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x14
pbl: 0x0670ba44
bdba: 0x00806a59
flag=-----------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x618
avsp=0x6d3
tosp=0x6d3
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x618
block_row_dump:
tab 0, row 0, @0x618
tl: 209 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 4] c3 02 01 02
col 1: [200]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 1 file#: 2 minblk 27225 maxblk 27225
Delayed block cleanout when the rollback segment still holds recent history:
The block SCN is higher than the transaction SCN (the difference is only one, as no transactions occurred between the original transaction and the read that cleaned the block), and the block flag is 0. The transaction flag is C—-, and we are no longer showing any locked rows. The transaction SCN is showing the correct value. The block is perfectly clean.
Start dump data blocks tsn: 1 file#: 2 minblk 27226 maxblk 27226
buffer tsn: 1 rdba: 0x00806a5a (2/27226)
scn: 0x0000.0067695e seq: 0x01 flg: 0x00 tail: 0x695e0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00806a5a
Object id on Block? Y
seg/obj: 0x8693 csc: 0x00.67695e itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.005.000018f3 uba: 0x00c071b9.041b.0f C--- 0 scn 0x0000.0067695d
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x14
pbl: 0x0670ba44
bdba: 0x00806a5a
flag=-----------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x618
avsp=0x6d3
tosp=0x6d3
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x618
block_row_dump:
tab 0, row 0, @0x618
tl: 209 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 4] c3 02 01 03
col 1: [200]
32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 1 file#: 2 minblk 27226 maxblk 27226
Delayed block cleanout when the rollback segment has lost all relevant history:
The block SCN is much higher than the transaction SCN (and the transaction SCN ought to be the same as the one in the example above as this block was changed in the same large-scale update), and the block flag is 0. The transaction flag is C-U-, and again we are no longer showing any locked rows. The transaction SCN is much higher than the SCN at which the transaction actually happened, as we destroyed the full history by thrashing the rollback segment, and the SCN recorded here is the lowest SCN that could be regenerated by the rollback segment (The U is for “upper bound commit”).
Start dump data blocks tsn: 1 file#: 2 minblk 27234 maxblk 27234
buffer tsn: 1 rdba: 0x00806a62 (2/27234)
scn: 0x0000.006770eb seq: 0x01 flg: 0x00 tail: 0x70eb0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00806a62
Object id on Block? Y
seg/obj: 0x8693 csc: 0x00.6770eb itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.005.000018f3 uba: 0x00c071ba.041b.03 C-U- 0 scn 0x0000.006770c0
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x14
pbl: 0x069bba44
bdba: 0x00806a62
flag=-----------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x618
avsp=0x6d3
tosp=0x6d3
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x618
block_row_dump:
tab 0, row 0, @0x618
tl: 209 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 4] c3 02 01 0b
col 1: [200]
31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 1 file#: 2 minblk 27234 maxblk 27234
InfiniBand技术是一种开放标准的、目前全球带宽最高的高速网络互联技术，InfiniBand产品是目前主流的高性能计算机互连设备之 一。目前基于InfiniBand技术的网络卡的单端口带宽最大可达到20Gbps，基于InfiniBand的交换机的单端口带宽最大可达 60Gbps，单交换机芯片可以支持达480Gbit每秒的带宽。到2006年，InfiniBand技术可以达到单端口120Gbps，其单端口的带宽 甚至远高于目前的主流交换机的总带宽, 为目前和未来对于网络带宽要求非常苛刻的应用提供了可靠的解决方案。
Block 的cache header部分，记录着一个block scn,它是当前块最后一次变更的时间戳（确切说，这个更新并不是指itl上的scn的最新更新，在接下来delayed block cleanout下的slot重用情况下，可以看到block scn并不等于itl上的最后一次更新的scn）。可以通过dump获得block scn/last itl scn 和发布ora_rowscn语句获得last itl scn。
1. SQL> select distinct ora_rowscn,dbms_rowid.rowid_block_number(rowid) from test_ind where dbms_rowid.rowid_block_number(rowid)=31501;
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(
———- ——————————
617405 31501
2. SQL> alter system dump datafile 4 block 31501;
Start dump data blocks tsn: 4 file#: 4 minblk 31501 maxblk 31501
buffer tsn: 4 rdba: 0×01007b0d (4/31501)
scn: 0×0000.00096bbd seq: 0×01 flg: 0×02 tail: 0×6bbd0601
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
――――――――――――――――――――――――――――――――――――――
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×000a.001.00000089 0×008033be.0046.14 C— 0 scn 0×0000.00065b9e
0×02 0×0008.02b.000000ac 0×00802fea.004a.28 –U- 1 fsc 0×0000.00096bbd
发布transaction后，未提交之前，block scn是不会改变的，对应的itl中也并不做scn记录。Block scn的改变，确切的说不是在发布commit之时（因为有delayed block cleanout的情况存在），而是在transaction对应的itl获得commit scn之时。
当发生fast commit cleanout，系统将transaction提交时刻的scn作为commit scn，更新block上 itl和undo segment header的Transaction table的slot上的 scn，并修改block scn，三者是一致的。发生delayed block cleanout的时候，之前的transaction commit更新的只是Transaction table，而并未做block上的处理，等待下次使用此blobk的时候，更新block scn和itl状态。block scn和itl的更新又分2种情况：
（1）当不产生slot重用的时候， delayed block cleanout时，根据Transaction table里面的信息，更新block scn和itl上的Scn/Fsc为transaction曾经提交时候的scn。
（2） 当产生slot重用的时候，更新对应itl上scn为control scn，而block scn 为delayed block cleanout发生时刻的scn。
简单看一下这个测试过程。
（一）fast commit cleanout
1.建表/插入数据
SQL> create table test_scn(see char(500)) pctfree 90 tablespace test_a ;
Table created
SQL> insert into test_scn values(’see1′);
1 row inserted
SQL> insert into test_scn values(’see2′);
1 row inserted
SQL> commit;
Commit complete
SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;
DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622604
2.发布事务更新
SQL> update test_scn set see=’kao’ where see=’see1′;
SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622604 ――发现block scn并未改变
SQL>commit;SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;
DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622683 ――已经更新
（二） delayed block cleanout：
1.创建一个小undo表空间.
SQL> create undo tablespace undo datafile ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\undo.dbf’ size 2M;
Tablespace created
SQL> alter system set undo_tablespace=’undo’;
System altered
2 .发布更新后，清空buffer_cache，创造延时块清除条件，然后提交
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid), ora_rowscn,t.* from test_scn t;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO( ORA_ROWSCN SEE
—————————— —————————— ———- ——————–
25617 6 625000 kao1
25618 6 693298 see2
SQL> update test_scn set see=’kao2′ where see=’see2′;
1 row updated
SQL> select XIDUSN，XIDSLOT，XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
———- ————- —————- —————- —————-
20 33 21 ――得到XIDUSN，XIDSLOT的使用情况，后续发布重用脚本
SQL> alter system flush buffer_cache;
System altered
SQL> commit;
Commit complete
SQL> select timestamp_to_scn(systimestamp) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
693323 —–大概的commit scn
3．发布脚本，使XIDUSN 20 XIDLOT 33 重用。
SQL>exec proc_go_break_reuse(20，33，21);
代码如下：
create or replace procedure proc_go_break_reuse
(v_XIDUSN number,
v_XIDSLOT number,
v_XIDSQN number)
/* ————————————————–
Create_user :Mecoyoo
Time:2008-5-08
Description:It’s used to make transaction slot reused
—————————————————*/
AS
nsid number;
type transaction_record_type is record(
XIDUSN number,
XIDSLOT number,
XIDSQN number);
transaction_record transaction_record_type;
begin
select sys_context(’userenv’, ’sid’) into nsid from dual;
loop
insert into goon
select * from dba_objects where rownum<100;
select XIDUSN, XIDSLOT, XIDSQN
into transaction_record
from v$transaction a, v$session b
where a.ADDR = b.TADDR
and b.SID = nsid;
if (transaction_record.XIDUSN = v_XIDUSN and
transaction_record.XIDSLOT = v_XIDSLOT and
transaction_record.XIDSQN > v_XIDSQN) then
goto resue_end;
end if;
commit;
delete from goon;
select XIDUSN, XIDSLOT, XIDSQN
into transaction_record
from v$transaction a, v$session b
where a.ADDR = b.TADDR
and b.SID = nsid;
if (transaction_record.XIDUSN = v_XIDUSN and
transaction_record.XIDSLOT = v_XIDSLOT and
transaction_record.XIDSQN > v_XIDSQN) then
goto resue_end;
end if;
commit;
end loop;
<< resue_end >>
commit;
end;
4.产生延时块清除，记录相应scn号
SQL> select * from test_scn;
SEE
——————————————————————————–
kao1
kao2
SQL> select timestamp_to_scn(systimestamp) from dual;TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
703860 ―――发生延时块清除时候的大概scnSQL> select dbms_rowid.rowid_block_number(rowid), ora_rowscn from test_scn t where see=’kao2′;
DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25618 701061 —最后一次itl 上的 commit scn
5.dump undo header和block 25618
SQL> alter system dump undo header ‘_SYSSMU15$’;
System altered
TRN CTL:: seq: 0×0340 chd: 0×002b ctl: 0×0021 inc: 0×00000000 nfb: 0×0001
mgc: 0×8201 xts: 0×0068 flg: 0×0001 opt: 2147483646 (0×7ffffffe)
uba: 0×0200009b.0336.0a scn: 0×0000.000ab285 －这是control scn
―――――――――――――――――――――――――――――――――――――
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
————————————————————————————————
0×00 9 0×00 0×0018 0×0001 0×0000.000ab2f0 0×0200009b 0×0000.000.00000000 0×00000001 0×00000000 1210262128
―――――――――――――――――――――――――――――――――――――――――――――
0×21 9 0×00 0×0018 0xffff 0×0000.000abd20 0×02000084 0×0000.000.00000000 0×00000052 0×00000000 1210262179 －－该slot已经被重用，重用的提交scn为000abd20
―――――――――――――――――――――――――――――――――――――――――――――
0×2f 9 0×00 0×0017 0×002e 0×0000.000ab387 0×0200009b 0×0000.000.00000000
0×000000SQL> alter system dump datafile 6 block 25618;
Start dump data blocks tsn: 6 file#: 6 minblk 25618 maxblk 25618
buffer tsn: 6 rdba: 0×01806412 (6/25618)
scn: 0×0000.000abd72 seq: 0×01 flg: 0×00 tail: 0xbd720601 ―――这个scn 就是block scn，scn号为703858，可见应该是延时块清除的时候的scn
frmt: 0×02 chkval: 0×0000 type: 0×06=trans dataHex dump of block: st=0, typ_found=1
Dump of memory from 0×074C8400 to 0×074CA400
……………………………………………………………………..
Block header dump: 0×01806412
Object id on Block? Y
seg/obj: 0×2966 csc: 0×00.abd72 itc: 2 flg: E typ: 1 – DATA
brn: 0 bdba: 0×180640e ver: 0×01 opc: 0
inc: 0 exflg: 0Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0014.021.00000015 0×0200009f.02c5.0e C-U- 0 scn 0×0000.000ab285 －－这个scn就是control scn，在TRN CTL有记录，对应为701061
0×02 0×0010.016.00000014 0×02000074.0294.0d C— 0 scn 0×0000.000a9432ref: http://www.easyora.net/blog/scn_block_scn.html