This discussion is archived
4 Replies Latest reply: Jan 28, 2013 8:11 PM by sb92075 RSS

v$sql

user522961 Newbie
Currently Being Moderated
Hi,
in 11g R2,
the records in v$sql disappear after a while or if we restart the database. Are they entered in any other table to save , to be available always? Which table ?
Thank you.
  • 1. Re: v$sql
    Padma.... Newbie
    Currently Being Moderated
    Hi,
    V$sql view is a dynamic view which gives the information of only the current instance. So when database starts definitely the information is not retained.

    The information of V$sql can be obtained in AWR if configured .

    Thanks
    Padma...
  • 2. Re: v$sql
    sb92075 Guru
    Currently Being Moderated
    user522961 wrote:
    Hi,
    in 11g R2,
    the records in v$sql disappear after a while or if we restart the database. Are they entered in any other table to save , to be available always? Which table ?
    Thank you.
    every V$WHATEVER view is just a memory structure within the SGA;
    which is not retained in/on any permanent backing storage device.
  • 3. Re: v$sql
    user522961 Newbie
    Currently Being Moderated
    Thank you.
    Yes AWR is configured. How can I find sql_id of quyeries ran some days ego ?
  • 4. Re: v$sql
    sb92075 Guru
    Currently Being Moderated
    user522961 wrote:
    Thank you.
    Yes AWR is configured. How can I find sql_id of quyeries ran some days ego ?
      1* select view_name from dba_views where owner = 'SYS' AND VIEW_NAME LIKE 'DBA%HIST%'
    SQL> /
    
    VIEW_NAME
    ------------------------------
    DBA_TSM_HISTORY
    DBA_TAB_STATS_HISTORY
    DBA_TAB_HISTOGRAMS
    DBA_TAB_HISTGRM_PENDING_STATS
    DBA_SUBPART_HISTOGRAMS
    DBA_STREAMS_SPLIT_MERGE_HIST
    DBA_REGISTRY_HISTORY
    DBA_RECOVERABLE_SCRIPT_HIST
    DBA_PART_HISTOGRAMS
    DBA_LOGSTDBY_HISTORY
    DBA_HIST_WR_CONTROL
    DBA_HIST_WAITSTAT
    DBA_HIST_WAITCLASSMET_HISTORY
    DBA_HIST_UNDOSTAT
    DBA_HIST_TOPLEVELCALL_NAME
    DBA_HIST_THREAD
    DBA_HIST_TEMPSTATXS
    DBA_HIST_TEMPFILE
    DBA_HIST_TBSPC_SPACE_USAGE
    DBA_HIST_TABLESPACE_STAT
    DBA_HIST_TABLESPACE
    DBA_HIST_SYS_TIME_MODEL
    DBA_HIST_SYSTEM_EVENT
    DBA_HIST_SYSSTAT
    DBA_HIST_SYSMETRIC_SUMMARY
    DBA_HIST_SYSMETRIC_HISTORY
    DBA_HIST_STREAMS_POOL_ADVICE
    DBA_HIST_STREAMS_CAPTURE
    DBA_HIST_STREAMS_APPLY_SUM
    DBA_HIST_STAT_NAME
    DBA_HIST_SQL_WORKAREA_HSTGRM
    DBA_HIST_SQL_SUMMARY
    DBA_HIST_SQL_PLAN
    DBA_HIST_SQL_BIND_METADATA
    DBA_HIST_SQLTEXT
    DBA_HIST_SQLSTAT
    DBA_HIST_SQLCOMMAND_NAME
    DBA_HIST_SQLBIND
    DBA_HIST_SNAP_ERROR
    DBA_HIST_SNAPSHOT
    DBA_HIST_SHARED_SERVER_SUMMARY
    DBA_HIST_SHARED_POOL_ADVICE
    DBA_HIST_SGA_TARGET_ADVICE
    DBA_HIST_SGASTAT
    DBA_HIST_SGA
    DBA_HIST_SESS_TIME_STATS
    DBA_HIST_SESSMETRIC_HISTORY
    DBA_HIST_SERVICE_WAIT_CLASS
    DBA_HIST_SERVICE_STAT
    DBA_HIST_SERVICE_NAME
    DBA_HIST_SEG_STAT_OBJ
    DBA_HIST_SEG_STAT
    DBA_HIST_RULE_SET
    DBA_HIST_RSRC_PLAN
    DBA_HIST_RSRC_CONSUMER_GROUP
    DBA_HIST_ROWCACHE_SUMMARY
    DBA_HIST_RESOURCE_LIMIT
    DBA_HIST_PROCESS_MEM_SUMMARY
    DBA_HIST_PLAN_OPTION_NAME
    DBA_HIST_PLAN_OPERATION_NAME
    DBA_HIST_PGA_TARGET_ADVICE
    DBA_HIST_PGASTAT
    DBA_HIST_PERSISTENT_SUBS
    
    VIEW_NAME
    ------------------------------
    DBA_HIST_PERSISTENT_QUEUES
    DBA_HIST_PERSISTENT_QMN_CACHE
    DBA_HIST_PARAMETER_NAME
    DBA_HIST_PARAMETER
    DBA_HIST_OSSTAT_NAME
    DBA_HIST_OSSTAT
    DBA_HIST_OPTIMIZER_ENV
    DBA_HIST_MVPARAMETER
    DBA_HIST_MUTEX_SLEEP
    DBA_HIST_MTTR_TARGET_ADVICE
    DBA_HIST_METRIC_NAME
    DBA_HIST_MEM_DYNAMIC_COMP
    DBA_HIST_MEMORY_TARGET_ADVICE
    DBA_HIST_MEMORY_RESIZE_OPS
    DBA_HIST_LOG
    DBA_HIST_LIBRARYCACHE
    DBA_HIST_LATCH_PARENT
    DBA_HIST_LATCH_NAME
    DBA_HIST_LATCH_MISSES_SUMMARY
    DBA_HIST_LATCH_CHILDREN
    DBA_HIST_LATCH
    DBA_HIST_JAVA_POOL_ADVICE
    DBA_HIST_IOSTAT_FUNCTION_NAME
    DBA_HIST_IOSTAT_FUNCTION
    DBA_HIST_IOSTAT_FILETYPE_NAME
    DBA_HIST_IOSTAT_FILETYPE
    DBA_HIST_IOSTAT_DETAIL
    DBA_HIST_INTERCONNECT_PINGS
    DBA_HIST_INST_CACHE_TRANSFER
    DBA_HIST_INSTANCE_RECOVERY
    DBA_HIST_IC_DEVICE_STATS
    DBA_HIST_IC_CLIENT_STATS
    DBA_HIST_FILESTATXS
    DBA_HIST_FILEMETRIC_HISTORY
    DBA_HIST_EVENT_NAME
    DBA_HIST_EVENT_HISTOGRAM
    DBA_HIST_ENQUEUE_STAT
    DBA_HIST_DYN_REMASTER_STATS
    DBA_HIST_DLM_MISC
    DBA_HIST_DISPATCHER
    DBA_HIST_DB_CACHE_ADVICE
    DBA_HIST_DATAFILE
    DBA_HIST_DATABASE_INSTANCE
    DBA_HIST_CURRENT_BLOCK_SERVER
    DBA_HIST_CR_BLOCK_SERVER
    DBA_HIST_COMP_IOSTAT
    DBA_HIST_COLORED_SQL
    DBA_HIST_CLUSTER_INTERCON
    DBA_HIST_BUFFER_POOL_STAT
    DBA_HIST_BUFFERED_SUBSCRIBERS
    DBA_HIST_BUFFERED_QUEUES
    DBA_HIST_BG_EVENT_SUMMARY
    DBA_HIST_BASELINE_TEMPLATE
    DBA_HIST_BASELINE_METADATA
    DBA_HIST_BASELINE_DETAILS
    DBA_HIST_BASELINE
    DBA_HIST_ASH_SNAPSHOT
    DBA_HIST_ACTIVE_SESS_HISTORY
    DBA_AUTOTASK_WINDOW_HISTORY
    DBA_AUTOTASK_JOB_HISTORY
    DBA_AUTOTASK_CLIENT_HISTORY
    DBA_ALERT_HISTORY
    
    125 rows selected.
    
    SQL>