4 Replies Latest reply: Jan 28, 2013 10:11 PM by sb92075 RSS

    v$sql

    user522961
      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....
          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
            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
              Thank you.
              Yes AWR is configured. How can I find sql_id of quyeries ran some days ego ?
              • 4. Re: v$sql
                sb92075
                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>