Oracle Notes
How do I use a literal ampersand within a SQL statement for INSERT, SELECT, etc.?
This is a sqlplus issue, I believe. See this link for a workaround: http://www.jlcomp.demon.co.uk/faq/litampersand.html
If you're on a *nix box - yasql is a great replacement for sql*plus.
Is there an equivalent of PostgreSQL's vacuum analyze
No, not really, but you can do something similar to speed up a table:
analyze table ticket_xrefs compute statistics;
This will look at the usage statistics, and update them. This can dramatically increase performance when the amount of data in a table has changed a lot.
Moving one Oracle instance to another server
Apparently, Oracle installations are fairly self-contained. In theory, at least, you should be able to move an installation from one server to another by shutting down the server, tarring up the /ora8 directory, sftp-ing it to another server, untarring it, and possibly running the setup_stubs.sh script.
Needless to say, this is much easier than reinstalling, exporting the database, and importing it back in. No guarantees on how well it works, however.
Turning on autotrace
Hierarchical queries and getting around join problem with CONNECT BY
https://openacs.org/forums/message-view?message_id=125969
Getting [too long] messages?
If you get an error message starting with SQL: [too long], then you need to read this. Sometimes, for long error messages, your error messages are truncated, which makes tracking down the errors more difficult.
Efficient updates
You can do this
UPDATE (SELECT col1, value FROM t1, t2 WHERE t1.key = t2.key AND t2.col2 = :other_value) SET col1 = value
Using lots of dynamic SQL (more than 32768 chars?)
You cannot use a clob, so use the dbms_sql package:
declare l_stmt dbms_sql.varchar2s; l_cursor integer default dbms_sql.open_cursor; l_rows number default 0; begin l_stmt(1) := 'insert'; l_stmt(2) := 'into foo'; l_stmt(3) := 'values'; l_stmt(4) := '( 1 )'; dbms_sql.parse( c => l_cursor, statement => l_stmt, lb => l_stmt.first, ub => l_stmt.last, lfflg => TRUE, language_flag => dbms_sql.native ); l_rows := dbms_sql.execute(l_cursor); dbms_sql.close_cursor( l_cursor ); end; /
This is from Tom Kyte, Oracle God.
Online table updates
PL/SQL exception handling
- Exception handling in PL/SQL
SPfile and Pfile startups
$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 28 19:04:30 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. 19:04:34 > connect / as sysdba Connected. 19:05:21 sys@vs> startup pfile=/u01/app/oracle/admin/vs/pfile/init.ora.192006104950 ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. 19:05:37 sys@vs> 19:05:37 sys@vs> 19:05:38 sys@vs> create spfile from pfile='/u01/app/oracle/admin/vs/pfile/init.ora.192006104950'; File created.
Troubleshooting Oracle Dates
Oracle has an internal representation for storing the data based on the number of seconds elapsed since some date. However, for the purposes of inputing dates into Oracle and getting them back out, Oracle needs to be told to use a specific date format. By default, it uses an Oracle-specific format which isn't copacetic. You want Oracle to use the ANSI-compliant date format which is of form 'YYYY-MM-DD'.
To fix this, you should include the following line in $ORACLE_HOME/dbs/initSID.ora or for the default case, $ORACLE_HOME/dbs/initora8.ora
nls_date_format = "YYYY-MM-DD"
You test whether this solved the problem by firing up sqlplus and typing:
SQL> select sysdate from dual;
You should see back a date like 2000-06-02. If some of the date is chopped off, i.e. like 2000-06-0, everything is still fine. The problem here is that sqlplus is simply truncating the output. You can fix this by typing:
SQL> column sysdate format a15 SQL> select sysdate from dual;
If the date does not conform to this format, double-check that you included the necessary line in the init scripts. If it still isn't working, make sure that you have restarted the database since adding the line:
[joeuser ~]$ svrmgrl SVRMGR> connect internal Connected. SVRMGR> shutdown Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> startup ORACLE instance started.
If you're sure that you have restarted the database since adding the line, check your initialization scripts. Make sure that the following line is not included:
export nls_lang = american
Setting this environment variable will override the date setting. Either delete this line and login again or add the following entry to your login scripts after the nls_lang line:
export nls_date_format = 'YYYY-MM-DD'
Log back in again. If adding the nls_date_format line doesn't help, you can ask for advice in our OpenACS forums.
Useful Procedures
-
Dropping a tablespace
-
Run sqlplus as the dba:
[oracle ~]$ sqlplus system/changeme
-
To drop a user and all of the tables and data owned by that user:
SQL> drop user oracle_user_name cascade;
-
To drop the tablespace: This will delete everything in the tablespace overriding any referential integrity constraints. Run this command only if you want to clean out your database entirely.
SQL> drop tablespace table_space_name including contents cascade constraints;
-
Creating an appropriate tuning and monitoring environment
The first task is to create an appropriate environment for finding out what is going on inside Oracle. Oracle provides Statspack, a package to monitor and save the state of the v$ performance views. These reports help finding severe problems by exposing summary data about the Oracle wait interface, executed queries. You'll find the installation instructions in $ORACLE_HOME/rdbms/admin/spdoc.txt. Follow the instructions carefully and take periodic snapshots, this way you'll be able to look at historical performance data.
Also turn on the timed_statistics in your init.ora file, so that Statspack reports (and all other Oracle reports) are timed, which makes them a lot more meaningful. The overhead of timing data is about 1% per Oracle Support information.
To be able to get a overview of how Oracle executes a particular query, install "autotrace". I usually follow the instructions for autotrace.
The Oracle Cost Based optimizer is a piece of software that tries to find the "optimal" execution plan for a given SQL statement. For that it estimates the costs of running a SQL query in a particular way (by default up to 80.000 permutations are being tested in a Oracle 8i). To get an adequate cost estimate, the CBO needs to have adequate statistics. For that Oracle supplies the dbms_stats package.
Useful links
in last 30 minutes
OpenACS.org
- HOME
- News
- OpenACS Projects
- Activity Graph
- Admin Package RFC
- Bugtracker Cleanup Project
- Collaboration Graph
- Community Metrics in OpenACS
- Debian/Ubuntu installer developing
- Documentation Project
- Documentation Project Discussion
- Documentation Project Plan (Approach 4)
- {done} Change Log from OpenACS 5.4.2 to OpenACS 5.4.3
- {done} OpenACS 5.3.x releases
- {done} Release Notes OpenACS 5.4.3
- Dynamic Object Types and Attributes
- Ecommerce G2
- E-Mail: Event Handling
- E-Mail: Incoming E-Mail
- E-Mail: Outgoing E-Mail
- Forums Project
- GETable resources, that should be POSTable resources
- Interface / CSS Coding Guidelines
- Logo
- .LRN
- Mentorship Program
- Migration from CVS to GIT
- Official Test Servers
- OpenACS 5.10.0 Change Summary
- OpenACS 5.10.1 Change Summary
- OpenACS 5.9 HTML validity fixes
- OpenACS/.LRN for Debian
- OpenACS Packaging for Debian and Ubuntu
- OpenACS Release Status
- OpenACS TODO List
- Package Object Types
- Package Testing Process
- Prerequisites and Procedures for Migrating to Subversion from CVS
- Site Nodes Proposal (Draft)
- Site Wide File Upload
- Site Wide Image Upload Widget
- (Sketch for) OpenACS Home
- Theme Manager
- Theming Project
- Translation server for OpenACS packages
- User interface mockups
- Website_Redesign
- XoWiki Design Ideas
- YUI Project
- Marketing
- Our Website
- Packages
- Available OpenACS Packages
- Core Packages
- ACS Admin
- ACS API Browser
- ACS Authentication
- ACS Automated Testing
- ACS Bootstrap Installer
- ACS Content Repository
- ACS Core Docs
- ACS Default Theme
- ACS Developer Support
- ACS Kernel
- ACS Lang
- ACS Mail Lite
- ACS Messaging
- ACS Reference Data
- ACS Service Contract
- ACS Subsite
- ACS Tcl
- ACS Templating
- ACS Translations
- Intermedia Driver
- Notifications
- Reference Data - Country
- Reference Data - Language
- Reference Data - Timezone
- Search
- Tsearch2 Driver
- Non-Core Packages
- ACS Date Time
- ACS Events
- ACS Interface
- ACS Object Management
- Address Book
- Ad Server
- Ajax Filestore UI
- Ajax Helper
- Ajax Photoalbum UI
- Anonymous Evaluation
- Assessment
- Attachments
- Attendance
- Attribute Management System
- Auth CAS
- Authentication Server
- Auth HTTP
- Authorize.net Gateway
- Beehive
- Bookmarks
- Bookshelf
- Boomerang Plugin
- B Responsive Theme
- Bug tracker
- Bulk mail
- Calendar
- Calendar Includelet
- Cards
- Categories
- Chat
- Chat Includelet
- Clickthrough
- Clipboard
- CMS
- CMS News Demo
- Connections
- Contacts
- Contacts Lite
- Content Includelet
- Cookie Consent Widget
- Cronjob
- Curriculum
- Curriculum Central
- Curriculum Tracker
- Datamanager
- dbm
- Diagram
- Directory
- Download
- Dynamic Object Type
- E-Commerce
- Ecommerce Serial Number Tracking
- Edit This Page
- EduWiki
- Email Handler
- Evaluation
- Expense
- Expense Tracking
- EZIC Gateway
- Facebook API
- FAQ
- Feed parser
- File Manager
- File storage
- File Storage Includelet
- Forums
- Forums Includelet
- GateKeeper
- General comments
- Glossar
- Glossary
- Image Magick
- IMS Enterprise
- Imsld
- Invoices
- Jabber
- Lab Report
- Lab Report Central
- LAMS Integration
- LAMS Integration Configuration
- Latest
- Layout Managed Subsite
- Layout Manager
- LDAP Authentication Driver
- Learning Content
- Logger
- LORS management Includelet
- Mail Tracking
- MMplayer
- Monitoring
- New portal
- News
- News aggregator
- News Includelet
- Notes
- OCT Election
- openacs-bootstrap3-theme
- Organization
- Outdated Library functions
- Package Builder
- Page
- Pages
- PAM Authentication Driver
- Payment Gateway
- Permissions Administrator
- Photo album
- Planner
- Poll
- Postal Address
- Post Card
- Press
- Profile provider
- Project Manager
- Q-Forms
- Quota
- Q-Wiki
- Ratings
- Recruiting
- Redirect
- Reference Data - Currency
- Reference Data - ITU Code
- Reference Data - UNSPSC code
- Reference Data - US County
- Reference Data - US State
- Reference Data - US Zipcode
- Related Items
- Richtext CKEditor 4
- Richtext CKEditor 5
- Richtext TinyMCE
- Richtext Xinha
- Robot Detection
- RSS support
- S5
- Sample Gateway
- Schema Browser
- Scholarship Fund
- Scorm Core
- Scorm Importer
- Scorm Player
- Scorm Simple LMS
- Selva theme
- Shipping Service Contract
- Simple Survey
- Simulation
- Site-wide Search
- Skin
- SOAP db
- SOAP Gateway
- Spreadsheet
- Static Pages
- Survey
- Survey Library
- Survey Reports
- T Account
- Tasks
- Tcl SOAP
- Telecom Information
- Trackback
- User preferences
- User profile
- Value-based Shipping
- Version Control
- Views
- WebDAV Support
- Weblogger
- Webmail System
- Wikipedia
- Wimpypoint slim
- Workflow
- XCMS User Interface
- XML RPC
- XO Learning Performance
- xolirc
- xooauth
- XOTcl Core
- XOTcl Request Monitor
- xowf plugin for Monaco code editor
- xowf (XoWiki Workflow)
- XoWiki
- XoWiki Includelet
- ecommerce-g2
- Accounts Desk
- Accounts Finance
- Accounts Payables
- Accounts Payroll
- Accounts Receivables
- Bulk Upload
- CL Custom Commerce
- Customer Service
- E-commerce 2
- Fabrication
- Field Service
- General Ledger
- Human Resources
- Inventory Control
- Manufacturing Design
- Online Catalog
- Production
- Reference Data - GIFI
- Ship-Track
- Vendors-Suppliers
- DotLrn
- Anon Eval Applet
- Anon Eval Portlet
- Application track
- Application track Applet
- Application track portlet
- Assessment Applet
- Assessment portlet
- Attendance Applet
- Beehive Applet
- Beehive Portlet
- Bulk mail Applet
- Bulk mail Portlet
- Calendar Applet
- Calendar portlet
- Cards applet
- Cards portlet
- Chat Applet
- Chat Portlet
- Contacts Applet
- Contacts Portlet
- Content Applet
- Content Portlet
- Courses
- Curriculum Applet
- Curriculum Portlet
- Datamanager Portlet
- dotLRN
- dotLRN Administration
- dotLRN applet
- dotLRN Bootstrap 3 Theme
- dotLRN Course Catalog
- dotLRN Datamanager Applet
- dotLRN - Ecommerce
- dotLRN portlet
- dotLRN Roadmap
- Edit-this-page Applet
- Edit-this-page Portlet
- EduWiki Applet
- EduWiki Portlet
- Evaluation applet
- Evaluation portlet
- Expense-tracking Applet
- FAQ Applet
- FAQ Portlet
- File Storage Applet
- File Storage Portlet
- Forums Applet
- Forums Portlet
- Glossar Applet
- Glossar Portlet
- Homework Applet
- IMS-LD Applet
- IMS LD Portlet
- Invoices Applet
- Invoices Portlet
- Jabber Applet
- Jabber Portlet
- LAMS Integration Applet
- LAMS Integration Portlet
- Latest Applet
- Latest Portlet
- Learning Content Applet
- Learning Content Portlet
- LORS central
- LORS - Learning Objects Repository Service
- LORS management
- LORS management Applet
- LORS management Portlet
- Messages Applet
- Messages Portlet
- MMplayer Applet
- MMplayer Portlet
- News Aggregator Applet
- News Aggregator Portlet
- News Applet
- News Portlet
- Photo Album Applet
- Photo Album Portlet
- Private-Message
- Project-manager Applet
- Project Manager Portlet
- Quota Applet
- Quota Portlet
- Random-photo Applet
- Random-photo Portlet
- Recruiting Applet
- Recruiting Portlet
- Research Applet
- Research Portlet
- Static Applet
- Static Portlet
- Survey Applet
- Survey Portlet
- Syllabus Applet
- Tasks Applet
- Tasks Portlet
- Theme Zen
- User Tracking
- User Tracking Applet
- User Tracking Portlet
- Weblogger Applet
- Weblogger Portlet
- Wikipedia Applet
- Wikipedia Portlet
- Wimpypoint Slim Applet
- Wimpypoint Slim Portlet
- XoWiki Applet
- XoWiki Portlet
- Contrib Packages
- Acknowledgement
- BCDS
- BCMS
- BCMS UI Base
- BCMS UI Wizard
- Classified Ads
- COP Base
- COP UI
- Events Management
- Form To Mail
- Irc Applet
- IRC Logger
- Mail Clickthrough
- mailing-lists
- PayFlowPro Gateway
- Personal Community
- Photobook
- Populate
- Research Papers
- Resource List
- Room Reservation
- Users Selection
- Vocabulary
- Deprecated Packages
- {deprecated} ACS Content
- {deprecated} ACS LDAP Authentication
- {deprecated} ACS Mail
- {deprecated} ACS Utility Services
- {deprecated} Bboard Portlet
- {deprecated} dotFOLIO
- {deprecated} dotFOLIO UI
- {deprecated} dotLRN BBoard Applet
- {deprecated} OpenFTS Driver
- {deprecated} Portal
- {deprecated} Sloan Bboard
- {deprecated} Spam System
- {deprecated} Webmail
- {deprecated} Wiki
- {deprecated} Workflow Service
- Community
- Getting admin-level help
- Getting help
- Goals / Ideas
- History of OpenACS
- Marketing Team
- Most Popular Pages
- OpenACS Translation server
- Events
- 2006 Fall Conference Interest in Attending
- 2006 Fall Conference Presentations
- 2006 Fall Conference Submissions and Program
- 2006 International Workshop on Community Based E-Learning Systems
- 2006 November 2nd (General Web Applications Focus - OpenACS)
- 2006 November 3rd and November 4th (Training and Hacking Days)
- 2006 OpenACS/.LRN Fall Conference
- 2006 Session 1: Towards full Accessibility in LMS
- 2007 Project Ideas for Google Summer of Code
- OpenACS conferences
- OCT
- F. A. Q.
- .LRN
- 2006 Fall Conference Submissions and Program
- Content development tools options
- Documentation and help pages for individual .LRN installations
- Educational Wiki (Eduwiki) Tool
- How to contribute code that passes accessibility tests
- Learning Content Tool
- .LRN
- .LRN Accessibility
- .LRN Core Team (DRAFT)
- .LRN Educational standards support
- .LRN Get Involved!
- .LRN Governance
- .LRN Installation
- .LRN Installation (up to .LRN 2.5.0)
- .LRN Leadership Team 2008
- .LRN Meetings
- .LRN Motions (DRAFT)
- Modelling Learners Preferences
- Plataforma Elearning
- SCORM support
- Simple Content Creator / Editor
- Time/Topics Planner for dotLRN Courses
- Consortium
- Releases
- .LRN 2.2 bugs
- .LRN 2.2 Release Management
- .LRN 2.2 to .LRN 2.3.0 Change Log
- .LRN 2.3.0 to .LRN 2.3.1 Change Log
- .LRN 2.3.1 Release Notes
- .LRN 2.3.1 to .LRN 2.4 Change Log
- .LRN 2.3 Release Management
- .LRN 2.4.0 to .LRN 2.4.1 Change Log
- .LRN 2.4.1 Release Notes
- .LRN 2.4 Release Management
- .LRN 2.4 Release Notes
- .LRN 2.5 Release Management
- Zen Project
- Coding Standards
- ADP Files
- Ajax and Accessibility
- Code Formatting
- Coding Standards - Index
- Commit Messages
- Emacs as an OpenACS IDE
- How to contribute code that passes accessibility tests
- Logging Conventions
- .LRN Zen Project: Standards
- Naming Conventions
- Security Guidelines
- SQL - XQL
- Tcl pages
- Tcl Procs
- template::head::*
- Vi as an OpenACS IDE
- WCAG 1.0 Checkpoints
- Web Forms
- Web Lists / Tables
- External Resources
- Cookbook
- Accessing LTI services from OpenACS
- Add extra headers
- Cookbook
- Creating adp box tags for consistent html/css
- Double Click Handling
- E-Mail: Event Handling
- E-Mail: Incoming E-Mail
- E-Mail: Outgoing E-Mail
- F. A. Q.
- Fresh install of OpenACS 5.10 on Oracle 19c
- Handling out of memory on "exec" calls
- Host Node Map
- How to configure a Network Place under Windows XP to access file-storage via WebDAV
- How to handle "connection already closed" errors
- How to manage/upgrade CKEditor versions
- How to tune cache sizes
- Interfacing with MS Teams and related services (Microsoft Graph)
- Managing Versions of External JavaScript Libraries
- Modifying the look of an installation
- NaviServer and OpenACS with Docker
- New Interface for Calling Database functions
- OpenACS Performance Tuning
- Passing values up from an include
- Permissions explored, a practical way exists
- Refactoring Recipes
- Running OpenACS behind a proxy
- Security: Content Security Policy (CSP)
- Security: Cross Site Request Forgery (CSRF)
- Server-sent events
- Setup with docker-s6
- SQL: How to log (slow) queries in the system log
- Streaming HTML
- Using OpenACS with External Identity Providers
- Using Pound as a reverse Proxy
- When to use URLencode
- XoWiki: How to save files directly in the wiki
- XoWiki: List of the available includelets
- Resources
- Accessibility
- Testing Pages
- category test
- Commit graph
- Commits
- Comparison of the CTRL Surveys Package vs. the OACS Assessment Package
- fullpage
- just playing around
- name with space
- OpenACS Object Types
- Pastebin for irc
- preview.png
- QUOTA
- Recently Changed Pages
- toc-test-page
- Wikipedia
- XoWiki Slides from the Vienna OpenACS conference
- Directory - Who's Who
- Documentation
- Collaboration Graph
- Documentation Credits
- Documentation History
- Documentation Introduction
- Documentation Process test-doc (Approach 3)
- Documentation Project Discussion
- Documentation Project Plan (Approach 4)
- for administrators
- for administrators - Table of Contents
- for beginning developers
- for developers
- for developers - Table of Contents
- for everyone
- for everyone - Table of Contents
- Most Popular Pages
- OpenACS Handbook
- Try Openacs
- WikiDoc Project Notice
- Package Documentation
- Aliases at CVS
- Available OpenACS Packages
- Packages available in the oacs-5-10 channel
- Packages available in the oacs-5-9 channel
- Documentation Non-Core Packages
- Marketing Documentation
- Tutorials
- Developer Tutorial - Req.
- for beginning developers
- Introduction to OpenACS
- Next Steps After Installation, Debian Specific
- OpenACS/dotLRN windows installer how to
- Theming in OpenACS
- Tutorials for Designers
- Tutorials for Users
- Tutorials for Administrators
- Tutorials for Programmers
- Add extra headers
- Automated Installs - Using install.xml
- Body Onload javascript event
- Emacs as an OpenACS IDE
- listbuilder tutorial
- OpenACS 5.1.4 / PG 7.3.6 => 5.2.3/8.0.7 Upgrade Path for acs-core
- OpenACS mode for Emacs
- Vi as an OpenACS IDE
- Webinar - Part 1 - Basics
- Webinar - Part 2 - Site Map Administration
- Webinar - Part 3 - Packages and ad_form
- Webinar - Part 4 - Survey
- Tutorials - The N00B Journey
- Core Documentation
- Subsystems Documentation
- Administrators - Req.
- AOLserver
- AOLserver administration
- Available OpenACS Packages
- Boost your application performance to serve large files!
- Conditional CREATE Index for Postgresql and Oracle
- Deployment feedback channel
- Developer - Requirements
- Developer Tutorial - Req.
- Emacs as an OpenACS IDE
- End-users - Requirements
- for administrators
- for administrators - Table of Contents
- for beginning developers
- for developers
- for developers - Table of Contents
- for everyone
- for everyone - Table of Contents
- Get the OpenACS Source Code
- Getting admin-level help
- hstore
- Install a *nix-based operating system (OS)
- Install AOLserver
- Install AOLserver 4.5
- Installation - Req.
- Installing OpenACS
- Installing OpenACS on Arch Linux
- Installing OpenACS on debian
- Installing OpenACS on FreeBSD
- Installing OpenACS on FreeBSD (quick)
- Installing OpenACS on FreeBSD with ports
- Installing OpenACS on Mac OS X
- Installing OpenACS on Redhat
- Installing OpenACS on RPM-based systems
- Installing OpenACS on Solaris
- Installing OpenACS on SuSE
- Installing OpenACS on win2k
- Installing OpenACS on Windows
- Install OpenACS distribution
- Install OpenACS on debian unstable / Ubuntu 7.10
- Install OpenACS on Linux
- Install OpenACS on Mac OS X 10.5 / 10.6 (Snow Leopard) Using Macports
- Install OpenACS - prereqs
- Install OpenACS with NaviServer from Scratch
- Install Oracle
- Install Postgresql
- Install Tcl
- Mail Transport Agents
- Monitoring Naviserver
- Nagios Monitoring
- NaviServer
- Next Steps After Installation, Debian Specific
- *nix operating system
- OpenACS compatibility matrix
- OpenACS Debian Install quicksheet
- OpenACS/dotLRN windows installer how to
- OpenACS/dotLRN Windows Installer Instructions
- OpenACS/.LRN for Debian
- OpenACS/.LRN for Ubuntu
- OpenACS Monitoring
- OpenACS Performance Tuning
- OpenACS Reference Platform
- OpenACS subsystem
- OpenACS system
- Oracle
- Oracle Notes
- Postgres 8.1.x to 9.1
- PostgreSQL
- PostgreSQL Administration
- PostgreSQL's Tsearch2
- Securing your system
- Source control
- Tcl
- Tcl Thread Library
- tDOM
- tDOM with OpenACS
- Testing with Selenium
- Testing with TCLWebtest
- Upgrade between PostgreSQL versions
- Upgrade from PostgreSQL 9.6 to 13.1
- Upgrade to OpenACS 5.10
- Upgrade to OpenACS 5.8
- Upgrade to OpenACS 5.9
- Vi as an OpenACS IDE
- ::xo::db Object Relational Database Interface
- XOTcl