{"id":24403,"date":"2024-02-15T14:53:34","date_gmt":"2024-02-15T22:53:34","guid":{"rendered":"https:\/\/docs.extraview.com\/v25\/book\/oracle-database-configuration-1\/"},"modified":"2025-03-04T17:37:15","modified_gmt":"2025-03-05T01:37:15","slug":"oracle-database-configuration-1","status":"publish","type":"page","link":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/","title":{"rendered":"Oracle Database Configuration"},"content":{"rendered":"

Creating the Database User and Tablespaces<\/h3>\n

It is important that the database is created using the AL32UTF16\/UTF8 character set. This step assumes that Oracle is already operational. It is recommended that an Oracle DBA carry out this step. To run the required scripts and database import, you must be signed in to the target computer as the oracle user. You must also have access to the Oracle system user.<\/p>\n

The first script will create four tablespaces required by ExtraView. When you run the script, you will be prompted for location of the data files. You can spread these out if you wish or keep them in one directory (for example \/oracle\/oradata\/ev). The script supplied allocates four tablespaces. If the size of your installation suggests you need differently sized tablespaces, this script can be modified. Please consult with ExtraView if you require help for this stage.<\/p>\n

The second script will create the ExtraView user account within Oracle. Note that you must supply a password. Please ensure you keep a record of the password for future access. You will also need this in the installation process when you configure the Apache Tomcat application server.<\/p>\n

Note: The time taken for the system to create and format the tablespaces is dependent on the size you select.<\/p>\n

Oracle 11g Configuration Requirement<\/h3>\n

There is a default setting in Oracle 11g, that must be changed before you import the ExtraView database.<\/p>\n

ALTER system SET deferred_segment_creation=false; <\/code><\/p>\n

For information on this setting, please refer to DEFERRED_SEGMENT_CREATION<\/a><\/p>\n

Required Scripts<\/h3>\n

create_tablespace.sql<\/h4>\n

CREATE TABLESPACE “EXTRAVIEW”
\nLOGGING
\nDATAFILE ‘&extraviewTsLocation\/extraview01.dbf’ SIZE 1000M
\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;<\/span>
\nCREATE TABLESPACE “EXTRAVIEW_IDX”
\nLOGGING
\nDATAFILE ‘&extraviewTsLocation\/extraview_idx01.dbf’ SIZE 1000M
\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;<\/span>
\nCREATE TABLESPACE “EXTRAVIEW_SESSION”
\nLOGGING
\nDATAFILE ‘&extraviewTsLocation\/extraview_session01.dbf’ SIZE 300M
\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;<\/span>
\nCREATE TABLESPACE “EXTRAVIEW_LOB”
\nLOGGING
\nDATAFILE ‘&extraviewTsLocation\/extraview_lob01.dbf’ SIZE 1000M
\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;<\/span><\/p>\n

create_user.sql<\/h4>\n

CREATE USER extraview PROFILE “DEFAULT”
\nIDENTIFIED BY &&PASSWORD
\nDEFAULT TABLESPACE “EXTRAVIEW” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;
\nGRANT “CONNECT” TO extraview;
\nGRANT “RESOURCE” TO extraview;
\nGRANT CREATE TABLE to extraview;
\nGRANT CREATE VIEW to extraview;
\nGRANT ALTER SESSION to extraview;
\nexit<\/span><\/p>\n

For Linux Installations<\/h3>\n

cd $INSTALL
\nsqlplus system\/password @create_tablespace.sql
\nsqlplus system\/password @create_user.sql<\/span><\/p>\n

For Windows Installations<\/h3>\n

Open a command prompt Navigate to the database directory<\/p>\n

sqlplus system\/password @create_tablespace.sql
\nsqlplus system\/password @create_user.sql<\/span><\/p>\n

Import the ExtraView Database into Oracle<\/h3>\n

Your contact at ExtraView will have provided you with a database export file, either containing a system that is designed for your company\u2019s business processes, or a standard ExtraView system. Place that file in the same directory as the two files above.<\/p>\n

Without signing off from the computer, perform this next step. This will import the ExtraView schema and initial data into Oracle from the installation directory.<\/p>\n

Note: if the fromuser was not specified in an email from ExtraView, it will generally be the same as the filename of the provided dmp file. For example, a file named “evt701.dmp” would have a fromuser of “evt701”<\/p>\n

imp system\/password file=<your company>.dmp fromuser= touser=extraview commit=y<\/span><\/p>\n

Oracle Database Maintenance<\/h3>\n

The Oracle database requires minimal maintenance, and most routine maintenance can be scheduled on your server, using cron or the equivalent with a Windows-based operating system.<\/p>\n

Oracle internally manages recovery of space from deleted records within your database. For efficiency, however, Oracle indexes may need to be rebuilt if there is a very intense use of updates and deletes. With most usage of ExtraView, this is not the case and there is no need to rebuild indexes on a routine basis.<\/p>\n

If you are using nightly exports for your backup, there should be no transaction logs to worry about. If you are using hot backups, then you should have a cron job that deletes the old archive log files after they are backed up.<\/p>\n

For the best performance, one task that should be done on a regular basis is to analyze the objects in the database. These are statistics used by the Oracle query optimizer when it builds query plans. It is recommended that you do this once per week, via a cron job, and whenever large amounts of data have been loaded using evimport, or the web-based import tool, etc. Following is an example script that can be adapted and used for this purpose. Note that you have to edit the file oracle.env<\/code> and analyzeExtraView.sh<\/code> and substitute the appropriate directory paths and passwords.<\/p>\n

File analyzeExtraView.sh<\/p>\n

#!\/bin\/bsh
\n# source in the env file
\nENV=\/u01\/oracle\/admin\/prod01\/dba\/oracle.env
\nif [ -f \"$ENV\" ]; then
\n. $ENV
\nelse
\nexit 1
\nfi
\nSCRIPT=$DBA\/analyzeExtraView.sql
\nLOG=$DBA\/analyzeExtraView.txt
\ncd $DBA
\nif [ -f \"$SCRIPT\" ]; then
\nsqlplus $EXTRAVIEW_AUTH\u00a0 @$SCRIPT
\nelse
\nexit 1
\nfi
\nmail -s \"Analyze schemas for $ORACLE_SID\" $NOTIFY < $LOG
\nrm -f $LOG<\/code><\/p>\n

File analyzeExtraView.sql<\/p>\n

SET SERVEROUTPUT ON SIZE 5000
\nset echo off
\nset linesize 400
\nset term on
\nset feedback off
\nset head off
\nset pages 0
\nset verify off
\nspool analyzeExtraView.txt
\nexec DBMS_OUTPUT.PUT_LINE('Analyzing objects ' || to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss'));
\nexec dbms_utility.analyze_schema(USER, 'COMPUTE');
\nexec DBMS_OUTPUT.PUT_LINE('Done analyzing objects ' || to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss')); spool off
\nexit<\/code><\/p>\n

Entry for the crontab table<\/p>\n

#MI HH DOM MOY DOW
\n13 01 * * * \/u01\/oracle\/admin\/prod01\/dba\/analyzeExtraView.sh<\/code><\/p>\n

Entries in the oracle.env file<\/p>\n

#!\/bin\/bsh
\n# Oracle Environment
\nexport ORACLE_BASE=\/u01\/oracle
\nexport ORACLE_HOME=\/u01\/oracle\/product\/9.2
\nexport ORACLE_SID=ev
\nexport ORACLE_TERM=xterm
\nexport NLS_LANG=American_America.UTF8;
\nexport ORA_NLS33=$ORACLE_HOME\/ocommon\/nls\/admin\/data
\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/lib:\/lib:\/usr\/lib:\/usr\/local\/lib
\n# Set shell search paths
\nexport PATH=$PATH:$ORACLE_HOME\/bin:$PATH:\/bin
\n# admin directories
\nexport UDUMP=$ORACLE_BASE\/admin\/$ORACLE_SID\/udump
\nexport BDUMP=$ORACLE_BASE\/admin\/$ORACLE_SID\/bdump
\nexport ARCH=\/u02\/oracle\/arch\/$ORACLE_SID
\nexport BIN=$ORACLE_BASE\/admin\/$ORACLE_SID\/bin
\nexport DBA=$ORACLE_BASE\/admin\/$ORACLE_SID\/dba
\n# misc
\nexport SYSTEM_AUTH=system\/XXX
\nexport EXTRAVIEW_AUTH=extraview\/XXX
\nexport TODAY=$(date +%d-%b-%y)
\nexport NOTIFY=\"valid email address\"
\nexport BACKUP_DIR=\/u03\/oracle\/backup
\nexport LOG=\/tmp\/log.txt
\n<\/code><\/p>\n

Finally, make sure you have Oracle\u2019s cursors configured to an adequate number. In a typical database installation, please configure at least 1,000, but if you are likely to support hundreds of concurrent users, consider a higher number. This is an inexpensive resource and there is little downside to configuring a large number of cursors.<\/p>\n","protected":false},"excerpt":{"rendered":"

Creating the Database User and Tablespaces It is important that the database is created using the AL32UTF16\/UTF8 character set. This step assumes that Oracle is already operational. It is recommended that an Oracle DBA carry out this step. To run the required scripts and database import, you must be signed in to the target computer…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":24373,"menu_order":6,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"_lmt_disableupdate":"no","_lmt_disable":"","_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"class_list":["post-24403","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"\nOracle Database Configuration - Product Documentation<\/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:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Database Configuration - Product Documentation\" \/>\n<meta property=\"og:description\" content=\"Creating the Database User and Tablespaces It is important that the database is created using the AL32UTF16\/UTF8 character set. This step assumes that Oracle is already operational. It is recommended that an Oracle DBA carry out this step. To run the required scripts and database import, you must be signed in to the target computer...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Product Documentation\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-05T01:37:15+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/\",\"url\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/\",\"name\":\"Oracle Database Configuration - Product Documentation\",\"isPartOf\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\"},\"datePublished\":\"2024-02-15T22:53:34+00:00\",\"dateModified\":\"2025-03-05T01:37:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ExtraView 25\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Installation & Upgrade Guide\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Software Installation & Upgrades\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"Oracle Database Configuration\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"name\":\"ExtraView Product Documentation\",\"description\":\"ExtraView Documentation\",\"publisher\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\",\"name\":\"ExtraView Corporation\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"contentUrl\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"width\":512,\"height\":512,\"caption\":\"ExtraView Corporation\"},\"image\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle Database Configuration - Product Documentation","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:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Database Configuration - Product Documentation","og_description":"Creating the Database User and Tablespaces It is important that the database is created using the AL32UTF16\/UTF8 character set. This step assumes that Oracle is already operational. It is recommended that an Oracle DBA carry out this step. To run the required scripts and database import, you must be signed in to the target computer...","og_url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/","og_site_name":"Product Documentation","article_modified_time":"2025-03-05T01:37:15+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/","url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/","name":"Oracle Database Configuration - Product Documentation","isPartOf":{"@id":"https:\/\/docs.extraview.com\/v25\/#website"},"datePublished":"2024-02-15T22:53:34+00:00","dateModified":"2025-03-05T01:37:15+00:00","breadcrumb":{"@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/oracle-database-configuration-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":2,"name":"ExtraView 25","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":3,"name":"Installation & Upgrade Guide","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/"},{"@type":"ListItem","position":4,"name":"Software Installation & Upgrades","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/"},{"@type":"ListItem","position":5,"name":"Oracle Database Configuration"}]},{"@type":"WebSite","@id":"https:\/\/docs.extraview.com\/v25\/#website","url":"https:\/\/docs.extraview.com\/v25\/","name":"ExtraView Product Documentation","description":"ExtraView Documentation","publisher":{"@id":"https:\/\/docs.extraview.com\/v25\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/docs.extraview.com\/v25\/#organization","name":"ExtraView Corporation","url":"https:\/\/docs.extraview.com\/v25\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/","url":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","contentUrl":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","width":512,"height":512,"caption":"ExtraView Corporation"},"image":{"@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/"}}]}},"taxonomy_info":[],"featured_image_src_large":false,"author_info":{"display_name":"carl.koppel","author_link":"https:\/\/docs.extraview.com\/v25\/author\/carl-koppel\/"},"comment_info":0,"_links":{"self":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/comments?post=24403"}],"version-history":[{"count":0,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24403\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24373"}],"wp:attachment":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/media?parent=24403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}