{"id":161,"date":"2005-07-17T15:33:01","date_gmt":"2005-07-17T05:33:01","guid":{"rendered":"http:\/\/thekua.com\/rant\/?p=161"},"modified":"2005-07-17T15:33:12","modified_gmt":"2005-07-17T05:33:12","slug":"useful-oracle-views","status":"publish","type":"post","link":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/","title":{"rendered":"Useful Oracle Views"},"content":{"rendered":"

Since we finished with our migration to the Oracle 10g database, I have been meaning to post some useful notes about developing in this new environment. This last two weeks, I have found myself using Oracle’s V$ views a fair amount so I thought it might be good to share (or at least archive this information for myself). <\/p>\n

What are V$ views?<\/strong>
\nV$ views are defined by Oracle as dynamic performance related views because they are continually updated to reflect the database state as it is running, and most of the information is pertinent to performance tuning and useful for performance monitoring. You can find more information about V$ views quite easily, both through the Oracle Technology Network<\/a> or the great AskTom<\/a> website. <\/p>\n

Why should I bother to use V$ views?<\/strong>
\nMany GUI interfaces are widely available to help administer and monitor Oracle databases and sometimes you may never have a need to use these views. However sometimes those tools are beyond the reach of your normal developer and interfacing with Oracle at this lower level can be the simplest solution for what you need. A database configuration where people who can execute any arbitrary SQL statement should also generally grant them with the access to read from these very informative views. <\/p>\n

What are some useful examples for making the most of these views?<\/strong>
\nEasily monitor through the number of connections with:<\/p>\n

SELECT SID, SERIAL#, PADDR, USERNAME, STATUS, MACHINE
\nFROM V$SESSION<\/code>\n<\/p><\/blockquote>\n

Monitoring the total number of connections as your application is running may give you a good example of how well your application is properly managing its connections. Those connections that sit there in an INACTIVE<\/code> state are probably ideal candidates for further investigation. If you have the DBA role, you can then kill sessions (with care) with:<\/p>\n

alter system kill session 'sid,serial#';<\/code><\/p><\/blockquote>\n

Uncover the SQL statements that are currently running with:<\/p>\n

SELECT text.SQL_TEXT
\nFROM V$SESSION session, V$SQLAREA text
\nWHERE session.sql_id = text.sql_id<\/code><\/p><\/blockquote>\n

Another view exposes settings for National Language Support (NLS) parameters. We once had issues with database instance managements that resulted in us creating a set of environmental acceptance tests as a cheap way of detecting differences in database configurations. These were useful for ensuring settings such as the NLS_DATE_FORMAT<\/code> were configured correctly for standard JDBC.<\/p>\n

SELECT *
\nFROM V$NLS_PARAMETERS<\/code><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Since we finished with our migration to the Oracle 10g database, I have been meaning to post some useful notes about developing in this new environment. This last two weeks, I have found myself using Oracle’s V$ views a fair amount so I thought it might be good to share (or at least archive this […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[39],"class_list":["post-161","post","type-post","status-publish","format-standard","hentry","category-technical","tag-work-related"],"yoast_head":"\nUseful Oracle Views - thekua.com@rest<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Useful Oracle Views - thekua.com@rest\" \/>\n<meta property=\"og:description\" content=\"Since we finished with our migration to the Oracle 10g database, I have been meaning to post some useful notes about developing in this new environment. This last two weeks, I have found myself using Oracle’s V$ views a fair amount so I thought it might be good to share (or at least archive this […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/\" \/>\n<meta property=\"og:site_name\" content=\"thekua.com@rest\" \/>\n<meta property=\"article:published_time\" content=\"2005-07-17T05:33:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2005-07-17T05:33:12+00:00\" \/>\n<meta name=\"author\" content=\"Patrick\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Patrick\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/\"},\"author\":{\"name\":\"Patrick\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"headline\":\"Useful Oracle Views\",\"datePublished\":\"2005-07-17T05:33:01+00:00\",\"dateModified\":\"2005-07-17T05:33:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/\"},\"wordCount\":355,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"keywords\":[\"Work-Related\"],\"articleSection\":[\"Work Related\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/\",\"name\":\"Useful Oracle Views - thekua.com@rest\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#website\"},\"datePublished\":\"2005-07-17T05:33:01+00:00\",\"dateModified\":\"2005-07-17T05:33:12+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2005\\\/07\\\/useful-oracle-views\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/thekua.com\\\/rant\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Useful Oracle Views\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#website\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/\",\"name\":\"thekua.com@rest\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/thekua.com\\\/rant\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\",\"name\":\"Patrick\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"contentUrl\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"width\":400,\"height\":400,\"caption\":\"Patrick\"},\"logo\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\"},\"sameAs\":[\"http:\\\/\\\/www.thekua.com\"],\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/author\\\/patrick\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Useful Oracle Views - thekua.com@rest","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/","og_locale":"en_US","og_type":"article","og_title":"Useful Oracle Views - thekua.com@rest","og_description":"Since we finished with our migration to the Oracle 10g database, I have been meaning to post some useful notes about developing in this new environment. This last two weeks, I have found myself using Oracle’s V$ views a fair amount so I thought it might be good to share (or at least archive this […]","og_url":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/","og_site_name":"thekua.com@rest","article_published_time":"2005-07-17T05:33:01+00:00","article_modified_time":"2005-07-17T05:33:12+00:00","author":"Patrick","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Patrick","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/#article","isPartOf":{"@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/"},"author":{"name":"Patrick","@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"headline":"Useful Oracle Views","datePublished":"2005-07-17T05:33:01+00:00","dateModified":"2005-07-17T05:33:12+00:00","mainEntityOfPage":{"@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/"},"wordCount":355,"commentCount":0,"publisher":{"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"keywords":["Work-Related"],"articleSection":["Work Related"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/","url":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/","name":"Useful Oracle Views - thekua.com@rest","isPartOf":{"@id":"https:\/\/thekua.com\/rant\/#website"},"datePublished":"2005-07-17T05:33:01+00:00","dateModified":"2005-07-17T05:33:12+00:00","breadcrumb":{"@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/thekua.com\/rant\/2005\/07\/useful-oracle-views\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/thekua.com\/rant\/"},{"@type":"ListItem","position":2,"name":"Useful Oracle Views"}]},{"@type":"WebSite","@id":"https:\/\/thekua.com\/rant\/#website","url":"https:\/\/thekua.com\/rant\/","name":"thekua.com@rest","description":"","publisher":{"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/thekua.com\/rant\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae","name":"Patrick","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","url":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","contentUrl":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","width":400,"height":400,"caption":"Patrick"},"logo":{"@id":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png"},"sameAs":["http:\/\/www.thekua.com"],"url":"https:\/\/thekua.com\/rant\/author\/patrick\/"}]}},"_links":{"self":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts\/161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/comments?post=161"}],"version-history":[{"count":0,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts\/161\/revisions"}],"wp:attachment":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/media?parent=161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/categories?post=161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/tags?post=161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}